Stores / inventory data base

[Ltmac70]

I am new to the forum and I apologize if this is a common question nut I need to start somewhere
my Access skill is beginner.

I have a stores data base

Stores Table that records who is issued what uniform parts
each component has it own table
lets use shirts
Each shirt is marked with an ID (primary key) number as it is added to inventory along with it's size and status

Question 1
in the stores table I would like to enter the ID # in one column and have another column show the size without any actual entry just by a look up based on the ID #?

Question 2
When I create my form for issuing uniform parts is there a way to automatically up date the status to issued in all the various tables with out actually putting a pull down menu for each item?

 

[Ranman256]

you would do this in a form, not a table.
enter the ID in the text box, then fill the size combo box using dlookup:

sub txtID_afterupdate
cboSize = Dlookup("[size]","tShirts","[id]=" & txtID)
end sub

or use a combo box to pick the shirt. the combo box would have 2 columns, shirtID, Size
then fill the size from the comboID
cboSize = cboID.column(1)

 

[jdraw]

Can you tell us about your business and show us some of the requirements/specifications?

I don't mean to be too critical, but it isn't clear that you have done sufficient analysis, modelling
and testing to ensure your tables and relationships support your requirements.

Good luck with your project.

 

[Ltmac70]

This is for a Cadet Unit. Each Cadet is issued a Uniform that they return at the end of the year, or sometimes in the middle of the year they need to exchange a part of their uniform if it is too small.

I think that the first reply has covered question 1. When I create the form for entry I will create a combo box that will let me see what size is associated with an Id # so that there is a double check. for example if someone types in ShirtID 11 and it returns "small" and the shirt is a Large then it makes the person doing the entry think to check the Shirt ID number on the shirt and they may realize that it should have been ShirtID 17 and that returns in the combo box "Large"

Question 2 is;
When I enter the ShirtID and confirm the size is correct visually and save the entry is there away to update the "shirttable" and change the status to Issued automatically or do I need a pull down menu in my form to manually change the status.
in excell I would make Status a large If statement that said "if the ShirtId appears in this Storesissued table then "issued" if the ShirtID appears in Decommissioned table then "Decommissioned"... with the last option being "in stock"

 

[Ltmac70]

Question 3
I have an Item that has no size and there is no difference between one or the other thus they are not unique and have no ID number on them. Is there a way in a table to make keep track of inventory available mathematically? For instance entering the total number available and then a column that counts how many are issued in the StoresIssued table and the column that makes a calculation between total available and number issued