Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 46
  1. #31
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42

    Hmm, so when I create the combo box on the form, it asks me where to store the value selected from the lookup table, so I thought that I would need a table field to store the selection?

    And thank you for the encouragement!

  2. #32
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    You will be storing the PK not the value which is all you should be storing. You can see the value in a combo box because you will have two column the first being 0" length. You do need or require the actual values in the table.

  3. #33
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    Okay, thanks so much Gina. I think I can envision that. I can see what you mean about the "weeds," as it seems like there will be a huge network of interconnected tables.

    So just to clarify--I am not setting any lookup options within the tables themselves; rather, I will be doing that all through the form controls, correct? (and I suppose that will be a good place for me to end this thread! )

  4. #34
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    That is correct, no look-up's at the table level. Everything happens via the Form or Report level.

    End if you like, we will all still be here should you have additional questions.

  5. #35
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    Quote Originally Posted by GinaWhipp View Post
    That is correct, no look-up's at the table level. Everything happens via the Form or Report level.

    End if you like, we will all still be here should you have additional questions.
    Thank you so much!

  6. #36
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    A related question that just occurred to me is how to handle values that would come from a lookup table where only one value is possible (this thread has thus far dealt with fields with multiple values possible). For example, in the first attached image (couldn't delete second image sorry), I have a table relating to the client's primary phone number (I'll have a separate table for alternate number, because either number could change at different times). Only one value would be possible for the fourth row (phone type).


    • I'm using an FK from ClientID because the phone number might change over time.
    • So then we come to the fourth row. I have a lookup table for the phone type (personal cell, VoIP, home landline, work landline, etc.) Am I doing this right by just having phone type as short text in my table, then in the form I will use a listbox to draw from the lookup table? Or do I have to use a FK and combobox for this too like with the multiple values earlier in this thread?

    Click image for larger version. 

Name:	tblClientPhonePrimary.jpg 
Views:	16 
Size:	112.6 KB 
ID:	41080
    Attached Thumbnails Attached Thumbnails tblClientPhonePrimary.jpg  

  7. #37
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, let me tell you what I do...
    Click image for larger version. 

Name:	tblEntityCommunications.png 
Views:	16 
Size:	9.0 KB 
ID:	41081

    So, I have a tblEntityCommunications in which which is ecCommunicationTypeID. This can be a Primary Number, eMail address, Website, etc. (I also added an Opt Out check box in case the person does not want to you to use the type of communication. I think something like this might be more beneficial because it is *fluid* and allows for your own custom Communication Types, i.e. Primary NUmber.

    Does that help?

  8. #38
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    Thanks Gina. I agree that that would be more adaptive overall.

    I probably have to keep similar to the current structure because I am following a paper form, and we have to have certain data entered electronically as required by our funders.

    So, considering that, I'm still wondering about my fourth row. Should I handle it with a FK just like with other tables in this discussion (e.g., race), or is the situation different given that only one value is possible from that lookup? From the discussion in this thread, it seemed like the use of a ID with FK was only necessary for lookups with multiple values? That's why I couldn't decide how to handle the fourth row (right now just short text and I plan to do lookup in the form).

  9. #39
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    So I will have tables for primary and secondary phone number with the same fields you see in the attached image. I'll also have a smaller table for email address. I have a separate table for mailing address. And finally I will have one sort of stranded field, which will be client's preferred method of contact, which I suppose I'll just make it's own table (again that last table would raise the same question of how to handle a field whose data will ultimately come from a form listbox where only one value from the lookup table is possible). I'm not sure if I'm asking the question clearly though.

  10. #40
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Forget about the paper form. You can use subreports, code and queries to make it look anyway you want. You can also use automation to import to the different tables. So no worries there, we just need to make sure you are storing it properly.

    You ALWAYS need a PK no matter what. And it is always better to use a NUMBER as ultimately they take up less space and you want to maximize space when storing data. Remember, you will get the value (SHORT TEXT) when it's time to do a report.

    You do not need another table for eMail's. You can use the same table just base the look-up on the Communication type ID you assigned to the eMail Address. Because I am thinking people might have more than one eMail Address.

  11. #41
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    That's true--I have to forget that mentality sometimes and remember I can manipulate as needed in the form.

    Attached is what I have so far (a bit different than yours based on needs). I can show or hide certain fields based on the selection of the type of contact, such as only showing the "okay to leave voicemail" field when a phone type is chosen.

    I'm actually not sure if I should call DateFrom something else, like DateLastVerified or something, because let's say the phone number has been active for 2 years--they might not remember the date when it was first active but the relevant part is whether it is still active?

    Click image for larger version. 

Name:	tblClientContact.jpg 
Views:	13 
Size:	131.9 KB 
ID:	41087

  12. #42
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    And here is my alternate contact table (it's got enough different fields I think it's worth having a separate table for these)

    Click image for larger version. 

Name:	tblAlternateContact.jpg 
Views:	13 
Size:	188.9 KB 
ID:	41088

  13. #43
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    You were going great until you created a separate table for Contacts. No need just add a field to the first table to indicate Contact Type which could be a Yes\No and if Yes then a Client the default being NO indicating it's a Contact. Just add the extra fields to the table, you don't have to show all of them on the Form.

    Also I see you have cc in some and ccc in others. Is there a reason the prefix changes?

  14. #44
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    Quote Originally Posted by GinaWhipp View Post
    You were going great until you created a separate table for Contacts. No need just add a field to the first table to indicate Contact Type which could be a Yes\No and if Yes then a Client the default being NO indicating it's a Contact. Just add the extra fields to the table, you don't have to show all of them on the Form.
    I see...that makes sense. I couldn't make up my mind, so you helped me decide!

    Quote Originally Posted by GinaWhipp View Post
    Also I see you have cc in some and ccc in others. Is there a reason the prefix changes?
    cc stands for Client Contact (tblClientContact). The extra c is only to indicate that the FK came from tblClients (and, similarly, in the next row, the extra ct is because the FK comes from tblCommunicationType). I thought I had seen a couple people using that convention earlier in the thread, so I just copied it. It was a bit much for me to process at first, but I'm getting the hang of it. Do you suggest another way to organize the prefixes?

  15. #45
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Oh okay. And nope, not suggesting another way you need to do what works for YOU, just be consistent. Everyone has their own naming convention which are sometimes identical but not always. I just wanted to know so when looking at your field names I don't think you made a mistake and typed an extra character.

Page 3 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