Solved Sum of Specific field based on multiple conditions from a Table data (1 Viewer)

mrk777

Member
Local time
Today, 05:56
Joined
Sep 1, 2020
Messages
60
Hi Team, I have been trying to solve this. I have 2 Tables

Table 1: Master Database
DateFunctionSavingsStatus of Project
01/01/2019ABC1000Completed
01/01/2019BCD900On-Going
01/01/2020ABCD1500Completed
01/01/2021BCGA1500Completed
01/01/2021HCFD1000Completed

Table 2: Targets Vs Actuals
YearsTargetsActuals
2019500Need help here!! (1000)
2020700Need help here!! (1500)
20211000Need 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

Banishment Pending
Local time
Yesterday, 19:26
Joined
May 11, 2011
Messages
11,613
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:

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

Enthusiastic Amateur
Local time
Today, 00:26
Joined
Sep 21, 2011
Messages
14,048
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

Member
Local time
Today, 05:56
Joined
Sep 1, 2020
Messages
60
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:

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

Member
Local time
Today, 05:56
Joined
Sep 1, 2020
Messages
60
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. :)
 

Users who are viewing this thread

Top Bottom