Help in limiting records to most recent date

[Jason H]

Hi,

I am sure I am just overlooking the obvious, but I am having a little trouble with this one...

I am setting up an inventory database that tracks company tools and their location. This database has a table tblToolInfo and another table tblTransferHistory which are linked by a Tool ID field. As tools are checked out they are recorded in tbl TransferHistory based on ID, location taken from, location taken to, authorizing person, and date.

I am looking for a way to query this Transfer table and list only the most recent transaction for each record (Tool ID) so that at any point in time I can bring up a view showing the general info from tblToolInfo and the current location from table tblTransferHistory.

I have tried using Select Distinct ordered by date, but of course I still get duplicate Tool ID's. I have also tried nested queries, but can't quite get the desired results.

I am hoping someone can shed some light on this or point me in the right direction.

Thanks in advance,
Jason H

[ADezii]

Hi,

I am sure I am just overlooking the obvious, but I am having a little trouble with this one...

I am setting up an inventory database that tracks company tools and their location. This database has a table tblToolInfo and another table tblTransferHistory which are linked by a Tool ID field. As tools are checked out they are recorded in tbl TransferHistory based on ID, location taken from, location taken to, authorizing person, and date.

I am looking for a way to query this Transfer table and list only the most recent transaction for each record (Tool ID) so that at any point in time I can bring up a view showing the general info from tblToolInfo and the current location from table tblTransferHistory.

I have tried using Select Distinct ordered by date, but of course I still get duplicate Tool ID's. I have also tried nested queries, but can't quite get the desired results.

I am hoping someone can shed some light on this or point me in the right direction.

Thanks in advance,
Jason H

'A Totals Query should do it:
[Tool ID] [Name] [Transfer Date]
Group By Group By Max

'If you prefer the SQL version:
SELECT tblToolInfo.[Tool ID], tblToolInfo.Name, Max(tblTransferHistory. [Transfer Date]) AS [Most Recent Date] FROM tblToolInfo INNER _JOIN tblTransferHistory ON tblToolInfo.[Tool ID]=tblTransferHistory.[Tool ID]
GROUP BY tblToolInfo.[Tool ID], tblToolInfo.Name;

[nico5038]

In general I prefer to build queries step by step. Here we could use a subquery, but first try this.
First we need to determine the [Tool ID] and the max date from the history file like:

select [Tool ID], Max([NameDateField]) from tblTransferHistory Group By [Tool ID];

This will be the selection needed and we can save this as qryMaxHistory.

Now we can simply create a new query that JOIN's qryMaxHistory by both fields with the tblTransferHistory and place the fields we want to display from tblTransferHistory.

Getting the idea ?

Nic;o)

[Jason H]

Thank you both for the help! I believe I am close. Using your posts I have isolated the Tool ID along with its most current date, however I am still getting duplicate values after joining with the other table. I am going to play with it some more...

Jason

[nico5038]

The only reason left for duplicates is the fact that you have the same Tool ID with multiple "max dates", or in other words, the Same Tool ID has been added to the history multiple times on the same date.
This can be an error or by design.
You should add the time to the history date to make sure no dupes can occur when multiple rows per date are allowed for a Tool ID, else you would need to create a unique index on the combined Tool ID and the DateSaved.

Nic;o)

[Jason H]

After some tweaking I realized what I was doing wrong...I forgot to join the calculated field in combination with the Tool ID field to tblTransferHistory. I have it working now. The (messy) final SQL ended up looking like this...

SELECT tblTransferHistory.[Transferred To], tblTransferHistory.[Tool ID], tblToolInfo.Quantity, tblToolInfo.Manufacturer, tblToolInfo.[Model Number], tblToolInfo.Description, tblToolInfo.[Serial Number], tblTransferHistory.[Date of Transfer]
FROM tblToolInfo INNER JOIN (qryMaxHistory INNER JOIN tblTransferHistory ON (tblTransferHistory.[Date of Transfer] = qryMaxHistory.Expr1001) AND (qryMaxHistory.[Tool ID] = tblTransferHistory.[Tool ID])) ON (qryMaxHistory.[Tool ID] = tblToolInfo.[Tool ID]) AND (tblToolInfo.[Tool ID] = tblTransferHistory.[Tool ID])
ORDER BY tblTransferHistory.[Tool ID];

Thanks for all the help!

Jason

[Jason H]

Nico,

You have pointed out a great flaw in the design, I ran a test checking a tool out twice on the same day and it does indeed cause a duplicate entry; however it is very unlikely that this will ever occur for us. If it becomes a problem I will add a time field but for now I am leaving it. Thanks for the foresight though!

Jason

[Killer42]

Nico,
You have pointed out a great flaw in the design...

This is a good lesson to keep in mind - try not to build-in future headaches. It's much easier to remove bugs before your code is in everyday use.

Ever hear of a little thing called Y2K? :)

[NeoPa]

Jason,
I strongly recommend that you take Killer's advice on board.
Your SQL doesn't include the SQL code for qryMaxHistory. Tweaking that could give you what you need without redesigning your data.