Complicated Table/Query Situation 1

[wendyp]

Hi all -

I have a fairly complicated table setup:

5 different product tables
tProdDoors (PK: id, field to link: order_item_id)
tProdGlass (PK: id, field to link: order_item_id)
tProdScreens (PK: id, field to link: order_item_id)
tProdOptions (PK: id, field to link: order_item_id)
tProdSunScreens (PK: id, field to link: order_item_id)

The reason I have these different tables for my products is that each requires very different information.

Then I have one table which keeps all the common information between the product - like order date and price - all products have those.
tOrderItem (PK: id, field to link: order_id)

I have 5 queries which unit tOrderItem with each of the product tables to give me the complete information for each product.

Now, here's the tricky part:

I want a list of all products, so that's my tOrderItem table - easy. BUT, I want some key information about each product.
Order Date Price Product Info #1
10/1/2006 $456.00 Door Location
10/1/2006 $56.00 Sun Screen 80%

I want about 5 fields - different things for each product, but will make a list that will help to identify the product (in case a customer orders more than one door, for example)

Since the field names in the product tables are different, I don't think there's a smooth way to do this.

Is my only option to create a temporary table, appending from each product table? I don't think that will be a good solution because it would have to be remade several times a day.

And I can't use a subform because I'm scrolling down a continuous list of orders already.

Thanks for any advice:
/Wendy

 

[PHV]

Have a look at UNION query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wendyp]

Sorry it took so long to get back to you.

UNION was exactly what I needed. Took 5 product tables and combined them into one great (but very, very long) query.

I knew it couldn't be that hard.

Thanks, a star to you.
/Wendy