Results 1 to 3 of 3
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Adding fields to a Union query (2000)

    You can't use a period in the aliases ... As This.That, change it to (for example) ... As ThisThat.

    But your query still won't run after that. It refers to a non-existent field mgp.mgp and there are other errors.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding fields to a Union query (2000)

    I need to modify the Query3x query in the attached mdb because the last two fields in each result set are different from one result set to another.
    So I've prepared the following query(Query3xx):

    SELECT mgpX.Data, mgpX.Ora, mgpX.NORD, mgpX.CNOR, mgpX.CSUD, mgpX.SUD, mgpX.SARD, mgpX.[Pr A], mgp.MGP, mgp.Campo12, 0 as ma1dom.MA1, 0 as ma1dom.Campo12, 0 as ma1off.MA1, 0 as ma1off.Campo12
    FROM mgpX LEFT JOIN mgp ON (mgpX.Ora = mgp.Ora) AND (mgpX.Data = mgp.Data)
    UNION
    SELECT mgpX.Data, mgpX.Ora, mgpX.NORD, mgpX.CNOR, mgpX.CSUD, mgpX.SUD, mgpX.SARD, mgpX.[Pr A], mgp.MGP, mgp.Campo12, 0 as ma1dom.MA1, 0 as ma1dom.Campo12, 0 as ma1off.MA1, 0 as ma1off.Campo12
    FROM mgpX RIGHT JOIN mgp ON (mgpX.Ora = mgp.Ora) AND (mgpX.Data = mgp.Data)
    union
    SELECT mgpX.Data, mgpX.Ora, mgpX.NORD, mgpX.CNOR, mgpX.CSUD, mgpX.SUD, mgpX.SARD, mgpX.[Pr A], 0 as mgp.MGP, 0 as mgp.Campo12, ma1dom.MA1, ma1dom.Campo12, 0 as ma1off.MA1, 0 as ma1off.Campo12
    FROM mgpX LEFT JOIN ma1dom ON (mgpX.Ora = ma1dom.Ora) AND (mgpX.Data = ma1dom.Data)
    union
    SELECT mgpX.Data, mgpX.Ora, mgpX.NORD, mgpX.CNOR, mgpX.CSUD, mgpX.SUD, mgpX.SARD, mgpX.[Pr A], 0 as mgp.MGP, 0 as mgp.Campo12, ma1dom.MA1, ma1dom.Campo12, 0 as ma1off.MA1, 0 as ma1off.Campo12
    FROM mgpX RIGHT JOIN ma1dom ON (mgpX.Ora = ma1dom.Ora) AND (mgpX.Data = ma1dom.Data)
    union
    SELECT mgpX.Data, mgpX.Ora, mgpX.NORD, mgpX.CNOR, mgpX.CSUD, mgpX.SUD, mgpX.SARD, mgpX.[Pr A], 0 as mgp.MGP, 0 as mgp.Campo12, 0 as ma1dom.MA1, 0 as ma1dom.Campo12, ma1off.MA1, ma1off.Campo12
    FROM mgpX LEFT JOIN ma1off ON (mgpX.Ora = ma1off.Ora) AND (mgpX.Data = ma1off.Data)
    UNION
    SELECT mgpX.Data, mgpX.Ora, mgpX.NORD, mgpX.CNOR, mgpX.CSUD, mgpX.SUD, mgpX.SARD, mgpX.[Pr A], 0 as mgp.MGP, 0 as mgp.Campo12, 0 as ma1dom.MA1, 0 as ma1dom.Campo12, ma1off.MA1, ma1off.Campo12
    FROM mgpX RIGHT JOIN ma1off ON (mgpX.Ora = ma1off.Ora) AND (mgpX.Data = ma1off.Data);

    but when I run it I get the message: "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding fields to a Union query (2000)

    Thank you Hans, I've removed the period from the aliases and now it works.

    SELECT mgpX.Data, mgpX.Ora, mgpX.NORD, mgpX.CNOR, mgpX.CSUD, mgpX.SUD, mgpX.SARD, mgpX.[Pr A], mgp.MGP, mgp.Campo12, 0 as ma1domMA1, 0 as ma1domCampo12, 0 as ma1offMA1, 0 as ma1offCampo12
    FROM mgpX LEFT JOIN mgp ON (mgpX.Ora = mgp.Ora) AND (mgpX.Data = mgp.Data)
    UNION
    SELECT mgpX.Data, mgpX.Ora, mgpX.NORD, mgpX.CNOR, mgpX.CSUD, mgpX.SUD, mgpX.SARD, mgpX.[Pr A], mgp.MGP, mgp.Campo12, 0 as ma1domMA1, 0 as ma1domCampo12, 0 as ma1offMA1, 0 as ma1offCampo12
    FROM mgpX RIGHT JOIN mgp ON (mgpX.Ora = mgp.Ora) AND (mgpX.Data = mgp.Data)
    union
    SELECT mgpX.Data, mgpX.Ora, mgpX.NORD, mgpX.CNOR, mgpX.CSUD, mgpX.SUD, mgpX.SARD, mgpX.[Pr A], 0 as mgpMGP, 0 as mgpCampo12, ma1dom.MA1, ma1dom.Campo12, 0 as ma1offMA1, 0 as ma1offCampo12
    FROM mgpX LEFT JOIN ma1dom ON (mgpX.Ora = ma1dom.Ora) AND (mgpX.Data = ma1dom.Data)
    union
    SELECT mgpX.Data, mgpX.Ora, mgpX.NORD, mgpX.CNOR, mgpX.CSUD, mgpX.SUD, mgpX.SARD, mgpX.[Pr A], 0 as mgpMGP, 0 as mgpCampo12, ma1dom.MA1, ma1dom.Campo12, 0 as ma1offMA1, 0 as ma1offCampo12
    FROM mgpX RIGHT JOIN ma1dom ON (mgpX.Ora = ma1dom.Ora) AND (mgpX.Data = ma1dom.Data)
    union
    SELECT mgpX.Data, mgpX.Ora, mgpX.NORD, mgpX.CNOR, mgpX.CSUD, mgpX.SUD, mgpX.SARD, mgpX.[Pr A], 0 as mgpMGP, 0 as mgpCampo12, 0 as ma1domMA1, 0 as ma1domCampo12, ma1off.MA1, ma1off.Campo12
    FROM mgpX LEFT JOIN ma1off ON (mgpX.Ora = ma1off.Ora) AND (mgpX.Data = ma1off.Data)
    UNION
    SELECT mgpX.Data, mgpX.Ora, mgpX.NORD, mgpX.CNOR, mgpX.CSUD, mgpX.SUD, mgpX.SARD, mgpX.[Pr A],0 as mgpMGP, 0 as mgpCampo12, 0 as ma1domMA1, 0 as ma1domCampo12, ma1off.MA1, ma1off.Campo12
    FROM mgpX RIGHT JOIN ma1off ON (mgpX.Ora = ma1off.Ora) AND (mgpX.Data = ma1off.Data);

Posting Permissions

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