Summary Report with related tables

[maureen227]

I don't know if this is a query problem or a report problem but here
it is.

I have 4 tables (invheader, equipcharge, laborcharge, payments) they
are all related to the invheader through the invoice number.
I need a report that is a summary of the equipcharge, the laborcharge
and the payments by invoice number.

I thought this would be fairly straight forward, but my problem is that
an invoice can have an equipcharge but no labor charge or a labor
charge but no equipcharge and the invoice can have payment or no
payments.

I would like my report to be like this:
Invoice# Total Equip Charge Total Labor Charge Total Paymts
100 $2,000.00 1500.00 1000.00
101 0.00 300.00 300.00
102 500.00 0.00 250.00

My query will give me all the invoices in the equipcharge table OR all
the invoices in the laborcharge OR all the invoices in the payments. I
know this is because there is no record in the laborcharge table when
there was no labor charge for an invoice and vice verser with the
equipcharge table. How do I get the query to see them all?

I will be greatly appreciative and totally impressed for help with
this.
Terabytes of thanks.
Maureen

 

[Marshall Barton]

I don't know if this is a query problem or a report problem but here
it is.

I have 4 tables (invheader, equipcharge, laborcharge, payments) they
are all related to the invheader through the invoice number.
I need a report that is a summary of the equipcharge, the laborcharge
and the payments by invoice number.

I thought this would be fairly straight forward, but my problem is that
an invoice can have an equipcharge but no labor charge or a labor
charge but no equipcharge and the invoice can have payment or no
payments.

I would like my report to be like this:
Invoice# Total Equip Charge Total Labor Charge Total Paymts
100 $2,000.00 1500.00 1000.00
101 0.00 300.00 300.00
102 500.00 0.00 250.00

My query will give me all the invoices in the equipcharge table OR all
the invoices in the laborcharge OR all the invoices in the payments. I
know this is because there is no record in the laborcharge table when
there was no labor charge for an invoice and vice verser with the
equipcharge table. How do I get the query to see them all?

Use outer joins instead of inner joins. To specify that in
the query design window, select a connecting line between
the tables, then right click on the line and select the join
type that includes all records from the invioce table and
any matching records from the other table.

 

[maureen227]

Use outer joins instead of inner joins. To specify that in
the query design window, select a connecting line between
the tables, then right click on the line and select the join
type that includes all records from the invioce table and
any matching records from the other table.

Marsh
You are a King among Access/Database developers. Tera-bytes of thanks.
I didn't know about the "connecting line between the tables." That
made all the difference.

Thanks again.
Maureen

PS I promise never to post the same question twice again. ;-)