asked on Need help with creating a cross tab report that would identify the last date an employee entered time.
I have two main tables where an employee (Employees) saves the date and hours spent on a task in a table called TimeSheetData.
The query I want is basically give me the last date each employee has saved time which would pull from the fieldname: DateEntered.
It would look like:
Employee DateTimeWasSubmitted
EmployeeA 3/21/2019
EmployeeB 2/1/2019
EmployeeC
EmployeeD 1/14/2019
I provided a Microsoft Access sample - note how some employees maybe blank cause the DateEntered did not get out.
How would I get this accomplished? I want it to list all employees whether time was entered or not.
ee-question-last-date-emp-entered-.accdb
The query I want is basically give me the last date each employee has saved time which would pull from the fieldname: DateEntered.
It would look like:
Employee DateTimeWasSubmitted
EmployeeA 3/21/2019
EmployeeB 2/1/2019
EmployeeC
EmployeeD 1/14/2019
I provided a Microsoft Access sample - note how some employees maybe blank cause the DateEntered did not get out.
How would I get this accomplished? I want it to list all employees whether time was entered or not.
ee-question-last-date-emp-entered-.accdb
Microsoft AccessVBASQL
Last Comment
PatHartman
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you sincerely.
You're welcome. I guess you didn't need a crosstab after all. If you do, figure out what your third field is. It could be a count or a sum or even first or last. Base the crosstab on the query I created once you have modified it to add a third field.
ASKER
No the next thing I need to do is give me the names of people whose date is more than 7 plus days from Now()-- that's what I'm trying to figure out now?
Took PatHartman's answer, placed it in an embedded/inline table, and added a where-clause
SELECT a.*
FROM
( SELECT Employees.EmployeeID, Employees.UserName, Max(TimeSheetData.DateEntered) AS MaxOfDateEntered
FROM Employees LEFT JOIN TimeSheetData ON Employees.EmployeeID = TimeSheetData.EmployeeID
GROUP BY Employees.EmployeeID, Employees.UserName
) a
WHERE a.MaxOfDateEntered >= DATEADD(day, 7, current_timestamp)
John,
There is no need for a subquery. The solution is to use a Having clause rather than a Where clause. I'm also pretty sure that despite the topics selected, an Access SQL solution is what the OP is looking for rather than T-SQL.
PS - Are you sure you want to use Now() rather than Date(). Now() includes the time of day. Date includes only the date. You probably also want to strip time from the DateEntry also. So you may actually want:
HAVING DateValue(Max(TimeSheetDat
Also Stephen,
Please start a new thread for a new question.
There is no need for a subquery. The solution is to use a Having clause rather than a Where clause. I'm also pretty sure that despite the topics selected, an Access SQL solution is what the OP is looking for rather than T-SQL.
SELECT Employees.EmployeeID, Employees.UserName, Max(TimeSheetData.DateEntered) AS MaxOfDateEntered
FROM Employees LEFT JOIN TimeSheetData ON Employees.EmployeeID = TimeSheetData.EmployeeID
GROUP BY Employees.EmployeeID, Employees.UserName
HAVING Max(TimeSheetData.DateEntered)>DateAdd("d",7,Now())
PS - Are you sure you want to use Now() rather than Date(). Now() includes the time of day. Date includes only the date. You probably also want to strip time from the DateEntry also. So you may actually want:
HAVING DateValue(Max(TimeSheetDat
Also Stephen,
Please start a new thread for a new question.