By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,934 Members | 1,692 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,934 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
my first name is Derek
last name is Ciula
email is Fi********@gmail.com

Nov 13 '05 #4

P: n/a
Ciula --- is the last part of the email

Nov 13 '05 #5

P: n/a
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

Nov 13 '05 #6

P: n/a
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


Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.