Ok, so my SQL skills and Access skills are very rusty. I have a excel sheet with some data that I would like to start tracking into a database. Currently I pull the data, clean it up a bit and transpose it and have some excel magic work.

Currently there are two fields. Field1 is just a normal number field, less than 10 digits. Field 2 is converted into an excel date format from a UNIX Epoch timestamp.

My goal is to have the Max of Field1 for each day. Most of the older data only had one data point per day, while the newer data will possibly have hundreds data points.

Example Data: Field1 being normal number Field2 being Excel Date format

CodePudding user response：

Assuming that you have your data in a table called Sheet1, you can type the following query in SQL view in MsAccess:

SELECT int(Field2) as ExcelDay, max(Field1) as MaxOfField1
FROM Sheet1
group by  int(Field2) 

int(Field2) removes the fraction from the time, leaving the Day you requested.

CodePudding user response：

If you need to return both date & time as output then could try-

SELECT First(Field2) AS [Date], Max(Field1) AS MaxValue
    FROM ExcelLinkTable
GROUP BY Format(Field2,"m/d/\e");