Need help with tricky query... well tricky for me

[pointBoarder]

Thanks in advance to all who read this.

I've got 3 tables which were created from a txt file dumped from some
old system.

Header
ID -- autonumber, primary
OrderNum -- field I want

Line
ID -- may be none, may be many
OrderNumSub -- field I want

Option
ID -- may be none, may be many
OrderNumOpt -- field I want

What I need is OrderNum, OrderNumSub, and OrderNumOpt all put into a
single column AllOrders.

Any advice would be appreciated,
Derek

[Johnny Meredith]

Is this a true statement:

One Header can have many Lines. One Line can have many Options.

or is it...

One Header can have many Lines. One Header can have many Options.

Also, please provide the Foreign Key fields in the downstream tables.

Assuming the first statement is correct and making up a foreign key
field, the query would be:

SELECT Header.OrderNum, Line.OrderNumSub, Option.OrderNumOpt FROM
(Header INNER JOIN Line ON Header.ID = Line.fkHeaderID) INNER JOIN
[Option] ON Line.ID = Option.fkLineID;

The fields prefixed "fk" are the foreign keys I ask for above. I
created a blank database to test the query. Let me know if you want it
and I'll email it to you.

Thanks,
Johnny

[pointBoarder]

Your first guess was correct. One Header can have many Line. Each
Line can have many Options.

If you could send the sample DB you spoke of to De*********@gmail.com
I'd like to take a look at it.

Thanks Johnny,
Derek

[pointBoarder]

my first name is Derek
last name is Ciula
email is Fi********@gmail.com

[pointBoarder]

Ciula --- is the last part of the email

[Johnny Meredith]

The email address is truncated. Please send an email to the following
address:

j[DELETETHISTEXT}meredith AT gee MAIL dot COM (THIS IS A GMAIL ADDRESS)

and I will reply with it as an attachment.

Johnny

[Salad]

pointBoarder wrote:

Thanks in advance to all who read this.

I've got 3 tables which were created from a txt file dumped from some
old system.

Header
ID -- autonumber, primary
OrderNum -- field I want

Line
ID -- may be none, may be many
OrderNumSub -- field I want

Option
ID -- may be none, may be many
OrderNumOpt -- field I want

What I need is OrderNum, OrderNumSub, and OrderNumOpt all put into a
single column AllOrders.

Any advice would be appreciated,
Derek

Since you want a single field, your best bet is a Union query. After
that, another query to get a field. Air code
Select ID, OrderNum As DisplayIT, 1 As Priority From Header
UNION ALL
Select ID, OrderNumSub As DisplayIT, 2 As Priority From Line
UNION ALL
Select ID, OrderNumOpt As DisplayIT, 3 As Priority From Option
Call this Query1

Now create another query.
Select DisplayIT from Query1 Order By ID, Priority