Adding fields to query without Grouping

[Guest]

I have the following records in a table called Info:

One Two Three Four Change Date
1 2 3 2 1/1/2005
1 2 3 3 3/1/2005
6 7 8 9 1 3/1/2005
6 7 8 9 2 3/1/2005

I have a query called InfoQuery:

SELECT Info.One, Info.Two, Info.Three, Info.Four, Max(Info.Change) AS
MaxOfChange, Info.Date
FROM Info
GROUP BY Info.One, Info.Two, Info.Three, Info.Four, Info.Date;

The query result is:
One Two Three Four MaxOfChange Date
1 2 3 2 1/1/2005
1 2 3 3 3/1/2005
6 7 8 9 2 3/1/2005


I want the results to be:
One Two Three Four MaxOfChange Date
1 2 3 3 3/1/05
6 7 8 9 2 3/1/05

How can I include the Date field in the query without grouping by Date?

Thanks

 

[John Spencer (MVP)]

Use one of the other aggregate functions on the field. Probably MAX, but it
could be First or Last

SELECT Info.One, Info.Two, Info.Three, Info.Four, Max(Info.Change) AS
MaxOfChange, Max(Info.Date) as MostRecent
FROM Info
GROUP BY Info.One, Info.Two, Info.Three, Info.Four

 

[Chris2]

I have the following records in a table called Info:

One Two Three Four Change Date
1 2 3 2 1/1/2005
1 2 3 3 3/1/2005
6 7 8 9 1 3/1/2005
6 7 8 9 2 3/1/2005

I have a query called InfoQuery:

SELECT Info.One, Info.Two, Info.Three, Info.Four, Max(Info.Change) AS
MaxOfChange, Info.Date
FROM Info
GROUP BY Info.One, Info.Two, Info.Three, Info.Four, Info.Date;

The query result is:
One Two Three Four MaxOfChange Date
1 2 3 2 1/1/2005
1 2 3 3 3/1/2005
6 7 8 9 2 3/1/2005


I want the results to be:
One Two Three Four MaxOfChange Date
1 2 3 3 3/1/05
6 7 8 9 2 3/1/05

How can I include the Date field in the query without grouping by Date?

Thanks

Alex,

Sample Data, excellent!


CREATE TABLE Info_03072005_1
(InfoID AUTOINCREMENT
,One LONG
,Two LONG
,Three LONG
,Four LONG
,Change LONG
,[Date] DATETIME
,CONSTRAINT pk_Info_03072005_1
PRIMARY KEY (MyTableID)
)

InfoQuery_03072005_1:

SELECT Info.One
,Info.Two
,Info.Three
,Info.Four
,Max(Info.Change) AS MaxOfChange
,Info.[Date]
FROM Info_03072005_1 AS Info
GROUP BY Info.One
,Info.Two
,Info.Three
,Info.Four
,Info.[Date]


InfoQuery_03072005_2:

SELECT Info.One
,Info.Two
,Info.Three
,Info.Four
,Max(Info.Change) AS MaxOfChange
,Info.[Date]
FROM Info_03072005_1 AS Info
WHERE Info.[Date] =
(SELECT MAX(Info2.[Date])
FROM Info_03072005_1 AS Info2
WHERE Info2.One = Info.One)
GROUP BY Info.One
,Info.Two
,Info.Three
,Info.Four
,Info.[Date]

The above Query makes the *radical* assumption that column One is the
colomun we're trying to get the MAX Date for. If that assumption
isn't true, then neither is the above Query.

It is producing the results requested, but further testing is needed.


Sincerely,

Chris O.