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

Can max be used on a date field?

P: n/a
Hi all,
I am a little stumped. I have a query I am trying to run to retrieve
the last Progress Note record from the database for the current
patient so that the therapists can see the last note while typing the
current note. This seemed to be simple to me, since I figured, you
take the max(TxDate) that exists before my current date and all should
be fine.
I tried that and got all the records that exist for this patient
before that date.

Here is my data:

ProgressNoteID TxWeekStartDate Notes
1828 11/5/2003 this is the fifth one of the motnhe so that i can
1829 11/7/2003 this is the seventh of the month and i think the
1831 11/9/2003 this is for the ninght of the monthe?
1830 11/10/2003 this is the note fro the tenth of the month
1827 11/16/2003 this is for the sixteenth so check it out if this
1826 11/18/2003 test on the eighteen tho see if this works properly

Here is the select that returns all these rows:

SELECT ProgressNotes.ProgressNoteID,
max(ProgressNotes.TxWeekStartDate), ProgressNotes.Notes
FROM ProgressNotes
WHERE ((ProgressNotes.PatientID)=241) AND
((ProgressNotes.DisciplineID)=1)
AND ((ProgressNotes.AdmissionID)=360) AND
((ProgressNotes.NoteType)=2)
GROUP BY ProgressNotes.TxWeekStartDate, ProgressNotes.Notes,
ProgressNotes.ProgressNoteID
ORDER BY ProgressNotes.TxWeekStartDate;

