Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002

    Unanswered: Going Crazy with Date Query

    First I am not a big Access Pro, but not a beginner.

    I have a query that works. But it does not get me all the data I want.

    There are 2 tables Contracts and Account Adjustments.
    This query shows me the adjustments that fall between the contract start and expire date. But I also need to see the contract account numbers in the same query that do not have an adjustment.

    Any suggestions, and if possible not to technical.


    SELECT Contracts.[Account Number], Contracts.Start, Contracts.Expire, Sum([Account Adjustments].AMOUNT) AS SumOfAMOUNT
    FROM Contracts LEFT JOIN [Account Adjustments] ON Contracts.[Account Number] = [Account Adjustments].CUST_NUM
    WHERE ((([Account Adjustments].ADJ_DATE)>=[start] And ([Account Adjustments].ADJ_DATE)<=[expire]))
    GROUP BY Contracts.[Account Number], Contracts.Start, Contracts.Expire;

  2. #2
    Join Date
    Jul 2002
    You might try building another query that gives you your "second" set of data. Then, just add the word UNION between your first query, and the second one in your query statement, and you should get both sets of data on one results page. The only down side is that you can't go back to the "Design" mode to make further changes, it will be stuck in the SQL statement view. So, if you like using the design view you'll need to save the two query's each under their own query name, then union them in a third query.

Posting Permissions

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