Error Message

[Ripper]

I have a query that worked fine in access, but when I upsized it to SQL 2000
I get this error message that says a colmn(day) is invalid because it is not
contained in either an aggregate function or the Group BY clause. Here is
the SQL view of the query.

SELECT qryDaysAdvance.TheDate, Count(*) AS NumOf
FROM tblIssIn, qryDaysAdvance
WHERE (((qryDaysAdvance.TheDate) Between [StartDate] And [EndDate]))
GROUP BY qryDaysAdvance.TheDate
ORDER BY qryDaysAdvance.TheDate;

The query counts students who are in ISS by looking if their assignment is
during the current and future dates. It then provides a count of students by
date up to 40 days from the current date. It works perfectly in Access.
Anyone know how to fix this? I am trying to SQL 2000 to make this work.

 

[Sylvain Lafontaine]

Your query looks fine (albeit a little strange to use a cross-join to
compute the number of records (day) in a table). There is Day column in
your select query and you don't show us the schemas of your tables. Please
show us these schemas, tell us if one of these columns is named DAY and more
important, how did you upsize your database: are using a MDB or ACCDB file
with ODBC linked tables or an ADP project and how are you executing this
query (ordinary query, passthrough query, etc.).

 

[Sylvain Lafontaine]

Sorry, wrong button.

Your query looks fine; albeit it's a little strange to see a cross-join used
to compute the number of records (days) in a table. Maybe you could find a
more optimal way to get at this result.

I don't see why there is mention of the [Day] column in the error message
because you didn't provide us with the schemas of your tables. You should
also tell us how you upsized your database (with a MDB/ACCDB database file
with ODBC linked tables or with an ADP project) and how you are executing
this query in your code.