Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    Normalization Advice

    I am trying to set up employees tables. Right now I have the following tables but i am not sure if this is the correct way. I am wanting to have all the employees first, middle and last names, their email address, the email group that they belong to( may be in more than one group), which section of the company they belong to, and their desk phone number. Please let me know if this is the right way to normalize this data. Thank you. I have worked through some of the links on normalization that i have been given here. The part I am having trouble with is in the tbl_EmailUser table an employee may have 4 or more records because of the Email group they are in. I dont think this is right. That is why i am asking for advice. Thank you for any help. --Walker

    I have the following tables so far.

    tbl_EmailUser
    EmailID AutoNumber
    UserFirstName Short Text
    UserMiddleInitial Short Text
    EmailAddress Short Text
    UserPhoneNumber Short Text
    EmailGroupID Number
    UserSectionID Number

    tbl_EmailGroup


    EmailGroupID AutoNumber
    Emailgroup Short Text

    tbl_UserSection
    UserSectionID AutoNumber
    UserSection Short Text

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the employee should only exist once in t
    EmailUser.
    they will exist many times in: t
    EmailGroup
    but you dont need EmailGrp.GroupID , instead it should be:
    UserID, UserName
    1, bob
    2, beth
    3, jim

    Emailgroup ,EmailID
    Acctg, 1
    Acctg, 3
    IT, 1
    IT,2


    then the user can exist in many groups


  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    F.W.I.W. Most would advise against the use of spaces in the name of any object including field names.

    Edit:
    Sorry, just looked properly at your field names and realised that you have no spaces. What I saw were the spaces in the Field Types
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    It seems like a user can belong to many email groups, and email groups can have many users. That sounds like a many-to-many relationship so you'd want a junction table to link users and groups.

    Click image for larger version. 

Name:	Untitled.png 
Views:	26 
Size:	14.0 KB 
ID:	41458

  5. #5
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Thanks Ranman. That makes more sense. I keep getting messed up on that part. Would I then go to the relationships and make a one to many relation from the t emailuser.MailID to t emailgroup.MailID? If so How do i know which join type to use?

  6. #6
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    KD2017

    I dont understand the middle table between the user and the group. Is that another way to do the same thing ranman said?

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by NightWalker View Post
    I am wanting to have all the employees first, middle and last names, their email address, the email group that they belong to( may be in more than one group)...
    Quote Originally Posted by NightWalker View Post
    KD2017

    I dont understand the middle table between the user and the group. Is that another way to do the same thing ranman said?
    It's not exactly what ranman suggested, but close. My suggestion goes one step further in normalizing the data structure by breaking out the email group description to it's own table.

    It's a junction table. It's necessary to create a many-to-many relationship. What that junction table does is allows you to link one user to many different groups, and vice-versa one group can have many users. That's how I understood your requirements, maybe I misunderstood.

    Search google or youtube for "many-to-many relationships" or "junction tables"

    Here's a video from a guy that does Access tutorials on youtube, I haven't watched this video but he usually explains things pretty well.
    https://www.youtube.com/watch?v=mtlIiUVVMHA

  8. #8
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    KD

    I was doing that very thing while i was waiting for your reply. I am not understanding the Join type part though. how do i know if it is all from one table and only ones from other table and which one of those do i use? there are 3 options. I have been reading on that but it doesnt explain it very well. I am not able to get to the youtube video here at work so i will have to wait till later to look at it.

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I'm not entirely sure what you're asking. Are you referring to the "Join Properties" dialog when click "Join Type..." in the "Edit Relationships" dialog? If so I virtually never change it from default option #1. I *think* that property only effects how SQL code is built by default by access when building queries and can and should be overridden/specified by you when building those queries anyway.

    I've attached the example db and threw together a few input forms, maybe it will help.

    [Edit]
    When you are building a query the join type will depend on the circumstances. But if you're trying to get a list of users and their groups, even if the user has no groups, then you would choose show all from users table and only those from groups table that match. If you only want users that belong to groups then you'd chose the option that says show only matches from both tables.
    Attached Files Attached Files

  10. #10
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    yes those are what i was referring to. So what you are saying is that best practice is to leave on option 1 and make the changes in my SQL statements? I opened your example and it has given me even more questions, lol. The forms on the bottom of the main form are really cool and i think i would have lots of uses for those. I will have to do much more googling. Thank you all for your help.

  11. #11
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by NightWalker View Post
    yes those are what i was referring to. So what you are saying is that best practice is to leave on option 1 and make the changes in my SQL statements?
    I won't comment on best practice but *my practice* is I always check enforce referential integrity, UNcheck both cascade update and cascade delete, and leave join type option on #1. The join type should be decided on a case by case basis when you're building your queries in the query designer window, you usually wouldn't need to edit the SQL code directly. In the query designer you'd just double click on that relationship line and it'll let you choose the join type in that query.

    I opened your example and it has given me even more questions, lol. The forms on the bottom of the main form are really cool and i think i would have lots of uses for those. I will have to do much more googling. Thank you all for your help.
    Consult with google about "subforms". Welcome to the rabbit hole!

  12. #12
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    KD

    Thank you for all your help. i am not really sure where the best place to start on this rabbit hole is. I have been teaching myself just the things i need to know when i get to it. I am sure there is a better way to go about this but i was thrown into it at work and wasn't given any time to learn properly. Again thank you for your help. I just wanted to say i clicked the star thing for your help and honesty.

  13. #13
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    KD

    One more question on this. How do you fill in the data on the junction table if they are just numbers? Right Now I have to keep going from table to table to remember what each number means. Is there a trick to this or is that what your forms are for?

  14. #14
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Yep, that's what the forms are for. In fact you shouldn't ever let your users work directly in the tables.

    If you look at the subforms in the example database notice that the form's recordset is based only on the junction table, the control is a combobox that then looks up the ID and a relevant label (either the user name or the email group, depending on which subform you're looking at). The ID number is behind the scenes and is what the computer understands how to use to link everything together, the relevant label is just for the user's benefit.

    Some more homework to google up down the road is "lookup tables". You'll probably come across "lookup fields" data types and that is a specific feature to access, DON'T use those.

  15. #15
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Is that where the data displayed in the table is not actually what is there because it is looking up other info from another table and displays that info?

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

Similar Threads

  1. Normalization
    By kaylachris in forum Database Design
    Replies: 3
    Last Post: 03-12-2019, 03:47 PM
  2. Normalization
    By jlt199 in forum Database Design
    Replies: 10
    Last Post: 03-21-2014, 12:22 PM
  3. Need Help about Normalization
    By Book3s in forum Access
    Replies: 2
    Last Post: 10-24-2013, 02:03 AM
  4. Normalization
    By KPAW in forum Database Design
    Replies: 1
    Last Post: 06-09-2011, 06:24 PM
  5. Normalization
    By U810190 in forum Access
    Replies: 1
    Last Post: 03-30-2010, 04:55 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