By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,829 Members | 1,798 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,829 IT Pros & Developers. It's quick & easy.

group by query help

P: n/a
I have to identify students who have attended more than one school
within a school district in the same academic year between certain
dates. I have a table with stuID, enterdate, leavedate and want to add
field "acctStatus". I need to update the AcctStatus field with a value
to designate those students who enrolled by 10/14/2002, attended more
than one school, and either did not leave (leavedate will be NULL) or
left after 5/1/03. These are the students who will be included in
calculation of district test accountability reports.
Students who attended more than one school will have a record for each
school attended (schoolnum is also a field).
I can do a groupby on studID and then have countofSchoolnum > 1 that
gives me the list of stuID's for those attending more than one school.
but i still can't get how to then select ONLY those who entered before
the given date and (did not leave or left after given date). I tried
min of enterdate and that seems to filter out that part but using min
of leavedate seems to filter out all the records (and it doesn't pick
up the null leavedates).
My table only has records for one district.
Any help would be most appreciated.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
js*****@sc.rr.com (John Segars) wrote in message news:<e1**************************@posting.google. com>...
.... but i still can't get how to then select ONLY those who entered
before
the given date and (did not leave or left after given date). I tried
min of enterdate and that seems to filter out that part but using min
of leavedate seems to filter out all the records (and it doesn't pick
up the null leavedates)...


If I understand you correctly, try adding the "arrival" and "leaving"
dates as columns in your QBE Grid and in the "Totals" row, where it
says "Group By", change it to read "Where". Then enter your criteria
in the criteria row, and away you go. Note that "Where" columns are
automatically hidden.

As a final resort, you could consider using one query to restrict the
rows, and a second to do the grouping based on the first query.

HTH

Regards
Stuart
Nov 12 '05 #2

P: n/a
I had tried that by putting Min(arrival) < #10/14/02# in arrival
column, and then max(leavedate) > "5/1/03# in leave column. But this
doesn't show any records then, even though I know there are some. The
other complication is that leave dates are null if the student did not
leave before end of school year and Max apparently ignores null
values.
ap**********@paradise.net.nz (Stuart MacKinnon) wrote in message news:<56**************************@posting.google. com>...
js*****@sc.rr.com (John Segars) wrote in message news:<e1**************************@posting.google. com>...
... but i still can't get how to then select ONLY those who entered
before
the given date and (did not leave or left after given date). I tried
min of enterdate and that seems to filter out that part but using min
of leavedate seems to filter out all the records (and it doesn't pick
up the null leavedates)...


If I understand you correctly, try adding the "arrival" and "leaving"
dates as columns in your QBE Grid and in the "Totals" row, where it
says "Group By", change it to read "Where". Then enter your criteria
in the criteria row, and away you go. Note that "Where" columns are
automatically hidden.

As a final resort, you could consider using one query to restrict the
rows, and a second to do the grouping based on the first query.

HTH

Regards
Stuart

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.