Results 1 to 11 of 11
  1. #1
    CitizenDolan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2018
    Posts
    5

    Multi value fields

    Hey everyone,

    I apologize if this question has already been asked in advance.



    I have been using Microsoft Access for about two years now; however, it wasn't until recently I started to fully understand the capabilities of Access and when from designing glorified spreadsheets to actual relational databases. Here is my current issue

    Also one caveat....I need to be able to import the data from an excel sheet when I am done.


    1st table -

    Each individual can have multiple records in that table; but, each record is assigned to one individual Within that table there is a field that tracks whether they met with Psychiatrist or a Clinical Therapist. (These are the only two values, both can be selected) Each intake form needs to show who they met with and what session they signed up for.

    For example an individual can meet with both a psychiatrist and a therapist. That meeting is associated with a session. I need to set up my database in such a way that it associates the type of session with the type of employee who recommended the session. I do not want to create multiple fields because there are 6 different types of sessions. An individual can meet with both a psychiatrist and a therapist and both the therapist and the psychiatrist can recommend session A. However; one can recommend session A while the other recommends session D. I need to be able to show the psychiatrist recommended A while the therapist recommended D. On the front end the electronic form needs to have a field that allows the data entry clerk to check off the criteria, as I don't want them entering free text.


    For context and the problem I am trying to solve. Previously my colleagues were using an excel spread sheet and creating multiple rows. (Doubling their work) They would fill out one showing they met with Psychiatrist and a recommendation of session A. Then another record (once again entering all that intake information) Showing they met with a therapist who recommended D. Now while these situations are outliers; it made the data when getting patient totals per month inaccurate.

    I have a separate table listing the sessions. The identifier is the name of the type of session. I also have a table listing the job titles. With the identifier being either "Psychiatrist" or Therapist." I assume I need some sort of many to many linking table; but, I am unsure how to set it up.

    Hopefully I explained this alright. It sorta gets tricky to explain it over a forum. Please let me know if you need additional context.

    Any help would be greatly appreciated. Thanks in advance.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Some initial questions:
    An individual can meet with both a psychiatrist and a therapist
    In the same meeting? Is that 1 meeting, 2 sessions or 1 meeting 1 session, basically making a session and a meeting the same thing?
    Either psychiatrist or therapist can recommend any of the 6 sessions?
    the psychiatrist can recommend program A. However; one can recommend session A
    careful when mixing terms; can make things unclear, e.g. there may or may not be programs and sessions.

    Are you up to speed on and understand normalization concepts? Entity/attribute concepts? Database planning? Don't want to bore you with links if you're well versed in those things. Believe it or not, good planning starts with paper (ok, white board is good too). If you can map it out, you're on your way.

    BTW - what is a multi value field (title of your thread) mean to you? I'm not seeing the connection. Hope you're not planning to use them...
    Last edited by Micron; 12-07-2018 at 07:39 PM. Reason: added questions
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CitizenDolan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2018
    Posts
    5
    Hey Micron,

    Haha, I'm glad you pointed that out, it's funny because i realized i started calling them different things and I thought i changed them all. Good eye haha. Lets refer to them all has sessions.


    Just to set the terms

    This what's in the main table. (There are other fields as well but I am simplifying it because with the other fields I am able to create relationships with referential integrity with no issue)

    Meeting = (each patient has one meeting)
    Employee Title = during the meeting, the patient could have an encounter with a therapist, or a psychiatrist, or both; but, a psychiatrist and therapist can only see 1 patient during the meeting.
    Session= (its sorta like the result of the meeting) A psychiatrist and therapist can both recommend different types of sessions.

    But to answer your question- Yes, in the same meeting. Essentially the table/form looks like this

    Patient Name - one to many relationship to a separate patient table
    Employee Title (Multi-value field) (Values are Psychiatrist, Therapist)
    Session Recommendation (Multi-value field) (Session A, Session B Session C etc)

    The types of question I want to be able to answer in a query are sorta like "How many Therapists recommended Session C vs. How many Psychiatrists recommend session D" but I also need to be able to answer "How many meetings were given in a year?" With the structure of how my coworkers were previously doing it. They were entering multiple records for the same meeting because they had to write for example

    (patient name field) patient a (employee title field) psychiatrist (session recommendation field ) Session A
    (patient name field) patient a (employee title field) therapist (session recommendation field ) Session B

    As far as I know, a psychiatrist and therapist can't recommend multiple sessions in the same meeting.

    I understand the basics, but I am always open to learning more. I am the beginning of my Data Analyst career and any sort of links would be by no means boring I am very interested in building out proper structurally sound databases.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IMHO, and I'm sure the opinion of many experienced developers here and elsewhere, you do NOT want to use multi value fields. Nor lookup fields.
    While this http://www.utteraccess.com/forum/Mul...-t1991337.html likely isn't the definitive and last word on whether or not to use them, the question is why would you if you don't have to, given the potential problems? Make sure you read that (and maybe other links that you find) through - some queries won't work at all, others will need special tweaking. Hate to sound snobbish, but I can only think of 2 reasons to use them. 1) you're working with SharePoint lists 2) you're unaware of the pitfalls or you are aware and don't care. They use deeply hidden tables which basically replicate what you should be creating manually following basic normalization rules.

    I would assist to the best of my ability with a normalized approach but can't when you want to use mvf's as I don't believe in helping anyone build a flawed design. Maybe others here will disagree and someone will assist. I'd have to refresh my memory on how to use them, as I simply haven't. My experience is limited to helping those who fell into that trap and had to come to this forum for help. In your search, you will find plenty of tutorials and such embracing their use. Not sure why. Maybe they missed the point that AFAIK, they were designed to work with SharePoint lists.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    CitizenDolan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2018
    Posts
    5
    Quote Originally Posted by Micron View Post
    IMHO, and I'm sure the opinion of many experienced developers here and elsewhere, you do NOT want to use multi value fields. Nor lookup fields.
    While this http://www.utteraccess.com/forum/Mul...-t1991337.html likely isn't the definitive and last word on whether or not to use them, the question is why would you if you don't have to, given the potential problems? Make sure you read that (and maybe other links that you find) through - some queries won't work at all, others will need special tweaking. Hate to sound snobbish, but I can only think of 2 reasons to use them. 1) you're working with SharePoint lists 2) you're unaware of the pitfalls or you are aware and don't care. They use deeply hidden tables which basically replicate what you should be creating manually following basic normalization rules.

    I would assist to the best of my ability with a normalized approach but can't when you want to use mvf's as I don't believe in helping anyone build a flawed design. Maybe others here will disagree and someone will assist. I'd have to refresh my memory on how to use them, as I simply haven't. My experience is limited to helping those who fell into that trap and had to come to this forum for help. In your search, you will find plenty of tutorials and such embracing their use. Not sure why. Maybe they missed the point that AFAIK, they were designed to work with SharePoint lists.

    Hey Micron,

    I have no problem with not using multi valued feels. I am not using sharepoint or anything. I know there are some pitfalls but I am not aware of all of them. If am totally open to designing my database without MVFs. I already saw some early issues with them when i tried to create a pivot chart and I kept getting unique IDs. Between that and your advice; I have no problem designing the database without them.

    Could you send me the stuff on normalization? How would you approach a situation similar to mine based on the criteria?

  6. #6
    CitizenDolan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2018
    Posts
    5
    Also I am totally open to designing this database from the ground up if necessary, as long as I can import the old records from excel at the end. I understand that I may have to re organize the data in excel to line up with the new fields/columns. My main goal of this project (besides increasing productivity for my job) is to build a structurally correct database from beginning to end and minimize flaws.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I may have to re organize the data in excel to line up with the new fields/columns
    Probably not, but you're not close to worrying about that yet. I'm just going to copy/paste from a list I have; might be some repetition here. One of the more important aspects is to grasp the entity/attribute relationship. Normalization is paramount. Diagramming maybe not so much for some people.

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.ca/...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

    Important for success:
    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

    Be sure to check this out; lots of links in post 3 and good comments throughout the thread https://www.accessforums.net/showthread.php?t=65220

    I have been playing around in the meantime and might as well post what I have rather than save or lose it. I'm sure there's a mistake or two as now I'd have to go back and review your comments regarding the way thing work. Sorry, cannot seem to control the individual table column widths.
    First 3 should be obvious. They're lookup tables (not lookup fields); basically an entity list.
    tblPatients
    PatientIdPk
    FName
    Lname
    etc. - pertaining to patient data


    tblCareProviders
    CpIdPk
    ProviderType *don't use Type
    PTypeDesc *don't use Description
    FName
    Lname
    etc.


    tblSessions - list of 6 (currently) session types
    SessionTypePk
    SessionName *don't use TypeName


    tblPatientMtgs
    PmtgIdPk
    PatientIdFk - allows multiple meetings per patient
    etc. -such as location, date, start/end times


    tblMtgCareProv - which care provider(s) attended meeting?
    MtgCpPk
    PmtgIdfk - patient meeting ID; allows multiple session recommendations for a meeting
    SessionTypefk - what session recommended by (next field)
    CpIdfk - from tblCareProvider - who recommended the session
    etc. - pertaining to session; e.g. start/end times**NO - this was left over from an edit.

    That's it for tonight; boss is calling me. Dog wants out too...
    Last edited by Micron; 12-08-2018 at 12:11 PM.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    CitizenDolan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2018
    Posts
    5
    Quote Originally Posted by Micron View Post
    Probably not, but you're not close to worrying about that yet. I'm just going to copy/paste from a list I have; might be some repetition here. One of the more important aspects is to grasp the entity/attribute relationship. Normalization is paramount. Diagramming maybe not so much for some people.

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.ca/...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

    Important for success:
    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

    Be sure to check this out; lots of links in post 3 and good comments throughout the thread https://www.accessforums.net/showthread.php?t=65220

    I have been playing around in the meantime and might as well post what I have rather than save or lose it. I'm sure there's a mistake or two as now I'd have to go back and review your comments regarding the way thing work. Sorry, cannot seem to control the individual table column widths.
    First 3 should be obvious. They're lookup tables (not lookup fields); basically an entity list.
    tblPatients
    PatientIdPk
    FName
    Lname
    etc. - pertaining to patient data


    tblCareProviders
    CpIdPk
    ProviderType *don't use Type
    PTypeDesc *don't use Description
    FName
    Lname
    etc.


    tblSessions - list of 6 (currently) session types
    SessionTypePk
    SessionName *don't use TypeName


    tblPatientMtgs
    PmtgIdPk
    PatientIdFk - allows multiple meetings per patient
    etc. -such as location, date, start/end times


    tblMtgCareProv - which care provider(s) attended meeting?
    MtgCpPk
    PmtgIdfk - patient meeting ID; allows multiple session recommendations for a meeting
    SessionTypefk - what session recommended by (next field)
    CpIdfk - from tblCareProvider - who recommended the session
    etc. - pertaining to session; e.g. start/end times

    That's it for tonight; boss is calling me. Dog wants out too...

    Ahh thank you! This is amazing, I really appreciate you taking the time to do all of this. I am going to read everything over and then look over these tables again. I assume they will make more sense to me once I get through all the articles. Thanks again and I will be reaching out probably in the next day or so once I get through everything. I just got done reading the normalization piece and it was VERY helpful. I wish I read this stuff years ago when I started making very simple databases; but, better late than never!

    Have a great weekend and once again THANK you. I look forward to speaking again so if you happen to have the time.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    One more article you might want to read to add to the excellent list already provided by Micron.
    MVFs and why you really shouldn't use them ... http://www.mendipdatasystems.co.uk/m...lds/4594468763
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @citizendolan - you gotta find the reply (not reply with quote) button, or just go to the bottom of the page and start typing above the 'post quick reply' button. Quoting my novels when you don't need to will turn a normal thread into a tome larger than War and Peace.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Micron
    You'll have to start writing short stories instead
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Multi Valued Fields
    By Poppy in forum Access
    Replies: 10
    Last Post: 11-23-2018, 05:27 AM
  2. Getting Rid of Multi-Value Fields
    By raychow22 in forum Access
    Replies: 5
    Last Post: 08-16-2018, 04:24 AM
  3. DCount multi value fields
    By Xterra14s in forum Access
    Replies: 6
    Last Post: 08-10-2016, 09:06 AM
  4. Multi Value Fields
    By lyncha in forum Access
    Replies: 5
    Last Post: 03-28-2013, 03:33 PM
  5. Multi-value fields and sub-datasheet.
    By specialk in forum Database Design
    Replies: 1
    Last Post: 12-05-2012, 03:18 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