Calculated result in query problem

[bluedragonsbreath]

I have written a query (using SQL) that has in it a calculated field
which in itself uses a subquery to perfrom a sum() function of a list
of values. When I view the query results directly, the returned
values are calculated correctly.

I then have a report that uses this query as the data source. When I
display this calculated result in the report, it is wrong. It is
always a larger number in the report than in the query, as though it
were being multiplied by some factor. The report does nothing fancy
but display the results from the query.

Has anyone else experienced this phenomenon? Found a cure?

Your help is greatly appreciated.

BTW, I'm using Access 2003.

 

[bluedragonsbreath]

I have written a query (using SQL) that has in it a calculated field
which in itself uses a subquery to perfrom a sum() function of a list
of values. When I view the query results directly, the returned
values are calculated correctly.

I then have a report that uses this query as the data source. When I
display this calculated result in the report, it is wrong. It is
always a larger number in the report than in the query, as though it
were being multiplied by some factor. The report does nothing fancy
but display the results from the query.

Has anyone else experienced this phenomenon? Found a cure?

Your help is greatly appreciated.

BTW, I'm using Access 2003.

Here's the query:

SELECT m.lot_number, m.project_name, v.contact_name, genus_name
+' ('+nz(i.variety,' ')+')' AS kind, i.Lot_num, b.inventory_used,
i.date_tested, i.purity, i.germination, i.inert_matter, i.weeds,
i.other_crops, i.pounds_per_bag, l.seeds_sqft, i.origen,
(b.inventory_used*nz(i.restricted_seeds,0))/nz((select
sum(inventory_used) from mix_sheets_batches where lot_number =
m.lot_number),0) AS restricted
FROM mix_sheets_master AS m, codes_vendor AS v, mix_sheets_batches AS
b, inventory AS i, codes_species AS s, mix_list AS l
WHERE m.lot_number=l.lot_number
AND i.Lot_num=b.lot_num
AND v.vendor_id=m.vendor_id
AND b.lot_number=m.lot_number
AND i.genus=b.genus
AND i.variety=b.variety
AND s.genus=i.genus
AND m.active=True
AND m.batched=True
AND b.genus=l.genus
AND b.variety=l.variety
ORDER BY m.lot_number;

Then I use another query that is select * from <the above query>, so
the report does not complain about multi-level group by not allowed in
subquery.

Thanks again.