Inventory based on ID and Location

[Thorson]

I just set up a table and a series of queries to track straw inventory at
three different units. I would like my final query to display the number of
straws at each unit, but something is calculating incorrectly throughout my
queries.

My first query "qryStrawsUsed" looks at a table and counts the number of
straws used for each ID, it also gives the location it was used at.

My second query "qryStrawInventoryBull" sums the number of straws for each
ID at each unit from an inventory table.

The third and final query "qryStrawInventory" subtracts the number of
straws used for each ID from "qryStrawsUsed" from the inventory
"qryStrawInventoryBull".

My problem is that it isn't matching up the locations and subtracting the
straws correctly. For example, I have ID Bushwacker, Bushwacker has 330
straws at UnitDSAC and 400 at UnitURB, accourding to "qryStrawsUsed" 2 straws
were used at UnitDSAC. In the final query "qryStrawInventory" it subtracts
two from both UnitDSAC and UnitURB for the ID Bushwacker, instead of just two
from UnitDSAC.

If someone could help me out with this it would be great, let me know if I
need to clarify more.
Thanks.

 

[Thorson]

1) The First Item is a table: 'tblAI', users enter new breeding information
into this table, the fields are:
CowID
BullID
Date

2) The second item is a query: 'qryStrawUsed', this counts the Bull IDs (1
straw for each Bull ID- Bull ID can also be referred to as StrawID) in
'tblAI' and links that table with a separate previous made query to identify
the location of the Cow and therefore the location the straw was used. The
fields are:
BullID
BullID -total is set to "count"
C-Unit -this is the location, locations include (DSAC, ORR, and URB)

3) The next item is a table: 'tblStrawInventory', users enter new
acquisitions of straws into this table. Fields include:
BullID
NumberOfStraws
Location -locations include (DSAC,ORR, and URB)

4) The next item is a query: 'qryStrawInventorySum', this query simply sums
the number of straws for each "bullID" at each "Location" in
'tblStrawInventory'. Fields include:
BullID
NumberOfStraws -total set to "sum"
Location

5) The last item is a query: 'qryStrawInventory'. This query joins
'qryStrawInventorySum' and 'qryStrawUsed' by BullID, the join is set so that
all records in 'qryStrawInventorySum' are shown and only those matching in
'qryStrawUsed' are shown. Fields include:
BullID
Straws: [SumOfNumberOfStraws]-[CountOfBull]
Location

The equation in the final query (Straws:
[SumOfNumberOfStraws]-[CountOfBull]) subtracts the total inventory of straws
used in 'qryStrawUsed' from the total straws in inventory summed up in
'qryStrawInventorySum'

Hopefully this offers a clearer picture of what is going on.

 

[Thorson]

Here is the SQL for qryStrawUsed:
SELECT tblArtificialInsemination.Bull, Count(tblArtificialInsemination.Bull)
AS CountOfBull, qryCurrentInventory2AllAnimals.[C-Unit]
FROM tblArtificialInsemination INNER JOIN qryCurrentInventory2AllAnimals ON
tblArtificialInsemination.EarTag = qryCurrentInventory2AllAnimals.EarTag
GROUP BY tblArtificialInsemination.Bull,
qryCurrentInventory2AllAnimals.[C-Unit];

Here is the SQL for qryStrawInventorySum:
SELECT tblStrawInventory.Bull, Sum(tblStrawInventory.NumberofStraws) AS
SumOfNumberofStraws1, tblStrawInventory.Location
FROM tblStrawInventory
GROUP BY tblStrawInventory.Bull, tblStrawInventory.Location;

Here is the SQL for qryStrawInventory:
SELECT qryStrawInventorySum.Bull, [SumOfNumberofStraws]-[CountOfBull] AS
Straws, qryStrawInventorySum.Location
FROM qryStrawInventorySum LEFT JOIN qryStrawUsed ON
qryStrawInventorySum.Bull = qryStrawUsed.Bull;

 

[Thorson]

I noticed that when I was coping and pasting the SQL on here, I had changed
some things around. I fixed the name so it no longer has the "1". It did
not change my problem with the queries.
--
Thorson

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

This may not be the answer but you have:

in query "qryStrawInventorySum":

Sum(tblStrawInventory.NumberofStraws) AS SumOfNumberofStraws1,

in query "qryStrawInventory":

[SumOfNumberofStraws]-[CountOfBull] AS Straws,

Notice the different names on SumOfNumberofStraws - you have an extra
"1" in qryStrawInventorySum. This will cause problems in the
calculation in qryStrawInventory.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSWfGWYechKqOuFEgEQIyCgCg0RtA0ZqZYAtKXmSrKRDna0GeHNcAnieR
pCUYOFgxijZ8GRFoJAwUXHyv
=O+ZY
-----END PGP SIGNATURE-----

Here is the SQL for qryStrawUsed:
SELECT tblArtificialInsemination.Bull, Count(tblArtificialInsemination.Bull)
AS CountOfBull, qryCurrentInventory2AllAnimals.[C-Unit]
FROM tblArtificialInsemination INNER JOIN qryCurrentInventory2AllAnimals ON
tblArtificialInsemination.EarTag = qryCurrentInventory2AllAnimals.EarTag
GROUP BY tblArtificialInsemination.Bull,
qryCurrentInventory2AllAnimals.[C-Unit];

Here is the SQL for qryStrawInventorySum:
SELECT tblStrawInventory.Bull, Sum(tblStrawInventory.NumberofStraws) AS
SumOfNumberofStraws1, tblStrawInventory.Location
FROM tblStrawInventory
GROUP BY tblStrawInventory.Bull, tblStrawInventory.Location;

Here is the SQL for qryStrawInventory:
SELECT qryStrawInventorySum.Bull, [SumOfNumberofStraws]-[CountOfBull] AS
Straws, qryStrawInventorySum.Location
FROM qryStrawInventorySum LEFT JOIN qryStrawUsed ON
qryStrawInventorySum.Bull = qryStrawUsed.Bull;

 

[Thorson]

Thank you, that fixed the problem
--
Thorson

I noticed that when I was coping and pasting the SQL on here, I had changed
some things around. I fixed the name so it no longer has the "1". It did
not change my problem with the queries.

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

I believe it may be query qryStrawInventory. If you also JOIN by the
Location = [C-Unit] it may work.

SELECT SIS.Bull, SIS.[SumOfNumberofStraws]-SU.[CountOfBull] AS Straws,
SIS.Location
FROM qryStrawInventorySum AS SIS LEFT JOIN qryStrawUsed AS SU ON
SIS.Bull = SU.Bull And SIS.Location = SU.[C-Unit]

The bull can eat straw at different units, therefore, the unit also has
to be considered when joining the tables/queries. The way to do that is
to include those columns in the ON phrase of the JOIN operation.

I aliased the table names so the query is easier to view in SQL view.
You can change them back to the query/table names if you want. Aliasing
the table/query names doesn't affect the performance of the query.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSWfn/oechKqOuFEgEQJLKACgiKQfO8zpBZxJf7ZfH+1gtUkaDUEAoLrC
patn5kRCIF05+kOIIW+fRFan
=OVjk
-----END PGP SIGNATURE-----