CODEWITHSUNDEEP
ms-accessms-access-2013
Ajster1989
Ajster1989

Reputation: 317

Create a query in Access to show last entry for a serial number

So I have a database that tracks location changes for assets. Every time the form is filled out it automatically makes a date and time stamp for every location change.

Columns look like this.

[Serial Number][New Location][Date][Time]

Example would be"

[DE1234567][Warehouse][5/7/2014][10:02PM]

What I want to do now is create a query that will show me all the assets that are currently in these locations.

[Location1][serial1][Date/Time]
[Location1][serial2][Date/Time]
[Location1][serial5][Date/Time]
[Location1][serial7][Date/Time]
[Location2][serial4][Date/Time]
[Location2][serial6][Date/Time]
[Location2][serial8][Date/Time]
[Location2][serial9][Date/Time]

Or Something along those lines.

What I need to do is get the query to find the last dated entry for each serial number and only show that entry and ignoring anything older. I can't delete older ones because i need the history every asset.

P.s. The reason I don't use any old asset tracking software is because this in the future will also be used to track in house repairs of these assets and answering this question will also help me do that later.

Thanks

~~~~Added 12:35AM 5/8/2014~~~~

Tried this code from answer by "GeoB". Still shows same data just sorted.

SELECT [Cassette Status History].[New Location], [Cassette Status History].[Serial Number], Max([Cassette Status History].[Date/Time]) AS [Last Date/Time]
FROM [Cassette Status History]
GROUP BY [Cassette Status History].[New Location], [Cassette Status History].[Serial Number];

This showed all entries just but sorted, and grouped. I want this query to hide all entries from the same serial number but the one with the latest date.

~~~~Added 9:29PM 5/8/2014~~~~

I Tried this code as per Answer by "Heinzi" 

SELECT [Cassette Status History].[New Location], [Cassette Status History].[Serial Number], [Cassette Status History].[Date/Time]
  FROM [Cassette Status History]
 WHERE [Cassette Status History].[Date/Time] = (SELECT MAX([Cassette Status History].[Date/Time])
                          FROM [Cassette Status History]
                         WHERE [Cassette Status History].[Serial Number] = [Cassette Status History].[Serial Number])

This now only shows the very last entry entered into that table.

Thanks again everyone for your help!

Upvotes: 0

Views: 2437

Answers (3)

Ajster1989
Ajster1989

Reputation: 317

I was able to figure it out. Took me hours of playing with it, but hey at least I'm smarter now because of it.

Here is the code I am using.

SELECT First([Cassette Status History].[New Location]) AS [FirstOfNew Location], [Cassette Status History].[Serial Number], Max([Cassette Status History].[Date/Time]) AS [MaxOfDate/Time]
FROM [Cassette Status History]
GROUP BY [Cassette Status History].[Serial Number]
ORDER BY First([Cassette Status History].[New Location]);

I did this in design view by first making the table that only shows Serial Numbers with Total set to "Group By" and Date/Time set to "Max" then after that I added in the New Location Field as just extra info using totals set to "First" and sorted ascending.

Now that I'm looking at it it seems really simple....

Thanks everyone for all your help, I learned alot!

Upvotes: 0

Heinzi
Heinzi

Reputation: 172230

You want all the records where Date/Time are the highest value for this serial number. So let's translate that directly to SQL:

SELECT A.[New Location], A.[Serial Number], A.[Date/Time]
  FROM [Cassette Status History] A
 WHERE A.[Date/Time] = (SELECT MAX(B.[Date/Time])
                          FROM [Cassette Status History] B
                         WHERE A.[Serial Number] = B.[Serial Number])

Alternatively, if you want to use the query designer, you can spit the problem into smaller pieces:

  • Create a Query LastMovements, which contains only Serial Number and the maximum Date/Time for that serial number. This is similar to the query you already have, just without the location.
  • Then create a new query that joins LastMovement to Cassette Status History to add the location.

Upvotes: 1

geoB
geoB

Reputation: 4704

Create a new query design and switch to SQL View. Copy in the following:

SELECT yourtable.[New Location], yourtable.[Serial Number], Max([yourtable.Date]+[yourtable.Time]) AS Timestamp
FROM yourtable
GROUP BY tTest.[New Location], tTest.[Serial Number];

Date and Time fields are added to make a complete timestamp.

In Design View you'll see how the query could be built.

[note: the above assumes that ACC2013 is substantially similar to ACC2010]

Upvotes: 0

Related Questions