Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union Query Fillers (Access 2003)

    I have the following in a Union Query and all works well. What I
    Cheers,
    Andy

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

    Re: Union Query Fillers (Access 2003)

    Hi Andy, here's what I think should work:

    SELECT Inv_Rcvd_MstSysKey AS SysKey, "Received" AS TranType, Inv_Rcvd_Created AS KDate, Inv_Rcvd_Qty AS Qty,KQ_KCost AS KCost,KQ_CurQOH AS NewQOH
    FROM [qry Inventory - 01 - Received], "Filler" as FldX
    UNION
    SELECT Inv_Issued_MstSysKey, "Issued", Inv_Issued_Created, Inv_Issued_Qty,Inv_Issued_Modified,NewIssuedQOH, FldX
    FROM [qry Inventory - 02 - Issued]
    UNION SELECT Inv_Returned_MstSysKey, "Returned", Inv_Returned_Created, Inv_Returned_Qty,Inv_Returned_Modified,NewReturned QOH, FldX
    FROM [qry Inventory - 03 - Returned];

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query Fillers (Access 2003)

    Giorgio,

    Thanks kindly for the help. I used your code, but had to make one small change before it worked. I moved "Filler" as FldX to directly after KQ_CurQOH AS NewQOH and then it worked perfectly.

    Cheers,
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union Query Fillers (Access 2003)

    I'd change the SQL to

    SELECT Inv_Rcvd_MstSysKey AS SysKey, "Received" AS TranType, Inv_Rcvd_Created AS KDate, Inv_Rcvd_Qty AS Qty,KQ_KCost AS KCost,KQ_CurQOH AS NewQOH, Null As FldX
    FROM [qry Inventory - 01 - Received]
    UNION
    SELECT Inv_Issued_MstSysKey, "Issued", Inv_Issued_Created, Inv_Issued_Qty,Inv_Issued_Modified,NewIssuedQOH, FldX
    FROM [qry Inventory - 02 - Issued]
    UNION SELECT Inv_Returned_MstSysKey, "Returned", Inv_Returned_Created, Inv_Returned_Qty,Inv_Returned_Modified,NewReturned QOH, FldX
    FROM [qry Inventory - 03 - Returned];

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query Fillers (Access 2003)

    Hi Hans,

    I saw your post immediately after I'd posted my reply to Giorgio. Thanks for jumping in,

    Cheers,
    Cheers,
    Andy

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

    Re: Union Query Fillers (Access 2003)

    My mistake Andy as I actually intended to place "Filler" as FldX where you moved it <img src=/S/blush.gif border=0 alt=blush width=15 height=15>.
    I believe using Null instead as Hans suggested is even better.

Posting Permissions

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