473,320 Members | 1,940 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Can max be used on a date field?

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
5 8550
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ricky | last post by:
I have a table with a field type date and null is set to yes. How can I insert a blank value without it being save as 0000-00-00. thanks Ricky
4
by: peashoe | last post by:
I have an asp page that uses a calendar.js (pop-up) file to add an exact date format in the text field (txtDDate). My problem is I need some javascript that sets an alert that does not allow them...
1
by: Liz Malcolm | last post by:
Hello and TIA. I have a DE form with an option group that if daily is selected todays date is used for start and end date, if weekly is selected Monday - Friday is used. I am trying to add a...
3
by: Kevin Hodgson | last post by:
I'm trying to import from a text file into a Sql DB, but I'm getting hung up on a date field. My text file has the date in the dd/mm/yyyy format. I Cast that field to CDate in VB.NET, which sets...
1
by: Graham Feeley | last post by:
I have a date field named rcdate it is a general date eg: 12/08/2006 3:30:00 PM 12/08/2006 3:00:00 PM I used to update another empty field named rdate with the with using in a update query "...
2
by: JKTLK | last post by:
I have been gone from working on databases for about 10 years, and have forgotten a lot of what used to come naturally to me. I have to learn some of the simple things again-- and my usual...
4
by: Miaaa Mukherjee | last post by:
I m using ASP.NET 2005 with VB,C#. I m using GridView to display the data. My problem is with the database field date. I want to use only the date and the fields are manufacturing date and...
7
by: sheri | last post by:
I have a field called date, the date is in the field like this 70925 (which means Sept. 25, 2007). I have another field called day, it is a text field. How do I write a query to populate the day...
10
by: Jes | last post by:
Dear all I have a date field on a HTML form where the user is asked to key in dd/mm/yyyy However, when that is written to MySql it is either not accepted or another value is tored in the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.