Group By Query

[KPR]

Problem I've always faced in Access....
I want to group by LastName and then show the [Max] date, and then bring out
the ID of the record that contained the [Max] date.

LName Date ID
Smith 01/01/1900 1111
Smith 03/01/1900 2222
Smith 04/01/1900 3333

I want my query to output.....Smith | 04/01/1900 | 3333

Thanks,
Ken

 

[Wayne-I-M]

SELECT TableName.LName, Max(TableName.Date) AS MaxOfDate, Max(TableName.ID)
AS MaxOfID
FROM TableName
GROUP BY TableName.LName;

 

[KARL DEWEY]

Try this --
SELECT [YourTable].*
FROM [YourTable]
WHERE ((([YourTable].[Date])=(SELECT Max([Date]) FROM [YourTable] as
Temp WHERE Temp.[ID]= [YourTable].[ID])));

You should not be using 'Date' as a field name as it is a reserved word and
may cause problems. Enclose in brackets to reduce the risk.

 

[Wayne-I-M]

Sorry clicked send before I should have ooops

Create the 1st query then create another link this

SELECT [1stQuery].LName, [1stQuery].MaxOfDate, TableName.ID
FROM TableName INNER JOIN 1stQuery ON (TableName.Date =
[1stQuery].MaxOfDate) AND (TableName.LName = [1stQuery].LName);

This will give you the answer every time

--
Wayne
Manchester, England.

SELECT TableName.LName, Max(TableName.Date) AS MaxOfDate, Max(TableName.ID)
AS MaxOfID
FROM TableName
GROUP BY TableName.LName;



--
Wayne
Manchester, England.

Problem I've always faced in Access....
I want to group by LastName and then show the [Max] date, and then bring out
the ID of the record that contained the [Max] date.

LName Date ID
Smith 01/01/1900 1111
Smith 03/01/1900 2222
Smith 04/01/1900 3333

I want my query to output.....Smith | 04/01/1900 | 3333

Thanks,
Ken

 

[KPR]

Thanks Wayne but the ID isn't always the Max ID

SELECT TableName.LName, Max(TableName.Date) AS MaxOfDate, Max(TableName.ID)
AS MaxOfID
FROM TableName
GROUP BY TableName.LName;



--
Wayne
Manchester, England.

Problem I've always faced in Access....
I want to group by LastName and then show the [Max] date, and then bring out
the ID of the record that contained the [Max] date.

LName Date ID
Smith 01/01/1900 1111
Smith 03/01/1900 2222
Smith 04/01/1900 3333

I want my query to output.....Smith | 04/01/1900 | 3333

Thanks,
Ken

 

[KPR]

That did it...thanks very much for your help.

Sorry clicked send before I should have ooops

Create the 1st query then create another link this

SELECT [1stQuery].LName, [1stQuery].MaxOfDate, TableName.ID
FROM TableName INNER JOIN 1stQuery ON (TableName.Date =
[1stQuery].MaxOfDate) AND (TableName.LName = [1stQuery].LName);

This will give you the answer every time

--
Wayne
Manchester, England.

SELECT TableName.LName, Max(TableName.Date) AS MaxOfDate, Max(TableName.ID)
AS MaxOfID
FROM TableName
GROUP BY TableName.LName;



--
Wayne
Manchester, England.

Problem I've always faced in Access....
I want to group by LastName and then show the [Max] date, and then bring out
the ID of the record that contained the [Max] date.

LName Date ID
Smith 01/01/1900 1111
Smith 03/01/1900 2222
Smith 04/01/1900 3333

I want my query to output.....Smith | 04/01/1900 | 3333

Thanks,
Ken

 

[Wayne-I-M]

Thats why I used a 2nd query. The first query gives you the max you wanted
and the 2nd just gives you the ID of the records filtered in the 1st.

Open the 2nd query in design view and make sure you have copied the sql i
gave.

In the 2nd query you need both the Table and the 1st query then double link
(sorry i don't know th right english word for that)

Table LName links to 1st query LName
Table Date links to 1st query MaxOfDate

In the table you could have this
ID LName Date
1111 Smith 01/01/1900
1234 Jones 12/12/1990
2222 Smith 01/03/1900
3333 Smith 01/04/1900
4444 jones 13/12/1987


1st query gives you this
LName MaxOfDate
jones 12/12/1990
Smith 01/04/1900


2nd Qery you get this
LName MaxOfDate ID
Smith 01/04/1900 3333
jones 12/12/1990 1234

I have just tried it with many records (well about 20 anyway) and it works
each time



--
Wayne
Manchester, England.

Thanks Wayne but the ID isn't always the Max ID

SELECT TableName.LName, Max(TableName.Date) AS MaxOfDate, Max(TableName.ID)
AS MaxOfID
FROM TableName
GROUP BY TableName.LName;



--
Wayne
Manchester, England.

Problem I've always faced in Access....
I want to group by LastName and then show the [Max] date, and then bring out
the ID of the record that contained the [Max] date.

LName Date ID
Smith 01/01/1900 1111
Smith 03/01/1900 2222
Smith 04/01/1900 3333

I want my query to output.....Smith | 04/01/1900 | 3333

Thanks,
Ken