Query for First date in group

[mikebres]

My data is scan data that shows the LabelID, Scan Date Time (SDT) and an
event type for each scan. I want to find each unique First SDT that
corresponds to the event type "03" which is the Induction time. So the
result will be a list of each Induction date available that I can enter into
a combobox for data selection. Using the query below worked until I came
across one LabelID that had two "03" events. So now I have an indcution date
that is a mistake in the process. Which is good to know, but it doesn't give
me the dates I need for the selection.

So, how would I change this to get result I need?

SELECT DISTINCT Int([eSDT]) AS IndDates, data.eCode
FROM data
GROUP BY Int([eSDT]), data.eCode
HAVING (((data.eCode)="03"));

Thanks
Mike

 

[John Spencer]

I'm not sure what you are attempting to do. Perhaps the following

SELECT Min([eSDT]) AS IndDates
, data.eCode
FROM data
WHERE data.eCode="03"
GROUP BY data.eCode

If you are trying to strip off a time component then use

SELECT DateValue(Min([eSDT])) AS IndDates
, data.eCode
FROM data
WHERE data.eCode="03"
GROUP BY data.eCode


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[mikebres]

Hi John,

I tried the MIN function already, that just gives me a single date from the
very first LabelID.

Here is a sample of my data. For each LabelID there is a cerresponding
eCode of "03". I want to get the eSDT that corresponds to that eCode, which
is the induction date. Now after I do that you can see that there will be
several dates that are the same. For example I will have 20 LabelID with the
same Induction Dates, then maybe 30 with a different Induction Date. So now
I want to rollup the information on the dates so that I get only one instance
of each date. Like 4/30/08, 5/1/08, 5/5/08, 5/6/08, etc. Each date might
have many LabelIDs associated with them. Taking the MIN of each equal to
eCode ="03" works for most cases. But I have one LabelID that has two eCodes
of "03", see the second set of data, so I get one with 4/30/08 and one with
5/3/08. Well this item really started on 4/30/08 so I don't want it to show
the second date of 5/3/08, only the first date in the group that has an eCode
="03".

The end result I'm looking for is a list of dates that I can use to filter
the data based on the day it was inducted. So if I enter 4/30/08 I get all
of the items that were inducted on 4/30/08 and only them.

Thanks
Mike

LabelID, eCode, eSDT
03001290000026823367, 03, 4/30/2008 6:32:56 PM
03001290000026823367, 07, 5/3/2008 7:22:43 AM
03001290000026823367, 01, 5/3/2008 12:42:22 PM

03001290000026823381, 03, 4/30/2008 6:19:25 PM
03001290000026823381, 07, 5/3/2008 6:45:22 AM
03001290000026823381, 03, 5/3/2008 1:39:07 PM
03001290000165113633, 01, 5/14/2008 2:16:31 PM

I'm not sure what you are attempting to do. Perhaps the following

SELECT Min([eSDT]) AS IndDates
, data.eCode
FROM data
WHERE data.eCode="03"
GROUP BY data.eCode

If you are trying to strip off a time component then use

SELECT DateValue(Min([eSDT])) AS IndDates
, data.eCode
FROM data
WHERE data.eCode="03"
GROUP BY data.eCode


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

My data is scan data that shows the LabelID, Scan Date Time (SDT) and an
event type for each scan. I want to find each unique First SDT that
corresponds to the event type "03" which is the Induction time. So the
result will be a list of each Induction date available that I can enter into
a combobox for data selection. Using the query below worked until I came
across one LabelID that had two "03" events. So now I have an indcution date
that is a mistake in the process. Which is good to know, but it doesn't give
me the dates I need for the selection.

So, how would I change this to get result I need?

SELECT DISTINCT Int([eSDT]) AS IndDates, data.eCode
FROM data
GROUP BY Int([eSDT]), data.eCode
HAVING (((data.eCode)="03"));

Thanks
Mike