SQL query showing diff between actual and budget

Hi All,

I have a table with the following attributes:
Customer_Code, Product_Code, Category, Month, Value

Where category has the domain BudgetTurnover and ActualTurnover
and Month has the domain naturally Jan to Dec

The budget figures are in for the year.

I get the monthly actual figures at the end of each month.

What I want to do is get a table which looks like this:

Customer_Code Product_Code Month Value(Budget) Value(Actual) Difference

I thought to do 2 queries, one which give me a (BUDGET QUERY TABLE) :

Customer_Code Product_Code Month Value(Budget)

and another which gives me a (ACTUAL QUERY TABLE)

Customer_Code Product_Code Month Value(Actual)

I then thought to join the 2 query tables to get this:

Customer_Code Product_Code Value(Budget) Value(Actual) Difference

but I need to include:
1. all the matching rows in both tables (ie where Cust_Code, Product_Code
and Month match)
2. all rows in the budget table and have zero where there is no match in the
actual table where we did not get the budgeted sale and
3. all rows in the actual table and have zero where there is no match in the
budget table in case we got orders from new customer not in the budget

I am sure this is a no brainer but I just cannot get it out.

Can someone help me out say with the appropriate SQL perhaps?

Thanks
Bon

It's not quite a no brainer, because you want all the records in BOTH
tables irrespective of whether there's a matching record in the other
table, right? With the left and right outer joins available in Access, you
can get all of the records out of one table, but not the other. That does
mean you can do something like this, though:

Have a query that returns all Budget figures and matching actual figures,
replacing the missing actual figures (which wil be Null) with zero:
All Budgets plus Matching Actuals
SELECT [BUDGET QUERY TABLE].Customer_Code, [BUDGET QUERY TABLE]
..Product_Code, [BUDGET QUERY TABLE].Month, [BUDGET QUERY TABLE].Budget, IIf
([ACTUAL QUERY TABLE]![Customer_Code] Is Null,0,[ACTUAL QUERY TABLE]!
[Actual]) AS ActualValue
FROM [BUDGET QUERY TABLE] LEFT JOIN [ACTUAL QUERY TABLE] ON ([BUDGET QUERY
TABLE].Customer_Code = [ACTUAL QUERY TABLE].Customer_Code) AND ([BUDGET
QUERY TABLE].Product_Code = [ACTUAL QUERY TABLE].Product_Code) AND ([BUDGET
QUERY TABLE].Month = [ACTUAL QUERY TABLE].Month);

You can then have the revrse of this query, except this time selecting ONLY
those Actuals that DON'T have matching Budgets:
Actuals without Matching Budgets:
SELECT [ACTUAL QUERY TABLE].Customer_Code, [ACTUAL QUERY TABLE]
..Product_Code, [ACTUAL QUERY TABLE].Month, 0 AS Budget, [ACTUAL QUERY TABLE]
..Actual
FROM [BUDGET QUERY TABLE] RIGHT JOIN [ACTUAL QUERY TABLE] ON ([BUDGET QUERY
TABLE].Customer_Code = [ACTUAL QUERY TABLE].Customer_Code) AND ([BUDGET
QUERY TABLE].Product_Code = [ACTUAL QUERY TABLE].Product_Code) AND ([BUDGET
QUERY TABLE].Month = [ACTUAL QUERY TABLE].Month)
WHERE ((([BUDGET QUERY TABLE].Customer_Code) Is Null));

Then what you can do is join the output of the two queries together using a
UNION query:
SELECT * FROM [ACTUAL QUERY TABLE]
union SELECT * FROM [BUDGET QUERY TABLE];

Note that you can't build the last query using the Query Design view - you
have to use the SQL window. Union queries have some quirks about column
names and so on, but you should be fine in this instance.

--
Message posted via http://www.accessmonster.com

Thank you so much! This was of enormous help. Perfect actually!

"Bon" wrote:

Hi All,

I have a table with the following attributes:
Customer_Code, Product_Code, Category, Month, Value

Where category has the domain BudgetTurnover and ActualTurnover
and Month has the domain naturally Jan to Dec

