We help IT Professionals succeed at work.

Need help with creating a cross tab report that would identify the last date an employee entered time.

155 Views
Last Modified: 2019-03-21
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
Comment
Watch Question

CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Thank you sincerely.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
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?
CERTIFIED EXPERT

Commented:
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)

Open in new window

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

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())

Open in new window


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(TimeSheetData.DateEntered)) > DateAdd("d",7,Date())[/code]

Also Stephen,
Please start a new thread for a new question.