[MS_AccessPros] Re: Help with reports.

 

Ok John, I placed a copy of the db on the need asistance folder. the file is called Fire Service Pro.

Maybe this way you can actually test the db. Feel free to make changes.

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Edgar-
>
> You said the field from qryServices was named NxtServ, so that's why I'm testing
> for that field. Did you really name it ProxServ?
>
> I guessed at the name of the linking Status field in tblCustomers and the actual
> underlying value that means "Active". If the field name or the value is
> different, you'll have to change that.
>
> I'm prompting for a "from" - "to" date because I assume "NxtServ" or "ProxServ"
> is a Date/Time value. The Format of the field has no impact. Even if you
> format the field as "mmm," the underlying value that the query tests is still a
> full date/time value. If you want the user to be able to enter a month
> abbreviation, then you would need to do:
>
> WHERE Format(qryServices.ProxServ, "mmm") = [Enter month (xxx) abbreviation for
> the next service:]
>
> But that will run much more slowly than entering a specific "from" and "to"
> date.
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> rivera_vazquez_edgar
> Sent: Wednesday, July 06, 2011 9:01 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Help with reports.
>
> Ok John, I made the changes according to the actual field names. Here is the
> update:
>
> PARAMETERS [Enter From Date:] DateTime, [Enter To Date:] DateTime;
> SELECT tblCustomers.*, tblAddresses.*, tblContacts.*, tblInventory.*
> FROM (((tblCustomers INNER JOIN tblAddresses ON
> tblCustomers.CustomerID=tblAddresses.IdCustomer) INNER JOIN tblContacts ON
> tblAddresses.IdAddress=tblContacts.IdAddress) INNER JOIN tblInventory ON
> tblAddresses.IdAddress=tblInventory.IdAddress) INNER JOIN qryServices ON
> tblInventory.IdInventory=qryServices.IdInventory
> WHERE tblCustomers.IdStatus=1 And qryServices.ProxServ Between [Enter From
> Date:] And [Enter To Date:];
>
> It's on the right track as to what I need, but can you make these changes?
>
> In the PARAMETERS it should only ask for the date in the field ProxServ from the
> qryService, wich is in the following format (mmm)
>
> Also, instead of asking for the customer id, it should ask for the city from
> tblAddress, so i'll get every customer in a given city for a specific month.
>
> If that's how it should work the way you wrote the code, than I might be doing
> something wrong.
>
> Edgar
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Edgar-
> >
> > Your query might look like:
> >
> > PARAMETERS [Enter From Date:] DateTime, [Enter To Date:] DateTime;
> > SELECT tblCustomers.*, tblAddresses.*, tblContacts.*, tblInventory.*
> > FROM (((tblCustomers INNER JOIN tblAddresses
> > ON tblCustomers.CustomerID = tblAddresses.CustomerID)
> > INNER JOIN tblContacts
> > ON tblAddresses.AddressID = tblContacts.AddressID)
> > INNER JOIN tblInventory
> > ON tblAddresses.AddressID = tblInventory.AddressID)
> > INNER JOIN qryServices
> > ON tblInventory.InventoryID = qryServices.InventoryID
> > WHERE tblCustomers.StatusID = 1
> > AND qryServices.NxtServ BETWEEN [Enter From Date:] And [Enter To Date:];
> >
> > I just guessed at the names of the linking fields.
> >
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > rivera_vazquez_edgar
> > Sent: Wednesday, July 06, 2011 2:20 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] Help with reports.
> >
> > Ah ok...
> >
> > tblCustomers many to one tblStatus
> > tblCustomers one to many tblAddresses
> > tblAddresses one to many tblContacts
> > tblAddresses one to many tblInventory
> > tblInventory one to many tblServices
> >
> > Now, I made a query (qryServices) to calculate the next service date, so the
> > query has the field "NxtServ" wich is not in tblServices. I use qryService in
> my
> > form to input the data and see the next service date. I guess I need to do
> the
> > same in the report to filter for this date.
> >
> > The report needs to look something like this:
> >
> > 1. Customer's main info from tblCustomer but only for those with "Active"
> Status
> > and a specific month in qryServices
> > 2. Customer address from tblAddresses
> > 3. Contact person for that specific address
> > 4. Inventory for that specific address
> > 5. Last service done for each inventory item from qryServices
> > 6. If the customer has another address with service due the same month, then
> > display #2-5 again for each address.
> >
> > Would I need to make a query for every month?
> > I'm preaty sure I need to make a query for every Status if I want a report of
> > the inactive and out-of-business customers also.
> >
> > Let me know if you need more info.
> > Edgar
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> > >
> > > Edgar-
> > >
> > > You say tblAddresses is related to tblCustomer, but you don't say how. I
> > assume
> > > it's many addresses for one customer. How is inventory and service related
> to
> > > these two tables?
> > >
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > rivera_vazquez_edgar
> > > Sent: Wednesday, July 06, 2011 4:09 AM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Help with reports.
> > >
> > > I'm still having problems making my report.
> > >
> > > Here is my structure:
> > >
> > > tblCustomer related to tblAddresses. Each address has a contact person and
> an
> > > inventory (tblInventory) and each inventory has a service (qryService).
> > >
> > > I need all this info on the report but I need to filter the info by two
> fields
> > > in diferent tables/query. There is a table for Status related to
> tblCustomer
> > > and a field for next service date in qryService.
> > >
> > > I need, for example, every active customer with their address and contact
> > person
> > > and the inventory for that address, but only show customers with a next
> > service
> > > date of July wich is in a table called tblService related to the inventory
> > > table.
> > >
> > > I'm realy stuck, HELP!
> > >
> > > Edgar
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (7)

Recent Activity:

• New Members 6
• New Links 1
• New Files 4

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___