Need top 1 time...

[imtheodore]

Ok, using the table below I need only the first temp for a patient

patient temp time
1 98 8:00
2 97.5 9:15
2 98 9:18
2 99.1 9:22
3 98.6 5:15
3 99 5:27
4 97 6:12
.....

My results should be:

Patient Temp Time
1 98 8:00
2 97.5 9:15
3 98.6 5:15
4 97 6:12

Can this be done?
Thanks,
Dave

 

[georgedwilkinson]

select Patient, Temp, Min(Time)
From DavesTable
Group By Patient, Temp;

Hopefully "Time" is a date/time type. If not, you will have to convert it.

Also, there might be a problem using a field named "Time", since it is a reserved word in some systems.

 

[boblarson]

select Patient, Temp, Min(Time)
From DavesTable
Group By Patient, Temp;

Hopefully "Time" is a date/time type. If not, you will have to convert it.

Also, there might be a problem using a field named "Time", since it is a reserved word in some systems.

You'd definitely have to enclose Time in square brackets in this case

Min([Time])

And I agree you need to change the name of the field. Here's a list of Reserved Words in Access - things to NOT use:
http://www.allenbrowne.com/AppIssueBadWord.html

 

[SQL_Hell]

select Patient, Temp, Min(Time)
From DavesTable
Group By Patient, Temp;

Hopefully "Time" is a date/time type. If not, you will have to convert it.

Also, there might be a problem using a field named "Time", since it is a reserved word in some systems.

Yep

Reserved word alert!! use square brackets at your own peril (well not peril exactly, they are just annoying to type)

 

[dkinley]

Okay ... I've attached a demo.

For the query, I used the 'Totals' to filter out the records you didn't want to see. I've set 'Min' to the time to get the reading that occurred first. I've set 'Group By' to SomeNumber and 'First' to Temp. This will limit to only one entry per SomeNumber for the output in your first post.

-dK

EDIT: Things went weird on the post- had too many winders opened.

 

[imtheodore]

Unfortunately the software I'm using does use "Time" as a field so I must deal with it. I do still, however, get multiple patients, I need only one temp per patient.
If I remove the "min" before time I get the same number of records, it does not affect the query.

 

[boblarson]

You'll probably have to do something similar to this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=158105
where you have to get the min FIRST and then link back to get the rest of the data.

 

[georgedwilkinson]

Do you mind posting your revised SQL? According to the information you gave us, this should work.

 

[imtheodore]

Okay ... I've attached a demo.

For the query, I used the 'Totals' to filter out the records you didn't want to see. I've set 'Min' to the time to get the reading that occurred first. I've set 'Group By' to SomeNumber and 'First' to Temp. This will limit to only one entry per SomeNumber for the output in your first post.

-dK

EDIT: Things went weird on the post- had too many winders opened.

This appears to work and is pretty slick! I'll do some more reconcilliation, but I think this will do it. Thank You!