Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 46
  1. #16
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    374
    Okay, well that is a lot to take in. So, I'm going to start with Gender. Can someone pick more than one Gender? That looks to me like it should be a look up table not separate fields. Hmm, this would also apply to Sex. Are you familiar with Look-up Tables?

    Do not touch or otherwise engage any table that start with MSys... those are for Access not for you.
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  2. #17
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    I admit I didn't pick the best example. Gender and sex would normally be mutually exclusive, but I work for an LGBTQIA+ organization, so I'd rather leave those categories open to multiple values because I'm pretty sure a few clients will want to mark multiple values at some point, and I wouldn't want them to feel excluded.

    So my understanding is:


    • when the responses are mutually exclusive, use a listbox in the table.
    • when the responses can have multiple values, use a listbox in the form but not the table. (so then back to my question of whether the yes/no approach in the table is what I should be using, or something else).


    Thank you all again, and sorry I'm such a noob. Just want to get this right from the beginning.

  3. #18
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    374
    Umm, I so work for such Clients and them being allowed to select multiple is not handled like that. You are setting yourself up for trouble. You use a Look-up Table then you create a separate Table, actually two in your case like...

    tblClientGenders
    cgID (PK for this table)
    cgClientID (Linked to tblClients)
    cgGenderID (Linked to tlkpGenders)
    cgOther (Short Text for them to write something else in)

    You put the Subform on as continuous and they can select as many as the like. The same principal would be applied to Sex. Trust me you do not want to do it they way you are as it will be a nightmare printing that because they are check boxes. You will need to work hard to get the actual value to show.

    No worries about being a n00b, we were all there once and someone took the time to *school* us.
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  4. #19
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    Thank you Gina. So I think I'm understanding this, except perhaps that for the third item you listed, I would naturally want to call it cgGender and not cgGender ID (because that is the value from the lookup, not a number, right?)

    Do my tables work now? (attached)
    Click image for larger version. 

Name:	tblClients.jpg 
Views:	37 
Size:	115.1 KB 
ID:	40930Click image for larger version. 

Name:	tblGender.jpg 
Views:	36 
Size:	134.3 KB 
ID:	40931Click image for larger version. 

Name:	luGender.jpg 
Views:	36 
Size:	59.2 KB 
ID:	40932

  5. #20
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    374
    Yep, you are correct, I forgot the *ID* part but I see you figured it out, good job! However I am missing something on your luGender table. Where is the ID field? It should be numeric as well as the field in your tblClientGender. Numbers take up less space and since it sounds like your database could grow you want to use prudence when selecting a Data Type. You will be able to show the Short Text on your Report or Form so no need to store it in the main table. You also want to use a continuous form as a subform on your main form. While List Boxes are nice for your purposes it will be easier if there is a record for each Gender. And no, no multi-value fields allowed. They just cause headaches.
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  6. #21
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,257
    See attached relationship example which you might find helpful. Based on this relationship
    Click image for larger version. 

