By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,201 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Help in limiting records to most recent date

P: 4
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
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,669
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
Expert 2.5K+
P: 3,072
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

P: 4
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
Expert 2.5K+
P: 3,072
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

P: 4
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

P: 4
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

Expert 5K+
P: 8,434
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
Expert Mod 15k+
P: 31,660
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.