Selecting the Last Row in SQL Access 97

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Selecting the Last Row in SQL Access 97

Author

Topic

Ibateur
Starting Member

2 Posts

Posted - 2004-06-11 : 05:15:29

I have two tables

Table A (main table - static inforamtion)
Project (primary key), other data

Table B (history table for projects)
Project, Status, Status_Number (primary key)

Using SQL how can I achieve, for each Table A record, getting only the last Table B row. Essentially, I'm looking to create a report of all projects and the current status.

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-06-11 : 08:54:38

is the "last" row the row with the highest status_number?

if so, break it down into logical parts.

you need a query that returns the highest status number per Project:

qryMaxStatusNumber:
select project, max(Status_Number) as LastStatus from TableB group by project

Now, from that, we can join back to TableB to get the latest status:

select tableB.*
from tableB
inner join qryMaxStatusNumber on tableB.Project = qryMaxStatusNumber.Project

the inner join acts as a filter to return only the rows that qryMaxStatusNumber matches, which is the desired resultset. Join to tableA for the additional static information you'd like to return.

makes sense?

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-06-11 : 08:56:16

FYI --

in TableB, your primary key should not be:

Project, Status, Status_Number

but rather:

Project, Status_Number

"Status" should not be included in the primary key constraint. you should have 1 status code per Project/Status_Number. If status code is included, you can potentially have data like this:

Jeff's Project,Active,1
Jeff's Project,InActive,1

if so -- what is the "current" status?

- Jeff

Ibateur
Starting Member

2 Posts

Posted - 2004-06-11 : 14:47:33

Hi Jeff

Sorry, I wasn't clear. I have the status_number (yes, the last is the highest) as the primary key, together with the project number.

Your "suggestion" is actually how I have it.

I will try it out, and yes it does make sense. My experience has never been with SQL, which is why I'm such a newbie. RDBMS yes, but not this.

Muchas thanks mate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources