SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: IIF problems

  1. #1
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    IIF problems

    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

    Code:
    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.

    help!

    MONKEY
    monkey - the rest is history

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    try this one:

    Code:
    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;
    It will give you the number of documents for each meeting.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  3. #3
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kleineme
    Hi,

    try this one:

    Code:
    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;
    It will give you the number of documents for each meeting.

    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!!

    cheers
    monkey - the rest is history


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •