Results 1 to 3 of 3
Thread: IIF problems
Aug 10, 2004, 06:26 #1
I have a table with meeting details in and a table with documents relevent to the meetings (each document has the meeting id it identifies with). Every meeting doesn't have related documents and some meeting have more that one document.
I have written a query to show which meetings have documents, using IIF
SELECT t_meetings.startdate, t_meetings.meetinginfo, IIf([t_meetings]![meeting_id]=([t_meetingdocs]![meeting_id]),'yes','no') AS docs, t_meetings.meeting_id FROM t_meetings LEFT JOIN t_meetingdocs ON t_meetings.meeting_id = t_meetingdocs.meeting_id
the trouble is the meeting with more than one document appear once for every document - i.e. if there are 3 documents then the meeting wil appear three times.
MONKEYmonkey - the rest is history
Aug 10, 2004, 07:27 #2
try this one:
SELECT m.startdate, m.meetinginfo, m.meeting_id, count(d.meeting_id) AS docs FROM t_meetings AS m LEFT JOIN t_meetingdocs AS d ON d.meeting_id = m.meeting_id group by m.startdate, m.meetinginfo, m.meeting_id;Never ascribe to malice,
that which can be explained by incompetence.
Your code should not look unmaintainable, just be that way.
Aug 10, 2004, 08:06 #3Originally Posted by kleineme
What a super star! Not only does this work like a dream, I understand it and I didnt realise you could represent tables using AS as well! MEGA BONUS!!
cheersmonkey - the rest is history