Hello John, I think you replied to the wrong person. I did not create a post with the subject "Help with reports."
Brad Griffis
--- On Thu, 7/7/11, John Viescas <john@viescas.com> wrote:
From: John Viescas <john@viescas.com>
Subject: RE: [MS_AccessPros] Re: Help with reports.
To: MS_Access_Professionals@yahoogroups.com
Date: Thursday, July 7, 2011, 12:26 AM
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
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar