Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118

    New Relational Database design

    I’ve done some work with Access, creating basic tables, queries , forms, macros etc. No coding other than copying and modifying existing code. I'm using Access 2016. I have a project where I don’t know where to start.


    All the data comes to me in an Excel sheet. Please refer to the attached Excel sheet for how the data is stored. I would want to create 2 queries.

    One that would pull all records where any one of the department fields would contain ENG(or another dept)
    AND
    One that would pull all records where any one one of the defect fields would contain ISSUE-Q(or another issue)

    I think it means creating a relational database. I think I need to import the data into 3 tables. Client data in one, Departments in another and Defects in a 3rd. There are 8 possible departments and 152 possible defects but we are only selecting the top 4 defects. The 4 defect fields can contain any one of the 152 possible defects. Obviously there are too many variables to create a field for each defect.
    Book1.zip
    Can someone help me get started on designing this?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not clear what purpose of db is. Field names are not meaningful.

    At a minimum, normalized structure would be like:

    tblClientInfo (ID, name, address, email, etc)

    tblDefectTypes (ID, Description)

    tblDepartments (ID, name)

    tblData (junction table relating the other tables)

    Why are there 5 department and 4 defect fields? Is each defect issue intended to apply to all of the entered departments?
    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
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Just reading this, I can tell you are using your "Excel brain". That will get you into trouble. Spreadsheet data is wide, not tall (for lack of a better way to put it). Relational data is tall, not wide, but more importantly, it is compartmentalized into related tables. Thus several 'defect' (or any other entity type) is the wrong approach.
    Can someone help me get started on designing this?
    First check out these, then find more links on the subjects so that you get varying viewpoints. Besides, some will speak to you better than others. MAKE SURE you understand normalization. When ready, might be wise to post something that shows your relationships. When it comes to using Excel data, post back in another thread on ways to handle it (IMHO, linked as tables but also having db tables to work with this data is sometimes the optimal approach, but that is another topic. Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.ca/...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.ca/...cation-in.html

    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118
    Clarification: I'm analyzing written comments about guest experiences. A single comment comes with the associated client data. A single can reference to anywhere from 1 to 8 departments. Also each comment can reference up to 152 defects although we are only storing the 4 most important.

    Comment example: "I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around."

    This single comment references 3 different departments. Accounting, Food&Beverage and Valet with 5 different defects. 3 for Food&Beverage and 1 each for Accounting and Valet. 1)wrong bill, 2)wrong order, 3)food quality, 4)server attitude, 5)vehicle damage.

    I need to associate all department and defect data with the single comment and client data. Does this help?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Since you don't want to associate specific defect with specific department, suggest possibly 3 more tables.

    tblClientComment (perhaps Comment will just be another field in tblClients, depends if you will have repeat clients)

    tblCommentDepartments

    tblCommentDefects

    The alternative is use of 2 multi-value fields in tblClientComment and I NEVER advocate use of multi-value fields.
    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
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118
    Would it be better if the defects were associated with a particular department? That’s an easy change

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Better is whatever meets your needs.

    Might be simpler. A tblCommentDetails would have a record for each Department/Defect pairing that must be associated with tblClientComment.
    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
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    maybe this totally misses the mark but maybe

    tblDepts tblCust tblDefects tblComment
    DeptID CustID DefectID CmntID
    DeptName Lname DefectDesc CustID_fk
    Fname CustCmnt

    then 1 table to join

    tblCmtData
    DataID
    CmntID_fk
    DeptID_fk
    DefectID_fk

    where join table would have 5 records with ID. Data might then look something like
    DataID
    CmntID_fk DeptID_fk
    DefectID_fk
    1 2 1 2
    2 2 1 3
    3 2 3 1
    4 2 4 4
    which enables any department to have more than one defect type. Basing that on only tracking 4 defect types as stated, otherwise, maybe not.
    Last edited by Micron; 07-09-2019 at 06:38 PM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Comment example: "I visited your establishment and my bill was wrong when I went to go. I also did not get what I ordered and the chicken I had was tough. The Manager who fixed my bill was very nice, however the server was rude. I also had a scratch on my car when the valet brought it around."
    remind me not to stay at your place

    However should you not also be catching good comments as well?

  10. #10
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118
    Micron, thanks for the info. What I' really struggling with is the concept of data normalization. I've read a lot about it but still can't "see" it in my head.

    Is there a sample database anywhere on line where I could see table relationships and queries?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can download many sample dbs from MS. When you open Access > File > New > choose
    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.

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by JonathanT View Post
    Micron, thanks for the info. What I' really struggling with is the concept of data normalization. I've read a lot about it but still can't "see" it in my head.
    Is there a sample database anywhere on line where I could see table relationships and queries?
    Samples will only help if the business case they support is entirely understood by you and that understanding bolsters the learning of the concepts. If you want to replicate that business model, then they certainly can help. However, if what you get from them is how to replicate that model but not a clearer understanding of the concepts that got the designer there, then you are still at a disadvantage. My suggestion would be to focus on the concepts, and if the links I provided are not making them clear then you should be looking for others that provide that "ah-haa" moment. There are TONS of other tutorials and videos on the subject.

  13. #13
    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
    Normalization is fundamental to relational database. And learning the basics of database is paramount as has been mentioned.
    I recommend a few tutorials from RogersAccessLibrary mentioned in the Database Planning and Design link in my signature. If you work through 1 or 2 of these, you will learn a procedure to design your tables and relationships. Once you understand the basics of the procedure, then work with your own set up and apply the procedure.

    However, each tutorial (and almost all samples you'll find) depend on a clear statement of requirements. It seems to me from your post that you are struggling with the business rules involved and what is expected. This signifies a lack or incomplete analysis or insufficient requirements.

    DO NOT start by trying to guess at tables with a physical Access database. That's a long, frustrating and extremely error prone approach. Learn some fundamentals; try another tutorial; watch a video.

    Good luck with your project.

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    From your Excel sheet it looks like Defects relate to 1 Specific Depart in each of the Clients Records

  15. #15
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118
    I've read about normalization and took and online class and I'm still not clear on how to design the table relationships. I'll try to explain. My original data is an excel spreadsheet that contains customer information, location information and a text comment field. The format of the original data can't be changed. The customer and location information is mostly irrelevant. We concentrate on the comment field as it explains in detail the customer experience.

    Based on the text in the comment field we need to reference(I hope that's the right word) for each record the department(s) mentioned and the specific issue(s) mentioned. A single record needs to be able to reference from 1 to 5 different departments. That same record needs to reference from 1 to 4 issues from a list of 150. If I understand what I've been reading I need 3 tables. One with the Comment information, one for the Departments and 1 for the Issues.

    The Departments table will have a 1 to many relationship with the Issues table. However there is no common field between the Comment table and the other 2. Is it possible to create a relationship is this situation by manually adding fields to one or more tables?

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

Similar Threads

  1. Need to hire out relational database design
    By janakybrent in forum Access
    Replies: 5
    Last Post: 02-19-2016, 05:11 AM
  2. Relational Database
    By Seb1128 in forum Database Design
    Replies: 1
    Last Post: 04-01-2015, 12:13 PM
  3. Relational Database
    By javier571 in forum Database Design
    Replies: 1
    Last Post: 04-13-2014, 07:05 AM
  4. Relational Database Design Questions
    By mribnik in forum Database Design
    Replies: 40
    Last Post: 08-09-2011, 02:57 PM
  5. Relational Database/query help
    By Champin4 in forum Queries
    Replies: 7
    Last Post: 07-14-2010, 08:49 PM

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