Problem Using DISTINCT

C

Chaplain Doug

Access 2003. I am using the following SQL code in my query:

SELECT DISTINCT GDate, DTotalLessons([GDate]) AS Lessons
FROM Grades;

I only want the DISTINCT to work on the GDate field and NOT on the "Lessons"
field. How may I accomplish this? Thanks and God bless.
 
J

Jerry Whittle

What does your DTotalLessons([GDate]) function do?

Which DTotalLessons([GDate]) record do you want to return? You only get one
pe GDate if you want it distinct.

Possibly show us the function and a sample of the data and expected return.
 
J

John Spencer

Distinct works on the entire row.

Perhaps if you posted a sample of the data you are getting and a sample of
what you want to get, someone can suggest a solution.
 
C

Chaplain Doug

Dear Jerry:

When I run

SELECT DISTINCT GDate FROM Grades;

I get about twenty distinct dates on which our graders posted grades. When
I use:

SELECT DISTINCT GDate, DTotalLessons([GDate]) AS Lessons FROM Grades;

I get about 5,000 records returned, one for each grade entered in the
database.

What I am after is just twenty records returned, one for each distinct date
along with the total number of lessons graded on that date. The number is
returned by the DTotalLessons function.

The function DTotalLessons is as follows:

Public Function DTotalLessons(DDate As Date)
DTotalLessons = DCount("[GDate]", "[Summary Query]", "[GDate]=#" +
CStr(DDate) + "# AND [Grade]>=0")
DTotalLessons = DTotalLessons + DCount("[GDate]", "[Summary Query
Archive]", "[GDate]=#" + CStr(DDate) + "# AND [Grade]>=0")
End Function

--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org


Jerry Whittle said:
What does your DTotalLessons([GDate]) function do?

Which DTotalLessons([GDate]) record do you want to return? You only get one
pe GDate if you want it distinct.

Possibly show us the function and a sample of the data and expected return.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Chaplain Doug said:
Access 2003. I am using the following SQL code in my query:

SELECT DISTINCT GDate, DTotalLessons([GDate]) AS Lessons
FROM Grades;

I only want the DISTINCT to work on the GDate field and NOT on the "Lessons"
field. How may I accomplish this? Thanks and God bless.
Click to expand...
Click to expand...
 
C

Chaplain Doug

Dear John:
When I run

SELECT DISTINCT GDate FROM Grades;

I get about twenty distinct dates on which our graders posted grades. When
I use:

SELECT DISTINCT GDate, DTotalLessons([GDate]) AS Lessons FROM Grades;

I get about 5,000 records returned, one for each grade entered in the
database.

What I am after is just twenty records returned, one for each distinct date
along with the total number of lessons graded on that date. The number is
returned by the DTotalLessons function.

The function DTotalLessons is as follows:

Public Function DTotalLessons(DDate As Date)
DTotalLessons = DCount("[GDate]", "[Summary Query]", "[GDate]=#" +
CStr(DDate) + "# AND [Grade]>=0")
DTotalLessons = DTotalLessons + DCount("[GDate]", "[Summary Query
Archive]", "[GDate]=#" + CStr(DDate) + "# AND [Grade]>=0")
End Function
 
J

John Spencer

I think you might want the following.

SELECT GDate, Count(Grades) as Lessons
FROM Grades
WHERE Grade >= 0
GROUP BY GDate

However, it seems looking a DTotalLessons that you may have more than just the
grades table involved.

When I look at your query, I really don't understand why it is giving you 5000
records instead of the 20 you expect. It should be returning the 20 you expect,
unless the calculated value for lessons is varying.
 
C

Chaplain Doug

Thank you for the help John. The "GROUP BY" phrase is new to me. God bless.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org


John Spencer said:
I think you might want the following.

SELECT GDate, Count(Grades) as Lessons
FROM Grades
WHERE Grade >= 0
GROUP BY GDate

However, it seems looking a DTotalLessons that you may have more than just the
grades table involved.

When I look at your query, I really don't understand why it is giving you 5000
records instead of the 20 you expect. It should be returning the 20 you expect,
unless the calculated value for lessons is varying.


Chaplain said:
Dear John:
When I run

SELECT DISTINCT GDate FROM Grades;

I get about twenty distinct dates on which our graders posted grades. When
I use:

SELECT DISTINCT GDate, DTotalLessons([GDate]) AS Lessons FROM Grades;

I get about 5,000 records returned, one for each grade entered in the
database.

What I am after is just twenty records returned, one for each distinct date
along with the total number of lessons graded on that date. The number is
returned by the DTotalLessons function.

The function DTotalLessons is as follows:

Public Function DTotalLessons(DDate As Date)
DTotalLessons = DCount("[GDate]", "[Summary Query]", "[GDate]=#" +
CStr(DDate) + "# AND [Grade]>=0")
DTotalLessons = DTotalLessons + DCount("[GDate]", "[Summary Query
Archive]", "[GDate]=#" + CStr(DDate) + "# AND [Grade]>=0")
End Function
Click to expand...
Click to expand...
 
Top