Use a query with a MAX column to show ONLY the max value... and it's not working

[GrandMasterTuck]

Hi folks. I have a complex database that has lots of tables, but for the purpose of this question, I'll simplify things:

I have a database with a table called Employees. Employees table has EmployeeID, Name, Date of Hire and Gender. I have a second table called WorkDays with EmployeeID, DateOfWork, TimeOfWork. The EmployeeID fields are linked in a one-to-many relationship.

I have a form that has a field called ChosenDate, and some DCount fields below that. The ChosenDate has an AfterUpdate event that writes a TempVar called TheDate, and sets that to the value you enter in the ChosenDate field. The query I mentioned above has the following Criteria in the DateOfWork field:

Total: Max
Criteria: <=[TempVars]![TheDate]

What this all is SUPPOSED to do is just show me the LATEST work date for EVERY employee in the Employees table that is less than or equal to TheDate. But it's not doing that. It's showing me ALL work dates for every employee in Employees that is before TheDate. So for example, if Joe Smith had a work dates of 6/1/15, 6/2/15 and 6/3/15 (3 separate records) in the WorkDates table, and I type 6/4/15 in ChosenDate, I want to see ONE record for Joe Smith in my query for the work date of 6/3/15. Instead, I see THREE records for Joe Smith, one for each of those dates, which throws my calculations off (obviously).

Here's the kicker: I deleted WorkTime from the query, and THEN it works and I get one record for each employee with their work date that was closest to TheDate. But I also want to see the time they worked.

Solution #1 I tried was to add WorkTime back into the query and set that field to Max like WorkDate is set, but then I get the wrong record because it shows me the maximum TIME instead of the maximum date. So for instance, if Joe Smith worked at 0900 on 6/1, 1800 on 6/2 and 1500 on 6/3, and I enter 6/4/2015 into ChosenDate, it shows me 6/2's record instead of 6/3's record.

