Stock Levels of Product by Customer(Grower). Pls Help!

N

niuginikiwi

Hi,
I have a report that is driving me off the edge.
Here is my table layout.
tblSowingDetails >--- tblSowing >---tblGrower ---< tblSeedOrders ---<
tblSeedOrderDetails
and
tblSowingDetails >--- tblCropVariety --- < tblSeedOrderDetails

---< denotes the one to many relationships , where --- is the one side
and < is the many side.

Now I want to create a report that is grouped by Grower and show the
stock levels of each crop under each grower ie get the total of a
field called qtyordered in tblSeedOrderDetails and then get the total
of field called qtysown in tblSowingDetails and then find the
difference between them which will be what is left of the crop seeds
that each grower ordered and sown... with me still?..
now that is what I want it to look like (format of the report)

GrowerName
CropVarietyName QtyOrdered QtySown QtyLeft
eg Lettuce 100,000
50,000 50,000

I am familier with doing basic things with reports but I can not
figure out how to design queries with these setout of relationships
and getting the right totals filtered under appropriate grower and
cropvariety.
I know there are some brilliant minds out there who can give me a
hand.

Thanks so much in advance.
niuginikiw
Nelson, NZ
 
A

Allen Browne

A totals query with a subquery should solve it.

1. Create a query, using tblGrower, tblSeedOrders, tblSeeOrderDetails, and
tblCropVariety (but not the sowing tables.)

2. Depress the Total button on the toolbar in query design view.
Access adds a Total row to the grid.

3. In the Total row, accept Group By under fields tblGrower.GrowerID and
tblCropVariety.CropVarietyID. In the Total row under QtyOrdered, choose Sum.

4. In a fresh column of the Field row, type a subquery to get the total sown
for that grower and crop variety. The subquery will be something like this:

SumOfQtySown: (SELECT Sum(QtySown) AS SumOfQtySown
FROM tblSowing INNER JOIN tblSowingDetails
ON tblSowing.CropVarietyID = tblSowingDetails.CropVarietyID
WHERE (tblSowing.GrowerID = tblGrower.GrowerID) AND
(tblSowingDetails.CropVarietyID = tblCropVariety.CropVarietyID))

5. You can now add a field to calculate the difference:
QtyLeft: [SumOfQtyOrdered] - [SumOfQtySown]
and choose Expression under this field in the Total row.

If subqueries are new, here's an explanation:
http://allenbrowne.com/subquery-01.html

An alterative approach would be to build another totals query grouping in
GrowerID and CropVarietyID and summing QtySown. Then use this 2nd query as
an input "table" in the query above, instead of the subquery.
 
P

Pedro

Hi Allen,
Thanks so much.
One question. In your 4th Step. In the totals row of that field (ie is
the subquery field), do I select Group By or Sum?
Thanks,
niuginikiwi
Nelson, NZ
 
P

Pedro

Allen,
Yes, that worked to the best and even more of my expectation.
I haven't dealt with subqueries in access since I started and now I
can see a whole new window of problem solving.
A heartfelt thank you from across the Tasman to a 'legend' in Access
going the mile to help out people like myself.
Thanks again,
niuginikiwi
Nelson, NZ
 
Top