Solved Sum of Specific field based on multiple conditions from a Table data

[mrk777]

Hi Team, I have been trying to solve this. I have 2 Tables

Table 1: Master Database

Date	Function	Savings	Status of Project
01/01/2019	ABC	1000	Completed
01/01/2019	BCD	900	On-Going
01/01/2020	ABCD	1500	Completed
01/01/2021	BCGA	1500	Completed
01/01/2021	HCFD	1000	Completed

Table 2: Targets Vs Actuals

Years	Targets	Actuals
2019	500	Need help here!! (1000)
2020	700	Need help here!! (1500)
2021	1000	Need help here!! (2500)

I need to get the results in the "Actuals" field in Table 2 based on Year and status (as Completed) from the Master Database (Table 1). I have tried using queries, but I'm unable. Can any please help me solve this?

Note: There is no relation between these two tables.

 

[plog]

First and foremost, you don't store calculated values in tables--you calculate them in a query and reference the query when you need them. That means [Actuals] should not be a field in Table2.

Second, there is a relationship between your tables, but you have to do a little work to get there. [Date] relates to [Years]--you just have to use a query to get the year of the [Date] field. Use this query for that:

SELECT Year([Date]) AS ActualsYear, SUM(Savings) AS ActualsSavings
FROM Table1
WHERE [Status of Project]='Completed'
GROUP BY Year([Date]);

Name the above something like 'ActualsTotals'. Then when you want the Targets and Actuals data together you build another query on ActualsTotals and Table2 and you JOIN them by [Years] and [ActualsYear]. You don't save the data in Table2 you reference that new query.

Third, you are using reserved words as field names (https://docs.microsoft.com/en-us/office/troubleshoot/access/reserved-words). That makes coding and querying harder. [Date] should not be a field name, instead prefix it with what date it represents (e.g. ActualsDate). [Function] should not be a field name either. Nor should any word on that list.

 

[Gasman]

Break it down to small steps.
One query to get the totals for the year.
Then link to Targets table by year.

I would not update as 2021 has not yet finished?

 

[mrk777]

First and foremost, you don't store calculated values in tables--you calculate them in a query and reference the query when you need them. That means [Actuals] should not be a field in Table2.

Second, there is a relationship between your tables, but you have to do a little work to get there. [Date] relates to [Years]--you just have to use a query to get the year of the [Date] field. Use this query for that:

SELECT Year([Date]) AS ActualsYear, SUM(Savings) AS ActualsSavings
FROM Table1
WHERE [Status of Project]='Completed'
GROUP BY Year([Date]);

Name the above something like 'ActualsTotals'. Then when you want the Targets and Actuals data together you build another query on ActualsTotals and Table2 and you JOIN them by [Years] and [ActualsYear]. You don't save the data in Table2 you reference that new query.

Third, you are using reserved words as field names (https://docs.microsoft.com/en-us/office/troubleshoot/access/reserved-words). That makes coding and querying harder. [Date] should not be a field name, instead prefix it with what date it represents (e.g. ActualsDate). [Function] should not be a field name either. Nor should any word on that list.

Thank you @plog, I did try and it worked well. And I'll try replacing the reserved words.

 

[mrk777]

Break it down to small steps.
One query to get the totals for the year.
Then link to Targets table by year.

I would not update as 2021 has not yet finished?

@Gasman, That was just mentioned for an example.