How best to Select the Latest of entry ?

[GrunleStan]

So I have a table_payscale with

ID <- Primary Autonumber key
Staff_ID<- Foreign key to another table
PayScale <- important information
DatePayScale <- Date important information last changed.
Branch <- branch person is in.

What I require is to filter out all the old information from the table and just cut it down to the last date of the person. I am currently using a query with a where statement that uses a dmax lookup in it.
SQL statements is like

SELECT tbl_PayScale.*
FROM tbl_PayScale
WHERE (((tbl_PayScale.PS_DateAssign)=DMax("PS_DateAssign","tbl_PayScale","PS_Staff_ID=" & "'" & [PS_Staff_ID] & "'")));

And it is slow... less than 500 records takes a good 5 minutes.

So the quick and difficult question... Is there an easier & faster way to filter the data ?

TIA

 

[June7]

I tested this on 3128 records and took 50 seconds. Then I tested with nested subquery using TOP N and then I tried calling a VBA function. All had same performance. So no, don't know faster way - not if you want to be able to edit the records.

 

[arnelgp]

create new query:

select ps_staff_id, max(ps_dateAssign) As MaxDate from tbl_payscale group by ps_staff_id



now join this with your orig query:

select T1.* from tbl_payscale As T1 inner join newQuery As T2 On T1.ps_staff_id=T2.ps_staff_id And T1.ps_dateAssign=T2.MaxDate;

 

[CJ_London]

in your application, do you need to return all the latest records or just the one for a particular employee?

Also do you have a autonumber primary key? - your code suggests not since you are surrounding your dmax criteria with single quotes

 

[The_Doc_Man]

If you don't already have too many indexes on that table, add an index on the date that is the basis of your selection. Then follow Arnel's suggestion.

 

[June7]

Arnel's suggestion will result in a non-editable dataset. This was implied in my earlier post. It is fast but cannot edit records.

 

[GrunleStan]

in your application, do you need to return all the latest records or just the one for a particular employee?

Also do you have a autonumber primary key? - your code suggests not since you are surrounding your dmax criteria with single quotes

All latest records please,
and yes I do have an autonumber PK.

 

[GrunleStan]

I'm not sure why branch is in this table. It seems to conflict with staff_ID. .

I keep the branch there in case the person has been reassigned to another branch. That way I can keep track of branch specific information.
Its a strange world.

Did I mention that this was meant to sit on a Sharepoint site since their IT dept doesn't want to touch this with a barge pole...

 

[GrunleStan]

Arnel's suggestion will result in a non-editable dataset. This was implied in my earlier post. It is fast but cannot edit records.

the original Query was uneditable due to the DMAX call as well.
so its no worries... Its just so I can get some information quickly.

 

[arnelgp]

it might be editable if you use Left Join instrad of Inner Join in the Sql i provided.

 

[GrunleStan]

Update : Thanks Everyone,
I managed to get Arnel's SQL working; then something strange happened.
I added more data in to the payscale just to test it out. And all of a sudden, it just stopped working and kept showing the old results.
I had added a later date (previous date for one of the staff id was 1992) like 1 Jan 1999;
and for the same staff id, the max date was 1992...

I did a compact and repair... but still the same results.

Is there something I'm missing ? shouldn't closing the query and reopening it force the query to rerun ?

 

[June7]

I tested using DMax and query was editable. However, since you don't need that, the join to aggregate query should work. You will have to provide query for analysis and sample data could be useful. If you want to provide db for analysis follow instructions at bottom of my post.

 

[arnelgp]

is the field a real date type or a text?

 

[GrunleStan]

It is working now !!
The latest shows up as intended... I can't believe I was foolish and left the date field as a short text type.... :banghead:

Thank you to everyone !! Especially ArnelGP, June7 and everyone who helped with this.

Now... how do I marked this solved ? hrmmm. ..
... don't tell me... I'll figure it out some how....

 

[Gasman]

I'll just give you a hint.

Thread tools in the header of this thread.