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.

 

[MGFoster]

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe the query that answers your implied question would be:

select Department, AVG(Question1) As Q1
from tblQuestions
where [date] > Date() - 7
and Department In ('MRI', 'XRay')
group by Department
order by Department

"Date" is a bad name for a column name since it is also a VBA function.
Also, good table design suggests that column names be more descriptive.
Therefore, the column name should be something like 'invoice_date' or
'inspection_date' or 'question_date,' etc.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSjA594echKqOuFEgEQLwJQCeNUG9wkHWGgDbclJOaRZ8/Zm1kNUAn0tf
GymkyB1n79aU/8kmo2g06pog
=tQJ7
-----END PGP SIGNATURE-----