Group by "Min" or "First"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

(my computer hiccupped when i tried to send this a few minutes ago so this
may be a repeat)...

i have a table (actually it's a query) i'm using as a source for this
current query.

the source file contains more than two columns, but the only two i think we
need focus on are "LastName" and "FollowUp" (a date field).

the records in the source can have repeats of "LastName" and what i want to
do is output a table/query result that captures the earliest value of the
"FollowUp" date column for each unique person (i.e. "LastName").

i thought i understood groupby and the like, but this just isn't working
today.

thoughts?
 
i'll just add the contents of my (test) source table below and show you which
rows the query would output:

here's the 'data' (actually, the working data would incorporate many more
columns would be included in the final output, but the same rows as shown
below would be output).

"Last_Name" "Months" " On_Study_Date" "FollowUp"
"Alkazar" 15 8/20/2004 11/21/2005 <--- output this one
"Alkazar" 18 8/20/2004 2/20/2006
"Alkazar" 21 8/20/2004 5/19/2006
"Alkazar" 24 8/20/2004 8/21/2006
"Alkazar" 30 8/20/2004 2/20/2007
"Alkazar" 36 8/20/2004 8/20/2007
"Alkazar" 42 8/20/2004 2/20/2008
"Alkazar" 48 8/20/2004 8/20/2008
"Alkazar" 54 8/20/2004 2/20/2009
"Alkazar" 60 8/20/2004 8/20/2009
"Alkazar" 72 8/20/2004 8/20/2010
"Alkazar" 84 8/20/2004 8/19/2011
"Alkazar" 96 8/20/2004 8/20/2012
"Alkazar" 108 8/20/2004 8/20/2013
"Alkazar" 120 8/20/2004 8/20/2014
"Mordred" 21 4/29/2004 1/30/2006 < ---- and output this one
"Mordred" 24 4/29/2004 4/28/2006
"Mordred" 30 4/29/2004 10/30/2006
"Mordred" 36 4/29/2004 4/30/2007
"Mordred" 42 4/29/2004 10/29/2007
"Mordred" 48 4/29/2004 4/29/2008
"Mordred" 54 4/29/2004 10/29/2008
"Mordred" 60 4/29/2004 4/29/2009
"Mordred" 72 4/29/2004 4/29/2010
"Mordred" 84 4/29/2004 4/29/2011
"Mordred" 96 4/29/2004 4/30/2012
"Mordred" 108 4/29/2004 4/29/2013
"Mordred" 120 4/29/2004 4/29/2014
"Valdez" 21 5/3/2004 2/3/2006 <---- and output this one
"Valdez" 24 5/3/2004 5/3/2006
"Valdez" 30 5/3/2004 11/3/2006
"Valdez" 36 5/3/2004 5/3/2007
"Valdez" 42 5/3/2004 11/2/2007
"Valdez" 48 5/3/2004 5/2/2008
"Valdez" 54 5/3/2004 11/3/2008
"Valdez" 60 5/3/2004 5/4/2009
"Valdez" 72 5/3/2004 5/3/2010
"Valdez" 84 5/3/2004 5/3/2011
"Valdez" 96 5/3/2004 5/3/2012
"Valdez" 108 5/3/2004 5/3/2013
"Valdez" 120 5/3/2004 5/2/2014
"Zanzibar" 21 5/3/2004 2/3/2006 <-- and output this one
"Zanzibar" 24 5/3/2004 5/3/2006
"Zanzibar" 30 5/3/2004 11/3/2006
"Zanzibar" 36 5/3/2004 5/3/2007
"Zanzibar" 42 5/3/2004 11/2/2007
"Zanzibar" 48 5/3/2004 5/2/2008
"Zanzibar" 54 5/3/2004 11/3/2008
"Zanzibar" 60 5/3/2004 5/4/2009
"Zanzibar" 72 5/3/2004 5/3/2010
"Zanzibar" 84 5/3/2004 5/3/2011
"Zanzibar" 96 5/3/2004 5/3/2012
"Zanzibar" 108 5/3/2004 5/3/2013
"Zanzibar" 120 5/3/2004 5/2/2014
 
