Results 1 to 4 of 4
  1. #1
    mai0f is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    1

    looking for guidance on designing efficient database

    Hello all,



    I'm new to Access, but I'm a fast learner (at least, I'd like to think I am). I'm not looking to be spoon fed or to have someone do my work for me, just looking for guidance on where/how to start.
    I don't know how to best describe the process, and where I'm looking to go, but I'll try my best.
    (I'll use vehicles as an example for what we're supporting)

    Currently, our parts ordering process is as follows:
    Customer looks through an old binder, fills out a slip, we process the request. All done on paper. The binder is used as a reference to parts, the "real" catalog is digital, but is complicated to use, and only supported on a handful of our laptops. If a reference in the binder needs updating, it's penciled in.

    I've been working on getting all the information from the reference binder in a database. I currently have excel spreadsheets for each system (fuel system, electrical, drive-train, etc), with over 3000 items.
    each spreadsheet contains the same type of information:
    Item Name; Stock Number; Part number; Item location1(book); Item location2(page); Item location3(index); Work code

    Question one, would it be best to have a table for each system, or just one table with all systems (I would just have to add another field for system)?

    The form I'd like to build would first request the following information:
    Requesting individual(employee number); Shop; Work center; VIN; Job number
    then, depending on the system (shop) the customer chooses, have a drop-down (or search function of some sort) for "Item name"; and once selected, the rest of the information would auto populate.


    That information would then go into a log of some sort where we would add the following information:
    Order number; Status

    Efficient design of the form and the order log would be my next question, but building the database is my main concern. Again, I can consolidate all the spreadsheets, but if it's not problematic, I'd like to keep all the systems separate (should be easier to update a whole table for one system if need be).
    I hope my explanation wasn't confusing. :-)

    thank you all,
    - u

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Suggest you work through this tutorial site http://www.rogersaccesslibrary.com/

    One table with a field for the system type, not a field for each system.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    There are many many many resources available. The advice I like to give is learn about relational database, first. Start by getting an Entity Relational Diagram established. This is easier said than done. It takes a lot of experience to incorporate business rules into your ERD, as you diagram your entities. What you are describing does not sound simple to me. So you have your work cut out for you.

    This 15 minute video might help provide you with a high level view what I am referring to.
    https://www.youtube.com/watch?v=-fQ-bRllhXc

  4. #4
    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
    I agree with June and ItsMe --make use of tutorials. Create an ERD before jumping into Access.
    If you have an hour or so, here are 2 videos that will help get you oriented to database concepts and realities. I'd recommend these after those mentioned by June and ItsMe.

    1 - Dr Soper 4 of 8 Data Modelling

    2 - A real life stock control system with a sample database

    Good luck with your project

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

Similar Threads

  1. Replies: 3
    Last Post: 10-05-2015, 01:21 PM
  2. Need guidance on a Scouting Database
    By JRT2006 in forum Access
    Replies: 4
    Last Post: 04-26-2015, 03:16 PM
  3. Seeking guidance with session database
    By melliott1712 in forum Access
    Replies: 5
    Last Post: 10-06-2014, 12:21 AM
  4. Help with designing database
    By nimalp in forum Access
    Replies: 6
    Last Post: 09-15-2010, 10:34 AM
  5. Need guidance on database
    By yak600 in forum Access
    Replies: 0
    Last Post: 08-04-2009, 07:46 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