By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 2,139 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

Pull data last entered by date/time stamp Access 2003

P: 38
I have a database that we use in our department for the status of our projects. In the form when you pick a project from the dropdown list I have a subform that pulls the data entered for the following week. I was able to do this by having when they input data it automatically pulls the date for week ending by this: =DateAdd("d",6-Weekday(Date()),Date()),
so therefore to see previous weeks data the subform date field has this: =DateAdd("d",-7,[Forms]![Enter Status for New Week Data]![DateOfWeekEnding])
Because they may have changes to make within that week to what they already input I would like for them to be able to pull last entered data for that certain project instead of the previous weeks. I have added a datetime stamp field to the subform but can not figure out what the criteria would be to have it read the last one entered. Can anyone help me with this?
Thanks
Apr 2 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Not sure exactly what you're asking but does this work?
Expand|Select|Wrap|Line Numbers
  1. =Max([Forms]![Enter Status for New Week Data]![DateOfWeekEnding]) 
Mary
Apr 2 '07 #2

P: 38
Well what I ended up doing was putting a query together that the report is reading from and so for the datetimestamp I grouped by Max then had it go in descending order. That way I bring the last entry to the top per each project description. However it is still bringing every record into the table. How do I only bring in the last entered record per project description?

Not sure exactly what you're asking but does this work?
Expand|Select|Wrap|Line Numbers
  1. =Max([Forms]![Enter Status for New Week Data]![DateOfWeekEnding]) 
Mary
Apr 3 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Well what I ended up doing was putting a query together that the report is reading from and so for the datetimestamp I grouped by Max then had it go in descending order. That way I bring the last entry to the top per each project description. However it is still bringing every record into the table. How do I only bring in the last entered record per project description?
Post the SQL of the query you are currently using.
Apr 3 '07 #4

P: 38
There are a lot of fields in this query. Here is what I want though. I want it to pull the last record entered for a specified Date of Week Ending for all current projects:
SELECT Max(Status_Fields.DateTimeStamp) AS MaxOfDateTimeStamp, Status_Fields.Date_Of_Week_Ending, Status_Fields.Master_Project_Name, Status_Fields.Status_Description, Status_Fields.Supervisor, Status_Fields.Description_Name1, Status_Fields.Description_Name2, Status_Fields.Description_Name3, Status_Fields.Description_Name4, Status_Fields.Description_Name5, Status_Fields.Summary_Rating, Status_Fields.Status_for_Week_Ending, Status_Fields.Next_Milestone, Status_Fields.Next_Steps, Status_Fields.ImagePath
FROM Status_Fields
GROUP BY Status_Fields.Date_Of_Week_Ending, Status_Fields.Master_Project_Name, Status_Fields.Status_Description, Status_Fields.Supervisor, Status_Fields.Description_Name1, Status_Fields.Description_Name2, Status_Fields.Description_Name3, Status_Fields.Description_Name4, Status_Fields.Description_Name5, Status_Fields.Summary_Rating, Status_Fields.Status_for_Week_Ending, Status_Fields.Next_Milestone, Status_Fields.Next_Steps, Status_Fields.ImagePath
ORDER BY Max(Status_Fields.DateTimeStamp) DESC;


Post the SQL of the query you are currently using.
Apr 3 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT Status_Fields.Master_Project_Name,
  2. Max(Status_Fields.DateTimeStamp) AS MaxOfDateTimeStamp,
  3. First(Status_Fields.Date_Of_Week_Ending),
  4. First(Status_Fields.Status_Description), 
  5. First(Status_Fields.Supervisor), 
  6. First(Status_Fields.Description_Name1),
  7. First(Status_Fields.Description_Name2), 
  8. First(Status_Fields.Description_Name3), 
  9. First(Status_Fields.Description_Name4),
  10. First(Status_Fields.Description_Name5), 
  11. First(Status_Fields.Summary_Rating),
  12. First(Status_Fields.Status_for_Week_Ending),
  13. First(Status_Fields.Next_Milestone), 
  14. First(Status_Fields.Next_Steps), 
  15. First(Status_Fields.ImagePath)
  16. FROM Status_Fields
  17. GROUP BY Status_Fields.Master_Project_Name 
  18. ORDER BY Max(Status_Fields.DateTimeStamp) DESC;
Apr 3 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.