query gives duplicate results in former fiscal year 1

[THWatson]

Using Access 2003 in 2000 format

I have a totals query for which the sql is as follows

SELECT tblMembers.MemberID, [LastName] & ", " & [PreferredName] AS FullName, tblAttendance.MeetingTypeID, tblMeetingType.MeetingType, Count(tblAttendance.MeetingDate) AS CountOfMeetingDate, tblAttendance.HoursSpent, tblMembers.LastName, tblMembers.PreferredName, First(tblAttendance.MeetingDate) AS FirstOfMeetingDate, [CountOfMeetingDate] AS Show, RetThur(Forms!frmDateSelector!txtStartDate,Forms!frmDateSelector!txtEndDate) AS Thursdays, IIf(Weekday(DateSerial(Year([MeetingDate]),12,25))=5 And Weekday(DateSerial(Year([MeetingDate]),1,1))=5,50,IIf(Weekday(DateSerial(Year([MeetingDate]),12,25))=5 Or Weekday(DateSerial(Year([MeetingDate]),1,1))=5,51,52)) AS WeekCount
FROM tblMeetingType INNER JOIN (tblMembers INNER JOIN tblAttendance ON tblMembers.MemberID = tblAttendance.MemberID) ON tblMeetingType.MeetingTypeID = tblAttendance.MeetingTypeID
WHERE (((tblAttendance.MeetingDate) Between [Forms]![frmDateSelector]![txtStartDate] And [Forms]![frmDateSelector]![txtEndDate]))
GROUP BY tblMembers.MemberID, [LastName] & ", " & [PreferredName], tblAttendance.MeetingTypeID, tblMeetingType.MeetingType, tblAttendance.HoursSpent, tblMembers.LastName, tblMembers.PreferredName, IIf(Weekday(DateSerial(Year([MeetingDate]),12,25))=5 And Weekday(DateSerial(Year([MeetingDate]),1,1))=5,50,IIf(Weekday(DateSerial(Year([MeetingDate]),12,25))=5 Or Weekday(DateSerial(Year([MeetingDate]),1,1))=5,51,52))
HAVING (((tblAttendance.MeetingTypeID)=1))
ORDER BY tblMembers.LastName, tblMembers.PreferredName;

The fiscal year for the organization is October 1 through September 30.

When I run this query for October 1,2008 through September 30, 2009 I get one result for each member.

However, when I run the query for October 1, 2007 through September 20, 2008 I get 2 results for each member. One result totals meetings attended for the 2007 months, the second result totals meetings attended for the 2008 months.

Is there a way to fix this?

Thanks.

Tom

 

[PHV]

Seems like WeekCount is not the same for 2007 and 2008 ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

PHV
Yep, of course. Hadn't spotted that.

Too bad that I need that for the report.

This query is built on a prior query, the sql for which is

SELECT tblMembers.MemberID, [LastName] & ", " & [PreferredName] AS FullName, tblAttendance.MeetingTypeID, tblMeetingType.MeetingType, Count(tblAttendance.MeetingDate) AS CountOfMeetingDate, tblAttendance.HoursSpent, tblMembers.LastName, tblMembers.PreferredName, First(tblAttendance.MeetingDate) AS FirstOfMeetingDate, [CountOfMeetingDate] AS Show, RetThur(Forms!frmDateSelector!txtStartDate,Forms!frmDateSelector!txtEndDate) AS Thursdays, IIf(Weekday(DateSerial(Year([MeetingDate]),12,25))=5 And Weekday(DateSerial(Year([MeetingDate]),1,1))=5,50,IIf(Weekday(DateSerial(Year([MeetingDate]),12,25))=5 Or Weekday(DateSerial(Year([MeetingDate]),1,1))=5,51,52)) AS WeekCount
FROM tblMeetingType INNER JOIN (tblMembers INNER JOIN tblAttendance ON tblMembers.MemberID = tblAttendance.MemberID) ON tblMeetingType.MeetingTypeID = tblAttendance.MeetingTypeID
WHERE (((tblAttendance.MeetingDate) Between [Forms]![frmDateSelector]![txtStartDate] And [Forms]![frmDateSelector]![txtEndDate]))
GROUP BY tblMembers.MemberID, [LastName] & ", " & [PreferredName], tblAttendance.MeetingTypeID, tblMeetingType.MeetingType, tblAttendance.HoursSpent, tblMembers.LastName, tblMembers.PreferredName, IIf(Weekday(DateSerial(Year([MeetingDate]),12,25))=5 And Weekday(DateSerial(Year([MeetingDate]),1,1))=5,50,IIf(Weekday(DateSerial(Year([MeetingDate]),12,25))=5 Or Weekday(DateSerial(Year([MeetingDate]),1,1))=5,51,52))
HAVING (((tblAttendance.MeetingTypeID)=1))
ORDER BY tblMembers.LastName, tblMembers.PreferredName;

I see that if I change the WeekCount from "Group By" to "First" that gives single rather that double results...however the picture I get at the end, in the report, is no longer fully accurate.

Tom

 

[PHV]

Do you really need this WeekCount field in the output ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

Yes. It has to do with Regular meetings and Makeup meetings, and calculations for Perfect Attendance.

A member can only do as many Makeup meetings in a month as there are available Thursdays (sometimes 3 if Christmas or New Years falls on a Thursday, sometimes 4, and sometimes 5)

Tom

 

[PHV]

Sorry, I can't help you mixing legal and fiscal years ...

 

[THWatson]

That's the problem. Thanks for trying, PHV

Tom

 

[THWatson]

PHV
Your question Do you really need this WeekCount field in the output? pushed me to find an alternate method for getting the Week Count into the report. I removed it from the query, and now get the query results that I want, and also the results that I want in the report.

Thanks.

Tom