In your new query, show the other query.
Now drag Last Name and FollowUp to the query grid. Click the totals button
(greek sum symbol). Both Last Name and FollowUp will say Group By. Change
Group By to Max or Last under FollowUp. This should work, assuming your
data type in FollowUp is a date.

HTH;

Amy
 
Dear Ted:

The function First() will somewhat randomly select one of the rows. It is
not sensitive to the actual order in which the rows were added to the table.
I strongly recommend against using it.

If FollowUp represents the sequencing of the rows within the group, then
MIN(FollowUp) will give you the value that is minimal.

For each "unique person" by which you group, this will then produce the
earliest date in FollowUp. As far as any other columns you want to display,
you must either aggregate or group by each of them. If you group by any of
them, then the query will find the earliest FollowUp date for each
combination of all the columns by which the query is grouped. The point is,
what you are doing with the other columns you want to see may seriously
affect what results are given.

You could perform a MIN() on other columns, but that would NOT result in you
seeing the values in the specific row (or rows) where the FollowUp date is
the earliest one. Rather, it would aggregate (in this case, find the
minimum value) of each of those other columns without respect to which of
the grouped rows contains that value. It would typically provide a result
in which the columns contain values from different rows.

In order to show values from a specific row where the FollowUp date is the
minimum for the group, a correlated subquery is required. If this is what
you want, please post the query you have so far and I'll try to modify that
to perform what you want.

If there is more than one row for the same LastName and FollowUp value, then
there may be more than one row that gives the earliest FollowUp for a given
LastName. The query would reflect this fact.

Tom Ellison
 
hi amy,

i don't think it does. i tried it and it returns the same 54 records
arranged in the same way. what i did was use the old query as the input
source until i remembered that you proposed i use just two columns and not
all of them as i had tried initially (because i actually want the data from
every column in my final product). with that query out of the way, i then
proceeded to create an additional query using a join the 2-column query with
the query that i used in the one you described and set the values of the
"FirstOfFollowUp" to the "FollowUp" date which (voila) seems to give me the
earliest follow-up date and the data on each individual in one row for every
one.

thanks!
 
Assuming that the field FollowUp is the one that determines which record to
return out of the group.

SELECT Last_Name, Months, On_Study_Date, Followup
FROM TheTable
WHERE TheTable.Followup IN
(SELECT Min(Followup)
FROM TheTable as TMP
WHERE TMP.LastName = TheTable.Last_Name)
 
hi tom,

i performed a modified version of the same query using the "Min" function
and the results are unchanged....however, i am prepared to use the "Min"
function for all the reasons you said.

i doubt there would be a duplicate of the "LastName" field. this was a
contrived field i created as a surrogate for the unique identifier in the
production database.
 
purdear john,

your approach seemed to encapsulate the essence of this thing so i thought i
would give it a whirl, and using the 'full' naming convention along with the
total complement of fields in this (albeit for-testing-purposes-only) version
of database, i cobbled the equivalent code you contributed into the SQL query
viewer

SELECT [Sort by Month].[Last Name], [Sort by Month].[First Name], [Sort by
Month].[IRB#], [Sort by Month].[Study #], [Sort by Month].[Seq#], [Sort by
Month].Months, [Sort by Month].[On-Study Date], [Sort by Month].Schedule,
[Sort by Month].[F/U Status], [Sort by Month].FollowUp
FROM [Sort by Month]
WHERE ((([Sort by Month].FollowUp) In (SELECT Min(FollowUp)
FROM [Sort by Month] as TMP
WHERE TMP.[Last Name] = [Sort by Month].[Last Name])));

since i'm a relative access newbie perhaps there's something i'm failing to
understand, but nothing happened when i clicked the "!" mark?

with best regards,

-ted
 
Nothing happened?
Does that mean the query ran and returned no records? or that the screen
still showed the query text? or that you got an error message?

