Get Last group of record (1 Viewer)

J

jjake

Registered User.

Local time

Yesterday, 22:29

Joined

Oct 8, 2015

Messages

291

• Feb 11, 2019

• #1

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

I’m here to help

Staff member

Local time

Yesterday, 20:29

Joined

Oct 29, 2018

Messages

21,453

• Feb 11, 2019

• #2

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

 

Minty

AWF VIP

Local time

Today, 04:29

Joined

Jul 26, 2013

Messages

10,366

• Feb 11, 2019

• #3

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.

 

J

jjake

Registered User.

Local time

Yesterday, 22:29

Joined

Oct 8, 2015

Messages

291

• Feb 11, 2019

• #4

theDBguy said:

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

Click to expand...

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

 

theDBguy

I’m here to help

Staff member

Local time

Yesterday, 20:29

Joined

Oct 29, 2018

Messages

21,453

• Feb 11, 2019

• #5

jjake said:

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

Click to expand...

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

Code:Copy to clipboard

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

 

J

jjake

Registered User.

Local time

Yesterday, 22:29

Joined

Oct 8, 2015

Messages

291

• Feb 11, 2019

• #6

theDBguy said:

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

Code:Copy to clipboard

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

Click to expand...

Bam, Solved.

 

theDBguy

I’m here to help

Staff member

Local time

Yesterday, 20:29

Joined

Oct 29, 2018

Messages

21,453

• Feb 11, 2019

• #7

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.

 

J

jjake

Registered User.

Local time

Yesterday, 22:29

Joined

Oct 8, 2015

Messages

291

• Feb 11, 2019

• #8

I don't but i will definitely research it.

 

theDBguy

I’m here to help

Staff member

Local time

Yesterday, 20:29

Joined

Oct 29, 2018

Messages

21,453

• Feb 11, 2019

• #9

jjake said:

I don't but i will definitely research it.

Click to expand...

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