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
|
|||
|
|||
Listing out Budget, Actual and Forecast
Dear all,
We have 3 tables for Budget, Actual and Forecast and the following is the example. The account in the tables can be different. We do not have a MASTER table to list out all the possible combination of centre and account Budget Centre Account Amount 001 100 $50 002 200 $500 Actual Centre Account Amount 001 100 $55 002 250 $300 Forecast Centre Account Amount 001 100 $60 002 250 $300 003 300 $100 I cannot figure how to make a query that link the above three table together and generate a report as below Centre Account Budget Actual Forecast 001 100 50 55 60 002 200 500 0 0 002 250 0 300 300 003 300 0 0 100 Thank you. Regards, BL |
#2
|
|||
|
|||
Listing out Budget, Actual and Forecast
Try this for your query. Paste it into the SQL view of query design.
SELECT tblBudget.Centre, tblBudget.Account, tblBudget.Amount AS Budget, tblActual.Amount AS Actual, tblEstimate.Amount AS Estimate FROM tblEstimate INNER JOIN (tblActual INNER JOIN tblBudget ON (tblActual.Account = tblBudget.Account) AND (tblActual.Centre = tblBudget.Centre)) ON (tblEstimate.Account = tblBudget.Account) AND (tblEstimate.Centre = tblBudget.Centre); Regards Kevin "BL" wrote in message ... Dear all, We have 3 tables for Budget, Actual and Forecast and the following is the example. The account in the tables can be different. We do not have a MASTER table to list out all the possible combination of centre and account Budget Centre Account Amount 001 100 $50 002 200 $500 Actual Centre Account Amount 001 100 $55 002 250 $300 Forecast Centre Account Amount 001 100 $60 002 250 $300 003 300 $100 I cannot figure how to make a query that link the above three table together and generate a report as below Centre Account Budget Actual Forecast 001 100 50 55 60 002 200 500 0 0 002 250 0 300 300 003 300 0 0 100 Thank you. Regards, BL |
#3
|
|||
|
|||
Listing out Budget, Actual and Forecast
BL -
First create a query that will contain each Centre/Account combination once: Select distinct [Centre], [Account] from [Budget] UNION Select distinct [Centre], [Account] from [Actual] UNION Select distinct [Centre], [Account] from [Forecast] GROUP BY [Centre], [Account]; Then create your final query using this query (use your name for CentreAccts) and your tables: Select CentreAccts.Centre, CentreAccts.Account, nz(Budget.Amount,0), nz(Actual.Amount,0), nz(Forecast.Amount,0) FROM ((CentreAccts LEFT JOIN Forecast ON (CentreAccts.Account = Forecast.Account) AND (CentreAccts.Centre = Forecast.Centre)) LEFT JOIN Actual ON (CentreAccts.Account = Actual.Account) AND (CentreAccts.Centre = Actual.Centre)) LEFT JOIN Budget ON (CentreAccts.Account = Budget.Account) AND (CentreAccts.Centre = Budget.Centre); -- Daryl S "BL" wrote: Dear all, We have 3 tables for Budget, Actual and Forecast and the following is the example. The account in the tables can be different. We do not have a MASTER table to list out all the possible combination of centre and account Budget Centre Account Amount 001 100 $50 002 200 $500 Actual Centre Account Amount 001 100 $55 002 250 $300 Forecast Centre Account Amount 001 100 $60 002 250 $300 003 300 $100 I cannot figure how to make a query that link the above three table together and generate a report as below Centre Account Budget Actual Forecast 001 100 50 55 60 002 200 500 0 0 002 250 0 300 300 003 300 0 0 100 Thank you. Regards, BL |
#4
|
|||
|
|||
Listing out Budget, Actual and Forecast
You should change to ONE table like this --
tblCosting -- CostID - Autonumber - primary key EntryType - text - Budget, Actual, Forecast PostDate - DateTime Centre - text Account - text Amount - Number - currency -- Build a little, test a little. "BL" wrote: Dear all, We have 3 tables for Budget, Actual and Forecast and the following is the example. The account in the tables can be different. We do not have a MASTER table to list out all the possible combination of centre and account Budget Centre Account Amount 001 100 $50 002 200 $500 Actual Centre Account Amount 001 100 $55 002 250 $300 Forecast Centre Account Amount 001 100 $60 002 250 $300 003 300 $100 I cannot figure how to make a query that link the above three table together and generate a report as below Centre Account Budget Actual Forecast 001 100 50 55 60 002 200 500 0 0 002 250 0 300 300 003 300 0 0 100 Thank you. Regards, BL |
#5
|
|||
|
|||
Listing out Budget, Actual and Forecast
Hi, Daryl S,
That is exactly what I need, thank you very much for your suggestion. Regards, BL "Daryl S" wrote: BL - First create a query that will contain each Centre/Account combination once: Select distinct [Centre], [Account] from [Budget] UNION Select distinct [Centre], [Account] from [Actual] UNION Select distinct [Centre], [Account] from [Forecast] GROUP BY [Centre], [Account]; Then create your final query using this query (use your name for CentreAccts) and your tables: Select CentreAccts.Centre, CentreAccts.Account, nz(Budget.Amount,0), nz(Actual.Amount,0), nz(Forecast.Amount,0) FROM ((CentreAccts LEFT JOIN Forecast ON (CentreAccts.Account = Forecast.Account) AND (CentreAccts.Centre = Forecast.Centre)) LEFT JOIN Actual ON (CentreAccts.Account = Actual.Account) AND (CentreAccts.Centre = Actual.Centre)) LEFT JOIN Budget ON (CentreAccts.Account = Budget.Account) AND (CentreAccts.Centre = Budget.Centre); -- Daryl S "BL" wrote: Dear all, We have 3 tables for Budget, Actual and Forecast and the following is the example. The account in the tables can be different. We do not have a MASTER table to list out all the possible combination of centre and account Budget Centre Account Amount 001 100 $50 002 200 $500 Actual Centre Account Amount 001 100 $55 002 250 $300 Forecast Centre Account Amount 001 100 $60 002 250 $300 003 300 $100 I cannot figure how to make a query that link the above three table together and generate a report as below Centre Account Budget Actual Forecast 001 100 50 55 60 002 200 500 0 0 002 250 0 300 300 003 300 0 0 100 Thank you. Regards, BL |
Thread Tools | |
Display Modes | |
|
|