Sort By LAST With a 3 Table Query

sambo

Registered User.
Local time
Today, 09:08
Joined
Aug 29, 2002
Messages
289
I have 3 tables in my relationship...

My relationships work like this:

1 SerialNum ("tblUnits") --> Many RmaNum ("tblRma")
1 RmaNum ("tblRma") --> Many Locations ("tblhistory")

This lets me track multiple points in history for each SerialNum (Everytime an entry is made regarding a SerialNum, there is a time stamp and a location tagged to the entry)

Simple Enough...

Now, I would like to have a query that returns 1 entry from each RmaNum, that will be the LAST entry made in "tblHistory" that correpsonds to each RMA.

So if RmaNum 1-234 has had 5 entries, I would like to return the last entry made (based on the criteria of DateModified).

I have not been able to successfuly do this.
I can get 1 entry for each RmaNum, but it is seemingly chosen at random, I would like it to be the LAST entry (not random 1 of 5).

I'll attatch a screen shot of my Query.

Thanks..
 

Attachments

  • last query.jpg
    last query.jpg
    99.1 KB · Views: 145
Assuming DateMod is a date/time field containing also the time, I think you can use a query with a subquery (i.e. a select statement in brackets) instead of a Total query.


First make a backup copy of your query.
Then try this:-

Open your query in Design View.
Click on the Total button on the tool bar, so that the Total row is removed.

Set these in a new column:
Field: [tblHistory].[RmaID] & [tblHistory].[DateMod]
Show: uncheck
Criteria: In (Select RmaID & max(DateMod) from tblHistory group by RmaID)


When the query is run, "RmaID & max(DateMod)" in the subquery will retrieve the maximum DateMod of each RmaID from tblHistory.


Hope this helps.
 
That worked well.
But performance slowed way, (and I mean WAAAAY) Down.
It takes about 7 or 8 seconds to run that query, and that is just too long.

Why is it taking so long, and how can I cut down on the time.

This is basically a current inventory query to find out "where stuff is now". It usually returns about 125 records because that is how many units we usually have in inventory.

Currently, there are only about 1,000 historyID data records, this number will grow well into the hundreds of thousands as time goes on.
I can't imagine that this will help performance. I need this thing faster...

Help Please
 
Maybe splitting the query into two and using Top 1 in the subquery can help speed up performance.

(1) Create the following query, which will retrieve the last entry of each RMA from tblHistory.

qryRmaLastDateMod:-
SELECT RmaID, Location, DateMod
FROM tblHistory AS a
WHERE DateMod in (Select Top 1 DateMod from tblHistory where RmaID=a.RmaID order by DateMod Desc);


(2) Build the final query, using tblUnits, tblRma and the above qryRmaLastDateMod (instead of tblHistory).

Run this second query.
Hope it helps.

-------------------------------------
As tblHistory will keep on growing, I think you can also use a workaround.

Change the above qryRmaLastDateMod into a make table query to create a table tblRmaLastDateMod (which should contain 125 records):-

SELECT RmaID, Location, DateMod INTO tblRmaLastDateMod
FROM tblHistory AS a
WHERE DateMod in (Select Top 1 DateMod from tblHistory where RmaID=a.RmaID order by DateMod Desc);


Rebuild the final query, using tblUnits, tblRma and this tblRmaLastDateMod.

Add code to update tblRmaLastDateMod too, whenever records are added to tblHistory.
 
Last edited:
Dude!!
That worked awesome. I ended up using that method all over the place in my Database. I totally sped things up and allowed me to do what I wanted to do.

Could you please comment on the theory behing the Make Table idea that you described earlier.

I understand how it works, but why would I eventually benefit from that more than using a simple Select Query?

Thanks..
 
Currently there are only about 1,000 records in tblHistory and query qryRmaLastDateMod can give satisfactory performance. But when tblHistory grows to hundreds of thousands of records, the query (which needs to run the subquery) will inevitably slow down significantly.

On the other hand, the make-table query is only one-off. Once the table tblRmaLastDateMod is created (which contains ONLY ONE record for each RMA), it can be used directly in the final query. All you need is thereafter have it also updated (i.e. add new RMA, edit Location and DateMod of existing RMA) whenever records are added to tblHistory.

The benefit is three-fold:-
(1) Table tblRmaLastDateMod contains much fewer records than tblHistory (currently about 125:1000).
(2) Table tblRmaLastDateMod will grow relatively slower than tblHistory.
(3) There is no need to run the time-consuming subquery any more.
 

Users who are viewing this thread

Back
Top Bottom