tracking Inventory Transfers

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]);
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

*REPOST* tracking inventory using queries 5
Desinging queries to track inventory 0
Inventory based on ID and Location 8
Creating a Chart on form from selected data in query. 0
Office 365 Calender 0
Adding Network Trusted Location on MS O365 Word per Computer Registry Setting 1
Consolidation Table from Different Excel Tabs with Different Format 1
Condition and equation problems 4

Top