Results 1 to 2 of 2
  1. #1
    RogerThat is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    1

    Learning Access and Database design

    Hi all,

    First time poster, big time access noob. Please be patient

    I am playing around with access for the first time. I have been reading different articles on basic design principles, primary keys, foreign keys etc.

    I am trying to make a super basic mock inventory management system. I am just a little unsure of what would be an ideal table design, what to set as my primary keys, and how to set up the relationships. At the moment, I have this:


    Click image for larger version. 

Name:	Capture.PNG 
Views:	12 
Size:	12.3 KB 
ID:	25996

    The test scenario is as follows; There is a warehouse with tools/resources for staff. All items need to be cataloged and stored in the table 'TBL_Products'. Staff (TBL_Clients) can access these resources as often as they like, each item just needs to be checked out.

    Ultimately, what is required is a table (or query???, or form???) that will show the current inventory, and another that will show what is checked out to who (current checked out inventory).

    Any help or pointers in the right direction would be greatly appreciated.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    unless your clients/staff only requisition one item at a time, your tblClientReq needs to be split into two - a 'header' table (PK, clientID, reqDate, details) and a 'transactions' table (PK, FK to header table, FK to product table, quantity?).

    If you want current inventory, you will also need a method of returning items - this might be a separate 'negative' transaction record or you might move the date field to the transactions table and add another field - returned date - all depends on how your business works.

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

Similar Threads

  1. Basic Database Design - Confirming my Learning
    By robbeh in forum Database Design
    Replies: 2
    Last Post: 01-02-2015, 02:26 PM
  2. I'm stumped with learning VBA in Access
    By phineas629 in forum Programming
    Replies: 2
    Last Post: 01-06-2012, 04:57 PM
  3. Learning Access - Design Questions
    By learning_access in forum Database Design
    Replies: 2
    Last Post: 02-15-2011, 09:13 AM
  4. Learning access, need work.
    By akius in forum Access
    Replies: 2
    Last Post: 07-06-2010, 12:42 PM
  5. Learning Access...need help.
    By Banner in forum Queries
    Replies: 1
    Last Post: 10-12-2009, 02:48 AM

Tags for this Thread

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