Query about a query

[Duncs]

Hi folks.

I have a database which has 8 linked tables. The linked tables all
have a common field which identifies the rows, fldSerialNumber. What
I want to do, is have a form that allows me to view all information in
all tables about each fldSerialNumber.

To explain further:

Table1 - Contains order transactions
Table2 - Contains sales transactions
Table3 - Contains delivery transactions
etc.

What I want to do, is select a serial numebr on my main form and then
display a history for that serial number, showing selected details,
sorted in date order.

So:

SO123X75

Date Status Quantity Net Value Gross Value
03/09/08 Sale 10 10.25 102.5
02/09/08 ReStock 1000 5.25 5250
etc.

Can someone advise on how to do this, as I am getting myself tied up
in queries!

TIA

Duncs

 

[KARL DEWEY]

I would suggest that the fldSerialNumber of Table1 be a primary key field.
Then set a one-to-many relationship to the other tables.
Use queries to feed a form and two subforms for order/sales and
order/deleivery. Set the Master/Child links of the form/subform using the
fldSerialNumber fields.