Hello All,
I am very new to access and am having a problem that I hope someone can help me with.
Context : I am building a database to keep track of items called C#s. Each item has only one C# to define it so that a different C# would refer to a different item.
I have three tables:
1) (CNumberT) A table of general information for each C# that very rarely changes. Each record in this table has information for just one specific C#. I am using the C# as the Primary Key.
2) (CNumberRecievedT) A table that displays all shipments of recieved C#s. The table displays the C# recieved, the quantity of C#s recieved in the shipment, the date the shipment was recieved, and the Shipment ID which I set as the primary Key using AutoNumber. An employee will enter the information in a form once a shipment is recieved.
3) (CNumberUsedT) A table that displays all used C#s. The table displays the C# used, the quantity of C#s used, the date that amount was used, and the UsedC# ID which I set as the primary key using AutoNumber. An employee enters the information in a form at the time that quantity of C#s is taken from the stock room.
So, my question is: How do I create a query that sums the total amount of C#s recieved for all shipments of one specific C# using the table CNumberRecievedT? As well as the sum of all the total C#s used for a specific C# using the table CNumberUsedT? Remembering that there will be multiple shipments for the same C#, and the same C# item can be used multiple times so that there are multiple records for that one C#.
My goal is to use these to values to know the Current On Hand quantity of each C#.
Thank you very much for any help!
-Ryan