Help trying to set up a relationship for a query.

[gnb621]

Hi All:
I need help with the following situation:

I have 2 tables:
Table 1 Table 2
Prod. ID Prod. ID
Nov $OH Dec $OH

I would like to run a query which contains the following:

Prod. ID Nov $OH Dec $OH

My problem is that a product may be available in Nov and not Dec and vice versa. I tried to create a relationships but I was given three options:
1. only records that match in both
2. all records from nov and those that match from dec
3. all records from dec and those that match from nov.

I need the records from both tables, therefore the 3 scenarios do not help me. Is there a way to overcome this?

Also I would like to eventually be able to view the data for additional months in this query. At the end of the fiscal year it should look like this:

Prod. ID Nov. $OH Dec. $OH Jan. $OH Feb $OH Mar $OH ..etc.

Hopefully someone can help

 

[jrbarnett]

What I would do would be to combine the two tables into one,

so you would have:
ProductID, Month, $OH

Then all you have to do is use a crosstab query on the table to take the month data across the top and the $OH value as the data with product ID down the side.

John

 

[gnb621]

Thank you so much....You just helped me complete my project.