Table Structure for large list of number fields

[Nokoff]

Hi everyone, I'm sorry I couldn't be more descriptive in the thread title.

Have a look at this screenshot from excel.


Basically I would like to capture the quantity in stock for the above list of phones at many stores.

I started out by adding each phone model as a numeric field in tblStock, because I need to obtain the quantity value for each and every model, for each and every store.

Is there a better way to do this? I was thinking of creating just 2 fields, Model and Quantity, then adding each model as a record, then using that record as a sort of template. I wander what would be the drawbacks of this, since with the first method, if a user needs to add a phone not on the list he would have to modify the table design.

Thanks
Nokoff

[CJ_London]

you wouldn't store your stock quantity, you would calculate it from your transactions - a transactions table with primarykey, familykey to phone description, a transaction type (stock in, stock out, stock adjustment etc), a quantity and a date. You would probably also have a column to identify order/delivery note numbers and sales invoice numbers. Being phones you would probably also have in this table fields for SIM, IMEI and CTN

the table you show is how I would expect the 'phone table to look but with the addition of a primary key and without the quantity in stock column. Perhaps also a column for device type (phone/tablet etc)

[Nokoff]

Hi Ajax, thank you for taking the time ti reply. There are no transactions Ajax,it's a stock count. So we are comparing it to a recommended stock level.

Also it is important to display the full listing on the form at once so that the user is reminded to check stock for each item and does not forget to check some of them.

[CJ_London]

for what you want - your image is fine - for completeness I would add a date and store identifier to the header and if the count is occurring during store opening hours I would add a time column (so you can adjust stock for subsequent sales during the day)

For a normal stocktake (which this sounds like) you don't start at the top of the list and work your way down per se - you start at one end of the 'aisle', shelf or whatever, count what is there, then find it on the list - if it is not on the list, you need to manually add it - then move on. If this is being run on a tablet, you provision to allow the user to add new stock items - this is not a change to the table design - which I would expect to be something like

PrimaryKey
StoreFK
Date
Item
Quantity
Time?

but the adding of another record.

Anyway, won't all your products be barcoded? Just go down the aisle with your reader and the items will be added to the table as you go?

[Nokoff]

It's not a normal stock, it is specifically Key Volume Indicator Stock, so we need to record the stock quantity of a list of specific items (not necessarily all items in the store).

[CJ_London]

OK - not sure how this

if a user needs to add a phone not on the list

gels with

so we need to record the stock quantity of a list of specific items

but what you have seems fine for that purpose

[orange]

Wouldn't you want to record the StockTakeDate?
I'm not convinced you have the problem/opportunity sufficiently defined.

There are no transactions, there is no date to the record with stockOnHand????
Your list of products for which counts are required is not defined?

This Key Volume Indicator Stock is not a common term (for most readers) so suggest you expand on a definition to help us with context.

If you cannot describe the issue clearly and completely, then no one can build/solve it.

[Nokoff]

Wouldn't you want to record the StockTakeDate?
I'm not convinced you have the problem/opportunity sufficiently defined.

There are no transactions, there is no date to the record with stockOnHand????
Your list of products for which counts are required is not defined?

This Key Volume Indicator Stock is not a common term (for most readers) so suggest you expand on a definition to help us with context.

If you cannot describe the issue clearly and completely, then no one can build/solve it.

I'm sorry I thought it would be better if I kept it simple and brief, it seems straight forward.

I have an audit table already in which I record things like the date of the audit. The list is exactly as I have provided in the picture, exactly as it is there, but it could change at a point during the audit. I just want to make it easy to add.

[Nokoff]

How would I go about comparing each quantity to a recommended stock level for each product?

So in the query I can filter out Samsung A5 < 5 for example. The output I would like is a print out of all of the phone models that are below their minimum stock requirement.

[orange]

Google "inventory reorder level or reorder point"
see: http://accountingexplained.com/manag.../reorder-level
https://en.wikipedia.org/wiki/Reorder_point

There is a sample data model at Barry Williams' site
Here are the "requirements" including Reorder level for this data model.

Good luck