Show latest dates of each record

[Behedwin]

Hey

I have have one table with computers (my companies computers).
ComputerName
ComputerSerialNumber
etc

Then i have Log table where i add data about what actions i have been taking on the computers
Repairs, handout, inventory etc.


I want to display when i last made a log entry on each computer and filter out all that are not the latest date.


so i get a list of when i last made a log entry on each computer. only show the latest entry in the log table on each computer

[JoeM]

Join your two tables in a query.
Add the ComputerName & ComputerSerial Number form the one table, and then Date field from your other table.
Then change it to a Totals Query by clicking on the Totals button (looks like a Sigma).
Under the Date field, change the value on the Totals row from "Group By" to "Max"

This should give you what you are looking for.
If you want additional fields from your Log table, then create a new query between this just created query and your Log table on the Computer ID fields AND the Date field.
Then you can return any fields from the Log table that you want.

[Behedwin]

It does not work all the way... i still get some duplicates.

I have a list with dates, names, serialnumber....
i did as you say and activated totals and set it to MAX on the date column.

some duplicates are gone and some are not.

[orange]

Please post the sql for the query you are using.

[JoeM]

It does not work all the way... i still get some duplicates.

I have a list with dates, names, serialnumber....
i did as you say and activated totals and set it to MAX on the date column.

some duplicates are gone and some are not.

That usually happens for one of three reasons:

1. You chose too many fields in the original Totals query. Remember, ALL the fields in that query that have "Group By" on the totals row are included in the duplicate determination!
That is why you cannot return all the fields, and need to join the back to the original table if you want other fields that are not being used to determine the de-duplication.

2. You have not done the match on the last query correctly. Remember, you need to join on multiple fields for this one, both the Company ID and the Date.

3. You have multiple records in your log table with the same max date for some Company IDs. Maybe repairs and handouts happened on the same day for a Company.

[Behedwin]

Still not getting the correct return as i want.

I might be doing something wrong or i dont even need Totals and Max function atall....

Ill try to explain what im after.

1. I have one table containing the info about IT equipment, computername, serial number etc.
2. I have one table containing actions done on IT equipment, repairs, handout, returns etc.

All this is done in different forms to read, add and remove records in above two forms.

Now i need to print a list of all units.
And i want to see in this list when i last had my hands on that unit.
I also want to see a list of units that i have not had my hands on.
So i know that "these units i need to look for"


So maybe im asking the wrong questions here.

1. How to create a list/query that returns all IT Equipments that have not had a Log entry in the last 3 mounts?
2. How to create a list/query that returns all IT Equipment that HAVE had a Log entry and only show the "latest" log entry?

My idea with those lists can easily see what units i need to look for. What units have been seen and when.



If i create a query now with the table containing the Log entries (date, reason, comment).
I open the query builder, select ITLog_Table
I then pick out the date column and the Secondary_key ID that have a relationship from another table (IT_Table containing computer names etc).
So now i have a query with two columns, date and IT_ID
I now go to Totals and make the date column MAX.

This seems to work now when im doing it and typing this (ffs).... so ill just keep going and maybe illl solve this
I name this query as a SUB query and then go and create a new query where i add the table with computernames etc togheter with the recently created sub query.
So i now have the dates and the names of the computers.

This seems to have worked now... but how can i now see what units have not had a log entry and are not even being included in this list.
I dont wanna miss those units
Tried to do this by creating a new query where ITLog_Table and IT_Table are in a relationship (3) to show all post from IT_Table and only those from ITLog_Table that are alike. Then use the criteria "is null" to filter out those that do have a log entry already....
Hope this sound correct to you....


Hopefully i now have two querys that are correct.
One that show all items that HAVE been logged and i get a list with the LATEST date.
One list that show all items that HAVE NOT been logged.


Then when i need to check if i have some units i have not seen in the last few mounths i can look in the list of NOT BEEN LOGGED items.... these units have not been seen for some time most likely. Eventually this list should always be empty unless i add a new computer.
And then i check the other list of items that HAVE been logged and i see the latest date... sort the list by date and see what units have not been seen in the last couple of months.


Im sorry if this post became quite messy...
took some time to write and test things at the same time and eventually i think i solved things... but i need to error check this so i dont miss any units.