Get Last group of record

[jjake]

Hi,

I have 2 tables,

tblUpdateVersions

| UpdateID | StartDate| VersionNum |


TblUpdateStatus

| UpdateStatusID | VersionNum | UpdateDescription |


I'm trying to run a query to show the latest group of records that all have the latest VersionNum from tblUpdateStatus

When i use Last or Max it will not query the records. It just displays the same ones.


e.g

1, 1.0.1 , update 1
2, 1.0.2 , update 2
3, 1.0.2 , update 2.1
4, 1.0.3 , update 3
5, 1.0.3 , update 3.1

 

[theDBguy]

Hi. Are you really storing VersionNum instead of UpdateID in tblUpdateStatus?

 

[Minty]

If you include the PK ID in any grouping query you'll get all the records, as it is unique. Only include the fields you need to perform the group on.

 

[jjake]

Hi. Are you really storing VersionNum instead of UpdateID in tblUpdateStatus?

it's a lookup field using both UpdateID and version num

 

[theDBguy]

it's a lookup field using both UpdateID and version num

Ah, bad news, but try this and let us know how it goes.

SELECT UpdateStatusID, VersionNum, UpdateDescription
FROM tblUpdateStatus
WHERE VersionNum = DMax("UpdateID","tblUpdateVersions")

 

[jjake]

Ah, bad news, but try this and let us know how it goes.

SELECT UpdateStatusID, VersionNum, UpdateDescription
FROM tblUpdateStatus
WHERE VersionNum = DMax("UpdateID","tblUpdateVersions")

Bam, Solved.

 

[theDBguy]

Hi. Glad to hear it worked for you. You probably already know why lookup fields are "bad news," so I won't lecture you on it. Good luck with your project.

 

[jjake]

I don't but i will definitely research it.

 

[theDBguy]

I don't but i will definitely research it.

Here's a link for you. The Evils of Lookup Fields in Tables