Query that lists items in an order

[upplepop]

I've got an inventory database that tracks which Order Numbers the items were purchased in. Each table has the Order Number as a foreign key. Here are the relevant tables:

Order(OrderNumber, Vendor, DateOrdered, Amount)

Hardware(HardwareID, Manufacturer, Model, Serial, OrderNumber)

Computer(ComputerID, Manufacturer, Model, Serial, OrderNumber)

Software(SoftwareID, Developer, Name, Licenses, OrderNumber)

I would like a query that can produce a table that shows all the items that were each order, keeping in mind that an order may have had hardware, computers, and software in it. It should look similar to the following.
[tt]
Order# Manuf/Devel Model/Name Serial/licenses
------ ----------- ---------- ---------------
001 Dell PowerEdge1 B342A556543
001 Dell TapeDrive7 R234C365465
002 Gateway Laptop2000 23433356545
002 Gateway Router9000 34232356546
002 Microsoft WindowsXP 30
...
[/tt]

Thanks in advance for your help!

 

[alvechurchdata]

One way would be to create three simple queries; one each for Order+Hardware, Order+Computer, Order+Software. Then write a fourth Union query to pull these three together. The Query Designer can't do a Union Query so you have to write the SQL yourself. There is sample code in Help.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[dhookom]

The union query as suggested by Geoff is a good answer. I'm not sure why you would need the "create three simple queries" first. You should be able to just create the union query based on the tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[alvechurchdata]

I'm not sure why you would need the "create three simple queries" first

The job could be done job in one single SQL Union but it would be difficult to debug. Doing the initial joins in separate queries means that I can look at the intermediate results and see where I've made a mistake. I'm a great believer in taking life one step at a time.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[dhookom]

Ahhh, divide and conquer. Not a bad idea.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]