Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    JM9x is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    9

    Questions about starting first database

    I have minimal experience in using a few existing Access databases, and no experience in building them. However, I have taken on a task at work and I think Access database would be the best solution.



    I need a system to track lock outs and tag outs in an industrial plant. It's a system of hanging a tag and locking device to prevent operation of equipment like valves, breakers, pumps, motors, etc... to ensure personnel and equipment safety during maintenance.

    What I want to build is a database that has every component in the plant with it's associated information already stored. Then when an item needs to be tagged, you can just populate all the info in the tagout form from the database. I would also need some kind of cross check to see if that same item is already tagged out on a different work order.

    I could go on about the details but I'm really only looking for a good starting point here. I know it's going to be a lot of work. I just want some expert advice on getting started. Also, if you know of a similarly set up database (even if it's purpose is different) I'd like to have some kind of template to go off of that I could modify.

    Thanks in advance!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sounds like a 'check-out/check-in application, like a lending library. Check out this template http://office.microsoft.com/en-us/te...010206883.aspx
    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
    JM9x is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    9
    Quote Originally Posted by June7 View Post
    Sounds like a 'check-out/check-in application, like a lending library. Check out this template http://office.microsoft.com/en-us/te...010206883.aspx

    Sounds like it may work as a good starting point. I'll play around with it and see how it works. Thanks!

  4. #4
    JM9x is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    9
    The check out one may work but it would need a lot of modification. Since I don't know much about the building of databases, it might help if I describe how the users would need to interact with it and maybe someone could give me some ideas from the design side of things.

    I'm going to use numbered tags to represent fields that would need to be cross referenced from different sheets/tables to try to make it more clear.

    Say a piece of equipment (1) needs to be tagged out for repairs. In this case we'll use a pump. It has an electric motor so there are breakers to tag as well as valves for the suction and discharge piping, which together, make up the isolation points (2).

    A user would need to be able to open the database and start a new Safety Clearance (3) which would be the overall record of the equipment (1), isolation points (2), and personnel involved.

    Each isolation point (2) would have multiple fields for it's description (2a), Location (2b), etc...

    I would need to be able to have Safety Clearances have an active or archived status, and be able to have isolation point (2) flagged when starting a new new Safety Clearance if it contains an isolation point that's already on another active clearance.

    There would be a table of isolation points where I could enter the detailed description with multiple fields, and another table for the equipment that just lists the required isolation points to tag it out.

    I would like most of this to be filled out automatically in the forms or at least have dropdowns that reference a field in the tables.

    Any advice would be appreciated. I'm starting from scratch and learning how to use Access at the same time so please be gentle

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Review the 'sticky' thread tutorials here http://forums.aspfree.com/microsoft-access-help-18/

    Design a schema as best you can and post it for analysis.
    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.

  6. #6
    JM9x is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    9
    Design a schema as best you can and post it for analysis.

    I'm not sure what you're saying here. Should I make a mock layout of what I want the database to look like but do it in Word or Excel?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Write it on a napkin if that works for you. Use the 'sticky' thread on database principles I referenced as a guideline. It demonstrates use of ERD (entity relationship diagram) technique.
    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.

  8. #8
    JM9x is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    9
    I've been reading the tutorials in the stickies for the last few hours and getting some good ideas. I'm "working backwards" by figuring out exactly what reports and forms I need in the end, so I can design the tables around that. I should have something to post tomorrow. Thanks

  9. #9
    JM9x is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    9
    Okay, general question-

    Let's say I have two tables and one form that is filled out using values from individual records in both tables. Each time the form is filled out by a user, a different combination of data from table one and two is entered.

    I want that specific combo of data to be saved as it's own record every time the form is filled out. Does that mean I need to make a third table with all the fields from tables 1 & 2? How do I go from 'filling out a form' to making that create this new record?

    (I hope that makes sense)

    In reference to my earlier description- there would be tables for isolation points, equipment, and personnel. The form would be the associated paper work for a specific maintenance item (a safety clearance). Basically, the records containing the data to fill out the form would be pre-existing but I would want that version of the form with it's own combo of data to become a new record in a different table.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The concept of a relational database (which Access is) is to relate records.

    So if these two tables have a many-to-many relationship, need a junction table with foreign key fields that store the primary keys from the other two tables. The data input form would be bound to the junction table. Select the keys of the other two tables from comboboxes that are bound to the fields of the form RecordSource. Values entered to data entry controls are passed directly to the table. Check out the tutorials here http://www.datapigtechnologies.com/AccessMain.htm
    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.

  11. #11
    JM9x is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    9
    Quote Originally Posted by June7 View Post
    The concept of a relational database (which Access is) is to relate records.

    So if these two tables have a many-to-many relationship, need a junction table with foreign key fields that store the primary keys from the other two tables. The data input form would be bound to the junction table. Select the keys of the other two tables from comboboxes that are bound to the fields of the form RecordSource. Values entered to data entry controls are passed directly to the table. Check out the tutorials here http://www.datapigtechnologies.com/AccessMain.htm
    It looks like I'm going to have to do quite a bit more reading before I even understand your suggestion LOL! Could you point me towards which one of the tutorials apply for this situation? There's quite a few in the link you sent.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You might find the ones about comboboxes of immediate interest. They are in the Access Forms: Control Basics section.

    You really need an understanding of relational database concepts before proceeding. Does this help http://computer.howstuffworks.com/question599.htm
    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.

  13. #13
    JM9x is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    9
    I get the relational database concept, as a concept- it's the implementing and the intricacies of Access that I'm still trying to grasp.

    You've given me a lot to start with though and I've got a really good plan on paper now. I think I have enough to start building the tables now and I can start another thread if I have any specific questions later.

    Thanks for your help

  14. #14
    JM9x is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Posts
    9
    By chance, are you familiar with MySQL or any other Access alternatives? I know MySQL is just a back end and Access is a front end with a GUI builder and back end, but before I get too deep I want to make sure I'm using the best tool for the job here. We have Access on the computers at work that will be needing to access this database. Access provides the front end and back end in one solution so to me it seems like a good choice, but I have a very limited knowledge of Access and know even less of the alternatives. The database will not need to be accessed by more than one or two people at a time so the web interface feature isn't a huge setting point. Remote access would be a plus but isn't necessary for our needs. I want something that will require minimal training for the users to print predefined reports, fill out forms and create new records.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I tried once to set up MySQL but ran into install problems and abandoned. Access is only db I have extensive experience with. Of all your choices I think Access will be easiest to setup and learn and offers all the features you described. There are techniques to serve Access data on the web. Check into SharePoint for one.
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Starting a new database
    By JFo in forum Access
    Replies: 9
    Last Post: 08-25-2011, 11:00 PM
  2. Help starting up a daily entry database
    By sparx in forum Database Design
    Replies: 1
    Last Post: 01-12-2011, 10:56 AM
  3. Replies: 7
    Last Post: 05-21-2010, 10:37 PM
  4. Starting an Access database for a group...
    By kkrishna in forum Access
    Replies: 8
    Last Post: 05-19-2010, 05:29 PM
  5. First Database Questions...
    By qu1ckdry in forum Database Design
    Replies: 0
    Last Post: 03-01-2010, 09:33 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