UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Unable To Get Distinct Value, Access 2010    
 
   
hafsarabic
post Feb 12 2019, 01:29 PM
Post#1



Posts: 156
Joined: 28-April 15



Hi,
I need to get distinct value for the StuID field. If I create the query using only one field (e.g. StuID) it works fine. But if I use multiple field it is showing all the records.

CODE
SELECT DISTINCT StuID, ClassID, AttDate
FROM tblAttendance
WHERE (((tblAttendance.AttDate)>=[Forms]![frmAttendanceReport]![txtStartingDay] And (tblAttendance.AttDate)<=[Forms]![frmAttendanceReport]![txtEndingDay]));


How can I solve this please?

Regards.
Go to the top of the page
 
MadPiet
post Feb 12 2019, 01:36 PM
Post#2



Posts: 2,828
Joined: 27-February 09



What's your expected output?

If you use DISTINCT, it applies to the combination of all columns in the field list. That's just the way DISTINCT works.
If you want to get the unique StudentID's and then return the other columns, do the DISTINCT first on the first query, and then join back to the table in a second query.

If you want only the StudentIDs that were present between the two dates, you can do something like

CODE
SELECT s.StudentID
FROM Student s
WHERE EXISTS (SELECT 1 FROM tblAttendance WHERE (((tblAttendance.AttDate)>=[Forms]![frmAttendanceReport]![txtStartingDay] And (tblAttendance.AttDate)<=[Forms]![frmAttendanceReport]![txtEndingDay])));

But if you want the dates in there, then you have to return values from the Attendance table.
This post has been edited by MadPiet: Feb 12 2019, 01:56 PM
Go to the top of the page
 
nvogel
post Feb 12 2019, 03:16 PM
Post#3



Posts: 911
Joined: 26-January 14
From: London, UK


Try this:

SELECT StuID, MAX(ClassID) AS ClassID, MAX(AttDate) AS AttDate
FROM tblAttendance
WHERE ...
GROUP BY StuID;
Go to the top of the page
 
hafsarabic
post Feb 13 2019, 06:58 AM
Post#4



Posts: 156
Joined: 28-April 15



Thank you both for your help. Now it's working fine. The modified SQL is :
CODE
SELECT StuID, MAX(ClassID) AS ClassID, MAX(AttDate) AS AttDate
FROM tblAttendance
WHERE (((tblAttendance.AttDate)>=[Forms]![frmAttendanceReport]![txtStartingDay] And (tblAttendance.AttDate)<=[Forms]![frmAttendanceReport]![txtEndingDay]))
GROUP BY StuID;

Best regards.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd February 2019 - 09:35 AM