Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Thanked 0 Times in 0 Posts
    I have this complex set of queries, with one query, querying another.

    QV1 and QV2 are queries, that pass results to QV3.

    QV3 is a union query, this is the SQL :-

    SELECT [MEMOID], [work area], [Document ID], [DOCISS], [Document Name], [Expr1], [shiftcode], [deptcode]
    FROM [QV1]
    UNION ALL SELECT [MEMOID], [work area], [SODOCID], [SOISS], [Document Name], [Expr2], [shiftcode], [deptcode]
    FROM [QV2];
    This returns, exactly 40 records. each a distinct document.

    QV4 has, as an input, the QV3 query, but then just has some links into a documents table, and another table (the work area column, has an index number, not text, the text is the wa table)

    When i run the QV4 query, i am now getting exactly 42 records.
    Two of the documents are repeating themselves. even though the query is based on the 40 records in QV3.
    The SQL of the QV4 query is:-

    SELECT Documents.[Reference Number], wa.wa, QV3.MEMOID, QV3.[work area], QV3.[Document ID], QV3.DOCISS, QV3.[Document Name], QV3.Expr1, QV3.shiftcode, QV3.deptcode
    FROM wa INNER JOIN (QV3 INNER JOIN Documents ON QV3.[Document Name] = Documents.[Document Name]) ON = QV3.[work area]
    ORDER BY Documents.[Reference Number];
    I don't understand why these 2 documents are repeating themselves. i have checked the Documents table, they are only in that table once.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts
    I suspect that either wa has multiple records with the same id, or Documents has multiple records with the same Document Name (or both).

Posting Permissions

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