simple assistance needed

[gkaro]

Hi all, newbie here.
Id like some help please.

I have 2 tables with fields as follows:

Table 1 (relatively fixed)
Product


Desired Inventory

Table 2 (updated every week)
Product
Week
Actual Inventory

Id like a query to output the following:
Product
Week
Desired Inventory
Actual Inventory

The problem that I am having is that for any week that a specific product has no Actual inventory reading I am not getting any output. I need to get every product listed whether or not an actual is present in
I have 3 products A,B C with desired inventory of 30, 30, 30
for week 10 I have an actual for A(20) & B(15) but nothing blank for C. Need the output to be:

A wk10 30 20
B wk10 30 15
C wk10 30 (blank or 0) This is the line that I can not get


thanks so much
G

[ssanfu]

I think you need to change your table structure.
Attachment 14085
I would have a structure like above. In the attached dB, open Query1.

[gkaro]

Hi Steve,,,thanks for your reply.
The example I gave was a very simplified example of what Im doing here.

If I understand correctly, in Table 2, I need to match the key in Table 1 line by line for every product?
I dont think this is realistic becasue Table 1 is a database of about 5,000 lines for combinations of various products and store locations.
Table 2 is a table that gets updated every week with actual inventory data for each product by store. I append approx 20,000 lines per week in table 2.

How would I ensure that the keys match?

[ItsMe]

How would I ensure that the keys match?

It is up to you, as the developer, to maintain referential integrity.

http://databases.about.com/cs/admini...fintegrity.htm

You probably need more tables.


.

[ssanfu]

Sorry. You asked a specific question "How do I...", with example data. Based on what you provided, that is how I would do it.

How would I ensure that the keys match?

Without knowing more about your process, the best I can say is "It Depends". Is your table structure normalized? Are you importing the weekly data into one table? What is your process?


You could try setting up a relationship on the "Product" fields, but it appears the "Product" fields are text type field. Relationships on text fields can be done, but they are slow compared to Long Integers.

Does each product have a unique identifier? Maybe a UPC? Barcode?