0

I am sure this has been asked before and I have found several references to similar things. I just can't work out how to make it work for me.

I have a table (tblHistory) with data

Unit Number,Action,LoggerID,SensorID,CableID,Date,Data Location etc

I also have another table (tblUnit) with more data

Unit,Logger,Sensor,Cable

I want to create a query that scours tblHistory to find the most recent LoggerID, SensorID and CableID for each Unit.

To add to this there may need to be safeguards in the query in case the user does not place Logger, Sensor or Cable IDs so that it does not just find the most recent date and return a null value.

The final aspect of this which may or may not be simple is that I want this to overwrite the data in the tblUnit table so that when a new logger, sensor or cable is in place the unit uses this new data. I don't want these fields to be just edited because I want to keep a history of it. My knowledge of SQL and Access is pretty limited as I'm usually more familiar with excel and vb.

I have tried using these: MSAccess: select N records from each category sql for finding most recent record in a group but being a beginner I'm not 100% sure how to apply them to my problem.

If anyone can help that would be great.

Example: (tblHistory)

Unit Number,Action,LoggerID,SensorID,CableID,ActionDate
1,Replace Sensor,,KTSM01S03,,15/02/2015
1,Replace Logger,KTSM01FM02,,,11/01/2014
1,Replace Cable,,,sa123124,10/01/2014
1,Replace Logger,KTSM01FM03,,,12/01/2014
2,Replace Sensor,,KTSM01S01,,13/01/2014
2,Replace Cable,,,sa123123,12/01/2014
2,Replace Cable,,,sa123124,16/02/2014
2,Replace Logger,KTSM01FM01,,,13/01/2014

tblUnit

Unit Number,Logger,SensorID,Cable
1,KTSM01FM01,KTSM01S01,sa123123
2,Logger1,Sensor1,Cable1
Community
  • 1
  • 1
user3180124
  • 3
  • 3

1 Answers1

0
SELECT TOP 1 * 
FROM tblHistory a
INNER JOIN 
   (SELECT Unit Number, Max([Date]) as MaxDate 
    FROM tblHistory
    GROUP BY Unit Number) b
on a.[Unit Number] = b.[Unit Number]
and a.[Date] = b.MaxDate

You have to bracket the field called Date because Date() is a reserved function and Access will think you're trying to use the function instead of calling the field in your table. In the future, always try to avoid using reserved words as field names, it will save you a lot of headaches.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • Thanks, it shows the Unit Number, Logger ID, Sensor ID nearly as I expected. I've altered the Date category to ActionDate so that issue has been taken out and I had to put brackets around the Unit Number as it is 2 words. That part of it works fine, however it doesn't select the top 1. Thanks for the help though – user3180124 Jan 13 '14 at 01:30
  • Can you create 2 mock tables with enough records so that when the query is run you'll know for sure it ran properly, and then edit your original post to include the data? It's really tough to work with code where you have no data to ensure it's working properly. – Johnny Bones Jan 13 '14 at 13:20
  • I have created 2 mock tables. Essentially I was hoping to pick the most recent of each change in tblHistory and then update it in tblUnit. I started work by creating one query for logger, sensor and cable to sort by date then create another query that references this to select the first of each. Now I just have to work out how to update the original data. It should work but it lacks elegance. – user3180124 Jan 14 '14 at 05:31
  • I updated the query syntax. This will give you the most recent record, but **will** return NULLs if they're there. You may need to add a WHERE clause so it will not return a record if there are NULLs in it, if that's what you want to do. – Johnny Bones Jan 14 '14 at 15:15
  • Thanks for your help. There are a few extra tweaks I need to make but I need to learn these for myself. Thanks a lot for your help. – user3180124 Jan 15 '14 at 00:56