searching over multiple tables

[Bob]

I have one table with customer information, including a customer ID
(tblCustomers). Each customer can generate on of two types of invoices -
each with their own set of criteria, therefore each invoice type has it's
own table (tblMfgInvoice, and tblSvcInvoice)

Each customer will have an Invoice number (PK) in one of these invoice
tables, potentially a customer can have an invoice in both tables.

I know there has to be a way to query or script something like
find/list all records in tblMfgInvoice records with CustomerID.
then find/list all records in tblSvcInvoice with same CustomerID
Display the criteria of each invoice found

Is this possible?

 

[Allen Browne]

The best solution would be to combine tblMfgInvoice and tblSvcInvoice into
one table, probably with an extra field to indicate which type of invoice it
is. You can still use separate forms for entering the invoices if you wish.
Each form would be based on a query that chooses just the appropriate
invoice type.

If that is not possible then the fields of the 2 tables must be radically
different. That means a UNION query that tries to combine the 2 into one
long list will not work very well, because the fields won't match, and yet
you want the details of each invoice found.

If you cannot combine them at the table level, and cannot UNION them at the
query level, all you are left with is showing 2 resultsets, i.e. show two
queries (or forms) that give the 2 sets of results.