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...
idejjedi2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Assuming for each state you have a state objective..Then you can simply do this...

select state,StateObjective-count(state) as abc
from table1
group by state;

Open in new window


Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard DanekeTrainerCommented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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
idejjedi2Author Commented:
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.
Saurabh Singh TeotiaCommented:
idejjedi2,

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

Saurabh...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.