ORDER TABLE FROM TWO PRODUCT TABLES

[SyntaxError]

I'm trying to create a report from my ORDERS TABLE, but I have two
PRODUCT TABLES. The REPORT only shows orders from one PRODUCT TABLE,
not the second...

I've tried all types of relationships to no avail. I even have a UNION
QUERY from the two PRODUCT TABLES, still no luck.

Can anyone help me...

 

[Damian S]

Please post the SQL of your query here so we can have a look at it.

Damian.

 

[Van T. Dinh]

I don't see your complete database with data to undestand why you have 2
Product Tables but the use of 2 Tables to represent the same *entity* in
your database may mean that the Table Structure is correct ...

You may need to consider combine the into one Table with perhaps an
additional Field to indicate, says, the Product Type if you use 2 different
Tables for 2 different Product types ...

 

[SyntaxError]

My problem is trying to get a REPORT from my ORDERS QUERY when a
customer orders from either PRODUCT TABLE. So far it's only picking up
from [PRODUCTS] and not [SAFE PRODUCTS]. The reason I have two
products tables is because the Safe Product tables require additional
information such as Weight, Height, Legnth, etc, that are not related
to the first products table.

I also have a UNION QUERY connecting the [PRODUCTS] & [SAFE PRODUCTS]
just to show basic information. I've tried various RELATIONSHIPS but
to no avail, any help would be appreciated. -SYNTAX!!!

[ORDERS] [PRODUCTS] [SAFE PRODUCTS]
ProductID ------------ProductID -------------------------ProductID
Order ID Description Description
CustomerID Quantity Quantity
Order Date Cost Cost Cost
Quantity Price Price
Reorder Level Reorder Level
Supplier ID Supplier ID
Department Department
Sub Dept Sub Dept
Tax Tax
Rated
Manufacturer Name
Rating No of Shelves
Outside HxWxD
App Weight Lbs

 

[John W. Vinson]

My problem is trying to get a REPORT from my ORDERS QUERY when a
customer orders from either PRODUCT TABLE. So far it's only picking up
from [PRODUCTS] and not [SAFE PRODUCTS]. The reason I have two
products tables is because the Safe Product tables require additional
information such as Weight, Height, Legnth, etc, that are not related
to the first products table.

This sounds like a good case for "Subclassing". Consider having all
your products in [PRODUCTS], with those fields that are in common
between the general products and the SAFE products; change the
structure of the SAFE PRODUCTS table to include just the PRODUCT ID as
the primary key, and the additional fields pertaining only to SAFE
PRODUCTS. In the relationships, join the two tables' PRODUCT ID,
dragging it from [PRODUCTS] to [SAFE PRODUCTS]; this will establish a
one to one relationship.

Now you'll have a record for every product in the PRODUCTS table and
can generate your report. If the report needs to display fields from
[SAFE PRODUCTS] you can include it in the report's recordsource query,
using a LEFT OUTER JOIN to display all records in PRODUCTS and
matching records in [SAFE PRODUCTS].

John W. Vinson [MVP]