Help -- Is my there any way to not count duplicate values here?

First, let me say that this query isn't mine, but it does almost
everything I want it to do. What it outputs is a horizontal columnar
calendar from a vertical table with the structure:

<RecNo><ID Number><Date><Mem Type>

The output is:

<Year><Month><Name><Total><1><2>...<31>
2006 1 [Name] 32 S I ... S
2006 2 [Name] 28 S S ... S
....
2006 12 [Name] 31 S S ... I

As you can see, above, it is possible for a student to attend two times
in one day (in Jan). I need that occurrence to only be counted as
once. This will eliminate any erroneous double entries, as well. The
SELECT statement yields the same totals if DISTINCT or DISTINCTROW are
applied. The query is this:

PARAMETERS [Forms].[Attendance_Entry_frm].[ID Number] IEEEDouble;
TRANSFORM First(IIf(Attendance_tbl![Mem Type]=1,"S","I")) AS Type
SELECT Year([Date]) AS [Year], Month([Date]) AS [Month], [First Name] &
" " & [Last Name] AS Name, Count(Attendance_tbl.Date) AS Total
FROM Member_List_tbl INNER JOIN Attendance_tbl ON Member_List_tbl.[ID
Number]=Attendance_tbl.[ID Number]
WHERE (((Member_List_tbl.[ID Number])=Forms.Attendance_Entry_frm.[ID
Number]) And (Year([Date])=Year(Date())))
GROUP BY Year([Date]), Month([Date]), [First Name] & " " & [Last Name]
PIVOT Day([Date]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

Is this just not possible, or is my basic design flawed?

.

Relevant Pages

  • Re: 2 combo boxes
    ... Providing Customers A Resource For Help With Access, ... Select the Calendar Control. ... which would run the query for all entry dates between the monday ...
    (microsoft.public.access.forms)
  • Re: 2 combo boxes
    ... Select the Calendar Control. ... Delete the combobox and add a textbox named ... MyMondayDate textbox. ... which would run the query for all entry dates between the monday date ...
    (microsoft.public.access.forms)
  • RE: Help Please
    ... ' Returns True if vardate is intNthDay instance of intWeekDay in month. ... Dim intCount As Integer ... While you can use this function directly in a query like this its not very ... Once you have this basic calendar table you can add Boolean ...
    (microsoft.public.access.queries)
  • Re: Count within intervals
    ... Each time the asset status changes, ... >I need to produce a report that counts the devices by type that were ... you need a calendar table that holds all dates this report could ... suggested query), we create a list of all dates each asset was IN. ...
    (microsoft.public.sqlserver.programming)
  • Re: Repeat record based on the start and end date
    ... Thanks for your response, however, is there any other possible soulution to ... Creating a calendar table does not seem practical. ... set up a query ... dates and all the dates in the needed range (good primary key candidate ...
    (microsoft.public.access.queries)