Need a join to combine tables

[BroncoMarc]

I've got 2 tables.. One with sales history and one with sales forecast. I'm trying to use a query to combine them. I've got joins set up with the common info (customer name, product) so it shows me history and forecast info at the same time in the result.

My problem is that we have some things that we have a history for, but we don't have forecasts for, and some things that we have forecasts for that we don't have history for.

I've got 3 choices with my joins:
#1: I can do a standard join, but then I only show common data and it cuts of things I don't have a history for or don't have a forecast for.

#2: I can do a join so it shows all history data and only forecast data that matches, but then it cuts off forecast data that there is no history for.

#3: I can do a join so it shows all forecast data and only history data that matches, but then it cuts off history data that there is no forecast for.


Each one of these cuts off records. How can I set it up so it doesn't cut anything off?

Thanks,
- Marc

 

[Mile-O]

Would a UNION query not suit you better?

 

[Pat Hartman]

You need a Full Outer Join which Jet doesn't directly support. Create a Left Join query. Then create a Right Join query. Then create a query that unions the other two queries. That will produce all the necessary conditions:

History - Forcast
No        - Yes
Yes      -  Yes
Yes      -  No

 

[BroncoMarc]

Thanks!!!

I've never heard of a Union Query and I don't know SQL, but I somehow managed to get it to work.

You suggestion to create the right join and the left join, then combine them really helped too.

- Marc

 

[Pat Hartman]

That's the way I do it because it saves typing. Remember you can't use QBE to build a union query. If you felt like typing, you could do the whole thing in a single query