How to Use Variables in Access SQL Queries

Question

Hi I'm working on a project which requires I use a large number of columns in my Access SQL queries. A typical query contains several fields such as the one below:

SELECT ([Score1]+[Score2]+[Score3])*.5 AS [Scores], [Subject] FROM [ScoresTable]
WHERE ([Score1]+[Score2]+[Score3])*.5 > 500

Is there any way to assign the value of ([Score1]+[Score2]+[Score3])*.5 to a variable so I could in effect write something like:

SELECT ([Score1]+[Score2]+[Score3])*.5 AS [Scores] *= VAR*, [Subject] 
FROM [ScoresTable] WHERE *VAR* > 500

If it is possible, could you please show me how to achieve such results?

Thank you.

You may find it convenient to use two queries, one which has the calculations and a second query where you select the records with the calculations that are of interest (WHERE). — Fionnuala
– Fionnuala, Commented Jul 19, 2010 at 14:03
I do not know Access, but can't you just create a VIEW that gets columns Scores and Subject from ScoresTable and then query that VIEW instead of the ScoresTable? — heijp06
– heijp06, Commented Jul 19, 2010 at 14:20
@Peter van der Heijden, in Access, Views are usually called queries :) — Fionnuala
– Fionnuala, Commented Jul 19, 2010 at 14:23

HansUp · Accepted Answer · 2010-07-19 14:35:07Z

1

SELECT sq.Scores, sq.Subject
FROM (
    SELECT (Score1+Score2+Score3)*.5 AS Scores, Subject
    FROM ScoresTable
    ) AS sq
WHERE sq.Scores > 500;

If Score1, Score2, or Score3 can be Null, you may want to use the Nz function to substitute zero for Null.

answered Jul 19, 2010 at 14:35

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to Use Variables in Access SQL Queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related