*REPOST* tracking inventory using queries

  • Thread starter Thread starter Thorson
  • Start date Start date
T

Thorson

I recently created a series of queries that track the inventory of straws
among 3 units. Each straw is identified by two things: the "Bull" and the
"Unit Location".

My problem is that when I transfer a number of straws with a "Bull" Id to a
"Unit Location" that does not have that "Bull" Id it doesn't report that the
"Unit Location" now has the straws in inventory. This is because of how I
have my relationships set up. However I do not know how to set them up
differently and get the results I want. The SQL for my query is below, any
suggestions??

SELECT qrySemenWithTransferOut.Bull,
qrySemenTransferredIn.SumOfNumberStrawsTransferred,
qrySemenWithTransferOut.SemenWithTransferOut,
IIf(IsNull([qrySemenTransferredIn]![SumOfNumberStrawsTransferred]),[qrySemenWithTransferOut]![SemenWithTransferOut],[qrySemenWithTransferOut]![SemenWithTransferOut]+[qrySemenTransferredIn]![SumOfNumberStrawsTransferred])
AS SemenWithTransferIn, qrySemenWithTransferOut.Location
FROM qrySemenWithTransferOut LEFT JOIN qrySemenTransferredIn ON
(qrySemenWithTransferOut.Bull = qrySemenTransferredIn.Bull) AND
(qrySemenWithTransferOut.Location = qrySemenTransferredIn.[New Location]);
 
Hi Thorson,
inventory databases can be quite complicated to set up. Post back with the
names of the key tables, their primary key and foreign keys and how the
tables are related.
Allen browne's website has some help for inventory databases. Try
http://allenbrowne.com/AppInventory.html

If you search these access discussion groups, you will find several
questions and answers about inventory databases.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Well I have it set up and working fine, the only problem is that when straws
for a bull are transferred from one of 3 locations that doesn't currently
have any straws for that bull, instead of counting it the query just ignores
it because of how the relationships are set up in the last query.

I looked at the link you sent me, and then more through Allen Browne's
Database but did not see an answer to my question.

I am new to this but I can go through what I have set up so far. I have
more than just a series of tables set up I also have several queries.

I do not have a primary key in my tables because the "bull ID" and every
other field may be entered more than once. However "Bull" which is the
bull's ID and Unit location is how the tables are related:

tblSemenInventory
Primary Key: None
Fields:
Bull (Indexed)-related to tblSemenInventoryTransfer and tblSemenLost and
tblArtificialInsemination
NumberOfStraws
Location

tblSemenInventoryTransfer
PrimaryKey: None
Fields:
Bull (Indexed)-related to tblSemenInventory and tblSemenLost
NumberOfStraws
DateTransferred
PrevousLocation -related through queries to tblSemenInventory and tblSemenLost
NewLocation

tblSemenLost:
Primary Key: None
Bull (Indexed)-related to tblSemenInventoryTransfer and tblSemenInventory
UnitLocation
NumberOfStraws
Date
Reason

tblArtificialInsemination:
Primary Key: EarTag
Fields:
EarTag (this is the cow's identification)
Date
Bull -related to tblSemenInventory

I also have several queries set up to calculate inventory:

qrySemenInventorySum:
Goal: to sum the straws of semen by "Bull" and "Location" using
tblSemenInventory
SQL:
SELECT tblSemenInventory.Bull, Sum(tblSemenInventory.NumberofStraws) AS
SumOfNumberofStraws, tblSemenInventory.Location
FROM tblSemenInventory
GROUP BY tblSemenInventory.Bull, tblSemenInventory.Location;

qrySemenLostBroken:
Goal: to count the number of straws recorded as lost or broken by "Bull" and
"UnitLocation" using tblSemenLost
SQL:
SELECT tblSemenLost.Bull, Sum(tblSemenLost.NumberOfStraws) AS
SumOfNumberOfStraws, tblSemenLost.UnitLocation
FROM tblSemenLost
GROUP BY tblSemenLost.Bull, tblSemenLost.UnitLocation;

qrySemenUsed:
Goal: to count the number of straws used to breed cows by "Bull" and
"Location" using tblArtificialInsemination and tblCurrentInventory2All
(tblCurrentInventory2All identifies the location of the cow and therefore the
location of the straw)
SQL:
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];

qrySemenTransferredIn
Goal: sum the number of straws transferred into the location by "Bull" and
"New Location" using tblSemenInventoryTransferred
SQL:
SELECT tblSemenInventoryTransfer.Bull,
Sum(tblSemenInventoryTransfer.NumberStrawsTransferred) AS
SumOfNumberStrawsTransferred, tblSemenInventoryTransfer.[New Location]
FROM tblSemenInventoryTransfer
GROUP BY tblSemenInventoryTransfer.Bull, tblSemenInventoryTransfer.[New
Location];

