ACCESS FEED: RE: [MS_AccessPros] Including all clubs in a report

Rabu, 28 Maret 2012

RE: [MS_AccessPros] Including all clubs in a report

Robin-

Add a filter:

SELECT ClubsActive.ClubID, tAttendance.TotalMembers,
tAttendance.MonthPerCent, tAttendance.MeetingsHeld,
tAttendance.NewMembers, tAttendance.Terminated,
tAttendance.ReportMonthID, tAttendance.ReportMonth
FROM tAttendance RIGHT JOIN ClubsActive ON tAttendance.ClubID =
ClubsActive.ClubID
WHERE tAttendance.ClubID IS NULL;

That should list all clubs that have no matching record in tAttendance.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

------------------------------------------

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Thursday, March 29, 2012 2:36 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Including all clubs in a report

Using Access 2007

This is a variation on a report that I had help with in September last year.

I have an attendance report needs to include clubs that have not made
a monthly report. This SQL was intended to show all clubs but it only
shows those with data in "tAttendance".

SELECT ClubsActive.ClubID, tAttendance.TotalMembers,
tAttendance.MonthPerCent, tAttendance.MeetingsHeld,
tAttendance.NewMembers, tAttendance.Terminated,
tAttendance.ReportMonthID, tAttendance.ReportMonth
FROM tAttendance RIGHT JOIN ClubsActive ON tAttendance.ClubID =
ClubsActive.ClubID

What have I missed?

Many thanks,

Robin Chapple