The budget figures are in for the year.

I get the monthly actual figures at the end of each month.

What I want to do is get a table which looks like this:

Customer_Code Product_Code Month Value(Budget) Value(Actual) Difference

I thought to do 2 queries, one which give me a (BUDGET QUERY TABLE) :

Customer_Code Product_Code Month Value(Budget)

and another which gives me a (ACTUAL QUERY TABLE)

Customer_Code Product_Code Month Value(Actual)

I then thought to join the 2 query tables to get this:

Customer_Code Product_Code Value(Budget) Value(Actual) Difference

but I need to include:
1. all the matching rows in both tables (ie where Cust_Code, Product_Code
and Month match)
2. all rows in the budget table and have zero where there is no match in the
actual table where we did not get the budgeted sale and
3. all rows in the actual table and have zero where there is no match in the
budget table in case we got orders from new customer not in the budget

I am sure this is a no brainer but I just cannot get it out.

Can someone help me out say with the appropriate SQL perhaps?

Thanks
Bon

Dear David,
I have been happily using your code until my manager one day had the idea to
add year-to-date data to my monthly report!
Currently, I cut and paste the figures into Excel and do the additions there.

Can you suggest some neat code that will allow me to get
Customer_Code Product_Code Value(ActualMTD) Value(BudgetMTD) DifferenceMTD
Value(ActualYTD) Value(BudgetYTD) DifferenceYTD

You helped me so far to get the MTD figures! Can you help me with the YTD
figures please!

Thanks
Bon


"David Seeto via AccessMonster.com" wrote:

It's not quite a no brainer, because you want all the records in BOTH
tables irrespective of whether there's a matching record in the other
table, right? With the left and right outer joins available in Access, you
can get all of the records out of one table, but not the other. That does
mean you can do something like this, though:

Have a query that returns all Budget figures and matching actual figures,
replacing the missing actual figures (which wil be Null) with zero:
All Budgets plus Matching Actuals
SELECT [BUDGET QUERY TABLE].Customer_Code, [BUDGET QUERY TABLE]
..Product_Code, [BUDGET QUERY TABLE].Month, [BUDGET QUERY TABLE].Budget, IIf
([ACTUAL QUERY TABLE]![Customer_Code] Is Null,0,[ACTUAL QUERY TABLE]!
[Actual]) AS ActualValue
FROM [BUDGET QUERY TABLE] LEFT JOIN [ACTUAL QUERY TABLE] ON ([BUDGET QUERY
TABLE].Customer_Code = [ACTUAL QUERY TABLE].Customer_Code) AND ([BUDGET
QUERY TABLE].Product_Code = [ACTUAL QUERY TABLE].Product_Code) AND ([BUDGET
QUERY TABLE].Month = [ACTUAL QUERY TABLE].Month);

You can then have the revrse of this query, except this time selecting ONLY
those Actuals that DON'T have matching Budgets:
Actuals without Matching Budgets:
SELECT [ACTUAL QUERY TABLE].Customer_Code, [ACTUAL QUERY TABLE]
..Product_Code, [ACTUAL QUERY TABLE].Month, 0 AS Budget, [ACTUAL QUERY TABLE]
..Actual
FROM [BUDGET QUERY TABLE] RIGHT JOIN [ACTUAL QUERY TABLE] ON ([BUDGET QUERY
TABLE].Customer_Code = [ACTUAL QUERY TABLE].Customer_Code) AND ([BUDGET
QUERY TABLE].Product_Code = [ACTUAL QUERY TABLE].Product_Code) AND ([BUDGET
QUERY TABLE].Month = [ACTUAL QUERY TABLE].Month)
WHERE ((([BUDGET QUERY TABLE].Customer_Code) Is Null));

Then what you can do is join the output of the two queries together using a
UNION query:
SELECT * FROM [ACTUAL QUERY TABLE]
union SELECT * FROM [BUDGET QUERY TABLE];

Note that you can't build the last query using the Query Design view - you
have to use the SQL window. Union queries have some quirks about column
names and so on, but you should be fine in this instance.

--
Message posted via http://www.accessmonster.com