ACCESS FEED: [MS_AccessPros] Re: Help with reports.

John, that IdCustomer in tblContacts is not suposed to be there, I erased it. tblContacts is suposed to be linked to tblAddresses not tblCustomers because every contact belongs to an address. I fixed this and now I get data out of the query.

I'm seeing something that makes me think of changing the way the db records the service dates. The way it's designed so far, if I want to see the customers I need to visit for service in july 2011 than I use the 1/jul/2011 to 31/jul/2011 dates. That will give me the right info, but, what if last year (2010) a db user forgot to change the status of an out-of business customer from active to out-of-business? That customer won't show up as a result in the query because the next service date was for july 2010 and it was not performed, BUT I would not see that the customer status was not changed, for as, if I recive that customer's data when I run the query for july 2011 and see that no service was done last year, I would know that the status was not changed.

I don't know if you understand the example but, the thing is, the db we are using right now, althoug very poor in design (reason to make a new one), manages the service date by month, reguardless of the year, ofcourse its not a DateTime field.

What comes to mind is (spit-balling):

Make a tblMonth with all 12 months to replace the ServDate field in tblServices using a combo box to edit the data...
Leave the interval field in tblServices but limit the input value to 12 or 6 (months) and for the ProxServ field (next service date), make a series of "if" conditions

where (if(tblService.ServDate = "January" and tblService.Interval = "12","January","June")) OR (if(tblService.ServDate = "February" and tblService.Interval = "12","Febuary","July"))

and so on for every month. The thing is, would that expression (being so long) work? or would I need to make a query for every month?

Edgar

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Edgar-
>
> You had an error in qryServices. It should be:
>
> PARAMETERS [Enter From Date:] DateTime, [Enter To Date:] DateTime;
> SELECT tblCustomers.*, tblAddresses.*, tblContacts.*, tblInventory.*
> FROM tblCustomers INNER JOIN ((tblAddresses INNER JOIN tblContacts ON
> tblAddresses.IdAddress = tblContacts.IdAddress) INNER JOIN (tblInventory INNER
> JOIN qryServices ON tblInventory.IdInventory = qryServices.IdInventory) ON
> tblAddresses.IdAddress = tblInventory.IdAddress) ON tblCustomers.IdCustomer =
> tblAddresses.IdCustomer
> WHERE (((tblCustomers.IdStatus)=1) AND ((qryServices.ProxServ) Between [Enter
> From Date:] And [Enter To Date:]));
>
> When I run qryServices, I find that services are to be scheduled in July 2011
> for inventory 4, 5, and 6. The addresses for these three items are 3 and 4.
> BUT there is no contact linked for those two addresses! Addresses 3 and 4 are
> for customers 4 and 5, respectively. The contacts listed for customers 4 and 5
> are contacts 5 and 6 - but those two contact records do not have a related
> IdAddress. The query will return zero rows as long as this is the case.
>
> Do you mean to link to contacts via IdCustomer? If so, why is there an
> IdAddress in tblContacts at all?
>
>
> 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:32 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [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
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>