querie relationships

[Guest]

I have 2 tables that I need to query. One is a customer order table where
many customers can have the same item on order for various dates. The other
is a production table showing what we have in production. Again the same
item can appear more than once coming in on different dates. There are also
different colors of each item on order and in production. I'm trying to
create a query for a report that will show the total on order for each style
by color and how much we have in production for the same style/color. The
relationship I've established is a one to many in both style and color- for
each on order (sum) there are many production orders. The result I get is
each style/color on order being duplicated as many times as there are the
same style/color in production. For example if style/color x is on order for
3 customers and there are 2 production orders, I'll get 6 results, each of
the on orders with each of the production orders. How can I change the
relationship, or something else for that matter, to get each record only once?

 

[Jeff Boyce]

Joanne

It sounds like you have style/color information stored in two locations
(production, order), but no table that holds ONLY style/color info. A
well-normalized database design would use a style/color "lookup" table to
keep that info, then use the primary key value from that table as the data
stored in the production and order tables.

What design are you using?

Regards

Jeff Boyce
Microsoft Office/Access MVP