473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 14279
ADezii
8,834 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,556 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

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

Similar topics

6
by: HandersonVA | last post by:
There are several day_timestamp for each index_id. Anyone can help me to write a sql to generate the most recent day_timestamp of index_ids which has not accessed into the system in 90 days from...
3
by: | last post by:
I have a collumn filled with dates. How would I go about selecting the record with the most recent date. using access 2000. thanks!
1
by: pht204 | last post by:
Hi there, I have a list of date on my table: ClientName ClientPurchase PurchaseDate PuarchesItems Let's say, the purchas dates are multiples and I want to get each clients with their most...
2
by: gafchic | last post by:
I manage a training database where I work and I would like to run a make table query. The table I want to query has a list of trainings our employees have taken and the dates they have taken the...
9
by: psuaudi | last post by:
i have a table with dates that events occured. it looks something like this: 11/18/2006 1:00PM Open 11/18/2006 1:25PM Close I created a select query that selects the last (most recent)...
1
by: Proaccesspro | last post by:
I have 2 tables in a query. One of the tables contains a field titled Action Date. How can I query for the most recent date in the field Action Date? Dmax?
3
by: Steve | last post by:
I have a databse tracking container movements. I am happily recording each movement and the date of movement and can see where each container is by sorting by date of movement. Is there a way I...
0
by: Simon S | last post by:
Apologies but I am an Access Novice… I have a contacts and tracking database that holds contact info and tracks people through interview processes for different roles/jobs. I have a table named...
3
by: Ginny28 | last post by:
I am running the following query and need to modify it to pull only 1 row, with the most recent Onset Date, when the pr.diagnosis_code_id and pr.description are not unique. I could actually use one...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.