Jet SQL Help

[Huber57]

To whom:

I am having trouble with translating T-SQL into Jet SQL.

I would like to write query that shows the avg question score by department.

So the query results would look like this:

Department Q1
MRI 3.5
XRay 3.7

Here is my query.

Select
'MRI' Department,
AVG(Question1) Q1
From
tblQuestions
Where
tblQuestions.date > Date() - 7
UNION
Select
'XRay' Department,
AVG(Question1) Q1
From
tblQuestions
Where
tblQuestions.date > Date() - 7

It doesn't work. Can anyone help me tweak it? Or am I totally screwed up?

Thanks.

 

[Bob Barrows]

To whom:

I am having trouble with translating T-SQL into Jet SQL.

I would like to write query that shows the avg question score by
department.

So the query results would look like this:

Department Q1
MRI 3.5
XRay 3.7

Here is my query.

Select
'MRI' Department,
AVG(Question1) Q1
From
tblQuestions
Where
tblQuestions.date > Date() - 7
UNION
Select
'XRay' Department,
AVG(Question1) Q1
From
tblQuestions
Where
tblQuestions.date > Date() - 7

It doesn't work. Can anyone help me tweak it? Or am I totally
screwed up?

Thanks.

Totally.
Why in the world would you use a union query instead of a group-by
query? This question applies to both T-SQL and JetSQL.

Also, why would you expect these unioned statements to give different
results?

You need to tell us how to differentiate the "MRI" questions from the
"XRay" ones. Is there a Department field in this tblQuestions table? If
so the following simple group-by query will give you the answer you
need:

select Department,AVG(Question1) Q1
from tblQuestions
group by Department
order by Department

Otherwise, you need to provide more details. Perhaps Question 1 applies
to MRI and a different question applies to XRay? If so, a union query is
required: you just need to specify the correct question in each select
statement.