I have a query which shows the number of items sold for the current and previous month for sale items. The query is currently made up of three queries: one which finds the number of items sold for the current month, one which finds the number of items sold for the previous month and one which joins the two together. I can then see the current and previous months figures for any month.
The problem is the query only shows the items which appear in both months. If a new item is sold in month two but it wasn't sold in month one then it won't appear.
you need a full outer join: items sold in both months, plus items sold in first month but not in second, plus items sold in second month but not in first
since access does not support full outer join syntax, you can get the same results with: (items sold in both months, plus items sold in first month but not in second) UNION ALL (items sold in second month but not in first)
the first part of the UNION ALL is a left outer join and the second part is a right outer join where you exclude the matches
you could also get the same results with (left outer join) UNION (right outer join) because here you are relying on UNION to remove the matched rows, which would be returned by both the left and right outer joins
if this doesn't make sense, show me your existing query, and i'll rewrite it for you
It's probably the wrong way of doing things, just my attempt at trying to solve the problem.
I basically have a table called month which I store fields about the month such as comments etc... At the end of the month I want to do a report which shows the information for the month table, and lists items sold in this month compared to last month e.g.
okay, with your existing qryCurrent and qryPrevious queries, the only thing i can suggest is that you take your last query, and change the inner joins to left outer:
, tblItem.[Item Number]
ON tblMonth.MonthNumber = qryCurrent.MonthNumber
ON tblMonth.MonthNumber = qryPrevious.MonthNumber
ON qryCurrent.[Item Number] = tblItem.[Item Number]
AND qryPrevious.[Item Number] = tblItem.[Item Number]
however, i have a feeling that this isn't going to work because qryCurrent and qryPrevious sometimes -- obviously -- don't find a particular item, and therefore the last join, to get the description, is not going to match the item, because it's built so that it assumes there's a match for both current and previous
in other words, you need to tear down qryCurrent and qryPrevious and/or join the month table to the item table first (this may need a cross join)
Thats more a less the stage i got to, where the results of the query are dependant upon a match of an item between qryPrevious and qryCurrent.
What would be the way to scrap qryPrevious and qryCurrent and do the sql where if the item was in the previous or current month but had no match in the corresponding month to show the total as zero (0) rather than just ommiting the item?