Name:	Capture.PNG 
Views:	35 
Size:	10.2 KB 
ID:	40934
    attached db has some example forms to demonstrate how these are presented
    Attached Files Attached Files

  7. #22
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,791
    PMFJI, I have a few comments/suggestions.

    You should read Microsoft Access Tables: Primary Key Tips and Techniques (<<-- ** Important)

    When I name fields in a table, they would look like
    tblClientGender
    ----------------
    cgClientGenderID_PK - Autonumber (PK)
    cgcClientID_FK - Number (Long Integer) (FK to tblClient)
    cggGenderID_FK - Number (Long Integer) (FK to tblGender)
    cgOther - Short Text

    The table name has "tbl" as a prefix.
    The PK field has a suffix of "_PK"; a foeign key field has a suffix of "_FK". (Ajax uses "PK'/ "FK" suffixes)
    The suffix "ID" (for me) means it is a numeric type field (autonumber/Long).

    I like that you (the OP) used a prefix (example "cg") to indicate what table the field is in.
    For a table I named "tblEmployee_Name_Def", I use field prefixes of "end_"..... (as in "end_FName")


    You might find it helpful to first design the tables/relationships using a whiteboard, a window or even paper & pencil BEFORE jumping into Access. It is easier to make changes and to see the "big picture" or at least it is for me (I use a big window).


    ----------------------------------------------------------------------------------------------------
    About your table design....
    Most of the field names in tblGender are data.
    Click image for larger version. 

Name:	Table1.png 
Views:	33 
Size:	109.3 KB 
ID:	40937
    The reason this design is bad is that to add another gender "type", say "Hermaphrodite" (sorry, couldn't come up with another example), first you have to edit/change the table.
    Then you have to edit the queries, forms, reports & then the code. All this just to add one more option.

    If you have a table
    Code:
    tblGender
    ----------
    gGenderID_PK    - Autonumber (PK)
    gGender        - Short Text
    all you have to do is add the name to the table. Everything else doesn't have to change.



    I'll step aside now... Good luck with your project.......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #23
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    Thank you all for your thoughtful responses. I know it takes time to explain these things to someone without much knowledge. I see the main PK, and I see the first FK. I'm still trying to wrap my mind around the second FK but I'll check the links you sent and play around a bit before posting again. Thank you again.

  9. #24
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    308
    Hi

    PMFJI but the following table is wrong.

    The DataType for cgClientID and cgGenderID should be Number Data Types.

    When you create your Form for Data input you would create a Combobox to lookup the required Client from tblClients and store the Primary Key for the Client Selected.

    The same applies to looking up the required Gender from tbluGender
    Attached Thumbnails Attached Thumbnails Gender.PNG  

  10. #25
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    Quote Originally Posted by Ajax View Post
    cgClientGenderID_PK - Autonumber (PK)
    cgcClientID_FK - Number (Long Integer) (FK to tblClient)
    cggGenderID_FK - Number (Long Integer) (FK to tblGender)
    cgOther - Short Text
    Hi Ajax,

    Quick question. I'm almost there in understanding this, but I'm curious for the two middle entries above, why not just prefix with "cg" rather than cgc and cgg? Is it because they are FKs? (because then the last item, for other, doesn't start with "cgo" so I'm guessing that's because the other category is not linked to a lookup table, and the third letter in the other two items must refer to the lookup table?)

  11. #26
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    Quote Originally Posted by Ajax View Post
    in that case you either change the name or address, but if you need to keep a history you would have additional tables something like

    tblClients
    ClientPK
    etc (you could store the latest name here or reference tblNames for the latest name)

    tblNames
    NamePK
    ClientFK
    ClientName
    DateFrom

    tblAddresses
    AddressPK
    ClientFK
    ClientAddress
    DateFrom
    So my only question about this is the "DateFrom." Would that be a field that records the date when the name is stored (in my case, the date of the current caseworker-client interaction)? Or does the date refer to the first date when the client began using that as their preferred name?

    Same thing for address--does DateFrom mean the address at the current interaction or the date when they began living there?

    Thank you!

  12. #27
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,257
    datefrom means what you want it to mean - my expectation is that it means the date from which you are required to start using the new name or address. If someone lives at address A then moves to address B and again to address C, you maintain a history of their addresses - the latest address (C) will have the latest date. But it is your app, you may only need the latest address in which case you simply overwrite the previous address

  13. #28
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,791
    Quote Originally Posted by PrincessApril View Post
    Hi Ajax,

    Quick question. I'm almost there in understanding this, but I'm curious for the two middle entries above, why not just prefix with "cg" rather than cgc and cgg? Is it because they are FKs? (because then the last item, for other, doesn't start with "cgo" so I'm guessing that's because the other category is not linked to a lookup table, and the third letter in the other two items must refer to the lookup table?)
    tblClientGender
    ----------------
    cgClientGenderID_PK - Autonumber (PK)
    cgcClientID_FK - Number (Long Integer) (FK to tblClient)
    cggGenderID_FK - Number (Long Integer) (FK to tblGender)
    cgOther - Short Text
    I believe that I posted this. (Post #22)


    I was trying to follow your naming convention.
    In "tblClients", the PK field is named "cClientID" to which I added "_PK". (it is an autonumber type)
    In "tblClientGender", you added "cg" (which I thought was for client gender) and the PK field is named "cClientID" (the c for Client?)

    So in "tblClientGender", the foreign key field name became "cgcClientID_FK"

    Same for "cggGenderID_FK" - cg for the table "tblClientGender" - "gGenderID" because the FK field is related to the PK field in "tblGender"


    So the two foreign key fields became "cgcClientID_FK" and"cggGenderID_FK"
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  14. #29
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    Hi folks,

    First, thank you all again for helping me as I begin to understand table structures and relationships. Attached is an example of my current setup.

    My main question is around the highlighted row in tblClientGender. I added that blue row so that there would be a field to store the client's gender (otherwise I don't see where the client's gender would be recorded). I plan to use the lookup table in the form, but a field still seemed needed in tblClientGender to store that data, right?

    Other than that, I'm hoping I am on solid footing for my tables, but if something jumps out at you from my current iteration, I'd be very appreciative to know!

    Click image for larger version. 

Name:	tblClients_updated.png 
Views:	15 
Size:	32.7 KB 
ID:	41048Click image for larger version. 

Name:	tblClientGender_updated.jpg 
Views:	16 
Size:	76.5 KB 
ID:	41049Click image for larger version. 

Name:	luGender_updated.png 
Views:	16 
Size:	23.8 KB 
ID:	41050Click image for larger version. 

Name:	luGender(Data)_updated.png 
Views:	16 
Size:	33.7 KB 
ID:	41051Click image for larger version. 

Name:	relationships_updated.png 
Views:	16 
Size:	41.6 KB 
ID:	41052
    Last edited by PrincessApril; 02-25-2020 at 04:31 PM. Reason: Clarity

  15. #30
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    374
    To answer your question, you only need to store the ID. You can use a Combo Box on the Form, as well as, using the table in the Query for the report which will allow you to see the value instead of just the ID.

    And let me just say, you are well on your way. Great job!
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-10-2017, 09:13 AM
  2. Replies: 9
    Last Post: 08-05-2015, 05:34 AM
  3. Question on database structure - limiting linked records
    By andrewb in forum Database Design
    Replies: 2
    Last Post: 03-22-2012, 05:41 PM
  4. Question on how to structure a database
    By ExterminatorJeff in forum Database Design
    Replies: 3
    Last Post: 03-17-2012, 06:34 AM
  5. Database Structure | Best Practice Question
    By davidson12 in forum Forms
    Replies: 0
    Last Post: 11-05-2009, 03:29 PM

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 - Senior Forums