How best to Select the Latest of entry ? (1 Viewer)

[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.

 

[Pat Hartman]

I'm not sure why branch is in this table. It seems to conflict with staff_ID. Either the row is for the specific staff person or an entire branch but not both. Branch probably belongs in the staff table. And you would need some way of archiving staff records so you know what a person's branch, paygrade, etc was at any particular point in time. If you want help with your schema, post back.

That said, to answer your specific question, you should be able to use a TOP 1 if you include criteria for the staff_ID. Just sort descending by DatePayScale.

If you are trying to get a list of all current records in the table, arnelgp's suggeston is probably best. Just keep in mind that no query that includes an aggregation fuction such as Max(), First(), etc or that joins to a query that aggregates will be updateable. If this is a common request, you should probably redesign your tables so that you have a table that contains the current assignments and another that holds history. That will simplify and speed up any "current" processing at the expense of historical reporting but at least for historical reporting, you won't have to worry about the updateability of the query.

 

[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.