Link to home
Start Free TrialLog in
Avatar of idejjedi2
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...
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes.  In QBE, you can reference a previously created calculated field in the same query in a newly created calculated field.  
For example, Newfield : [StateObjective] - [CountExpression]  would do just that (assuming CountExpression is your previously calculated field.
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.CurrentCount, 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
Avatar of idejjedi2
idejjedi2

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.
idejjedi2,

Anytime..Happy to help... :-)

Saurabh...