If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
YTD Budget Sum if Actual Month has activities | AGnes | Worksheet Functions | 1 | March 24th, 2005 09:25 PM |
Automatically change tab reference | TWC | General Discussion | 2 | February 2nd, 2005 10:17 PM |
Access 2002-budgets and expenses | DonnaG | Running & Setting Up Queries | 7 | November 25th, 2004 01:29 AM |
Changing Colors of Bar Graphs | Lynn Bartling | Charts and Charting | 5 | February 2nd, 2004 03:16 PM |