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

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Commented:
Cross tabs require three pieces of information.  You've only specified two - Employee, Max(DateEntered).

EmployeeID	UserName	MaxOfDateEntered
1	(unknown)	
2	Mickey Mouse	3/21/2019 8:54:21 AM
3	Donald Duck	2/21/2019 5:28:24 AM
4	Minnie Mouse	3/21/2019 8:54:21 AM
5	Bugs Bunny	3/21/2019 8:54:21 AM
6	Daffy Duck	
7	Tom the cat	3/15/2019 1:55:53 PM
8	Jerry the Mouse	
9	Droopy the Dog	
10	Woody Woodpecker	
11	"George Geef ""Goofy"""	
13	Daisy Duck	

Open in new window


This is the query that produces the list above.
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;

Open in new window

The key element is using a left join.  That tells the query engine to include ALL employees even when there is no related record in the timesheet table.

Author

Commented:
Thank you sincerely.
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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?
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

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial