Hi,
I've been tasked with designing a database to look after our stock of alcohol. There will be stock being used (consumed) and stock being ordered and replenished.
The old data will be coming from an existing Sharepoint/Sql solution and is in one huge table. I'd like to split this up to cut down on the number of fields and just make things a little neater etc. I was thinking about something like this:
tbl_product - contains the different products, they were sourced from and their prices
tbl_inventory - the current stock levels of the items, how many are booked out for functions, how many are coming in (allows us to do some sums later on down the line if necessary). I was thinking of having a field for 'initial value'. This will be the starting value and the stock amount as of now. From there anything else is just an addition or deletion from the stock number. Thoughts?
tbl_consumed - a table showing products as they're used. (5 wines being used = a new line in this table)
tbl_suppliers - showing supplies
tbl_grade - showing wine grade
etc etc
I have comparatively little Access knowledge. Would this be the best way to go about designing the database? I realise I have given very little in the way of field names etc but hopefully this gives you guys some idea of what I'm trying to achieve.
There'll be 4 main areas of the database:
Products > view all products / check products that may need re-ordering soon / Create new products
Consumption > Checking stock out for functions
Orders > Stock coming in / Suppliers maintenance
Reporting > Custom reporting
How does this all look?
Thanks very much for any help anyone can provide. I really appreciate it.
Cheers
David
I've been tasked with designing a database to look after our stock of alcohol. There will be stock being used (consumed) and stock being ordered and replenished.
The old data will be coming from an existing Sharepoint/Sql solution and is in one huge table. I'd like to split this up to cut down on the number of fields and just make things a little neater etc. I was thinking about something like this:
tbl_product - contains the different products, they were sourced from and their prices
tbl_inventory - the current stock levels of the items, how many are booked out for functions, how many are coming in (allows us to do some sums later on down the line if necessary). I was thinking of having a field for 'initial value'. This will be the starting value and the stock amount as of now. From there anything else is just an addition or deletion from the stock number. Thoughts?
tbl_consumed - a table showing products as they're used. (5 wines being used = a new line in this table)
tbl_suppliers - showing supplies
tbl_grade - showing wine grade
etc etc
I have comparatively little Access knowledge. Would this be the best way to go about designing the database? I realise I have given very little in the way of field names etc but hopefully this gives you guys some idea of what I'm trying to achieve.
There'll be 4 main areas of the database:
Products > view all products / check products that may need re-ordering soon / Create new products
Consumption > Checking stock out for functions
Orders > Stock coming in / Suppliers maintenance
Reporting > Custom reporting
How does this all look?
Thanks very much for any help anyone can provide. I really appreciate it.
Cheers
David