Listing out Budget, Actual and Forecast

  • Thread starter Thread starter BL
  • Start date Start date
B

BL

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
 
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 -

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);
 
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
 
Hi, Daryl S,

That is exactly what I need, thank you very much for your suggestion.

Regards,

BL

Daryl S said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top