you tried to execute a query that does not include the specified expression...

[Moon]

Hi all,
I'm getting the error in the subject when I execute this query.


SELECT [Table 1].Key
FROM [Table 1] RIGHT JOIN [Query Date Visits] ON [Table 1].Key = [Query
Date Visits].Key
GROUP BY [Table 1].KeyPatient, [Table 1].Gender, [Query Date
Visits].elpsdDays
HAVING ((([Table 1].Gender)=1) AND (([Query Date
Visits].elpsdDays)>365))
ORDER BY [Query Date Visits].elpsdDays;

QUery Date Visit is as follow:

SELECT T1.Key, Max(T1.DatVisit) AS MaxOfDatVisit, Min(T1.DatVisit) AS
MinOfDatVisit, DateDiff("d",[MinOfDatVisit],[MaxOfDatVisit]) AS
elpsdDays
FROM [Table 1] AS T1
GROUP BY T1.Key
HAVING (((Max(T1.DatVisit)) Is Not Null) AND ((Min(T1.DatVisit)) Is Not
Null));

The above query finds the number of days between the max date and min
date.

The first query should return all the Keys with elpsdDays > 365 but I'm
getting the error instead.
Can someone tell me where I'm going wrong?
THanks so much,
Moon

 

[Van T. Dinh]

In the first posted Query, since [Table 1].Key in the selection list, it
*must* be in the GROUP BY clause. I am not sure whether it is a typing
mistake or not but you have "Key" in the selection list and "KeyPatient" in
the GROUP BY. Are they supposed to be the same Field.

I think you will also have problem with the second Query in the DateDiff
expression since you cannot use an Alias defined in the Query for
calculations in the same Query.

Use Divide & Conquer: Make sure the second Query works first before trying
to get first Query to work.

 

[John Spencer (MVP)]

Why not do it all in one query? I would think the following would work

SELECT T1.Key
FROM [Table 1] AS T1
WHERE T1.DatVisit is not Null
GROUP BY T1.Key
HAVING DateDiff("d",Min(DatVisit),Max(DatVisit))>365

 

[Moon]

Hi All,
Thanks so much for the help. Yes, I should have just used one query
which worked.Thanks again!

Why not do it all in one query? I would think the following would work

SELECT T1.Key
FROM [Table 1] AS T1
WHERE T1.DatVisit is not Null
GROUP BY T1.Key
HAVING DateDiff("d",Min(DatVisit),Max(DatVisit))>365

Hi all,
I'm getting the error in the subject when I execute this query.

SELECT [Table 1].Key
FROM [Table 1] RIGHT JOIN [Query Date Visits] ON [Table 1].Key = [Query
Date Visits].Key
GROUP BY [Table 1].KeyPatient, [Table 1].Gender, [Query Date
Visits].elpsdDays
HAVING ((([Table 1].Gender)=1) AND (([Query Date
Visits].elpsdDays)>365))
ORDER BY [Query Date Visits].elpsdDays;

QUery Date Visit is as follow:

SELECT T1.Key, Max(T1.DatVisit) AS MaxOfDatVisit, Min(T1.DatVisit) AS
MinOfDatVisit, DateDiff("d",[MinOfDatVisit],[MaxOfDatVisit]) AS
elpsdDays
FROM [Table 1] AS T1
GROUP BY T1.Key
HAVING (((Max(T1.DatVisit)) Is Not Null) AND ((Min(T1.DatVisit)) Is Not
Null));

The above query finds the number of days between the max date and min
date.

The first query should return all the Keys with elpsdDays > 365 but I'm
getting the error instead.
Can someone tell me where I'm going wrong?
THanks so much,
Moon