This is driving me crazy. Does anyone have any insight into why this is messing with me? Does it have anything to do with the order of the columns in the query? (I'm still really patchy at SQL queries, so I use the builder)

Thanks for any input you guys might have. I really appreciate it.

 

[jdraw]

Please post the sql for the query.

 

[plog]

A poorly structured database strikes again. You should not have these 2 distinct fields: DateOfWork, TimeOfWork. You should only have one of them to store both pieces of data--its called a Date/Time field for a reason.

You do that and you can get the query you want. The problem is occuring because you GROUP BY the TimeOfWork field. Because of that, for each unique TimeOfWork value, you are going to get a record in your query.

It gets better. Suppose you change TimeOfWork to Max to get the latest value--not gonna work. Its gonna pull the latest TimeOfWork value regardless of the DateOfWork record it belongs to--you'll get the number of records you expect, but the data will not be . The way to fix this is to structure those fields together and then MAX that Date/Time field.

 

[GrandMasterTuck]

A poorly structured database strikes again.

Yeah, I know, bad example. Again, the data I referenced in my question is not representative of the data I'm working with. It's just there to try to explain how the two tables are working with each other, and I picked a poor set of field types to try to illustrate with. I know about the date/time field issue, and I wish the problem I had were that simple.

Let's try a different example. Let's say that tblEmployees table has these fields:

EmployeeName
EmployeeID (primary key)
EmployeeHireDate

And the tblWorkDates table has these fields:

WorkID
EmployeeID (linked)
WorkDate
HoursClocked
StationNumber

I want a dataset that shows me EVERY employee in tblEmployees and the MAXIMUM (newest) WorkDate from tblWorkDates, as well as the number of hours clocked for that particular WorkDate (regardless as to whether or not it's higher or lower than any other WorkDates' ClockedHours) and the StationNumber worked (regardless of whether it's higher or lower than any other WorkDate for that employee). I want the HoursClocked and StationNumber values that are associated to the WorkDate that shows up in my query (the Max([WorkDate]).

Now let's say I have the following records in tblEmployees:

EmployeeName: Joe ; EmployeeID: 1
EmployeeName: Sally; EmployeeID: 2

And the following records in tblWorkDates:

EmployeeID: 1 ; WorkDate: 1/1/16 ; HoursClocked: 5 : StationNumber: 1
EmployeeID: 2 ; WorkDate: 1/1/16 ; HoursClocked: 3 ; StationNumber: 2
EmployeeID: 1 ; WorkDate: 1/2/16 ; HoursClocked: 8 ; StationNumber: 1
EmployeeID: 2 ; WorkDate: 1/3/16 ; HoursClocked: 4 ; StationNumber: 1 <--This should be in my query
EmployeeID: 1 ; WorkDate: 1/4/16 ; HoursClocked: 7 ; StationNumber: 1 <--This should be in my query
EmployeeID: 2 ; WorkDate: 1/2/16 ; HoursClocked: 3 ; StationNumber: 2

Given the above data, when I run the query, I want TWO records, one for each of the EmployeeID's, and the record with the MAX date AND THAT RECORD'S ASSOCIATED HOURS AND STATION. So I should get this:

EmployeeID: 1 ; Max([WorkDate]): 1/4/16 ; HoursClocked: 7 ; StationNumber: 1
EmployeeID: 2 ; Max([WorkDate]): 1/3/16 ; HoursClocked: 4 ; StationNumber: 1

Does that make sense?

Please post the sql for the query.

SELECT tblEmployees.EmployeeID, Max(tblWorkDates.WorkDate) AS MaxOfWorkDate, tblWorkDates.HoursClocked, tblWorkDates.StationNumber
FROM tblEmployees 
INNER JOIN tblWorkDates ON tblEmployees.EmployeeID = tblWorkDates.EmployeeID
GROUP BY tblEmployees.EmployeeID, tblWorkDates.HoursClocked, tblWorkDates.StationNumber;

I have also attached the offending database to this post so you guys can see what I'm talking about. Thanks a million!

 

[jdraw]

I mocked up your data in a table called WorkHours.

I'm using 2 queries.

The first is called QMaxWorkDateByEmployee

SELECT Workhours.employeeID
, Max(Workhours.WorkDate) AS MaxOfWorkDate
FROM Workhours
GROUP BY Workhours.employeeID;

And the second is called QWorkHoursFinal and uses the first query joined with the table.

SELECT QMaxWorkDateByEmployee.employeeID, 
QMaxWorkDateByEmployee.MaxOfWorkDate, 
Workhours.HoursClocked, Workhours.StationNumber
FROM QMaxWorkDateByEmployee INNER JOIN Workhours ON 
(QMaxWorkDateByEmployee.employeeID = Workhours.EmployeeID) 
AND (QMaxWorkDateByEmployee.MaxOfWorkDate = Workhours.WorkDate);

The table:

ID	EmployeeID	HoursClocked	WorkDate	StationNumber
1	1	               5	01/01/2016	1
2	2	               3	01/01/2016	2
3	1	               8	01/02/2016	1
4	2	               4	01/03/2016	1
5	1	               7	01/04/2016	1
6	2	               3	01/02/2016	2

The result:

employeeID	MaxOfWorkDate	HoursClocked	StationNumber
2	               01/03/2016	4	1
1	               01/04/2016	7	1

Hope it's helpful.

 

[GrandMasterTuck]

jdraw, that's awesome, and YES, it works. I never realized you could JOIN two fields together, I always thought it was limited to one (though don't ask me why... I often think silly things like this). Thanks so much for your help, as this has helped me solve yet another problem. To wit (for anyone that cares):

It is possible that any one employee may have MORE THAN ONE record for any one date, each with different StationNumbers and different numbers of HoursClocked. For instance, Joe might work Station 1 for 8 hours on 1/4/16, and then work Station 2 for 2 hours on that same date, which means he's got two records in that WorkDates table for that one date (which is, as it happens, the date that shows in my query of Max dates). To keep it so it only returns ONE record for Joe, I had to modify that second query to only fetch the Max HoursClocked from that first query.

I also figured out how to incorporate a third table into my query to fetch data from yet another place and hook it to the correct dataset. And yeah, I know, it's not a representation of good database design, because I should just record that data in tblStationJobs as a column in tblWorkDates. I just did it for illustration purposes.

I have attached my database to this message to help anyone else that's struggling with this. Thanks again to jdraw for diving in to save a drowning swimmer.

 

[jdraw]

Good stuff. Happy to help.