I am developing a simple database for stock control as a short term measure until we get a permanent solution. All I wish to do is to be able to record additions to stock and record issues from stock. I need to be able to query for a raw material Part # and see what Lots are available and their locations and then be able to issue stock from these locations. Ideally this would be done from the same form.
I am looking for some advice on the best way to set this up.
Here is my thoughts/approach -
I so far have set up three tables
tblStock
tblLocation
tblMaterialPN
I have set up a tblStock with the following fields
transaction# - autonumber - primary key
MaterialPN - text - alphanumeric field
MatlLot# - text - Each raw material delivery or unit is given a unique # but the quantity of material is not fixed.
Location - text - warehouse locations where lot is stored.
WorkOrderLot# - text - This is the work order(job) number that stock is issued to from the warehouse. (Raw materials added to stock will not have a WorkOrderLot#).
Qty - number - quantity transacted
Date - date/Time - Date of transaction
Signoff - text - Name/initials of individual that conducted transaction.
AddStock - Yes/No - tick box for adding stock
RemoveStock - Yes/No - tick box for removing stock.
The Location table has just a list of the locations.
The MaterialPN table has the following fields
s_code - text - material part #
s_desc - text - material part description
The tables are linked as follows
MaterialPN (tblStock) to s_code (tblMaterialPN) N:1
Location (tblStock) to Location (tblLocation) N:1
I have set up three forms
frmLocations - for entering/creating new warehouse locations - linked to tblLocation
frmMaterial - for entering/creating new raw material parts and descriptions - linked to tblMaterialPN
frmStock - for entering all stock transactions - This form with be used to populate the tblStock. This form will take MaterialPN from tblMaterialPN using a drop down list and put the selected value in the MaterialPN field of the tblStock. The Date field will default to the current date. The user will add all other data. Either the AddStock box or the RemoveStock box will be ticked depending on the transaction type.
To calculate stock I will add all the records with a tick in the add stock box and add all the records with a tick in the remove stock box and subtract totals to find stock on hand.
How does the above approach look?
What is the best way to display stock and locations and to be able to remove stock from a selected location without having to leave form? Maybe using a new form that while still been able to view the result of the original query??
All advice would be appreciated.
I am looking for some advice on the best way to set this up.
Here is my thoughts/approach -
I so far have set up three tables
tblStock
tblLocation
tblMaterialPN
I have set up a tblStock with the following fields
transaction# - autonumber - primary key
MaterialPN - text - alphanumeric field
MatlLot# - text - Each raw material delivery or unit is given a unique # but the quantity of material is not fixed.
Location - text - warehouse locations where lot is stored.
WorkOrderLot# - text - This is the work order(job) number that stock is issued to from the warehouse. (Raw materials added to stock will not have a WorkOrderLot#).
Qty - number - quantity transacted
Date - date/Time - Date of transaction
Signoff - text - Name/initials of individual that conducted transaction.
AddStock - Yes/No - tick box for adding stock
RemoveStock - Yes/No - tick box for removing stock.
The Location table has just a list of the locations.
The MaterialPN table has the following fields
s_code - text - material part #
s_desc - text - material part description
The tables are linked as follows
MaterialPN (tblStock) to s_code (tblMaterialPN) N:1
Location (tblStock) to Location (tblLocation) N:1
I have set up three forms
frmLocations - for entering/creating new warehouse locations - linked to tblLocation
frmMaterial - for entering/creating new raw material parts and descriptions - linked to tblMaterialPN
frmStock - for entering all stock transactions - This form with be used to populate the tblStock. This form will take MaterialPN from tblMaterialPN using a drop down list and put the selected value in the MaterialPN field of the tblStock. The Date field will default to the current date. The user will add all other data. Either the AddStock box or the RemoveStock box will be ticked depending on the transaction type.
To calculate stock I will add all the records with a tick in the add stock box and add all the records with a tick in the remove stock box and subtract totals to find stock on hand.
How does the above approach look?
What is the best way to display stock and locations and to be able to remove stock from a selected location without having to leave form? Maybe using a new form that while still been able to view the result of the original query??
All advice would be appreciated.