qrySemenTransferredOut:
Goal: to sum the number of straws transferred out of a location by "Bull"
and "PreviousLocation" using tblSemenTransferred
SQL:
SELECT tblSemenInventoryTransfer.Bull,
Sum(tblSemenInventoryTransfer.NumberStrawsTransferred) AS
SumOfNumberStrawsTransferred, tblSemenInventoryTransfer.PreviousLocation
FROM tblSemenInventoryTransfer
GROUP BY tblSemenInventoryTransfer.Bull,
tblSemenInventoryTransfer.PreviousLocation;

qrySemenStrawInventory:
Goal: to subtract the straws used from the beginning straw inventory using
qrySemenInventory and qrySemenUsed
SQL:
SELECT qrySemenInventorySum.Bull,
IIf(IsNull([qrySemenUsed]![CountOfBull]),[SumOfNumberofStraws],([SumOfNumberofStraws]-[qrySemenUsed]![CountOfBull])) AS Semen, qrySemenInventorySum.Location
FROM qrySemenInventorySum LEFT JOIN qrySemenUsed ON
(qrySemenInventorySum.Location = qrySemenUsed.[C-Unit]) AND
(qrySemenInventorySum.Bull = qrySemenUsed.Bull);

qrySemenWithoutLostBroken:
Goal: to subtract the straws that were lost or broken from the
qrySemenStrawInventory using qrySemenLostBroken
SQL:
SELECT qrySemenStrawInventory.Bull,
IIf(IsNull([qrySemenLostBroken]![SumOfNumberOfStraws]),[qrySemenStrawInventory]![Semen],[qrySemenStrawInventory]![Semen]-[qrySemenLostBroken]![SumOfNumberOfStraws]) AS [SemenW/outLost], qrySemenStrawInventory.Location
FROM qrySemenStrawInventory LEFT JOIN qrySemenLostBroken ON
(qrySemenStrawInventory.Bull = qrySemenLostBroken.Bull) AND
(qrySemenStrawInventory.Location = qrySemenLostBroken.UnitLocation);

qrySemenWithTransferOut
Goal: to subtract the straws transferred out by "Bull" and
"PreviousLocation" using qrySemenTransferredOut and qrySemenWithoutLostBroken
SQL:
SELECT qrySemenWithoutLostBroken.Bull,
qrySemenTransferredOut.SumOfNumberStrawsTransferred,
qrySemenWithoutLostBroken.[SemenW/outLost],
IIf(IsNull([qrySemenTransferredOut]![SumOfNumberStrawsTransferred]),[qrySemenWithoutLostBroken]![SemenW/outLost],[qrySemenWithoutLostBroken]![SemenW/outLost]-[qrySemenTransferredOut]![SumOfNumberStrawsTransferred])
AS SemenWithTransferOut, qrySemenWithoutLostBroken.Location
FROM qrySemenWithoutLostBroken LEFT JOIN qrySemenTransferredOut ON
(qrySemenWithoutLostBroken.Bull = qrySemenTransferredOut.Bull) AND
(qrySemenWithoutLostBroken.Location =
qrySemenTransferredOut.PreviousLocation);

