Get the LAST record from a group

  • Thread starter Thread starter Don Cardoza
  • Start date Start date
D

Don Cardoza

Hopefully I can explain myself well here.......

I want to get the HIGHEST or MAXIMUM date for each person listed in a query
prior to a date given by the user. Only one record for each person listed
should result in the query.

For example, I might have 3 fields in a table called (Name, Position,
Achievement Date, Primary key is an autonum)
Lets day I have this data
Don, Associate, 10/1/1998
Don, Counsel, 11/4/2000
Don, Director, 5/1/2003
Kate, Mailroom Clerk, 6/14/1999
Kate, Director 4/17/2000
Kate, CEO, 1/1/2003

If I make an textbox on a form where the user enters 12/31/2000..........
I want it too pull Don's Counsel record as well as Kate's Director record.
(Highest date value prior to a given date)

I tried to do a <TextBox criteria but of course it pulls up multiple records
for each person. Also tried the Group By, but could not figure it out. Can
anyone lead me in the right direction?

Thanks in Advance,
Don
 
Use a query; something like this should get you started (note that textbox
should be date/time format):

SELECT * FROM TableName
WHERE [Achievement Date] =
(SELECT TOP 1 [Achievement Date] FROM
TableName AS T
WHERE [Achievement Date] < [Forms]![FormName]![TextBoxName]
ORDER BY [Achievement Date] DESC);
 
Thanks for the input Ken....

I actually tried something similar. Unless I implemented it incorrectly,
this only gives me the top value in the entire table, not the top value for
each Name.

I wonder if the Group by Name comes into play here.
Back to the Drawing board........

Thanks again for the feedback though.

Ken Snell said:
Use a query; something like this should get you started (note that textbox
should be date/time format):

SELECT * FROM TableName
WHERE [Achievement Date] =
(SELECT TOP 1 [Achievement Date] FROM
TableName AS T
WHERE [Achievement Date] < [Forms]![FormName]![TextBoxName]
ORDER BY [Achievement Date] DESC);


--
Ken Snell
<MS ACCESS MVP>

Don Cardoza said:
Hopefully I can explain myself well here.......

I want to get the HIGHEST or MAXIMUM date for each person listed in a query
prior to a date given by the user. Only one record for each person listed
should result in the query.

For example, I might have 3 fields in a table called (Name, Position,
Achievement Date, Primary key is an autonum)
Lets day I have this data
Don, Associate, 10/1/1998
Don, Counsel, 11/4/2000
Don, Director, 5/1/2003
Kate, Mailroom Clerk, 6/14/1999
Kate, Director 4/17/2000
Kate, CEO, 1/1/2003

If I make an textbox on a form where the user enters 12/31/2000..........
I want it too pull Don's Counsel record as well as Kate's Director record.
(Highest date value prior to a given date)

I tried to do a <TextBox criteria but of course it pulls up multiple records
for each person. Also tried the Group By, but could not figure it out. Can
anyone lead me in the right direction?

Thanks in Advance,
Don
Click to expand...
Click to expand...
 
Perhaps a Crosstab Query would work here?

Ken Snell said:
Use a query; something like this should get you started (note that textbox
should be date/time format):

SELECT * FROM TableName
WHERE [Achievement Date] =
(SELECT TOP 1 [Achievement Date] FROM
TableName AS T
WHERE [Achievement Date] < [Forms]![FormName]![TextBoxName]
ORDER BY [Achievement Date] DESC);


--
Ken Snell
<MS ACCESS MVP>

Don Cardoza said:
Hopefully I can explain myself well here.......

I want to get the HIGHEST or MAXIMUM date for each person listed in a query
prior to a date given by the user. Only one record for each person listed
should result in the query.

For example, I might have 3 fields in a table called (Name, Position,
Achievement Date, Primary key is an autonum)
Lets day I have this data
Don, Associate, 10/1/1998
Don, Counsel, 11/4/2000
Don, Director, 5/1/2003
Kate, Mailroom Clerk, 6/14/1999
Kate, Director 4/17/2000
Kate, CEO, 1/1/2003

If I make an textbox on a form where the user enters 12/31/2000..........
I want it too pull Don's Counsel record as well as Kate's Director record.
(Highest date value prior to a given date)

I tried to do a <TextBox criteria but of course it pulls up multiple records
for each person. Also tried the Group By, but could not figure it out. Can
anyone lead me in the right direction?

Thanks in Advance,
Don
Click to expand...
Click to expand...
 
No, I don't think a Cross-Tab Query is appropriate here. See Access Help on
Cross-Tab Query.

I think Ken is very close to it, try:

****Untested SQL****
PARAMETERS [Forms]![FormName]![TextBoxName] DateTime;
SELECT M.[Name], M.Position, M.[Achievement Date]
FROM YourTable As M
WHERE M.[Achievement Date] =
(
SELECT Max(S.[Achievement Date])
FROM YourTable As S
WHERE S.[Name] = M.[Name]
AND S.[Achievement Date] < [Forms]![FormName]![TextBoxName]
)
****SQL ends****

This is a Query with a *correlated* SubQuery. Unfortunately, the SubQuery
is executed for each Record in the Main Query so it may be slow. Make sure
you index the Fields [Name] and [Achievement Date].

There may be more efficient DQL Strings.
 
Thanks, Van. ... I did leave out the Name = Name part.

Sorry, Don.... I was typing without completely careful thinking!

--
Ken Snell
<MS ACCESS MVP>

Van T. Dinh said:
No, I don't think a Cross-Tab Query is appropriate here. See Access Help on
Cross-Tab Query.

I think Ken is very close to it, try:

****Untested SQL****
PARAMETERS [Forms]![FormName]![TextBoxName] DateTime;
SELECT M.[Name], M.Position, M.[Achievement Date]
FROM YourTable As M
WHERE M.[Achievement Date] =
(
SELECT Max(S.[Achievement Date])
FROM YourTable As S
WHERE S.[Name] = M.[Name]
AND S.[Achievement Date] < [Forms]![FormName]![TextBoxName]
)
****SQL ends****

This is a Query with a *correlated* SubQuery. Unfortunately, the SubQuery
is executed for each Record in the Main Query so it may be slow. Make sure
you index the Fields [Name] and [Achievement Date].

There may be more efficient DQL Strings.

--
HTH
Van T. Dinh
MVP (Access)



Don Cardoza said:
Perhaps a Crosstab Query would work here?
Click to expand...
Click to expand...
 
****Untested SQL****
PARAMETERS [Forms]![FormName]![TextBoxName] DateTime;
SELECT M.[Name], M.Position, M.[Achievement Date]
FROM YourTable As M
WHERE M.[Achievement Date] =
(
SELECT Max(S.[Achievement Date])
FROM YourTable As S
WHERE S.[Name] = M.[Name]
AND S.[Achievement Date] < [Forms]![FormName]![TextBoxName]
)
****SQL ends****

This is a Query with a *correlated* SubQuery. Unfortunately, the SubQuery
is executed for each Record in the Main Query so it may be slow. Make sure
you index the Fields [Name] and [Achievement Date].
Click to expand...

The query will run faster if the subquery is saved as a separate query, and
then the above SQL uses the stored query instead of the subquery.

For example, create this query and name it qry_MaxDatePerPerson

PARAMETERS [Forms]![FormName]![TextBoxName] DateTime;
SELECT Max(S.[Achievement Date]) AS MaxADate, S.[Name]
FROM YourTable As S
WHERE S.[Achievement Date] < [Forms]![FormName]![TextBoxName]
GROUP BY S.[Name];


Then change Van's query to this:

SELECT M.[Name], M.Position, M.[Achievement Date]
FROM YourTable As M INNER JOIN qry_MaxDatePerPerson AS S
ON M.[Achievement Date] = S.[MaxADate] AND
M.[Name] = S.[Name];


Of course, the caveat is, "UNTESTED"!
 
Ken and Van....

Thanks for the input. Although this is not the best way
by far, it seems to work (now to implement it in the real
database....). Ultimately, I had to create three
queries! I only have about 300 records, so I don't think
it will slow it down too much.

But here are the SQL statements after I designed them in
the design view. I'll change the date to reflect a text
box on a form.

Query 1:
SELECT Table.Name, Table.Position, Table.Date
FROM

WHERE (((Table.Date)<#11/1/2002#));

Query 2:
SELECT Query1.Name, Max(Query1.Date) AS MaxOfDate
FROM Query1
GROUP BY Query1.Name;

Query 3
SELECT Query2.Name, Query2.MaxOfDate, Table.Position
FROM Query2 LEFT JOIN
ON Query2.MaxOfDate =
Table.Date;

Thanks.

Eventually, I'll figure out a way to combine all of these
into one query.


-----Original Message-----
"Van T. Dinh"
****Untested SQL****
PARAMETERS [Forms]![FormName]![TextBoxName] DateTime;
SELECT M.[Name], M.Position, M.[Achievement Date]
FROM YourTable As M
WHERE M.[Achievement Date] =
(
SELECT Max(S.[Achievement Date])
FROM YourTable As S
WHERE S.[Name] = M.[Name]
AND S.[Achievement Date] < [Forms]![FormName]! [TextBoxName]
)
****SQL ends****

This is a Query with a *correlated* SubQuery.
Click to expand...
Unfortunately, the
SubQuery
is executed for each Record in the Main Query so it may
Click to expand...
be slow. Make
sure
you index the Fields [Name] and [Achievement Date].
Click to expand...

The query will run faster if the subquery is saved as a separate query, and
then the above SQL uses the stored query instead of the subquery.

For example, create this query and name it qry_MaxDatePerPerson

PARAMETERS [Forms]![FormName]![TextBoxName] DateTime;
SELECT Max(S.[Achievement Date]) AS MaxADate, S.[Name]
FROM YourTable As S
WHERE S.[Achievement Date] < [Forms]![FormName]! [TextBoxName]
GROUP BY S.[Name];


Then change Van's query to this:

SELECT M.[Name], M.Position, M.[Achievement Date]
FROM YourTable As M INNER JOIN qry_MaxDatePerPerson AS S
ON M.[Achievement Date] = S.[MaxADate] AND
M.[Name] = S.[Name];


Of course, the caveat is, "UNTESTED"!

--
Ken Snell
<MS ACCESS MVP>


.
Click to expand...
 
You can combine Q1 & Q2 together as:

SELECT Table.Name, Max(Table.Date) AS MaxOfDate
FROM Table
WHERE (((Table.Date)<#11/1/2002#))
GROUP BY Table.Name;

In fact, try combining all into ONE Parametrised Query
with the SQL String:

PARAMETERS [Forms]![FormName]![TextBoxName] DateTime;
SELECT T1.[Name], T1.Position, T1.[Achievement Date]
FROM YourTable As T1
INNER JOIN
(
SELECT T2.[Name], Max(T2.[Achievement Date])
AS MaxOfDate
FROM YourTable As T2
WHERE (T2.[Achievement Date] <
[Forms]![FormName]![TextBoxName])
GROUP BY T2.[Name]
) As SQ
ON T1.[Name] = SQ.[Name]
AND T1.[Achievement Date] = SQ.MaxOfDate

HTH
Van T. Dinh
MVP (Access)
 
Back
Top