So I tried to put in a subquery to bring up the max date as follows:
SELECT max(ProgressNotes.ProgressNoteID) AS ProgressNoteID1,
ProgressNotes.TxWeekStartDate, ProgressNotes.Notes
FROM ProgressNotes
WHERE ((ProgressNotes.PatientID)=241) AND
((ProgressNotes.DisciplineID)=1)
AND ((ProgressNotes.AdmissionID)=360) AND ((ProgressNotes.NoteType)=2)
AND TxWeekStartDate = (Select max(TxWeekstartDate) from ProgressNotes
where TxWeekStartDate < #11/16/2003# )
GROUP BY ProgressNotes.TxWeekStartDate, ProgressNotes.Notes,
ProgressNotes.ProgressNoteID
ORDER BY ProgressNotes.TxWeekStartDate;

And this brings back no results. It seems like max is not working on a
datevalue. I tried doing last instead of max - bring back the last
record, but this gives funny results. It keeps bringing back the
record from 11/9/2003 since this is the one that seems to have the
latest datevalue as opposed to bringing back the 11/10/2003 record.
How can I get this right? Anyone have a suggestion?

I would appreciate your help.
Thank you.

bayla
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
I don't think you need any of those GROUP BY fields.
What they are doing is saying "give me a separate record for each value of
this field".
For example, you have included ProgressNotes.ProgressNoteID in your GROUP BY
fields.
This means your resulting recordset will have one record for each value of
ProgressNotes.ProgressNoteID -
so no grouping will happen!
To remove the GROUP BY in the query grid, change the Totals line from GROUP
BY to WHERE.

HTH
- Turtle

"Bayla Frankl" <ba****@hotmail.com> wrote in message
news:77**************************@posting.google.c om...
Hi all,
I am a little stumped. I have a query I am trying to run to retrieve
the last Progress Note record from the database for the current
patient so that the therapists can see the last note while typing the
current note. This seemed to be simple to me, since I figured, you
take the max(TxDate) that exists before my current date and all should
be fine.
I tried that and got all the records that exist for this patient
before that date.

Here is my data:

ProgressNoteID TxWeekStartDate Notes
1828 11/5/2003 this is the fifth one of the motnhe so that i can
1829 11/7/2003 this is the seventh of the month and i think the
1831 11/9/2003 this is for the ninght of the monthe?
1830 11/10/2003 this is the note fro the tenth of the month
1827 11/16/2003 this is for the sixteenth so check it out if this
1826 11/18/2003 test on the eighteen tho see if this works properly

Here is the select that returns all these rows:

SELECT ProgressNotes.ProgressNoteID,
max(ProgressNotes.TxWeekStartDate), ProgressNotes.Notes
FROM ProgressNotes
WHERE ((ProgressNotes.PatientID)=241) AND
((ProgressNotes.DisciplineID)=1)
AND ((ProgressNotes.AdmissionID)=360) AND
((ProgressNotes.NoteType)=2)
GROUP BY ProgressNotes.TxWeekStartDate, ProgressNotes.Notes,
ProgressNotes.ProgressNoteID
ORDER BY ProgressNotes.TxWeekStartDate;

So I tried to put in a subquery to bring up the max date as follows:
SELECT max(ProgressNotes.ProgressNoteID) AS ProgressNoteID1,
ProgressNotes.TxWeekStartDate, ProgressNotes.Notes
FROM ProgressNotes
WHERE ((ProgressNotes.PatientID)=241) AND
((ProgressNotes.DisciplineID)=1)
AND ((ProgressNotes.AdmissionID)=360) AND ((ProgressNotes.NoteType)=2)
AND TxWeekStartDate = (Select max(TxWeekstartDate) from ProgressNotes
where TxWeekStartDate < #11/16/2003# )
GROUP BY ProgressNotes.TxWeekStartDate, ProgressNotes.Notes,
ProgressNotes.ProgressNoteID
ORDER BY ProgressNotes.TxWeekStartDate;

And this brings back no results. It seems like max is not working on a
datevalue. I tried doing last instead of max - bring back the last
record, but this gives funny results. It keeps bringing back the
record from 11/9/2003 since this is the one that seems to have the
latest datevalue as opposed to bringing back the 11/10/2003 record.
How can I get this right? Anyone have a suggestion?

I would appreciate your help.
Thank you.

bayla

Nov 12 '05 #2

P: n/a
Access will not allow me to put a MAX in my selection unless every
field is in the GROUP BY list, It gives the error " You tried to
execute a query that does not include the specified expression
'ProgressNoteID' as part of an aggregate function". But I don't see
how this would solve my problem either way...

Thanks for your help.
Bayla

"MacDermott" <ma********@nospam.com> wrote in message news:<jH*****************@newsread2.news.atl.earth link.net>...
I don't think you need any of those GROUP BY fields.
What they are doing is saying "give me a separate record for each value of
this field".
For example, you have included ProgressNotes.ProgressNoteID in your GROUP BY
fields.
This means your resulting recordset will have one record for each value of
ProgressNotes.ProgressNoteID -
so no grouping will happen!
To remove the GROUP BY in the query grid, change the Totals line from GROUP
BY to WHERE.

HTH
- Turtle

"Bayla Frankl" <ba****@hotmail.com> wrote in message
news:77**************************@posting.google.c om...
Hi all,
I am a little stumped. I have a query I am trying to run to retrieve
the last Progress Note record from the database for the current
patient so that the therapists can see the last note while typing the
current note. This seemed to be simple to me, since I figured, you
take the max(TxDate) that exists before my current date and all should
be fine.
I tried that and got all the records that exist for this patient
before that date.

Here is my data:

ProgressNoteID TxWeekStartDate Notes
1828 11/5/2003 this is the fifth one of the motnhe so that i can
1829 11/7/2003 this is the seventh of the month and i think the
1831 11/9/2003 this is for the ninght of the monthe?
1830 11/10/2003 this is the note fro the tenth of the month
1827 11/16/2003 this is for the sixteenth so check it out if this
1826 11/18/2003 test on the eighteen tho see if this works properly

Here is the select that returns all these rows:

SELECT ProgressNotes.ProgressNoteID,
max(ProgressNotes.TxWeekStartDate), ProgressNotes.Notes
FROM ProgressNotes
WHERE ((ProgressNotes.PatientID)=241) AND
((ProgressNotes.DisciplineID)=1)
AND ((ProgressNotes.AdmissionID)=360) AND
((ProgressNotes.NoteType)=2)
GROUP BY ProgressNotes.TxWeekStartDate, ProgressNotes.Notes,
ProgressNotes.ProgressNoteID
ORDER BY ProgressNotes.TxWeekStartDate;

So I tried to put in a subquery to bring up the max date as follows:
SELECT max(ProgressNotes.ProgressNoteID) AS ProgressNoteID1,
ProgressNotes.TxWeekStartDate, ProgressNotes.Notes
FROM ProgressNotes
WHERE ((ProgressNotes.PatientID)=241) AND
((ProgressNotes.DisciplineID)=1)
AND ((ProgressNotes.AdmissionID)=360) AND ((ProgressNotes.NoteType)=2)
AND TxWeekStartDate = (Select max(TxWeekstartDate) from ProgressNotes
where TxWeekStartDate < #11/16/2003# )
GROUP BY ProgressNotes.TxWeekStartDate, ProgressNotes.Notes,
ProgressNotes.ProgressNoteID
ORDER BY ProgressNotes.TxWeekStartDate;

And this brings back no results. It seems like max is not working on a
datevalue. I tried doing last instead of max - bring back the last
record, but this gives funny results. It keeps bringing back the
record from 11/9/2003 since this is the one that seems to have the
latest datevalue as opposed to bringing back the 11/10/2003 record.
How can I get this right? Anyone have a suggestion?

I would appreciate your help.
Thank you.

bayla

Nov 12 '05 #3

P: n/a
Bayla Frankl previously wrote:
Hi all,
I am a little stumped. I have a query I am trying to run to retrieve
the last Progress Note record from the database for the current
patient so that the therapists can see the last note while typing the
current note. This seemed to be simple to me, since I figured, you
take the max(TxDate) that exists before my current date and all should
be fine.
I tried that and got all the records that exist for this patient
before that date.

Here is my data:

ProgressNoteID TxWeekStartDate Notes
1828 11/5/2003 this is the fifth one of the motnhe so that i can
1829 11/7/2003 this is the seventh of the month and i think the
1831 11/9/2003 this is for the ninght of the monthe?
1830 11/10/2003 this is the note fro the tenth of the month
1827 11/16/2003 this is for the sixteenth so check it out if this
1826 11/18/2003 test on the eighteen tho see if this works properly

If you just want the last record then use a Top Values query with the
number set to 1. Sort your query by date descending.
It's the box in the middle of the toolbar when you are designing a query.

Regards

Peter Russell

Nov 12 '05 #4

P: n/a
As Peter Russell points out, using TOP is the simplest way of getting the
information you want.

To use a MAX, you'll have to return just the date field (remove the X from
the Show column for fields which have WHERE in the Totals column).
You can then build another query which will return the remaining fields for
the record you identified in your first query.

HTH
- Turtle

"Bayla Frankl" <ba****@hotmail.com> wrote in message
news:77**************************@posting.google.c om...
Access will not allow me to put a MAX in my selection unless every
field is in the GROUP BY list, It gives the error " You tried to
execute a query that does not include the specified expression
'ProgressNoteID' as part of an aggregate function". But I don't see
how this would solve my problem either way...

Thanks for your help.
Bayla

"MacDermott" <ma********@nospam.com> wrote in message

news:<jH*****************@newsread2.news.atl.earth link.net>...
I don't think you need any of those GROUP BY fields.
What they are doing is saying "give me a separate record for each value of this field".
For example, you have included ProgressNotes.ProgressNoteID in your GROUP BY fields.
This means your resulting recordset will have one record for each value of ProgressNotes.ProgressNoteID -
so no grouping will happen!
To remove the GROUP BY in the query grid, change the Totals line from GROUP BY to WHERE.

HTH
- Turtle

"Bayla Frankl" <ba****@hotmail.com> wrote in message
news:77**************************@posting.google.c om...
Hi all,
I am a little stumped. I have a query I am trying to run to retrieve
the last Progress Note record from the database for the current
patient so that the therapists can see the last note while typing the
current note. This seemed to be simple to me, since I figured, you
take the max(TxDate) that exists before my current date and all should
be fine.
I tried that and got all the records that exist for this patient
before that date.

Here is my data:

ProgressNoteID TxWeekStartDate Notes
1828 11/5/2003 this is the fifth one of the motnhe so that i can
1829 11/7/2003 this is the seventh of the month and i think the
1831 11/9/2003 this is for the ninght of the monthe?
1830 11/10/2003 this is the note fro the tenth of the month
1827 11/16/2003 this is for the sixteenth so check it out if this
1826 11/18/2003 test on the eighteen tho see if this works properly

Here is the select that returns all these rows:

SELECT ProgressNotes.ProgressNoteID,
max(ProgressNotes.TxWeekStartDate), ProgressNotes.Notes
FROM ProgressNotes
WHERE ((ProgressNotes.PatientID)=241) AND
((ProgressNotes.DisciplineID)=1)
AND ((ProgressNotes.AdmissionID)=360) AND
((ProgressNotes.NoteType)=2)
GROUP BY ProgressNotes.TxWeekStartDate, ProgressNotes.Notes,
ProgressNotes.ProgressNoteID
ORDER BY ProgressNotes.TxWeekStartDate;

So I tried to put in a subquery to bring up the max date as follows:
SELECT max(ProgressNotes.ProgressNoteID) AS ProgressNoteID1,
ProgressNotes.TxWeekStartDate, ProgressNotes.Notes
FROM ProgressNotes
WHERE ((ProgressNotes.PatientID)=241) AND
((ProgressNotes.DisciplineID)=1)
AND ((ProgressNotes.AdmissionID)=360) AND ((ProgressNotes.NoteType)=2)
AND TxWeekStartDate = (Select max(TxWeekstartDate) from ProgressNotes
where TxWeekStartDate < #11/16/2003# )
GROUP BY ProgressNotes.TxWeekStartDate, ProgressNotes.Notes,
ProgressNotes.ProgressNoteID
ORDER BY ProgressNotes.TxWeekStartDate;

And this brings back no results. It seems like max is not working on a
datevalue. I tried doing last instead of max - bring back the last
record, but this gives funny results. It keeps bringing back the
record from 11/9/2003 since this is the one that seems to have the
latest datevalue as opposed to bringing back the 11/10/2003 record.
How can I get this right? Anyone have a suggestion?

I would appreciate your help.
Thank you.

bayla

Nov 12 '05 #5

P: n/a
Thanks for responding.
I ended up breaking it into two queries, since one seems not to allow
the max to work properly on the date. Now I get the maxDate and then
do another query to get the corresponding progressNote and info.

Thanks again.
Bayla

ba****@hotmail.com (Bayla Frankl) wrote in message news:<77**************************@posting.google. com>...
Access will not allow me to put a MAX in my selection unless every
field is in the GROUP BY list, It gives the error " You tried to
execute a query that does not include the specified expression
'ProgressNoteID' as part of an aggregate function". But I don't see
how this would solve my problem either way...

Thanks for your help.
Bayla

"MacDermott" <ma********@nospam.com> wrote in message news:<jH*****************@newsread2.news.atl.earth link.net>...
I don't think you need any of those GROUP BY fields.
What they are doing is saying "give me a separate record for each value of
this field".
For example, you have included ProgressNotes.ProgressNoteID in your GROUP BY
fields.
This means your resulting recordset will have one record for each value of
ProgressNotes.ProgressNoteID -
so no grouping will happen!
To remove the GROUP BY in the query grid, change the Totals line from GROUP
BY to WHERE.

HTH
- Turtle

"Bayla Frankl" <ba****@hotmail.com> wrote in message
news:77**************************@posting.google.c om...
Hi all,
I am a little stumped. I have a query I am trying to run to retrieve
the last Progress Note record from the database for the current
patient so that the therapists can see the last note while typing the
current note. This seemed to be simple to me, since I figured, you
take the max(TxDate) that exists before my current date and all should
be fine.
I tried that and got all the records that exist for this patient
before that date.

Here is my data:

ProgressNoteID TxWeekStartDate Notes
1828 11/5/2003 this is the fifth one of the motnhe so that i can
1829 11/7/2003 this is the seventh of the month and i think the
1831 11/9/2003 this is for the ninght of the monthe?
1830 11/10/2003 this is the note fro the tenth of the month
1827 11/16/2003 this is for the sixteenth so check it out if this
1826 11/18/2003 test on the eighteen tho see if this works properly

Here is the select that returns all these rows:

SELECT ProgressNotes.ProgressNoteID,
max(ProgressNotes.TxWeekStartDate), ProgressNotes.Notes
FROM ProgressNotes
WHERE ((ProgressNotes.PatientID)=241) AND
((ProgressNotes.DisciplineID)=1)
AND ((ProgressNotes.AdmissionID)=360) AND
((ProgressNotes.NoteType)=2)
GROUP BY ProgressNotes.TxWeekStartDate, ProgressNotes.Notes,
ProgressNotes.ProgressNoteID
ORDER BY ProgressNotes.TxWeekStartDate;

So I tried to put in a subquery to bring up the max date as follows:
SELECT max(ProgressNotes.ProgressNoteID) AS ProgressNoteID1,
ProgressNotes.TxWeekStartDate, ProgressNotes.Notes
FROM ProgressNotes
WHERE ((ProgressNotes.PatientID)=241) AND
((ProgressNotes.DisciplineID)=1)
AND ((ProgressNotes.AdmissionID)=360) AND ((ProgressNotes.NoteType)=2)
AND TxWeekStartDate = (Select max(TxWeekstartDate) from ProgressNotes
where TxWeekStartDate < #11/16/2003# )
GROUP BY ProgressNotes.TxWeekStartDate, ProgressNotes.Notes,
ProgressNotes.ProgressNoteID
ORDER BY ProgressNotes.TxWeekStartDate;

And this brings back no results. It seems like max is not working on a
datevalue. I tried doing last instead of max - bring back the last
record, but this gives funny results. It keeps bringing back the
record from 11/9/2003 since this is the one that seems to have the
latest datevalue as opposed to bringing back the 11/10/2003 record.
How can I get this right? Anyone have a suggestion?

I would appreciate your help.
Thank you.

bayla

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.