Last record per date from timestamp (1 Viewer)

[WardC]

Hi, I am fairly new to Access/SQL and maybe this is so basic that nobody asks, but I can't find the answer.

I have an Access table setup to collect daily stats from a team. The team wanted to use an Excel spreadsheet for entering their data so I just made a macro that runs on closing of the spreadsheet to push the data into my table. They are only supposed to be entering the data once per day, but I have found that they update the data multiple times per day.

I have a timestamp field that records the date/time of each new entry called acc_timestamp.

Now I am trying to create a query that only shows the last record that is created each day, but I can't seem to find a way to do it. I have tried using DatePart for the day to get unique days, but I am not even getting that right and that doesn't even start to tackle the limiting to the last time of the day.

Any suggestions?

 

[HiTechCoach]

If I was doing this, I have have the Excel macro update a cell to show that the record had been sent to Access. You can then update the Excel macro to only send the records taht have not been marked as sent. I like to use a date/time stamp of when the data was transfered as the flag. This is the same way I would do it is I was using an Access database to collect the data "off line" and transfer that data in a batch later.

 

[WardC]

Thanks HiTech,

I thought of this, but when I looked into it further I found that the team is refining their data through the day and that the last update of the day is most accurate. Rather than limiting their ability to send me data I would rather just ignore the data that I don't need.

 

[HiTechCoach]

Thanks HiTech,

I thought of this, but when I looked into it further I found that the team is refining their data through the day and that the last update of the day is most accurate. Rather than limiting their ability to send me data I would rather just ignore the data that I don't need.

Sounds like you really do need to have then use an Access form that edits the data directly.

I can see that you will easily continue to have data integrity issues in the future is you all then to edit data once it has been transfered to your tables.

 

[HiTechCoach]

The more I think about this and how you can maintain some type of data integrity without have the user enter the data directly into Access, I have come up with an idea:

It may be better to have the data from excel go into a work/temp table. At some point you can process the data and only import the records that you want. I normally never import data from an external source directly to my table's. I like to import the data to a "workspace" that will allow me to validate the data before the final import.

Note:
Entering the data with an Access form would solve all you data issues and probably be less than 10% of the work that it will require to use Excel.

IMHO, as the database developer, it is your job to protect the data and do what is best for the data. User may request things that may sounds good, but are bad for data processing/data integrity. It is the developer's job to do the right thing, not just what the user thinks they need.

 

[WardC]

Because of the way the data is collected it is static, it is not being edited. There is a new record created each time that they change the workbook. This is why I need the query to limit the records to the last by time of day for each unique day.

Once I am able to get past this hurdle I don't see any further integrity issues. I am surprised that it is not a common problem to want to limit multiple entries per day to one. Is this really that hard to do? I was trying to avoid using two columns to achieve it, but if I had to I would think I could have one for date and one for time then Select based upon Unique date/Max Time.

It just seems to me that a Date/Time field should be able to do both.

 

[HiTechCoach]

Because of the way the data is collected it is static, it is not being edited. There is a new record created each time that they change the workbook. This is why I need the query to limit the records to the last by time of day for each unique day.

Once I am able to get past this hurdle I don't see any further integrity issues. I am surprised that it is not a common problem to want to limit multiple entries per day to one. Is this really that hard to do? I was trying to avoid using two columns to achieve it, but if I had to I would think I could have one for date and one for time then Select based upon Unique date/Max Time.

It just seems to me that a Date/Time field should be able to do both.

Note:
I normally always store the date and time in the same field. The I use the DateValue() and TimeValue() functions to extract out the parts.


Your original post stated:

I found that the team is refining their data through the day and that the last update of the day is most accurate.

I took this as meaning tht hey were making edits.


So you are getting multiple records per day, which is OK. You just need to find the last one for each day. Is that correct?

You probably could use a sub query to get the TOP 1 record for a date with the date sorted descending so that the more current one was on top ad the criteria to limit the records.

 

[gemma-the-husky]

what you need is some unique identifier in the spreadsheet to indicate which rows need processing - have you anything suitable?

the problem with excel compared with access IS the complete lack of rigour - rows can be added/moved/etc, and cell contents changed etc

its all very well saying this wont happen, but how would your app react if it did!

---------
anyway, assume the spreadsheet does have integrity, and find a way of picking the new rows

 

[WardC]

Hi Gemma,

I am not sure that I am following your post. If you are commenting on the challenges of choosing to use the Excel spreadsheet as the input device, I agree there are challenges and I am working those out. I am not sure what problem you are referring to that would be resolved with the unique identifier in the spreadsheet, or how that might apply to my original post.

At this point I don't feel that I am having trouble with the data that I am receiving in Access. I guess I feel that everything is working as planned up to when I need to report out of Access, and I need to parse out the last entry of each date.

There is no processing involved by my definition of the term, just basic entry of data. The data is all pushed to Access at one time so I have a timestamp that is created at that time. Rather than that happening one time per day it is happening several times, but I only want to report on the data entered one time per day, and I feel that the last time is the best to use.

One solution that I was thinking might work would be a scheduled update query at the end of the day everyday that looked up that days data sorted descending and then pushed the first unique entry into another table. Kinda convoluted, but it gets me the clean data that I need. Thoughts?

 

[gaby]

As I understand you are stuck to the way the data is collected and your issue is finding the last occurence of the record which is supposed to be the most accurate.
I'm having a similar issue where I need to find the first ocurrence and then update.
I'm able to find the first occurence using a query but when it comes to update I have failed. I found this article were you use GROUP BY on the fields that aren't duplicate and FIRST or LAST on the duplicate field to find the first or last s.occurence.

You can read the article I'm referring microsoft support com kb 292634
Tried to include the link but it's not possible.

Hope this help