Complex Report With Multiple One-to-Many Relationships 2

[theUN4GVEN]

I have a set of three tables that I am using to print out a report, they are:

tblContact
----------
ContactID
.
.

tblInvoice
----------
Invoice ID
ContactID
.
.

tblParts
--------
PartID
InvoiceID
PartName
.
.

There is a one-to-many relationship between tblContact and tblInvoice with there being multiple invoices for each contact. The same is true with tblInvoice and tbleParts with there being multiple parts for each invoice. My report is based upon a sole invoice, InvoiceID and needs to print out all of the contact and invoice information for that specific invoice. I also need it to print out a list of the parts assocated with that invoice and their quantities and prices.

I have attempted writing many queries to join the tables in different ways but always end up with too many tuples with the same InvoiceID. I am wondering if anyone has any suggestions as to how to display this information on a report. I have considered using only the parts that pertain to the particular InvoiceID as the source of the report so that the detail section will list out all of the parts, and then possibly coming up with a way to include only a query for the page header that joins tblContact and tblInvoice and selects only the correct invoice but I have no idea how to go about doing this. The report will print for only one InvoiceID so I am not worried about multiple pages and would hope for it to look something like this:

Contact Info
Invoice Product Info

Parts Used
Qty Name Description
--- ---- -----------
. . .
. . .


I would greatly appreciate any help anyone can provide.

Thanks,
Tom

 

[dhookom]

This should be a simple query joining all three tables assuming ContactID is the primary key of tblContacts and InvoiceID is the primary key of tblInvoice.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[formerTexan]

Tom

You must set up sub reports for the 'many' side ofthe one to many relationships.

The main report for the contact info; a subreport for the invoice items; and a sub-sub report for the parts. Keep in mind that invoices are usually printed in a one to one (client to invoice) fashion, so you may only have to nest one layer of sub reports.

Also remember that the main and sub reports will need to contain the primary and foreign key fields to maintain the relationships.

Cheers
Bill

 

[dhookom]

My assumption was there is only one contact per Invoice and InvoiceID in tblInvoice is the Primary Key. If so, a single query should work. You should need any subreports.

If you filter your report on InvoiceID, you should only get one related contact and one Invoice record and one or more records from tblParts.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.