qrySemenWithTransferIn: (The problem is with this query, if the
"NewLocation" and "Bull" combination was not in the original table
tblSemenInventory, then this query will not show the inventory for that bull
and location combination"
Goal: to add the semen transferred into a location by "bull" and
"NewLocation" using qrySemenWithTransferOut and qrySemenTransferredIn
SQL:
SELECT qrySemenWithoutLostBroken.Bull,
qrySemenTransferredOut.SumOfNumberStrawsTransferred,
qrySemenWithoutLostBroken.[SemenW/outLost],
IIf(IsNull([qrySemenTransferredOut]![SumOfNumberStrawsTransferred]),[qrySemenWithoutLostBroken]![SemenW/outLost],[qrySemenWithoutLostBroken]![SemenW/outLost]-[qrySemenTransferredOut]![SumOfNumberStrawsTransferred])
AS SemenWithTransferOut, qrySemenWithoutLostBroken.Location
FROM qrySemenWithoutLostBroken LEFT JOIN qrySemenTransferredOut ON
(qrySemenWithoutLostBroken.Bull = qrySemenTransferredOut.Bull) AND
(qrySemenWithoutLostBroken.Location =
qrySemenTransferredOut.PreviousLocation);
 
Thorson,
I can't see a way to do this using your current table setup.

If you are using Access 2007, there is a template for an inventory database
that would be a start for you.

If you set up your tables like shown below, you can run queries and they
will work.

tblBull
BullID primary key
other fields

tblTransaction
TransID primary key
BullID foreign key
TransDate
TransDirection
TransTypeID
LocationID
Quantity - No. of straws
other fields

tblLocation
LocationID primary key
other fields

tblTransType
TransTypeID primary key
other fields

Everything that happens with semen is entered in the tblTransaction, and
categorized with the bullID, date, quantity, locationID, the type of
transaction and the direction.
The type of transactions could be: Received, Transferred, Shrinkage, etc.
The trans direction can have only 2 values 1 or -1.
If semen is received, the trans direction is 1.
If semen is lost (shrinkage) the trans direction is -1.
When semen is transferred, the trans direction is 1 for the location it is
transferred to.
And in the matching transaction, the trans direction is -1 for the location
it it transferred from.

To calculate quantities, in a query create a calculated field
[TransDirection]*[Quantity]
Then you use a totals or group by query to sum the calculated field to get
the final quantity for each bullID.

There is another example database on Allen Browne's website that shows how
to set this up for a simple case of buying and selling groceries.
Here is the link
http://allenbrowne.com/TechniqueEnterCalcText.html

The title suggests something else, but it is an excellent simple example of
how to track inventory for groceries bought and sold.
The same principles can be used for your database.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



Thorson said:
Well I have it set up and working fine, the only problem is that when
straws
for a bull are transferred from one of 3 locations that doesn't currently
have any straws for that bull, instead of counting it the query just
ignores
it because of how the relationships are set up in the last query.

I looked at the link you sent me, and then more through Allen Browne's
Database but did not see an answer to my question.

I am new to this but I can go through what I have set up so far. I have
more than just a series of tables set up I also have several queries.

I do not have a primary key in my tables because the "bull ID" and every
other field may be entered more than once. However "Bull" which is the
bull's ID and Unit location is how the tables are related:

tblSemenInventory
Primary Key: None
Fields:
Bull (Indexed)-related to tblSemenInventoryTransfer and tblSemenLost and
tblArtificialInsemination
NumberOfStraws
Location

tblSemenInventoryTransfer
PrimaryKey: None
Fields:
Bull (Indexed)-related to tblSemenInventory and tblSemenLost
NumberOfStraws
DateTransferred
PrevousLocation -related through queries to tblSemenInventory and
tblSemenLost
NewLocation

tblSemenLost:
Primary Key: None
Bull (Indexed)-related to tblSemenInventoryTransfer and tblSemenInventory
UnitLocation
NumberOfStraws
Date
Reason

tblArtificialInsemination:
Primary Key: EarTag
Fields:
EarTag (this is the cow's identification)
Date
Bull -related to tblSemenInventory

I also have several queries set up to calculate inventory:

qrySemenInventorySum:
Goal: to sum the straws of semen by "Bull" and "Location" using
tblSemenInventory
SQL:
SELECT tblSemenInventory.Bull, Sum(tblSemenInventory.NumberofStraws) AS
SumOfNumberofStraws, tblSemenInventory.Location
FROM tblSemenInventory
GROUP BY tblSemenInventory.Bull, tblSemenInventory.Location;

qrySemenLostBroken:
Goal: to count the number of straws recorded as lost or broken by "Bull"
and
"UnitLocation" using tblSemenLost
SQL:
SELECT tblSemenLost.Bull, Sum(tblSemenLost.NumberOfStraws) AS
SumOfNumberOfStraws, tblSemenLost.UnitLocation
FROM tblSemenLost
GROUP BY tblSemenLost.Bull, tblSemenLost.UnitLocation;

qrySemenUsed:
Goal: to count the number of straws used to breed cows by "Bull" and
"Location" using tblArtificialInsemination and tblCurrentInventory2All
(tblCurrentInventory2All identifies the location of the cow and therefore
the
location of the straw)
SQL:
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];

qrySemenTransferredIn
Goal: sum the number of straws transferred into the location by "Bull" and
"New Location" using tblSemenInventoryTransferred
SQL:
SELECT tblSemenInventoryTransfer.Bull,
Sum(tblSemenInventoryTransfer.NumberStrawsTransferred) AS
SumOfNumberStrawsTransferred, tblSemenInventoryTransfer.[New Location]
FROM tblSemenInventoryTransfer
GROUP BY tblSemenInventoryTransfer.Bull, tblSemenInventoryTransfer.[New
Location];

qrySemenTransferredOut:
Goal: to sum the number of straws transferred out of a location by "Bull"
and "PreviousLocation" using tblSemenTransferred
SQL:
SELECT tblSemenInventoryTransfer.Bull,
Sum(tblSemenInventoryTransfer.NumberStrawsTransferred) AS
SumOfNumberStrawsTransferred, tblSemenInventoryTransfer.PreviousLocation
FROM tblSemenInventoryTransfer
GROUP BY tblSemenInventoryTransfer.Bull,
tblSemenInventoryTransfer.PreviousLocation;

qrySemenStrawInventory:
Goal: to subtract the straws used from the beginning straw inventory using
qrySemenInventory and qrySemenUsed
SQL:
SELECT qrySemenInventorySum.Bull,
IIf(IsNull([qrySemenUsed]![CountOfBull]),[SumOfNumberofStraws],([SumOfNumberofStraws]-[qrySemenUsed]![CountOfBull]))
AS Semen, qrySemenInventorySum.Location
FROM qrySemenInventorySum LEFT JOIN qrySemenUsed ON
(qrySemenInventorySum.Location = qrySemenUsed.[C-Unit]) AND
(qrySemenInventorySum.Bull = qrySemenUsed.Bull);

qrySemenWithoutLostBroken:
Goal: to subtract the straws that were lost or broken from the
qrySemenStrawInventory using qrySemenLostBroken
SQL:
SELECT qrySemenStrawInventory.Bull,
IIf(IsNull([qrySemenLostBroken]![SumOfNumberOfStraws]),[qrySemenStrawInventory]![Semen],[qrySemenStrawInventory]![Semen]-[qrySemenLostBroken]![SumOfNumberOfStraws])
AS [SemenW/outLost], qrySemenStrawInventory.Location
FROM qrySemenStrawInventory LEFT JOIN qrySemenLostBroken ON
(qrySemenStrawInventory.Bull = qrySemenLostBroken.Bull) AND
(qrySemenStrawInventory.Location = qrySemenLostBroken.UnitLocation);

qrySemenWithTransferOut
Goal: to subtract the straws transferred out by "Bull" and
"PreviousLocation" using qrySemenTransferredOut and
qrySemenWithoutLostBroken
SQL:
SELECT qrySemenWithoutLostBroken.Bull,
qrySemenTransferredOut.SumOfNumberStrawsTransferred,
qrySemenWithoutLostBroken.[SemenW/outLost],
IIf(IsNull([qrySemenTransferredOut]![SumOfNumberStrawsTransferred]),[qrySemenWithoutLostBroken]![SemenW/outLost],[qrySemenWithoutLostBroken]![SemenW/outLost]-[qrySemenTransferredOut]![SumOfNumberStrawsTransferred])
AS SemenWithTransferOut, qrySemenWithoutLostBroken.Location
FROM qrySemenWithoutLostBroken LEFT JOIN qrySemenTransferredOut ON
(qrySemenWithoutLostBroken.Bull = qrySemenTransferredOut.Bull) AND
(qrySemenWithoutLostBroken.Location =
qrySemenTransferredOut.PreviousLocation);

qrySemenWithTransferIn: (The problem is with this query, if the
"NewLocation" and "Bull" combination was not in the original table
tblSemenInventory, then this query will not show the inventory for that
bull
and location combination"
Goal: to add the semen transferred into a location by "bull" and
"NewLocation" using qrySemenWithTransferOut and qrySemenTransferredIn
SQL:
SELECT qrySemenWithoutLostBroken.Bull,
qrySemenTransferredOut.SumOfNumberStrawsTransferred,
qrySemenWithoutLostBroken.[SemenW/outLost],
IIf(IsNull([qrySemenTransferredOut]![SumOfNumberStrawsTransferred]),[qrySemenWithoutLostBroken]![SemenW/outLost],[qrySemenWithoutLostBroken]![SemenW/outLost]-[qrySemenTransferredOut]![SumOfNumberStrawsTransferred])
AS SemenWithTransferOut, qrySemenWithoutLostBroken.Location
FROM qrySemenWithoutLostBroken LEFT JOIN qrySemenTransferredOut ON
(qrySemenWithoutLostBroken.Bull = qrySemenTransferredOut.Bull) AND
(qrySemenWithoutLostBroken.Location =
qrySemenTransferredOut.PreviousLocation);
Click to expand...
 
I did look at that template and I didn't felt it fit my scenerio, also this
is part of a much larger database and I wasn't sure how I would incoporate
it, except to duplicate the tables into my database.

I do like your suggestions, I will try them out and see how they work with
my database.

Thank you.
--
Thorson


Jeanette Cunningham said:
Thorson,
I can't see a way to do this using your current table setup.

If you are using Access 2007, there is a template for an inventory database
that would be a start for you.

If you set up your tables like shown below, you can run queries and they
will work.

tblBull
BullID primary key
other fields

tblTransaction
TransID primary key
BullID foreign key
TransDate
TransDirection
TransTypeID
LocationID
Quantity - No. of straws
other fields

tblLocation
LocationID primary key
other fields

tblTransType
TransTypeID primary key
other fields

Everything that happens with semen is entered in the tblTransaction, and
categorized with the bullID, date, quantity, locationID, the type of
transaction and the direction.
The type of transactions could be: Received, Transferred, Shrinkage, etc.
The trans direction can have only 2 values 1 or -1.
If semen is received, the trans direction is 1.
If semen is lost (shrinkage) the trans direction is -1.
When semen is transferred, the trans direction is 1 for the location it is
transferred to.
And in the matching transaction, the trans direction is -1 for the location
it it transferred from.

To calculate quantities, in a query create a calculated field
[TransDirection]*[Quantity]
Then you use a totals or group by query to sum the calculated field to get
the final quantity for each bullID.

There is another example database on Allen Browne's website that shows how
to set this up for a simple case of buying and selling groceries.
Here is the link
http://allenbrowne.com/TechniqueEnterCalcText.html

The title suggests something else, but it is an excellent simple example of
how to track inventory for groceries bought and sold.
The same principles can be used for your database.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



Thorson said:
Well I have it set up and working fine, the only problem is that when
straws
for a bull are transferred from one of 3 locations that doesn't currently
have any straws for that bull, instead of counting it the query just
ignores
it because of how the relationships are set up in the last query.

I looked at the link you sent me, and then more through Allen Browne's
Database but did not see an answer to my question.

I am new to this but I can go through what I have set up so far. I have
more than just a series of tables set up I also have several queries.

I do not have a primary key in my tables because the "bull ID" and every
other field may be entered more than once. However "Bull" which is the
bull's ID and Unit location is how the tables are related:

tblSemenInventory
Primary Key: None
Fields:
Bull (Indexed)-related to tblSemenInventoryTransfer and tblSemenLost and
tblArtificialInsemination
NumberOfStraws
Location

tblSemenInventoryTransfer
PrimaryKey: None
Fields:
Bull (Indexed)-related to tblSemenInventory and tblSemenLost
NumberOfStraws
DateTransferred
PrevousLocation -related through queries to tblSemenInventory and
tblSemenLost
NewLocation

tblSemenLost:
Primary Key: None
Bull (Indexed)-related to tblSemenInventoryTransfer and tblSemenInventory
UnitLocation
NumberOfStraws
Date
Reason

tblArtificialInsemination:
Primary Key: EarTag
Fields:
EarTag (this is the cow's identification)
Date
Bull -related to tblSemenInventory

I also have several queries set up to calculate inventory:

qrySemenInventorySum:
Goal: to sum the straws of semen by "Bull" and "Location" using
tblSemenInventory
SQL:
SELECT tblSemenInventory.Bull, Sum(tblSemenInventory.NumberofStraws) AS
SumOfNumberofStraws, tblSemenInventory.Location
FROM tblSemenInventory
GROUP BY tblSemenInventory.Bull, tblSemenInventory.Location;

qrySemenLostBroken:
Goal: to count the number of straws recorded as lost or broken by "Bull"
and
"UnitLocation" using tblSemenLost
SQL:
SELECT tblSemenLost.Bull, Sum(tblSemenLost.NumberOfStraws) AS
SumOfNumberOfStraws, tblSemenLost.UnitLocation
FROM tblSemenLost
GROUP BY tblSemenLost.Bull, tblSemenLost.UnitLocation;

qrySemenUsed:
Goal: to count the number of straws used to breed cows by "Bull" and
"Location" using tblArtificialInsemination and tblCurrentInventory2All
(tblCurrentInventory2All identifies the location of the cow and therefore
the
location of the straw)
SQL:
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];

qrySemenTransferredIn
Goal: sum the number of straws transferred into the location by "Bull" and
"New Location" using tblSemenInventoryTransferred
SQL:
SELECT tblSemenInventoryTransfer.Bull,
Sum(tblSemenInventoryTransfer.NumberStrawsTransferred) AS
SumOfNumberStrawsTransferred, tblSemenInventoryTransfer.[New Location]
FROM tblSemenInventoryTransfer
GROUP BY tblSemenInventoryTransfer.Bull, tblSemenInventoryTransfer.[New
Location];

qrySemenTransferredOut:
Goal: to sum the number of straws transferred out of a location by "Bull"
and "PreviousLocation" using tblSemenTransferred
SQL:
SELECT tblSemenInventoryTransfer.Bull,
Sum(tblSemenInventoryTransfer.NumberStrawsTransferred) AS
SumOfNumberStrawsTransferred, tblSemenInventoryTransfer.PreviousLocation
FROM tblSemenInventoryTransfer
GROUP BY tblSemenInventoryTransfer.Bull,
tblSemenInventoryTransfer.PreviousLocation;

qrySemenStrawInventory:
Goal: to subtract the straws used from the beginning straw inventory using
qrySemenInventory and qrySemenUsed
SQL:
SELECT qrySemenInventorySum.Bull,
IIf(IsNull([qrySemenUsed]![CountOfBull]),[SumOfNumberofStraws],([SumOfNumberofStraws]-[qrySemenUsed]![CountOfBull]))
AS Semen, qrySemenInventorySum.Location
FROM qrySemenInventorySum LEFT JOIN qrySemenUsed ON
(qrySemenInventorySum.Location = qrySemenUsed.[C-Unit]) AND
(qrySemenInventorySum.Bull = qrySemenUsed.Bull);

qrySemenWithoutLostBroken:
Goal: to subtract the straws that were lost or broken from the
qrySemenStrawInventory using qrySemenLostBroken
SQL:
SELECT qrySemenStrawInventory.Bull,
IIf(IsNull([qrySemenLostBroken]![SumOfNumberOfStraws]),[qrySemenStrawInventory]![Semen],[qrySemenStrawInventory]![Semen]-[qrySemenLostBroken]![SumOfNumberOfStraws])
AS [SemenW/outLost], qrySemenStrawInventory.Location
FROM qrySemenStrawInventory LEFT JOIN qrySemenLostBroken ON
(qrySemenStrawInventory.Bull = qrySemenLostBroken.Bull) AND
(qrySemenStrawInventory.Location = qrySemenLostBroken.UnitLocation);

qrySemenWithTransferOut
Goal: to subtract the straws transferred out by "Bull" and
"PreviousLocation" using qrySemenTransferredOut and
qrySemenWithoutLostBroken
SQL:
SELECT qrySemenWithoutLostBroken.Bull,
qrySemenTransferredOut.SumOfNumberStrawsTransferred,
qrySemenWithoutLostBroken.[SemenW/outLost],
IIf(IsNull([qrySemenTransferredOut]![SumOfNumberStrawsTransferred]),[qrySemenWithoutLostBroken]![SemenW/outLost],[qrySemenWithoutLostBroken]![SemenW/outLost]-[qrySemenTransferredOut]![SumOfNumberStrawsTransferred])
AS SemenWithTransferOut, qrySemenWithoutLostBroken.Location
FROM qrySemenWithoutLostBroken LEFT JOIN qrySemenTransferredOut ON
(qrySemenWithoutLostBroken.Bull = qrySemenTransferredOut.Bull) AND
(qrySemenWithoutLostBroken.Location =
qrySemenTransferredOut.PreviousLocation);

qrySemenWithTransferIn: (The problem is with this query, if the
"NewLocation" and "Bull" combination was not in the original table
tblSemenInventory, then this query will not show the inventory for that
bull
and location combination"
Goal: to add the semen transferred into a location by "bull" and
"NewLocation" using qrySemenWithTransferOut and qrySemenTransferredIn
SQL:
SELECT qrySemenWithoutLostBroken.Bull,
qrySemenTransferredOut.SumOfNumberStrawsTransferred,
qrySemenWithoutLostBroken.[SemenW/outLost],
IIf(IsNull([qrySemenTransferredOut]![SumOfNumberStrawsTransferred]),[qrySemenWithoutLostBroken]![SemenW/outLost],[qrySemenWithoutLostBroken]![SemenW/outLost]-[qrySemenTransferredOut]![SumOfNumberStrawsTransferred])
AS SemenWithTransferOut, qrySemenWithoutLostBroken.Location
FROM qrySemenWithoutLostBroken LEFT JOIN qrySemenTransferredOut ON
(qrySemenWithoutLostBroken.Bull = qrySemenTransferredOut.Bull) AND
(qrySemenWithoutLostBroken.Location =
qrySemenTransferredOut.PreviousLocation);
Click to expand...
Click to expand...
 
The design layout you gave me works great, I altered it a little to pull one
transaction from a different table.

thanks for your help!
--
Thorson


Jeanette Cunningham said:
Thorson,
I can't see a way to do this using your current table setup.

If you are using Access 2007, there is a template for an inventory database
that would be a start for you.

If you set up your tables like shown below, you can run queries and they
will work.

tblBull
BullID primary key
other fields

tblTransaction
TransID primary key
BullID foreign key
TransDate
TransDirection
TransTypeID
LocationID
Quantity - No. of straws
other fields

tblLocation
LocationID primary key
other fields

tblTransType
TransTypeID primary key
other fields

Everything that happens with semen is entered in the tblTransaction, and
categorized with the bullID, date, quantity, locationID, the type of
transaction and the direction.
The type of transactions could be: Received, Transferred, Shrinkage, etc.
The trans direction can have only 2 values 1 or -1.
If semen is received, the trans direction is 1.
If semen is lost (shrinkage) the trans direction is -1.
When semen is transferred, the trans direction is 1 for the location it is
transferred to.
And in the matching transaction, the trans direction is -1 for the location
it it transferred from.

To calculate quantities, in a query create a calculated field
[TransDirection]*[Quantity]
Then you use a totals or group by query to sum the calculated field to get
the final quantity for each bullID.

There is another example database on Allen Browne's website that shows how
to set this up for a simple case of buying and selling groceries.
Here is the link
http://allenbrowne.com/TechniqueEnterCalcText.html

The title suggests something else, but it is an excellent simple example of
how to track inventory for groceries bought and sold.
The same principles can be used for your database.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



Thorson said:
Well I have it set up and working fine, the only problem is that when
straws
for a bull are transferred from one of 3 locations that doesn't currently
have any straws for that bull, instead of counting it the query just
ignores
it because of how the relationships are set up in the last query.

I looked at the link you sent me, and then more through Allen Browne's
Database but did not see an answer to my question.

I am new to this but I can go through what I have set up so far. I have
more than just a series of tables set up I also have several queries.

I do not have a primary key in my tables because the "bull ID" and every
other field may be entered more than once. However "Bull" which is the
bull's ID and Unit location is how the tables are related:

tblSemenInventory
Primary Key: None
Fields:
Bull (Indexed)-related to tblSemenInventoryTransfer and tblSemenLost and
tblArtificialInsemination
NumberOfStraws
Location

tblSemenInventoryTransfer
PrimaryKey: None
Fields:
Bull (Indexed)-related to tblSemenInventory and tblSemenLost
NumberOfStraws
DateTransferred
PrevousLocation -related through queries to tblSemenInventory and
tblSemenLost
NewLocation

tblSemenLost:
Primary Key: None
Bull (Indexed)-related to tblSemenInventoryTransfer and tblSemenInventory
UnitLocation
NumberOfStraws
Date
Reason

tblArtificialInsemination:
Primary Key: EarTag
Fields:
EarTag (this is the cow's identification)
Date
Bull -related to tblSemenInventory

I also have several queries set up to calculate inventory:

qrySemenInventorySum:
Goal: to sum the straws of semen by "Bull" and "Location" using
tblSemenInventory
SQL:
SELECT tblSemenInventory.Bull, Sum(tblSemenInventory.NumberofStraws) AS
SumOfNumberofStraws, tblSemenInventory.Location
FROM tblSemenInventory
GROUP BY tblSemenInventory.Bull, tblSemenInventory.Location;

qrySemenLostBroken:
Goal: to count the number of straws recorded as lost or broken by "Bull"
and
"UnitLocation" using tblSemenLost
SQL:
SELECT tblSemenLost.Bull, Sum(tblSemenLost.NumberOfStraws) AS
SumOfNumberOfStraws, tblSemenLost.UnitLocation
FROM tblSemenLost
GROUP BY tblSemenLost.Bull, tblSemenLost.UnitLocation;

qrySemenUsed:
Goal: to count the number of straws used to breed cows by "Bull" and
"Location" using tblArtificialInsemination and tblCurrentInventory2All
(tblCurrentInventory2All identifies the location of the cow and therefore
the
location of the straw)
SQL:
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];

qrySemenTransferredIn
Goal: sum the number of straws transferred into the location by "Bull" and
"New Location" using tblSemenInventoryTransferred
SQL:
SELECT tblSemenInventoryTransfer.Bull,
Sum(tblSemenInventoryTransfer.NumberStrawsTransferred) AS
SumOfNumberStrawsTransferred, tblSemenInventoryTransfer.[New Location]
FROM tblSemenInventoryTransfer
GROUP BY tblSemenInventoryTransfer.Bull, tblSemenInventoryTransfer.[New
Location];

qrySemenTransferredOut:
Goal: to sum the number of straws transferred out of a location by "Bull"
and "PreviousLocation" using tblSemenTransferred
SQL:
SELECT tblSemenInventoryTransfer.Bull,
Sum(tblSemenInventoryTransfer.NumberStrawsTransferred) AS
SumOfNumberStrawsTransferred, tblSemenInventoryTransfer.PreviousLocation
FROM tblSemenInventoryTransfer
GROUP BY tblSemenInventoryTransfer.Bull,
tblSemenInventoryTransfer.PreviousLocation;

qrySemenStrawInventory:
Goal: to subtract the straws used from the beginning straw inventory using
qrySemenInventory and qrySemenUsed
SQL:
SELECT qrySemenInventorySum.Bull,
IIf(IsNull([qrySemenUsed]![CountOfBull]),[SumOfNumberofStraws],([SumOfNumberofStraws]-[qrySemenUsed]![CountOfBull]))
AS Semen, qrySemenInventorySum.Location
FROM qrySemenInventorySum LEFT JOIN qrySemenUsed ON
(qrySemenInventorySum.Location = qrySemenUsed.[C-Unit]) AND
(qrySemenInventorySum.Bull = qrySemenUsed.Bull);

qrySemenWithoutLostBroken:
Goal: to subtract the straws that were lost or broken from the
qrySemenStrawInventory using qrySemenLostBroken
SQL:
SELECT qrySemenStrawInventory.Bull,
IIf(IsNull([qrySemenLostBroken]![SumOfNumberOfStraws]),[qrySemenStrawInventory]![Semen],[qrySemenStrawInventory]![Semen]-[qrySemenLostBroken]![SumOfNumberOfStraws])
AS [SemenW/outLost], qrySemenStrawInventory.Location
FROM qrySemenStrawInventory LEFT JOIN qrySemenLostBroken ON
(qrySemenStrawInventory.Bull = qrySemenLostBroken.Bull) AND
(qrySemenStrawInventory.Location = qrySemenLostBroken.UnitLocation);

qrySemenWithTransferOut
Goal: to subtract the straws transferred out by "Bull" and
"PreviousLocation" using qrySemenTransferredOut and
qrySemenWithoutLostBroken
SQL:
SELECT qrySemenWithoutLostBroken.Bull,
qrySemenTransferredOut.SumOfNumberStrawsTransferred,
qrySemenWithoutLostBroken.[SemenW/outLost],
IIf(IsNull([qrySemenTransferredOut]![SumOfNumberStrawsTransferred]),[qrySemenWithoutLostBroken]![SemenW/outLost],[qrySemenWithoutLostBroken]![SemenW/outLost]-[qrySemenTransferredOut]![SumOfNumberStrawsTransferred])
AS SemenWithTransferOut, qrySemenWithoutLostBroken.Location
FROM qrySemenWithoutLostBroken LEFT JOIN qrySemenTransferredOut ON
(qrySemenWithoutLostBroken.Bull = qrySemenTransferredOut.Bull) AND
(qrySemenWithoutLostBroken.Location =
qrySemenTransferredOut.PreviousLocation);

qrySemenWithTransferIn: (The problem is with this query, if the
"NewLocation" and "Bull" combination was not in the original table
tblSemenInventory, then this query will not show the inventory for that
bull
and location combination"
Goal: to add the semen transferred into a location by "bull" and
"NewLocation" using qrySemenWithTransferOut and qrySemenTransferredIn
SQL:
SELECT qrySemenWithoutLostBroken.Bull,
qrySemenTransferredOut.SumOfNumberStrawsTransferred,
qrySemenWithoutLostBroken.[SemenW/outLost],
IIf(IsNull([qrySemenTransferredOut]![SumOfNumberStrawsTransferred]),[qrySemenWithoutLostBroken]![SemenW/outLost],[qrySemenWithoutLostBroken]![SemenW/outLost]-[qrySemenTransferredOut]![SumOfNumberStrawsTransferred])
AS SemenWithTransferOut, qrySemenWithoutLostBroken.Location
FROM qrySemenWithoutLostBroken LEFT JOIN qrySemenTransferredOut ON
(qrySemenWithoutLostBroken.Bull = qrySemenTransferredOut.Bull) AND
(qrySemenWithoutLostBroken.Location =
qrySemenTransferredOut.PreviousLocation);
Click to expand...
Click to expand...
 
Back
Top