Query question

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

Hi,
I have an app for pesticide tracking in a greenhouse. A combo box in a
subform allows selection of various pesticides as ingredients for a spray
application. This combo box is fed by the following query:

SELECT tbl_Pesticides.PesticideID, tbl_Pesticides.[Pesticide Name],
tbl_Pesticides.[EPA Reg #], [tbl_Pesticides]![Quantity
Purchased]-[qry_Quantities Used]![Quantity Used] AS [Quantity on Hand],
tbl_Units.Unit, tbl_Pesticides.REI, tbl_Pesticides.[Quantity Purchased]
FROM tbl_Units INNER JOIN ([qry_Quantities Used] RIGHT JOIN tbl_Pesticides
ON [qry_Quantities Used].tbl_Ingredients_PesticideID =
tbl_Pesticides.PesticideID) ON tbl_Units.UnitID = tbl_Pesticides.UnitID
ORDER BY tbl_Pesticides.[Pesticide Name];

The problem I'm having is with this part of it:

[tbl_Pesticides]![Quantity Purchased]-[qry_Quantities Used]![Quantity Used]
AS [Quantity on Hand],

it works fine for each pesticide for which [qry_Quantities Used] returns a
[Quantity Used], but where there is an entry in [tbl_Pesticides] with a
[Quantity Purchased], but no [Quantity Used], it fails to show a [Quantity
on Hand].

Here's the SQL from [qry_Quantities Used] if that helps any.

SELECT tbl_Pesticides.[Quantity Purchased],
qry_Ingredients.tbl_Ingredients_PesticideID, Sum(qry_Ingredients.Quantity)
AS [Quantity Used]
FROM tbl_Pesticides INNER JOIN (tbl_Ingredients INNER JOIN qry_Ingredients
ON tbl_Ingredients.PesticideID =
qry_Ingredients.tbl_Ingredients_PesticideID) ON tbl_Pesticides.PesticideID =
tbl_Ingredients.PesticideID
GROUP BY tbl_Pesticides.[Quantity Purchased],
qry_Ingredients.tbl_Ingredients_PesticideID;

Thanks for any ideas.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put Nz() around the values that may have NULL. E.g.:

Nz([tbl_Pesticides]![Quantity Purchased],0) - Nz([qry_Quantities
Used]![Quantity Used], 0) AS [Quantity on Hand]

Read the Access help article on the Nz() function.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDK0yoechKqOuFEgEQI8LgCgublc6g8SEcNt0OVknx9J1Rldf+QAnAzi
6Z1eMH4OLeb5B0l7nUb53J5L
=7zH7
-----END PGP SIGNATURE-----
 
Perfect.

Thanks much.


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put Nz() around the values that may have NULL. E.g.:

Nz([tbl_Pesticides]![Quantity Purchased],0) - Nz([qry_Quantities
Used]![Quantity Used], 0) AS [Quantity on Hand]

Read the Access help article on the Nz() function.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDK0yoechKqOuFEgEQI8LgCgublc6g8SEcNt0OVknx9J1Rldf+QAnAzi
6Z1eMH4OLeb5B0l7nUb53J5L
=7zH7
-----END PGP SIGNATURE-----

Hi,
The problem I'm having is with this part of it:

[tbl_Pesticides]![Quantity Purchased]-[qry_Quantities Used]![Quantity Used]
AS [Quantity on Hand],

it works fine for each pesticide for which [qry_Quantities Used] returns a
[Quantity Used], but where there is an entry in [tbl_Pesticides] with a
[Quantity Purchased], but no [Quantity Used], it fails to show a [Quantity
on Hand].
Click to expand...
Click to expand...
 
Back
Top