Solved Summarise by date (1 Viewer)

Krayna

Member
Local time
Today, 22:33
Joined
Sep 24, 2020
Messages
514
Hi,
Would anyone be able to help me with my query - it was working previously and now it is no longer doing its job and can't figure out why!
I would like to summarise by date so although each student may have multiple entries on one day, I would like to show one with first and last times.
Database enclosed
 

Attachments

  • testAbsences.accdb
    1.4 MB · Views: 148

Ranman256

Well-known member
Local time
Today, 17:33
Joined
Apr 9, 2015
Messages
3,962
to get the MIN date of a student, you can only query 2 fields : studentID and the MIN(Date)
same with MAX.

use this MIN query as an example:

SELECT tblStudentAbsenceAhead.fldAStudentID, Min(tblStudentAbsenceAhead.fldADate) AS MinOffldADate
FROM tblStudentAbsenceAhead
WHERE (((tblStudentAbsenceAhead.fldARegisterCodeID)<>53))
GROUP BY tblStudentAbsenceAhead.fldAStudentID;
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:33
Joined
May 7, 2009
Messages
14,607
some records will appear twice on same day
because they have different "registrar" reasons?
 

Krayna

Member
Local time
Today, 22:33
Joined
Sep 24, 2020
Messages
514
@Ranman256 - your query is too selective. Would like to view each date student is absent with same register code.

@arnelgp - I would have thought that was the case but if you look through my sample database in qryAbsencesSummaryList you find student #256 twice and all details seem to match up, same with #241??
 

plog

Banishment Pending
Local time
Today, 16:33
Joined
May 11, 2011
Messages
10,652
First, you should never use First nor Last. The should be removed from Access because they don't function like they people intuitively believe. Instead use MIN or MAX.

Second, you have 2 records for #256 because you are using a GROUP BY on [fldANote] and you have 2 distinct values in that field causing them to go into different records.

fldAbsenceLogID=12383 has a NULL value in [fldANote]
fldAbsenceLogID=12384 has an empty string in [fldANote]

To our human eyes, they both look devoid of data, but to a computer there's a very real difference between NULL and an empty string. Because of that difference and your GROUP BY they are appearing on different lines. To fix this you need to make them either both NULL or empty string.

How do I know this? Because I did a LEN(fldANote). The length of a NULL value is NULL, the length of an empty string is 0.

Code:
SELECT tblStudentAbsenceAhead.fldAbsenceLogID, tblStudentAbsenceAhead.fldANote, Len([fldANote]) AS FieldLength
FROM tblStudentAbsenceAhead
WHERE (((tblStudentAbsenceAhead.fldAbsenceLogID)=12383 Or (tblStudentAbsenceAhead.fldAbsenceLogID)=12384));
 

Krayna

Member
Local time
Today, 22:33
Joined
Sep 24, 2020
Messages
514
Interesting. Thanks for looking into that.
I have changed to Max/Min instead of First/Last and works great!
 

Users who are viewing this thread

Top Bottom