Most recent records - help

[Paul S]

I have a Select Query with the fields,

Date1 Name Pace

I want to find the three most recent Pace figures for each name.

I've trawled the newsgroup and the advice for similar posts is to order by
Date and set the TOP VALUES property to 3.

This does'nt work for me, as Access only picks out the three most recent
records; I want the three most recent for EACH name, as shown below;

DATE1 NAME PACE
12/03/04 JOE 6
10/02/04 JOE 5
09/01/04 JOE 7
19/03/04 BILL 8
15/02/04 BILL 2
10/01/04 BILL 6

Anyone any ideas.

 

[John Spencer (MVP)]

This requires a subquery.

SELECT Date1, [Name], Pace
FROM YourTable
WHERE YourTable.Date1 IN (
SELECT TOP 3 Date1
FROM YourTable as Tmp
WHERE T.[Name] = YourTable.[Name]
ORDER BY Date1 Desc)

Replace YourTable with the name of your table and the field names with the names
of your fields.

If you are using the query grid, you put the following in the criteria cell
under Date1
IN (SELECT TOP 3 Date1
FROM YourTable as Tmp
WHERE T.[Name] = YourTable.[Name]
ORDER BY Date1 Desc)

I've formatted it for easy reading, but you can remove the extra spaces and line
feeds when you enter this.

 

[Paul S]

John,

This did'nt work at first. A parameter box came up asking me to input a
value for T.[Name]. Did you mean me to type this bit or was T. shorthand
for YourTable?

I removed T. and the square brackets and it seems to work, although it
does seem to take a while to process. 10 seconds actually. Is the query
having to do a lot of work ( There are 4000 records in the main table)

I now want to display the output in the format;

NAME Pace1 Pace2 Pace3
Joe
Bill

I tried a Crosstab query but it did'nt work. It seems at least 3 items are
needed to make a Crosstab query

This requires a subquery.

SELECT Date1, [Name], Pace
FROM YourTable
WHERE YourTable.Date1 IN (
SELECT TOP 3 Date1
FROM YourTable as Tmp
WHERE T.[Name] = YourTable.[Name]
ORDER BY Date1 Desc)

Replace YourTable with the name of your table and the field names with the names
of your fields.

If you are using the query grid, you put the following in the criteria cell
under Date1
IN (SELECT TOP 3 Date1
FROM YourTable as Tmp
WHERE T.[Name] = YourTable.[Name]
ORDER BY Date1 Desc)

 

[John Spencer (MVP)]

Didn't work because I used TMP as an alias to the table and then mistyped "T"
instead of "Tmp".

And yes is it will take a little while to run. The subquery probably runs for
each record in the main query.

As for getting this into a crosstab query, you have a problem, since you don't
have a group of values that are a unique set.

You would get columns of 2,5,6,7, and 8 with the data you gave. It can be done
with some rather snarky code that would RANK the results returned for each name.

Your record set would end up with Name, DateRank, and Pace. Then you could use
that as the source for your crosstab. THAT gets to be really slow.

Let's save the first query as QryResults (or whatever name you gave it).

In a new query built on QryResults.

SELECT DCount("*","qryResults","[Name]=""" & [Name] & """ AND Date1 <=#" & Date1
& "#") as Rank,
[Name],
PACE
FROM QryResults

Save that and use it as the basis of your final crosstab query.

THere are other methods that will work, but I think this one may be the simplest
to understand.

John,

This did'nt work at first. A parameter box came up asking me to input a
value for T.[Name]. Did you mean me to type this bit or was T. shorthand
for YourTable?

I removed T. and the square brackets and it seems to work, although it
does seem to take a while to process. 10 seconds actually. Is the query
having to do a lot of work ( There are 4000 records in the main table)

I now want to display the output in the format;

NAME Pace1 Pace2 Pace3
Joe
Bill

I tried a Crosstab query but it did'nt work. It seems at least 3 items are
needed to make a Crosstab query

This requires a subquery.

SELECT Date1, [Name], Pace
FROM YourTable
WHERE YourTable.Date1 IN (
SELECT TOP 3 Date1
FROM YourTable as Tmp
WHERE T.[Name] = YourTable.[Name]
ORDER BY Date1 Desc)

Replace YourTable with the name of your table and the field names with the names
of your fields.

If you are using the query grid, you put the following in the criteria cell
under Date1
IN (SELECT TOP 3 Date1
FROM YourTable as Tmp
WHERE T.[Name] = YourTable.[Name]
ORDER BY Date1 Desc)

I've formatted it for easy reading, but you can remove the extra spaces and line
feeds when you enter this.

 

[Paul S]

I'd just like to say thanks for your help John. Much appreciated.

Didn't work because I used TMP as an alias to the table and then mistyped "T"
instead of "Tmp".

And yes is it will take a little while to run. The subquery probably runs for
each record in the main query.

As for getting this into a crosstab query, you have a problem, since you don't
have a group of values that are a unique set.

You would get columns of 2,5,6,7, and 8 with the data you gave. It can be done
with some rather snarky code that would RANK the results returned for each name.

Your record set would end up with Name, DateRank, and Pace. Then you could use
that as the source for your crosstab. THAT gets to be really slow.

Let's save the first query as QryResults (or whatever name you gave it).

In a new query built on QryResults.

SELECT DCount("*","qryResults","[Name]=""" & [Name] & """ AND Date1 <=#" & Date1
& "#") as Rank,
[Name],
PACE
FROM QryResults

Save that and use it as the basis of your final crosstab query.

THere are other methods that will work, but I think this one may be the simplest
to understand.

John,

This did'nt work at first. A parameter box came up asking me to input a
value for T.[Name]. Did you mean me to type this bit or was T. shorthand
for YourTable?

I removed T. and the square brackets and it seems to work, although it
does seem to take a while to process. 10 seconds actually. Is the query
having to do a lot of work ( There are 4000 records in the main table)

I now want to display the output in the format;

NAME Pace1 Pace2 Pace3
Joe
Bill

I tried a Crosstab query but it did'nt work. It seems at least 3 items are
needed to make a Crosstab query

This requires a subquery.

SELECT Date1, [Name], Pace
FROM YourTable
WHERE YourTable.Date1 IN (
SELECT TOP 3 Date1
FROM YourTable as Tmp
WHERE T.[Name] = YourTable.[Name]
ORDER BY Date1 Desc)

Replace YourTable with the name of your table and the field names with

the
names

of your fields.

If you are using the query grid, you put the following in the criteria cell
under Date1
IN (SELECT TOP 3 Date1
FROM YourTable as Tmp
WHERE T.[Name] = YourTable.[Name]
ORDER BY Date1 Desc)

I've formatted it for easy reading, but you can remove the extra spaces

and
line

feeds when you enter this.

Paul S wrote:

I have a Select Query with the fields,

Date1 Name Pace

I want to find the three most recent Pace figures for each name.

I've trawled the newsgroup and the advice for similar posts is to order by
Date and set the TOP VALUES property to 3.

This does'nt work for me, as Access only picks out the three most recent
records; I want the three most recent for EACH name, as shown below;

DATE1 NAME PACE
12/03/04 JOE 6
10/02/04 JOE 5
09/01/04 JOE 7
19/03/04 BILL 8
15/02/04 BILL 2
10/01/04 BILL 6

Anyone any ideas.