There are several steps to get this to work.
Firstly, use totals queries to get the total supplied and sold:
1. Create a query using the Supply table.
2. Depress the Total button on the toolbar
Access adds a total row to the grid.
3. In the total row under ItemID, accept Group By.
4. In the Total row under Quantity, choose Sum.
In the Field row in front of Quantity, type an alias for the column name,
e.g.:
SupplyQty: Quantity
5. In the Field row, enter an expression that represents your date period.
For example, to report by quarter, you would enter this in one column:
TheYear: Year(Supply.[Date])
and then in the Field row in the next column:
TheQuarter: DatePart("q", Supply.[Date])
Accept Group By under both fields.
6. Test: the query should show the total quantity supplied per item per
period. Save the query with a name such as qrySupplySummary.
7. Repeat the process for the Sale table, to get qrySaleSummary.
8. Now, you say you want a row output for every every product in every
period, whether there were sales or not. The dates must come from somewhere,
so you will need to create a table of the periods. The 2 fields to match the
example above would be:
TheYear Number Integer
TheQuarter Number Integer
with the combination of the 2 being the primary key.
Save as (say) tblQuarter.
9. Create a query using your Stock and tblQuarter tables. There should be no
line joining these to in the upper pane of query design (a Cartesian
product.) This gives every possible combination (i.e. every quarter for
every stock item.) The query will need to output 4 fields: ItemID, [Item
Name], TheYear, and TheQuarter. Save as (say) qryStockQuarter.
10. Create a new query, using the 3 queries you created as input 'tables.'
Join them on the fields: StockID, TheYear, and TheMonth.
11. Double-click the line joining ItemID in qryStockQuarter to ItemID in
qrySupplySummary. Access pops up a dialog with 3 choices. Choose the one
that says:
All items from qryStockQuarter, and any matches from qrySupplySummary.
Repeat for TheYear and TheMonth.
(This is called an outer join.)
12. Repeat step 10 and 11, to make an outer join between
qryStockQuarter.ItemID and qrySaleSummary.ItemID as well.
13. To get the difference, type an expression like this into a fresh column
in the Field row:
Diff: IIf([SupplyQty] Is Null, 0, [SupplyQty]) - IIf([SaleQty] Is Null,
0, [SaleQty])
That gets you every combination of stock item and date period, with the
totals supplied and sold, and the difference.
At this point, you have a query that gives you the figures you wanted, so
you can create report. There are a couple of ways to to this. One is to
create the columns in the report (Page Setup dialog.) The other is to use a
crosstab query. That's going to get tricky, since you want 3 values and not
just one for each item. If you want to proceed down that path, see:
http://allenbrowne.com/ser-67.html#MultipleValues
I have a database for monitoring a small shop. Need help in creating the
report for the stock status. I have the following tables
[quoted text clipped - 12 lines]
