T
Thorson
I had a series of queries set up to track inventories, however I recently
added an additional table and I am having trouble designing the database to
incorporate the new table. My problem is that the inventories are tracked by
two things, not just one: BullID and Location.
The initial query design first summed the number of Straws for each Bull ID
at each location using "qrySemenInventorySum":
SELECT tblSemenInventory.Bull, Sum(tblSemenInventory.NumberofStraws) AS
SumOfNumberofStraws, tblSemenInventory.Location
FROM tblSemenInventory
GROUP BY tblSemenInventory.Bull, tblSemenInventory.Location;
I then had the database calculate the number of breedings (1 breeding=1
straw) from "tblAI" in "qrySemenUsed":
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];
that number was then subtracted from "tblSemenInventory" using the
"qrySemenStrawInventory":
SELECT qrySemenInventorySum.Bull, [SumOfNumberofStraws]-[CountOfBull] AS
Semen, qrySemenInventorySum.Location
FROM qrySemenInventorySum LEFT JOIN qrySemenUsed ON
(qrySemenInventorySum.Bull = qrySemenUsed.Bull) AND
(qrySemenInventorySum.Location = qrySemenUsed.[C-Unit]);
I have now added two tables, the first would show transfers of breeding
straws from one unit to another ("tblSemenTransfer") and the other table
shows breeding straws that were broken or had malfunctions ("tblSemenLost").
If someone could help me design my queries to calculate the total Breeding
Straw Inventory that would be great. Here is what I have so far:
"tblSemenInventoryTransfer" Fields include:
BullID
NumberOfStraws
PreviousLocation
NewLocation
"tblSemenLost" Fields include:
BullID
NumberOfStraws
Date
ReasonForLoss
I would like to set up queries that would subtract straws that left a
location in "tblSemenInventoryTransfer", represented in the PreviousLocation
field, and add straws that added to a different location, represented in the
NewLocation field. I would also like to subtract the NumberOfStrawsLost in
"tblSemenLost".
I started by creating a table that joined "tblSemenInventoryTransfer" and
"tblSemenInventory" and subtracted the straws from semen Inventory. My
problem was when I tried to transfer new straws to the new location. A
location may not have had previous straws (which would be represented in
"tblSemenInventory") which was messing up what was displayed when I ran the
queries. I also am not sure how to set up the basic order of my queries since
I have to things to join (BullID and Location).
This is probably really confusing and long, but if someone could give me
some ideas for my design that would be great, thanks.
added an additional table and I am having trouble designing the database to
incorporate the new table. My problem is that the inventories are tracked by
two things, not just one: BullID and Location.
The initial query design first summed the number of Straws for each Bull ID
at each location using "qrySemenInventorySum":
SELECT tblSemenInventory.Bull, Sum(tblSemenInventory.NumberofStraws) AS
SumOfNumberofStraws, tblSemenInventory.Location
FROM tblSemenInventory
GROUP BY tblSemenInventory.Bull, tblSemenInventory.Location;
I then had the database calculate the number of breedings (1 breeding=1
straw) from "tblAI" in "qrySemenUsed":
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];
that number was then subtracted from "tblSemenInventory" using the
"qrySemenStrawInventory":
SELECT qrySemenInventorySum.Bull, [SumOfNumberofStraws]-[CountOfBull] AS
Semen, qrySemenInventorySum.Location
FROM qrySemenInventorySum LEFT JOIN qrySemenUsed ON
(qrySemenInventorySum.Bull = qrySemenUsed.Bull) AND
(qrySemenInventorySum.Location = qrySemenUsed.[C-Unit]);
I have now added two tables, the first would show transfers of breeding
straws from one unit to another ("tblSemenTransfer") and the other table
shows breeding straws that were broken or had malfunctions ("tblSemenLost").
If someone could help me design my queries to calculate the total Breeding
Straw Inventory that would be great. Here is what I have so far:
"tblSemenInventoryTransfer" Fields include:
BullID
NumberOfStraws
PreviousLocation
NewLocation
"tblSemenLost" Fields include:
BullID
NumberOfStraws
Date
ReasonForLoss
I would like to set up queries that would subtract straws that left a
location in "tblSemenInventoryTransfer", represented in the PreviousLocation
field, and add straws that added to a different location, represented in the
NewLocation field. I would also like to subtract the NumberOfStrawsLost in
"tblSemenLost".
I started by creating a table that joined "tblSemenInventoryTransfer" and
"tblSemenInventory" and subtracted the straws from semen Inventory. My
problem was when I tried to transfer new straws to the new location. A
location may not have had previous straws (which would be represented in
"tblSemenInventory") which was messing up what was displayed when I ran the
queries. I also am not sure how to set up the basic order of my queries since
I have to things to join (BullID and Location).
This is probably really confusing and long, but if someone could give me
some ideas for my design that would be great, thanks.