Trying to match forecasts with actuals

[vangogh228]

I am attaching a pic of my relationships page to show the table structure I am dealing with. What I need to do is match forecasted values with actuals based on the same date range, sales_num and unit.

The two tables that hold this data after downloading from our systems are

ForecastOE_T
ActualOE_T

The other tables are crossrefs for additional information and aggregation.

I have been able to query successfully each of these separately based on my desired date range, and successfully aggregate based on salesrep number and unit... but, I don't know how to properly write the query joining them.

Here are my issues:
Not all reps forecast all units.
Not all reps sell all units.

So, when I try to join these values (forecast and actual), the query I write gives me only those rep/unit combinations that BOTH a forecast was made and an actual appears (we don't get a download of zero activity). What I need is output that shows the rep/unit combination for all entries that have EITHER the forecast or the actual, with zeroes defaulted wherever appropriate. This has been done in excel in the past, but the file sizes are prohibitive now... and I'm trying to help out, but am stumped here.

ANY HELP IS GREATLY APPRECIATED!!

Tom

 

[neileg]

You need to include your table SalesRepD... in the query. Make sure that the joins in the query are left and right, not inner. In other words, you need all of the records in SalesRepD... and any that match in ForecastOE_T and ActualOE_T. This will ensure that you get all the records.