How can I do this?

  • Thread starter Thread starter SF
  • Start date Start date
S

SF

Hi,

I want to generate a list of communes that did not have any meeting (in
every month of the year) from 2 table:

tblOperationCommune (CommuneID, CommuneName, Partner...) 356 records
tblMeeting (MeetingID, MeetingDate, CommuneID, MeetingType...)

Can I generate a query to get the following:

Partner CommuneID Month that meeting not help
AAAA 129987 Jan 08
AAAA 123456 Jun 08
BBBB 233444 Aug 08

Hope someone give me some advice

Cheers

SF
 
First generate a list of communes that did have a meeting and the months they met.

Saved Query Name: QMeetings
SELECT Distinct CommuneID, Format(MeetingDate,"yyyymm") as MeetingMonth
FROM tblMeeting

Next you will need a table that has all the months in it in the format yyyymm
tblMonths
MMonth: Text field: Values like 200801, 200802. If you have multiple years
then you either need to cover all of them in this table or limit the records
returned in the queries with criteria.

Saved Query Name: qPossibleMeetings
SELECT CommuneID, MMonth
FROM tblOperationCommune, tblMonths

Now combine that

SELECT tblOperationCommune.*, qPossibleMeeting.MMonth as MissedMeeting
FROM (tblOperationCommune INNER JOIN (
qPossibleMeetings LEFT JOIN QMeetings
ON qPossibleMeetings.CommuneID = qMeetings.CommuneID
AND qPossibleMeetings.MMonth = qMeetings.MeetingMonth)
tblOperationsCommune.CommuneID = qPossibleMeetings.CommuneID
WHERE qMeetings.CommuneID IS NULL

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
You're going to need at least one more table, filled with the months
of the year. Using a Carthesian Product query you can get a list of
all meetings that should have taken place. Then you compare that to
the list that did take place, and the difference is the result you
seek.

-Tom.
Microsoft Access MVP
 
Back
Top