Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    Table Help with Relationships

    I have 4 tables (below). Needing help with the relationships.


    tblGXPS6S -- (PK) ID, Emp_ID, EnteredDate, Area_ID, Ques1.....Ques25
    tblAreas --(PK) Area_ID, Areas, Emp_ID


    tblEmployees -- (PK) Emp_ID, FirstNames, LastNames, CurEmp
    tblGXPSRms -- (PK) Rm_ID, RmNum, Area_ID


    Each employee has to answer a 25 questionnaire weekly for their areas. Some employees have multiple areas but each area only has one employee. The areas can have multiple room numbers in them and some large rooms can have multiple areas in them. I don't know how to relate the areas to the rooms so that I don't have multiple Area_ID for one area for tblGXPS6S. Could someone help me with this and explain in English how to take care of my relationships properly.


    Thank you for your time and knowledge,
    Walker
    Last edited by NightWalker; 08-04-2016 at 04:06 PM. Reason: added info

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please read and work through this tutorial. You will learn about relationships and table design.
    It will take you 30-60 minutes and you will learn. There is a solution included, but working through the tutorial will give you some experience that can be used with any database.


    It is helpful to name tables to show/illustrate what the table is about. GXPS6S is not meaningful to readers.
    Good luck,

  3. #3
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    GXPS6S is not meaningful to readers.
    GXPS is the actual name of the Company and 6S is the project that this DB belongs to.

    I am not understanding what you are saying that it is not meaningful. Could you please explain so I can learn what I am doing wrong?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Ques1.....Ques25
    &
    GXPS is the actual name of the Company and 6S is the project that this DB belongs to.
    Both of these quotes indicate you also need to understand normalisation as well - Orange's link is excellent and this link can also help http://www.teach-ict.com/as_a2_ict_n...iniweb/pg3.htm

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

  6. #6
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Ques1....Ques25 is actually 25 separate yes/no fields. I just didn't want to write all that out. Sorry about the confusion. Thank you for the links.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Ques1....Ques25 is actually 25 separate yes/no fields. I just didn't want to write all that out. Sorry about the confusion. Thank you for the links.
    you have missed the point. You should have a separate table with 1 question per record

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

  9. #9
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    I am currently working through the tutorial you gave me second time. Please forgive me for missing the point. I am trying to understand it but I think I am missing some piece that will turn on the light bulb for everything.

    You should have a separate table with 1 question per record
    So I should have 25 tables One for each question? They are all different questions. I am not understanding the reasoning for separating them.

    I really want to understand the design process better. I ordered the book in that tutorial last night. I try not to ask questions here until I have been banging my head against my monitor for a couple days and googled what I am trying to do but sometimes I don't understand what I find on other sites.


    I really appreciate the time and help you all give to us novices. I know your time is valuable so I try to take as little of it as possible. I just need to find that "AHA!!!!" thing that will make everything clear. Again Thank you all greatly for your patience with me and my ignorance on this process.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    So I should have 25 tables One for each question?
    no - one table, 25 records, one for each question. Go through the tutorials and it should become clearer

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    NightWalker,
    When working through the tutorial, focus on the tutorial. Don't try to do the tutorial and your issue at the same time. Learn the "process" that is the essence of the tutorial, do a second and third tutorial if necessary, THEN use the same technique with your own issue/business opportunity/database.
    Here are more tutorials to work through.
    Catering business
    Consolidated Widgets
    ER Diagramming

    There may be an "a ha!" moment, but the recognition that tables don't get arbitrarily defined/designed; and that relationships are not after thoughts or "lines between tables", is the light bulb so to speak. Tables and relationships are directly derived from your description of your business and the business rules/facts.

    There is an old saying in "computer programming generally" --if you can't describe it, then nobody can build it. Get as complete a description of the business issue/problem/opportunity as you can --bounce it off colleagues to ensure it covers the facts, is clear and is devoid of jargon.

    The data model can be an excellent communications tool for:
    designers, users, trainers, programmers and maintenance staff......

    Back to this from post 3
    GXPS is the actual name of the Company and 6S is the project that this DB belongs to.
    you wouldn't have a table for each Company, would you?

  12. #12
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    This is what I am trying to reverse engineer into a database. I guess I am going about it the wrong way.

  13. #13
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    6s.pdf

    I hope this works

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How about stepping back and telling us -in very simple English -- what exactly is the business issue/opportunity that you are trying to support with this database? Give us the list of requirements.

    Did you try the tutorials? Did you see the type of "business description" he starts with?
    That's where you start.

    Also:
    I'm not sure what this really means or where it fits.
    This is what I am trying to reverse engineer into a database. I guess I am going about it the wrong way.
    Reverse engineer usually means to take an existing database, and break it into its component parts --tables, queries, forms....

  15. #15
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    I am working through all the tutorials that you sent me. I am also reading through all of the normalization in the link Ajax sent. I will probably go through all of it a few times this weekend. I usually have to do my reading at home. Here is the business issue and requirements.

    I have the form in post #13 attachment. It was created in excel. Then it was turned into a fillable PDF. (this is what I meant by reverse engineer) Now they want me to convert it to a DB. that page is what they want to form and report to look like. I need to capture the date the form was filled out, who filled it out and what area it belongs to (tblAreas in above post). I am also needing to capture the yes/no answers for all 25 questions for each week for each area. then do calculation on results(I know this wont be stored in table but code to show result in afterupdate event). Then the users need to be able to print out their report. Just doing that would have been easy I almost had that done BUT..... Then they added all the room numbers and descriptions of the rooms. Management wants to be able to select an area in another form and see which room(s) (could be multiple rooms in an area) associated with each area or enter a room number to find out which area(s) (could be multiple areas in some larger rooms). They also want to be able to select an employee and find out which area and/or rooms that the employee is responsible for.

    I ran into my dilemma trying to relate the rooms and areas so each room only has one ID# and each area has only one ID# but with the many to many relationship I don't know how to split it to a better level of normalization. So I came up with the tables in post#1.

    I hope you understand better what I am trying to do. I really want to learn the proper way to build the foundation.

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

Similar Threads

  1. Table Relationships
    By Beanie_d83 in forum Access
    Replies: 23
    Last Post: 06-20-2016, 08:22 AM
  2. Table Relationships help
    By rzw0wr in forum Database Design
    Replies: 3
    Last Post: 06-21-2013, 07:42 PM
  3. Table Relationships
    By edthened in forum Access
    Replies: 12
    Last Post: 06-24-2012, 05:52 PM
  4. Table Relationships
    By carrod65 in forum Database Design
    Replies: 15
    Last Post: 04-04-2012, 09:22 AM
  5. Table Relationships?
    By Meld51 in forum Access
    Replies: 3
    Last Post: 03-01-2010, 08:34 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