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
Tidak ada komentar:
Posting Komentar