idejjedi2
asked on
Access 2013 Query
Hey Experts,
I am working in Access 2013. Would consider myself somewhere between a beginner and intermediate user of this database application.
I am wondering if there is a simple way of creating an additional calculated field column in a query that will calculate based on a dynamically generated field or expression in the same query. I hope I phrased that correctly. Here is the idea of the problem.
I have a number that represents the objectives of each State that I labeled StateObjective. For example, let us say the StateObjective of Hawaii is 50. Meanwhile, I have an expression field already in the query that calculates the current number in Hawaii with a Count (*) expression. Let us say it is 25. I want my query, which currently calculates the current number in a Count expression and is currently outputting 25 for Hawaii, to subtract from the StateObjective number of 50 to equate to a new calculated field/expression with a result of 25...
I hope that I did not butcher my question.
Hopefully a solution or path will present itself...
I am working in Access 2013. Would consider myself somewhere between a beginner and intermediate user of this database application.
I am wondering if there is a simple way of creating an additional calculated field column in a query that will calculate based on a dynamically generated field or expression in the same query. I hope I phrased that correctly. Here is the idea of the problem.
I have a number that represents the objectives of each State that I labeled StateObjective. For example, let us say the StateObjective of Hawaii is 50. Meanwhile, I have an expression field already in the query that calculates the current number in Hawaii with a Count (*) expression. Let us say it is 25. I want my query, which currently calculates the current number in a Count expression and is currently outputting 25 for Hawaii, to subtract from the StateObjective number of 50 to equate to a new calculated field/expression with a result of 25...
I hope that I did not butcher my question.
Hopefully a solution or path will present itself...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
From the way you describe this, it sounds like you have two tables, one containing the StateObjective value and a State field, another which contains multiple records for each State, which you use to compute your CurrentCount.
The way to accomplish this is generally with either two queries, or with a sub query. The first query would use the Count(*) to get the number for each State.
SELECT State, Count(*) as CurrentCount
FROM yourTable
GROUP BY State
You could save that as a saved query, and join it to the other table, but I generally prefer to have a single query where possible, which would involve creating a subquery (see the query surrounded by () below).
SELECT T2.State, T2.StateObjective, T1.CurrentCount
, T2.StateObjective-NZ(T1.Cu rrentCount , 0) as Diff
FROM TableWithObjective as T2
LEFT JOIN (
SELECT State, Count(*) as CurrentCount
FROM SomeOtherTable
GROUP by State
) as T1
ON T2.State = T1.State
The way to accomplish this is generally with either two queries, or with a sub query. The first query would use the Count(*) to get the number for each State.
SELECT State, Count(*) as CurrentCount
FROM yourTable
GROUP BY State
You could save that as a saved query, and join it to the other table, but I generally prefer to have a single query where possible, which would involve creating a subquery (see the query surrounded by () below).
SELECT T2.State, T2.StateObjective, T1.CurrentCount
, T2.StateObjective-NZ(T1.Cu
FROM TableWithObjective as T2
LEFT JOIN (
SELECT State, Count(*) as CurrentCount
FROM SomeOtherTable
GROUP by State
) as T1
ON T2.State = T1.State
ASKER
Saurabh,
Thanks. Your SQL Statement was helpful in me deciphering what I was doing wrong. I was not referencing State field correctly in my SQL Statement, because I had labeled "AS State" incorrectly. Thanks for the assistance.
Thanks. Your SQL Statement was helpful in me deciphering what I was doing wrong. I was not referencing State field correctly in my SQL Statement, because I had labeled "AS State" incorrectly. Thanks for the assistance.
idejjedi2,
Anytime..Happy to help... :-)
Saurabh...
Anytime..Happy to help... :-)
Saurabh...
For example, Newfield : [StateObjective] - [CountExpression] would do just that (assuming CountExpression is your previously calculated field.