Reformatting your query and stripping out the extra parentheses, I end up
with the following. And it looks as if it should work.
SELECT [Sort by Month].[Last Name],
[Sort by Month].[First Name],
[Sort by Month].[IRB#],
[Sort by Month].[Study #],
[Sort by Month].[Seq#],
[Sort by Month].Months,
[Sort by Month].[On-Study Date],
[Sort by Month].Schedule,
[Sort by Month].[F/U Status],
[Sort by Month].FollowUp
FROM [Sort by Month]
WHERE [Sort by Month].FollowUp In
(SELECT Min(TMP.FollowUp)
FROM [Sort by Month] as TMP
WHERE TMP.[Last Name] = [Sort by Month].[Last Name])

I am suspicious of just using Last Name to match on, but you know your data,
so I would guess this would work. I also assume that [Sort by Month] has
data in it. Is Sort By Month a table or a query?


Ted said:
purdear john,

your approach seemed to encapsulate the essence of this thing so i thought
i
would give it a whirl, and using the 'full' naming convention along with
the
total complement of fields in this (albeit for-testing-purposes-only)
version
of database, i cobbled the equivalent code you contributed into the SQL
query
viewer

SELECT [Sort by Month].[Last Name], [Sort by Month].[First Name], [Sort by
Month].[IRB#], [Sort by Month].[Study #], [Sort by Month].[Seq#], [Sort by
Month].Months, [Sort by Month].[On-Study Date], [Sort by Month].Schedule,
[Sort by Month].[F/U Status], [Sort by Month].FollowUp
FROM [Sort by Month]
WHERE ((([Sort by Month].FollowUp) In (SELECT Min(FollowUp)
FROM [Sort by Month] as TMP
WHERE TMP.[Last Name] = [Sort by Month].[Last Name])));

since i'm a relative access newbie perhaps there's something i'm failing
to
understand, but nothing happened when i clicked the "!" mark?

with best regards,

-ted

John Spencer said:
Assuming that the field FollowUp is the one that determines which record
to
return out of the group.

SELECT Last_Name, Months, On_Study_Date, Followup
FROM TheTable
WHERE TheTable.Followup IN
(SELECT Min(Followup)
FROM TheTable as TMP
WHERE TMP.LastName = TheTable.Last_Name)
Click to expand...
Click to expand...
 
dear john,

i pasted in the formatted SQL text you added to your last email in the SQL
viewer and can report that whether i run it from that view or the design
view, 'nothing' happens -- the system/screen sort of what i call 'blinks' at
one and no results are forthcoming.

these data are found in a 'sandbox' i created for purposes of testing the
approach to get the intended results. and as i recollect, amy's approach
worked with the data in it.

lastly, Sort By Month is a query and not a table. is this somehow
shortcircuiting all this?

with best regards,

-ted

John Spencer said:
Nothing happened?
Does that mean the query ran and returned no records? or that the screen
still showed the query text? or that you got an error message?

Reformatting your query and stripping out the extra parentheses, I end up
with the following. And it looks as if it should work.
SELECT [Sort by Month].[Last Name],
[Sort by Month].[First Name],
[Sort by Month].[IRB#],
[Sort by Month].[Study #],
[Sort by Month].[Seq#],
[Sort by Month].Months,
[Sort by Month].[On-Study Date],
[Sort by Month].Schedule,
[Sort by Month].[F/U Status],
[Sort by Month].FollowUp
FROM [Sort by Month]
WHERE [Sort by Month].FollowUp In
(SELECT Min(TMP.FollowUp)
FROM [Sort by Month] as TMP
WHERE TMP.[Last Name] = [Sort by Month].[Last Name])

I am suspicious of just using Last Name to match on, but you know your data,
so I would guess this would work. I also assume that [Sort by Month] has
data in it. Is Sort By Month a table or a query?


Ted said:
purdear john,

your approach seemed to encapsulate the essence of this thing so i thought
i
would give it a whirl, and using the 'full' naming convention along with
the
total complement of fields in this (albeit for-testing-purposes-only)
version
of database, i cobbled the equivalent code you contributed into the SQL
query
viewer

SELECT [Sort by Month].[Last Name], [Sort by Month].[First Name], [Sort by
Month].[IRB#], [Sort by Month].[Study #], [Sort by Month].[Seq#], [Sort by
Month].Months, [Sort by Month].[On-Study Date], [Sort by Month].Schedule,
[Sort by Month].[F/U Status], [Sort by Month].FollowUp
FROM [Sort by Month]
WHERE ((([Sort by Month].FollowUp) In (SELECT Min(FollowUp)
FROM [Sort by Month] as TMP
WHERE TMP.[Last Name] = [Sort by Month].[Last Name])));

since i'm a relative access newbie perhaps there's something i'm failing
to
understand, but nothing happened when i clicked the "!" mark?

with best regards,

-ted

John Spencer said:
Assuming that the field FollowUp is the one that determines which record
to
return out of the group.

SELECT Last_Name, Months, On_Study_Date, Followup
FROM TheTable
WHERE TheTable.Followup IN
(SELECT Min(Followup)
FROM TheTable as TMP
WHERE TMP.LastName = TheTable.Last_Name)


i'll just add the contents of my (test) source table below and show you
which
rows the query would output:

here's the 'data' (actually, the working data would incorporate many
more
columns would be included in the final output, but the same rows as
shown
below would be output).

"Last_Name" "Months" " On_Study_Date" "FollowUp"
"Alkazar" 15 8/20/2004 11/21/2005 <--- output this one
"Alkazar" 18 8/20/2004 2/20/2006
"Alkazar" 21 8/20/2004 5/19/2006
"Alkazar" 24 8/20/2004 8/21/2006
"Alkazar" 30 8/20/2004 2/20/2007
"Alkazar" 36 8/20/2004 8/20/2007
"Alkazar" 42 8/20/2004 2/20/2008
"Alkazar" 48 8/20/2004 8/20/2008
"Alkazar" 54 8/20/2004 2/20/2009
"Alkazar" 60 8/20/2004 8/20/2009
"Alkazar" 72 8/20/2004 8/20/2010
"Alkazar" 84 8/20/2004 8/19/2011
"Alkazar" 96 8/20/2004 8/20/2012
"Alkazar" 108 8/20/2004 8/20/2013
"Alkazar" 120 8/20/2004 8/20/2014
"Mordred" 21 4/29/2004 1/30/2006 < ---- and output this one
"Mordred" 24 4/29/2004 4/28/2006
"Mordred" 30 4/29/2004 10/30/2006
"Mordred" 36 4/29/2004 4/30/2007
"Mordred" 42 4/29/2004 10/29/2007
"Mordred" 48 4/29/2004 4/29/2008
"Mordred" 54 4/29/2004 10/29/2008
"Mordred" 60 4/29/2004 4/29/2009
"Mordred" 72 4/29/2004 4/29/2010
"Mordred" 84 4/29/2004 4/29/2011
"Mordred" 96 4/29/2004 4/30/2012
"Mordred" 108 4/29/2004 4/29/2013
"Mordred" 120 4/29/2004 4/29/2014
"Valdez" 21 5/3/2004 2/3/2006 <---- and output this one
"Valdez" 24 5/3/2004 5/3/2006
"Valdez" 30 5/3/2004 11/3/2006
"Valdez" 36 5/3/2004 5/3/2007
"Valdez" 42 5/3/2004 11/2/2007
"Valdez" 48 5/3/2004 5/2/2008
"Valdez" 54 5/3/2004 11/3/2008
"Valdez" 60 5/3/2004 5/4/2009
"Valdez" 72 5/3/2004 5/3/2010
"Valdez" 84 5/3/2004 5/3/2011
"Valdez" 96 5/3/2004 5/3/2012
"Valdez" 108 5/3/2004 5/3/2013
"Valdez" 120 5/3/2004 5/2/2014
"Zanzibar" 21 5/3/2004 2/3/2006 <-- and output this one
"Zanzibar" 24 5/3/2004 5/3/2006
"Zanzibar" 30 5/3/2004 11/3/2006
"Zanzibar" 36 5/3/2004 5/3/2007
"Zanzibar" 42 5/3/2004 11/2/2007
"Zanzibar" 48 5/3/2004 5/2/2008
"Zanzibar" 54 5/3/2004 11/3/2008
"Zanzibar" 60 5/3/2004 5/4/2009
"Zanzibar" 72 5/3/2004 5/3/2010
"Zanzibar" 84 5/3/2004 5/3/2011
"Zanzibar" 96 5/3/2004 5/3/2012
"Zanzibar" 108 5/3/2004 5/3/2013
"Zanzibar" 120 5/3/2004 5/2/2014


:

(my computer hiccupped when i tried to send this a few minutes ago so
this
may be a repeat)...

i have a table (actually it's a query) i'm using as a source for this
current query.

the source file contains more than two columns, but the only two i
think
we
need focus on are "LastName" and "FollowUp" (a date field).

the records in the source can have repeats of "LastName" and what i
want
to
do is output a table/query result that captures the earliest value of
the
"FollowUp" date column for each unique person (i.e. "LastName").

i thought i understood groupby and the like, but this just isn't
working
today.

thoughts?
Click to expand...
Click to expand...
Click to expand...
 
If you run the following, do you get data returned?

SELECT * FROM [Sort By Month]

If you try the following, do you get data returned?

SELECT [Last Name],
Min(FollowUp) as EarlyDate
FROM [Sort By Month]
GROUP BY [Last Name]

If so, try saving the latter and using it in an inner join query as follows

SELECT S.*
FROM [Sort By Month] as S
INNER JOIN SavedQueryName as Q
ON S.[Last Name] = Q.[Last Name] AND
S.Followup = Q.EarlyDate

If none of that works, then I am stuck.


Ted said:
dear john,

i pasted in the formatted SQL text you added to your last email in the SQL
viewer and can report that whether i run it from that view or the design
view, 'nothing' happens -- the system/screen sort of what i call 'blinks'
at
one and no results are forthcoming.

these data are found in a 'sandbox' i created for purposes of testing the
approach to get the intended results. and as i recollect, amy's approach
worked with the data in it.

lastly, Sort By Month is a query and not a table. is this somehow
shortcircuiting all this?

with best regards,

-ted

John Spencer said:
Nothing happened?
Does that mean the query ran and returned no records? or that the screen
still showed the query text? or that you got an error message?

Reformatting your query and stripping out the extra parentheses, I end up
with the following. And it looks as if it should work.
SELECT [Sort by Month].[Last Name],
[Sort by Month].[First Name],
[Sort by Month].[IRB#],
[Sort by Month].[Study #],
[Sort by Month].[Seq#],
[Sort by Month].Months,
[Sort by Month].[On-Study Date],
[Sort by Month].Schedule,
[Sort by Month].[F/U Status],
[Sort by Month].FollowUp
FROM [Sort by Month]
WHERE [Sort by Month].FollowUp In
(SELECT Min(TMP.FollowUp)
FROM [Sort by Month] as TMP
WHERE TMP.[Last Name] = [Sort by Month].[Last Name])

I am suspicious of just using Last Name to match on, but you know your
data,
so I would guess this would work. I also assume that [Sort by Month] has
data in it. Is Sort By Month a table or a query?


Ted said:
purdear john,

your approach seemed to encapsulate the essence of this thing so i
thought
i
would give it a whirl, and using the 'full' naming convention along
with
the
total complement of fields in this (albeit for-testing-purposes-only)
version
of database, i cobbled the equivalent code you contributed into the SQL
query
viewer

SELECT [Sort by Month].[Last Name], [Sort by Month].[First Name], [Sort
by
Month].[IRB#], [Sort by Month].[Study #], [Sort by Month].[Seq#], [Sort
by
Month].Months, [Sort by Month].[On-Study Date], [Sort by
Month].Schedule,
[Sort by Month].[F/U Status], [Sort by Month].FollowUp
FROM [Sort by Month]
WHERE ((([Sort by Month].FollowUp) In (SELECT Min(FollowUp)
FROM [Sort by Month] as TMP
WHERE TMP.[Last Name] = [Sort by Month].[Last Name])));

since i'm a relative access newbie perhaps there's something i'm
failing
to
understand, but nothing happened when i clicked the "!" mark?

with best regards,

-ted

:

Assuming that the field FollowUp is the one that determines which
record
to
return out of the group.

SELECT Last_Name, Months, On_Study_Date, Followup
FROM TheTable
WHERE TheTable.Followup IN
(SELECT Min(Followup)
FROM TheTable as TMP
WHERE TMP.LastName = TheTable.Last_Name)


i'll just add the contents of my (test) source table below and show
you
which
rows the query would output:

here's the 'data' (actually, the working data would incorporate many
more
columns would be included in the final output, but the same rows as
shown
below would be output).

"Last_Name" "Months" " On_Study_Date" "FollowUp"
"Alkazar" 15 8/20/2004 11/21/2005 <--- output this one
"Alkazar" 18 8/20/2004 2/20/2006
"Alkazar" 21 8/20/2004 5/19/2006
"Alkazar" 24 8/20/2004 8/21/2006
"Alkazar" 30 8/20/2004 2/20/2007
"Alkazar" 36 8/20/2004 8/20/2007
"Alkazar" 42 8/20/2004 2/20/2008
"Alkazar" 48 8/20/2004 8/20/2008
"Alkazar" 54 8/20/2004 2/20/2009
"Alkazar" 60 8/20/2004 8/20/2009
"Alkazar" 72 8/20/2004 8/20/2010
"Alkazar" 84 8/20/2004 8/19/2011
"Alkazar" 96 8/20/2004 8/20/2012
"Alkazar" 108 8/20/2004 8/20/2013
"Alkazar" 120 8/20/2004 8/20/2014
"Mordred" 21 4/29/2004 1/30/2006 < ---- and output this one
"Mordred" 24 4/29/2004 4/28/2006
"Mordred" 30 4/29/2004 10/30/2006
"Mordred" 36 4/29/2004 4/30/2007
"Mordred" 42 4/29/2004 10/29/2007
"Mordred" 48 4/29/2004 4/29/2008
"Mordred" 54 4/29/2004 10/29/2008
"Mordred" 60 4/29/2004 4/29/2009
"Mordred" 72 4/29/2004 4/29/2010
"Mordred" 84 4/29/2004 4/29/2011
"Mordred" 96 4/29/2004 4/30/2012
"Mordred" 108 4/29/2004 4/29/2013
"Mordred" 120 4/29/2004 4/29/2014
"Valdez" 21 5/3/2004 2/3/2006 <---- and output this one
"Valdez" 24 5/3/2004 5/3/2006
"Valdez" 30 5/3/2004 11/3/2006
"Valdez" 36 5/3/2004 5/3/2007
"Valdez" 42 5/3/2004 11/2/2007
"Valdez" 48 5/3/2004 5/2/2008
"Valdez" 54 5/3/2004 11/3/2008
"Valdez" 60 5/3/2004 5/4/2009
"Valdez" 72 5/3/2004 5/3/2010
"Valdez" 84 5/3/2004 5/3/2011
"Valdez" 96 5/3/2004 5/3/2012
"Valdez" 108 5/3/2004 5/3/2013
"Valdez" 120 5/3/2004 5/2/2014
"Zanzibar" 21 5/3/2004 2/3/2006 <-- and output this one
"Zanzibar" 24 5/3/2004 5/3/2006
"Zanzibar" 30 5/3/2004 11/3/2006
"Zanzibar" 36 5/3/2004 5/3/2007
"Zanzibar" 42 5/3/2004 11/2/2007
"Zanzibar" 48 5/3/2004 5/2/2008
"Zanzibar" 54 5/3/2004 11/3/2008
"Zanzibar" 60 5/3/2004 5/4/2009
"Zanzibar" 72 5/3/2004 5/3/2010
"Zanzibar" 84 5/3/2004 5/3/2011
"Zanzibar" 96 5/3/2004 5/3/2012
"Zanzibar" 108 5/3/2004 5/3/2013
"Zanzibar" 120 5/3/2004 5/2/2014


:

(my computer hiccupped when i tried to send this a few minutes ago
so
this
may be a repeat)...

i have a table (actually it's a query) i'm using as a source for
this
current query.

the source file contains more than two columns, but the only two i
think
we
need focus on are "LastName" and "FollowUp" (a date field).

the records in the source can have repeats of "LastName" and what i
want
to
do is output a table/query result that captures the earliest value
of
the
"FollowUp" date column for each unique person (i.e. "LastName").

i thought i understood groupby and the like, but this just isn't
working
today.

thoughts?
Click to expand...
Click to expand...
Click to expand...
 
she works, john!

many thanks uu :-)

-ted

John Spencer said:
If you run the following, do you get data returned?

SELECT * FROM [Sort By Month]

If you try the following, do you get data returned?

SELECT [Last Name],
Min(FollowUp) as EarlyDate
FROM [Sort By Month]
GROUP BY [Last Name]

If so, try saving the latter and using it in an inner join query as follows

SELECT S.*
FROM [Sort By Month] as S
INNER JOIN SavedQueryName as Q
ON S.[Last Name] = Q.[Last Name] AND
S.Followup = Q.EarlyDate

If none of that works, then I am stuck.


Ted said:
dear john,

i pasted in the formatted SQL text you added to your last email in the SQL
viewer and can report that whether i run it from that view or the design
view, 'nothing' happens -- the system/screen sort of what i call 'blinks'
at
one and no results are forthcoming.

these data are found in a 'sandbox' i created for purposes of testing the
approach to get the intended results. and as i recollect, amy's approach
worked with the data in it.

lastly, Sort By Month is a query and not a table. is this somehow
shortcircuiting all this?

with best regards,

-ted

John Spencer said:
Nothing happened?
Does that mean the query ran and returned no records? or that the screen
still showed the query text? or that you got an error message?

Reformatting your query and stripping out the extra parentheses, I end up
with the following. And it looks as if it should work.
SELECT [Sort by Month].[Last Name],
[Sort by Month].[First Name],
[Sort by Month].[IRB#],
[Sort by Month].[Study #],
[Sort by Month].[Seq#],
[Sort by Month].Months,
[Sort by Month].[On-Study Date],
[Sort by Month].Schedule,
[Sort by Month].[F/U Status],
[Sort by Month].FollowUp
FROM [Sort by Month]
WHERE [Sort by Month].FollowUp In
(SELECT Min(TMP.FollowUp)
FROM [Sort by Month] as TMP
WHERE TMP.[Last Name] = [Sort by Month].[Last Name])

I am suspicious of just using Last Name to match on, but you know your
data,
so I would guess this would work. I also assume that [Sort by Month] has
data in it. Is Sort By Month a table or a query?


purdear john,

your approach seemed to encapsulate the essence of this thing so i
thought
i
would give it a whirl, and using the 'full' naming convention along
with
the
total complement of fields in this (albeit for-testing-purposes-only)
version
of database, i cobbled the equivalent code you contributed into the SQL
query
viewer

SELECT [Sort by Month].[Last Name], [Sort by Month].[First Name], [Sort
by
Month].[IRB#], [Sort by Month].[Study #], [Sort by Month].[Seq#], [Sort
by
Month].Months, [Sort by Month].[On-Study Date], [Sort by
Month].Schedule,
[Sort by Month].[F/U Status], [Sort by Month].FollowUp
FROM [Sort by Month]
WHERE ((([Sort by Month].FollowUp) In (SELECT Min(FollowUp)
FROM [Sort by Month] as TMP
WHERE TMP.[Last Name] = [Sort by Month].[Last Name])));

since i'm a relative access newbie perhaps there's something i'm
failing
to
understand, but nothing happened when i clicked the "!" mark?

with best regards,

-ted

:

Assuming that the field FollowUp is the one that determines which
record
to
return out of the group.

SELECT Last_Name, Months, On_Study_Date, Followup
FROM TheTable
WHERE TheTable.Followup IN
(SELECT Min(Followup)
FROM TheTable as TMP
WHERE TMP.LastName = TheTable.Last_Name)


i'll just add the contents of my (test) source table below and show
you
which
rows the query would output:

here's the 'data' (actually, the working data would incorporate many
more
columns would be included in the final output, but the same rows as
shown
below would be output).

"Last_Name" "Months" " On_Study_Date" "FollowUp"
"Alkazar" 15 8/20/2004 11/21/2005 <--- output this one
"Alkazar" 18 8/20/2004 2/20/2006
"Alkazar" 21 8/20/2004 5/19/2006
"Alkazar" 24 8/20/2004 8/21/2006
"Alkazar" 30 8/20/2004 2/20/2007
"Alkazar" 36 8/20/2004 8/20/2007
"Alkazar" 42 8/20/2004 2/20/2008
"Alkazar" 48 8/20/2004 8/20/2008
"Alkazar" 54 8/20/2004 2/20/2009
"Alkazar" 60 8/20/2004 8/20/2009
"Alkazar" 72 8/20/2004 8/20/2010
"Alkazar" 84 8/20/2004 8/19/2011
"Alkazar" 96 8/20/2004 8/20/2012
"Alkazar" 108 8/20/2004 8/20/2013
"Alkazar" 120 8/20/2004 8/20/2014
"Mordred" 21 4/29/2004 1/30/2006 < ---- and output this one
"Mordred" 24 4/29/2004 4/28/2006
"Mordred" 30 4/29/2004 10/30/2006
"Mordred" 36 4/29/2004 4/30/2007
"Mordred" 42 4/29/2004 10/29/2007
"Mordred" 48 4/29/2004 4/29/2008
"Mordred" 54 4/29/2004 10/29/2008
"Mordred" 60 4/29/2004 4/29/2009
"Mordred" 72 4/29/2004 4/29/2010
"Mordred" 84 4/29/2004 4/29/2011
"Mordred" 96 4/29/2004 4/30/2012
"Mordred" 108 4/29/2004 4/29/2013
"Mordred" 120 4/29/2004 4/29/2014
"Valdez" 21 5/3/2004 2/3/2006 <---- and output this one
"Valdez" 24 5/3/2004 5/3/2006
"Valdez" 30 5/3/2004 11/3/2006
"Valdez" 36 5/3/2004 5/3/2007
"Valdez" 42 5/3/2004 11/2/2007
"Valdez" 48 5/3/2004 5/2/2008
"Valdez" 54 5/3/2004 11/3/2008
"Valdez" 60 5/3/2004 5/4/2009
"Valdez" 72 5/3/2004 5/3/2010
"Valdez" 84 5/3/2004 5/3/2011
"Valdez" 96 5/3/2004 5/3/2012
"Valdez" 108 5/3/2004 5/3/2013
"Valdez" 120 5/3/2004 5/2/2014
"Zanzibar" 21 5/3/2004 2/3/2006 <-- and output this one
"Zanzibar" 24 5/3/2004 5/3/2006
"Zanzibar" 30 5/3/2004 11/3/2006
"Zanzibar" 36 5/3/2004 5/3/2007
"Zanzibar" 42 5/3/2004 11/2/2007
"Zanzibar" 48 5/3/2004 5/2/2008
"Zanzibar" 54 5/3/2004 11/3/2008
"Zanzibar" 60 5/3/2004 5/4/2009
"Zanzibar" 72 5/3/2004 5/3/2010
"Zanzibar" 84 5/3/2004 5/3/2011
"Zanzibar" 96 5/3/2004 5/3/2012
"Zanzibar" 108 5/3/2004 5/3/2013
"Zanzibar" 120 5/3/2004 5/2/2014


:

(my computer hiccupped when i tried to send this a few minutes ago
so
this
may be a repeat)...

i have a table (actually it's a query) i'm using as a source for
this
current query.

the source file contains more than two columns, but the only two i
think
we
need focus on are "LastName" and "FollowUp" (a date field).

the records in the source can have repeats of "LastName" and what i
want
to
do is output a table/query result that captures the earliest value
of
the
"FollowUp" date column for each unique person (i.e. "LastName").

i thought i understood groupby and the like, but this just isn't
working
today.

thoughts?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top