Problem with multiple invoice reports

[forkEP]

Im having a small problem with my invoice report running over multiple pages for each invoice entry I have. I have a query based on 5 tables:
tblInvoiceNumber (which I am setting the criteria to the text box of the open form [Forms]![frmInvoice]![tblInvoiceNumber_InvoiceNumber])
tblCustomer
tblJob
tblTickets
tblInvoice

My problem is, tblInvoiceNumber holds the single instance of the Invoice Number. The tblInvoice, holds and instance of the Invoice Number for each ticket on that invoice. When I am generating the report, I get multiple matching invoice pages for each of the Invoice Number instances. Basically, for each ticket on that invoice, I get another page added to the Invoice report, that matches the others. If anybody has an idea of how to remedy this situation, I appreciate any help that you can give.

 

[GingerR]

Hi. Please post your query's sql. Thanks.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

I agree with GingerR. We have no idea how your tables are related.

In every other similar application I have seen, the InvoiceNumber field is the primary key in tblInvoice.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[forkEP]

SELECT tblCustomer.*, tblJob.*, tblTickets.*, tblInvoiceNumber.FuelSurcharge, tblInvoiceNumber.DateCreated, tblInvoiceNumber.JobNumber, tblInvoiceNumber.WeekEndingDateShown, tblInvoice.TicketNumber, tblInvoiceNumber.InvoiceNumber
FROM ((tblInvoiceNumber INNER JOIN (tblCustomer INNER JOIN tblJob ON tblCustomer.CustomerNumber = tblJob.CustomerNumber) ON tblInvoiceNumber.JobNumber = tblJob.JobNumber) INNER JOIN tblTickets ON tblJob.JobNumber = tblTickets.JobNumber) INNER JOIN tblInvoice ON (tblTickets.TicketNumber = tblInvoice.TicketNumber) AND (tblInvoiceNumber.InvoiceNumber = tblInvoice.InvoiceNumber)
WHERE (((tblInvoiceNumber.InvoiceNumber)=[Forms]![frmInvoice]![tblInvoiceNumber_InvoiceNumber]));

Hope that mess helps ahhaha

 

[dhookom]

If you look at your datasheet view of your report's record source, do you see duplicates? Are you using any subreports?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[forkEP]

I realized late last night that I forgot to list that I have a subreport, which I realize is a HUGE thing to leave out

Yes i am seeing duplicates for invoice number, one for each ticket that is attached to that invoice number.

For the Subreport:
Link Child Field = tblInvoiceNumber.InvoiceNumber
Link Master Field = InvoiceNumber

Once again, thank you for any assistance.

 

[dhookom]

If you have a subreport, then you should not have the same level of detail in the main report as you do in the subreport.

However, we don't understand your table relationships or the record source of your subreport.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]