locked
Need help developing a query RRS feed

  • Question

  • I have one table, A, which has a date in its key.  The dates may have gaps, such as 10/12/2012, 10/15,2012, 10/20/2012, etc.

    I have another table, B, which also has a date in its key.   It, too, may have gaps between the days. 

    The rows in the two tables can be connected by an ID field.

    I want to create a query so that I can make a report like the one below.  Can someone help me with the SQL statement I would need for the query to drive this report?

    TIA

    LAS

    A date

          B date  >= A date above

          B date  > = A date above  (sorted by date descending

    10/12/2012

          10/12/2012   data

          10/12/2012    data

          10/13/2012    data

    10/15/2012        

          10/16/2012    data

          10/17/2012    data

          10/18/2012    data

    10/19/2012

       etc.


    • Edited by LAS14 Monday, December 31, 2012 1:58 AM
    Monday, December 31, 2012 1:51 AM

Answers

  • Use a union query to build a complete date list.  Use your table and field names --   

    SELECT Activities.Date AS DATELIST
    FROM  Activities
    ORDER BY Activities.Date 
    UNION SELECT  camcafe.DueDate
    FROM camcafe;

    SELECT IIf(Activities.Date Is Null,(SELECT MAX([XX].DATE) FROM  Activities AS [XX] WHERE [XX].DATE <= camcafe.DueDate),Activities.Date) AS ACTIVITYDATE, camcafe.DueDate
    FROM (DATELIST LEFT JOIN Activities ON DATELIST.DATELIST = Activities.Date) LEFT JOIN camcafe ON DATELIST.DATELIST = camcafe.DueDate
    WHERE (((camcafe.DueDate) Is Not Null))
    ORDER BY DATELIST.DATELIST;

    • Marked as answer by LAS14 Tuesday, January 1, 2013 2:26 AM
    Monday, December 31, 2012 4:39 AM

All replies

  • You mention that the tables are connected by an ID field but in your sample there is no trace of this field. How does the ID come into this?


    Regards, Hans Vogelaar

    Monday, December 31, 2012 2:08 AM
  • Use a union query to build a complete date list.  Use your table and field names --   

    SELECT Activities.Date AS DATELIST
    FROM  Activities
    ORDER BY Activities.Date 
    UNION SELECT  camcafe.DueDate
    FROM camcafe;

    SELECT IIf(Activities.Date Is Null,(SELECT MAX([XX].DATE) FROM  Activities AS [XX] WHERE [XX].DATE <= camcafe.DueDate),Activities.Date) AS ACTIVITYDATE, camcafe.DueDate
    FROM (DATELIST LEFT JOIN Activities ON DATELIST.DATELIST = Activities.Date) LEFT JOIN camcafe ON DATELIST.DATELIST = camcafe.DueDate
    WHERE (((camcafe.DueDate) Is Not Null))
    ORDER BY DATELIST.DATELIST;

    • Marked as answer by LAS14 Tuesday, January 1, 2013 2:26 AM
    Monday, December 31, 2012 4:39 AM
  • Hans, I would include A.ID = B.ID as part of any where clase.


    • Edited by LAS14 Tuesday, January 1, 2013 2:25 AM
    Tuesday, January 1, 2013 2:24 AM