none
Joinning the results of two queries RRS feed

  • Question

  • Hi,

    I'm using Access 2003, on XP.

    Using SQL, I would like two join 2 query's results into one column.

    I have one table:

    ZMXC01p

    Coulmns:

    Type/Micron: text
    NSI: number
    Date: date

    Two queries:

    SELECT TOP 3 TBL_ZMXC015p.Date, TBL_ZMXC015p.[Type/Micron], TBL_ZMXC015p.[NSI]
    FROM TBL_ZMXC015p
    WHERE TBL_ZMXC015p.[Date]<#01/03/2007# AND TBL_ZMXC015p.[Type/Micron] = '238/190'
    ORDER BY TBL_ZMXC015p.Date DESC;

    and

    SELECT TOP 3 TBL_ZMXC015p.Date, TBL_ZMXC015p.[Type/Micron], TBL_ZMXC015p.NSI
    FROM TBL_ZMXC015p
    WHERE (((TBL_ZMXC015p.Date)>#1/3/2007#) AND ((TBL_ZMXC015p.[Type/Micron])='238/190'))
    ORDER BY TBL_ZMXC015p.Date ASC;

    I want the results of these two queries in 1 coulmn named NSI.

    So if the first query returns:

    01/02/2007 238/190  £23.45
    01/01/2007 238/190  £34.54

    and the second:

    01/06/2007 238/190  £12.00
    01/05/2007 238/190  £232.50
    01/04/2007 238/190  £3.25

    i would like:

    01/06/2007 238/190  £12.00
    01/05/2007 238/190  £232.50
    01/04/2007 238/190  £3.25
    01/02/2007 238/190  £23.45
    01/01/2007 238/190  £34.54

    So the results from two queries into one column. Thks

    Wednesday, October 29, 2008 9:01 AM

Answers

  • Hi, not sure about right syntaxis for Access, but probably:

     

     

    SELECT TOP 3 TBL_ZMXC015p.Date, TBL_ZMXC015p.[Type/Micron], TBL_ZMXC015p.[NSI]
    FROM TBL_ZMXC015p
    WHERE TBL_ZMXC015p.[Date]<#01/03/2007# AND TBL_ZMXC015p.[Type/Micron] = '238/190'
    union all
    SELECT TOP 3 TBL_ZMXC015p.Date, TBL_ZMXC015p.[Type/Micron], TBL_ZMXC015p.NSI
    FROM TBL_ZMXC015p
    WHERE (((TBL_ZMXC015p.Date)>#1/3/2007#) AND ((TBL_ZMXC015p.[Type/Micron])='238/190'))
    ORDER BY TBL_ZMXC015p.Date ASC;
    Wednesday, October 29, 2008 9:05 AM

All replies

  • Hi, not sure about right syntaxis for Access, but probably:

     

     

    SELECT TOP 3 TBL_ZMXC015p.Date, TBL_ZMXC015p.[Type/Micron], TBL_ZMXC015p.[NSI]
    FROM TBL_ZMXC015p
    WHERE TBL_ZMXC015p.[Date]<#01/03/2007# AND TBL_ZMXC015p.[Type/Micron] = '238/190'
    union all
    SELECT TOP 3 TBL_ZMXC015p.Date, TBL_ZMXC015p.[Type/Micron], TBL_ZMXC015p.NSI
    FROM TBL_ZMXC015p
    WHERE (((TBL_ZMXC015p.Date)>#1/3/2007#) AND ((TBL_ZMXC015p.[Type/Micron])='238/190'))
    ORDER BY TBL_ZMXC015p.Date ASC;
    Wednesday, October 29, 2008 9:05 AM
  • Hi Denis,

    Thank you very much, this has worked correctly, many thks.

    Tom.

    Tuesday, November 11, 2008 10:37 AM