pulling data from 2 tables into report 1

[THWatson]

Using Access 2000

I give up. I have worked at this puzzle for far too long. I have prodded it, poked it, thought about it, dreamed about it, worried it, to no avail. I resisted making this post for several days but ran out of ideas.

The database is for a Kiwanis club. It's principle function is to track members and their attendance.

A member is granted a perfect attendance award when he/she attends 52 meetings in any consecutive 12 month period. This is a compilation of Regular meetings attended plus Makeup meetings attended.

The relevant tables and their structure is as follows...
tblMembers
MemberID (PK)
LastName
PreferredName

tblAttendance
AttendanceID (PK)
MemberID (FK to tblMembers)
MeetingDate
Present(Yes/No)
MeetingID This field is related to MemberID in tblGuests, which tracks the guests that a member brings to meetings.

tblMakeupMeetings
MakeupID (PK)
MemberID (FK to tblMembers)
MakeupType (could be any of 9 different types)
MakeupDate

What I want to be able to produce is a report which lists the members, their Total attendance for a specific period of time - dates selected through text boxes on frmDateSelector. The report would list the member's name, the # of regular meetings attended, and the # of makeup meetings attended, during that period.
e.g.
Member Regular Meetings Makeup Meetings
Fred Amers 12 1
Susan Bond 4 8
Frank Frobisher 2 1

I can get a report for Regular meetings attended. I can also get a separate report for the Makeup meetings attended.

However, ideally, I would like to get this data into one report. And that looks like a straight-forward problem. However, I end up with any member who has attended on more than one regular and more than one makeup meeting date being listed in correspondingly multiple times in the report.

e.g.
Fred Amers 1 1 Total 2
Fred Amers 1 1 Total 2
Where, in fact, Amers attended 2 Regular meetings plus 1 Makeup meeting during the period selected.

I have tried Totals queries and combined the data. I have tried Union queries.

Any push in the correct direction would be wonderfully appreciated.

Thanks.

Tom

 

[dhookom]

I expect a union query should work. What happens when you try it? I'm not sure why you have two separate attendance tables.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
What happens is that I get incorrect results - duplicate entries, etc.

The way the database is designed is this...
1. From the Main Menu click "Post Attendance." This takes you to frmAttendance.
2. On frmAttendance there is a calendar from which you can select the date for which you wish to post attendance, and a list box of members. In the list box, you can select 1 member or several members, or all members and then click a command button that posts attendance for that date.
3. On frmAttendance, there is also a command button for posting Makeup Meetings. Clicking that takes you to frmMakeupMeetings. That form has a list of members plus a list of the 15 makeup meeting types - District Executive, Other Kiwanis Club, etc.
Click one member and one makeup meeting type and then click a command button to post the makeup meeting. You are then asked if you wish to post another Makeup meeting (Yes/No). If Yes, make additional member and makeup type meeting...if No, return to Attendance form.

There may well be a more efficient way of designing this. It's just that the Regular Attendance is simple and straight-forward (select date, then number of members and post, and attendance posted for Regular meetings can only be on a Thursday). The Makeup Meeting is more complex (select date, only 1 member at a time, only 1 Makeup type at a time, and Makeup meetings are not restricted to Thursdays - they can be any day). There's also a restriction on banking: a Makeup meeting can only be counted if it is 7 days prior to or 7 days following a Regular meeting.

Your thoughts?

Tom

 

[dhookom]

Why are you mixing the addition of makeup meetings with the printing of a report? Add data is adding data and printing reports of data is printing reports of data. Why not enter makeup meetings about when they occur?

Why would you have duplicates with a union query? Are you entering duplicates between the two tables>?

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Thanks for the questions. Unless I'm not totally understanding the questions, here are my answers:

I didn't think I was mixing the addition of Makeup meetings with the printing of a report. Makeup meetings are entered for the date when the occur and stored in a table. The reports can be printed anytime.

I'm also not clear why I end up with duplicates in the union query. I'm not entering duplicates between the 2 tables. Although it could be possible for a member to attend a Regular meeting and a Makeup meeting on the same day.

What I want to get is a report that shows the Total meetings attended by each member - Regular and Makeups - within dates selected. I can get that for Regular meetings attended. And I can get that, separately, for Makeup meetings attended. It's only when I try to pull the two together and produce one report that it goes haywire.

Would you design the tables differently from the way they are?

Tom

 

[dhookom]

I think I would have had one table for meetings and makeups.

What is the SQL view of the UNION query that you created that you think is returning duplicates?

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Thanks. I'll have a look at how to put the Attendance stuff together into one table.

Here's the SQL

SELECT DISTINCTROW tblMembers.MemberID, [PreferredName] & " " & [LastName] AS FullName, Count(tblAttendance.Present) AS CountOfPresent, tblMembers.LastName, tblMembers.PreferredName, tblAttendance.MeetingDate AS TheDate,''
FROM tblMembers INNER JOIN tblAttendance ON tblMembers.MemberID = tblAttendance.MemberID
WHERE (((tblAttendance.MeetingDate) Between [Forms]![frmDateSelector]![txtStartDate] And [Forms]![frmDateSelector]![txtEndDate]))
GROUP BY tblMembers.MemberID, [PreferredName] & " " & [LastName], tblMembers.LastName, tblMembers.PreferredName, tblAttendance.MeetingDate
ORDER BY tblMembers.LastName, tblMembers.PreferredName
UNION SELECT tblMakeupMeetings.MemberID, [LastName] & ", " & [PreferredName] AS FullName, tblMakeupMeetings.MakeupType, tblMembers.LastName, tblMembers.PreferredName, tblMakeupMeetings.MakeupDate AS TheDate, Count(tblMakeupMeetings.MakeupDate) AS CountOfMakeupDate
FROM tblMembers INNER JOIN tblMakeupMeetings ON (tblMembers.MemberID = tblMakeupMeetings.MemberID) AND (tblMembers.MemberID = tblMakeupMeetings.MemberID)
WHERE (((tblMakeupMeetings.MakeupDate) Between [Forms]![frmDateSelector]![txtStartDate] And [Forms]![frmDateSelector]![txtEndDate]))
GROUP BY tblMakeupMeetings.MemberID, [LastName] & ", " & [PreferredName], tblMakeupMeetings.MakeupType, tblMembers.LastName, tblMembers.PreferredName, tblMakeupMeetings.MakeupDate
ORDER BY tblMembers.LastName, tblMembers.PreferredName;

Tom

 

[dhookom]

I'm not sure how this query would return duplicates without seeing your data. However, you have a double join in the second select

FROM tblMembers INNER JOIN tblMakeupMeetings ON (tblMembers.MemberID = tblMakeupMeetings.MemberID) AND (tblMembers.MemberID = tblMakeupMeetings.MemberID)

I would probably create the union without any filtering. Then filter the union query afterward.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Thanks, Duane
I removed that double join, but it didn't make any difference in the output.

I have worked on and around this for long enough that I'm starting to lose focus.

So, I'm going to take a run at remaking the Attendance table, to incorporate the Makeup Meetings into it, and thus have only 1 table.

That may help me get my focus back.

It's one of those things where I'm doing this for a friend of mine. He provided me with initial details about what he wanted, and I started creating the database. Then he raised the subject of makeup meetings, and showed me the rules concerning them and I tried to do that as a separate piece because of the rules. But I'm going to go back to the drawing boards.

Thanks a lot for your helpful input.

Tom

 

[THWatson]

Duane
Just to close this off, I redesigned the tblAttendance to include the Makeup meetings as well as the Regular meetings, and that solved the problem.

Thanks a lot for the push to do that.

Tom