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;
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.