Newbie Here! Please help me to solve this query..

[EileenAchil]

Hi, I'm new to access. I'm trying to make a attendance report (Who's Still In) based on this data.



Table

My Query SELECT Attendance.[Staff ID], Attendance.StaffName, Attendance.Process, Attendance.Shiff, Attendance.Date, Attendance.Time, Attendance.Code FROM Attendance INNER JOIN Query1 ON (Attendance.Date = Query1.MaxOfDate) AND (Attendance.Time = Query1.MaxOfTime) AND (Attendance.[Staff ID] = Query1.[Staff ID]) GROUP BY Attendance.[Staff ID], Attendance.StaffName, Attendance.Process, Attendance.Shiff, Attendance.Date, Attendance.Time, Attendance.Code HAVING (((Attendance.Code)="IN")); Result: What i actually want is to calculate (total staff)& who's still in the building.
http://www.access-programmers.co.uk/...=1#post1512343

[orange]

I realize this is your first post here. It is helpful to readers if you describe your "business" in simple terms. Give an example with sample data showing input and desired output.


Seems to be cross posted. http://www.access-programmers.co.uk/...d.php?t=290664

When you cross post, tell readers you have done so. Here's why.

[CJ_London]

The answer was provided there, but the OP doesn't seem to have followed it. I can see there is one variation - from the original data provided it looked like date and time was one field, but from this thread they are separate - which is fine for a singles days worth of data, but if the table contains multiple days, it won't work. Also Date and Time are reserved words which will cause issues

e.g.

staffID...Date.............Time
1..........11/16/2016...17:30
1..........11/17/2016...17:29


max of date= 11/17/2016

max of time=17:30

result - no records returned.

change your query from

max(date) as maxofdate, max(time) as maxoftime

to max(date+time) as maxofdatetime



and your posted query to

....ON (Attendance.Date+Attendance.Time = Query1.MaxOfDateTime) AND (Attendance.[Staff ID] = Query1.[Staff ID])

and change the field names to avoid conflicts: Date means today, and Time mean now

here is a link to reserved words

https://support.office.com/en-us/art...ad=US&fromAR=1