469,306 Members | 1,850 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

Help in limiting records to most recent date

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
Dec 27 '06 #1
8 13872
ADezii
8,800 Expert 8TB
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;
Dec 27 '06 #2
nico5038
3,080 Expert 2GB
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)
Dec 27 '06 #3
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
Dec 27 '06 #4
nico5038
3,080 Expert 2GB
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)
Dec 27 '06 #5
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...

Expand|Select|Wrap|Line Numbers
  1. SELECT tblTransferHistory.[Transferred To], tblTransferHistory.[Tool ID], tblToolInfo.Quantity, tblToolInfo.Manufacturer, tblToolInfo.[Model Number], tblToolInfo.Description, tblToolInfo.[Serial Number], tblTransferHistory.[Date of Transfer]
  2. 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])
  3. ORDER BY tblTransferHistory.[Tool ID];
Thanks for all the help!

Jason
Dec 27 '06 #6
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
Dec 27 '06 #7
Killer42
8,435 Expert 8TB
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? :)
Dec 28 '06 #8
NeoPa
32,173 Expert Mod 16PB
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.
Dec 30 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by HandersonVA | last post: by
3 posts views Thread by | last post: by
1 post views Thread by pht204 | last post: by
3 posts views Thread by Steve | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.