Results 1 to 10 of 10
  1. #1
    Nokoff is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    17

    Table Structure for large list of number fields

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

    Have a look at this screenshot from excel.
    Click image for larger version. 

Name:	accessQ1.png 
Views:	21 
Size:	18.1 KB 
ID:	21476

    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

  2. #2
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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)

  3. #3
    Nokoff is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    17
    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.

  4. #4
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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?

  5. #5
    Nokoff is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    17
    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).

  6. #6
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.
    Last edited by orange; 07-31-2015 at 06:49 AM. Reason: spelling

  8. #8
    Nokoff is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    17
    Quote Originally Posted by orange View Post
    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.

  9. #9
    Nokoff is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    17
    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.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 01-07-2015, 04:46 PM
  2. Replies: 4
    Last Post: 11-04-2013, 02:24 PM
  3. Loop skips a large number of records unless stepped through
    By Monterey_Manzer in forum Programming
    Replies: 1
    Last Post: 10-04-2013, 05:01 PM
  4. Query speed over a large number of records
    By GrantRawlinson in forum Queries
    Replies: 2
    Last Post: 06-15-2012, 11:03 AM
  5. Linking large number of forms and tables
    By jlcaviglia-harris in forum Forms
    Replies: 2
    Last Post: 04-17-2009, 09:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums