Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30

    Using linked tables or look-up tables for cascading combo boxes in a form in Access 2013

    Hi Access Nation!

    I'm in the planning stages of designing a database and wanted feedback on which direction to go.

    How Users Will Enter Data
    Multiple users, each with their own ID and Port location, will enter data called Effort counts. At each Port, they will make Effort counts at several Areas. Ports have their own unique Areas. There are multiple Areas for a Port. In a form, I would like them to enter Port and have only the Area choices for that port, instead of the (too) many choice for all ports.

    Which Solution?


    Since there are multiple Areas for a Port, I'm thinking linked tables like below won't work since tbl_Effort would actually have Area1, Time1, Count1; Area2, Time2, Count2; etc.

    Attachment 33218
    Does this mean using look-up tables? In the form, would I use a cascading combobox? How does this work with lookups (one for each Port)? I've looked at this thread and the warnings about lookups here. I think the difference between that thread and my situation is that there are multiple entries in tbl_Effort, all which call on Port and Area. What am I missing? What is the best practice?

    Again, I haven't actually designed anything yet. This is an example of multiple types of data that will go into the final database. Thanks for your help!

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The attachment is invalid - can't look at it.

    Lookup FIELDS are BAD. Lookup TABLES are normal and used a lot.


    Since there are multiple Areas for a Port, I'm thinking linked tables like below won't work since tbl_Effort would actually have Area1, Time1, Count1; Area2, Time2, Count2; etc.
    This indicates (to me) a non-normalized table design. But can't tell until can see an image or dB.



    I would like them to enter Port and have only the Area choices for that port, instead of the (too) many choice for all ports.
    This is doable and not hard.

  3. #3
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30

    Image of DB

    Not sure why the image isn't attaching properly. Can you see it now?
    Click image for larger version. 

Name:	notblLu.jpg 
Views:	25 
Size:	49.9 KB 
ID:	33243

  4. #4
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30

    What tbl_Effort should have

    Again, the image of the table relationships is a bit inaccurate. tbl_Effort would actually have Area1, Time1, Count1, then data for the next visited area, etc. Not sure why this is non-normalized.
    1.Tables contain logical groupings,
    2. there are non-duplicated data (no, Area 1 and Area 2 aren't the same and can't be the same, even though they come from the same possible group of Areas).

    Still, I don't think the above design will work. What about this:
    lu_Port
    lu_Area
    tbl_Effort with fields:
    Area1
    Time1
    Count1
    Area2
    Time2
    Count2, etc.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by chelonidae View Post
    Again, the image of the table relationships is a bit inaccurate. tbl_Effort would actually have Area1, Time1, Count1, then data for the next visited area, etc. Not sure why this is non-normalized.
    When you have fields with a incrementing number (Area1, Area2, Area3, ...), they are called repeating fields. This is typically an Excel spreadsheet design - there is even a name creating a table with this design - "Committing spreadsheet".

    Lets say your table has fields Area1, Area2, Area3, Area4 & Area5. You've been using the dB for 1 year. Now you are told "We need an Area6".
    What happens? You add the field to the table. Then you have to modify queries, forms, reports and possibly (probably) edit the VBA code. All to add 1 field.

    Whereas if you have a field "Area" with another field (call it "Sequence") for which area, all you have to do is add a new sequence number 6.
    Table would look like:
    tbl_Effort with fields:
    EffortID_PK (autonumber)
    AreaID_FK (linked to tblAreas)
    PortID_FK (linked to tblPorts)
    CountTime (can't use TIME - it is a reserved word)
    Count
    Sequence (sequence ->> 1, 2, 3, 4, 5, ...)

    tbl_Effort is actually a junction table, so I would name it "jctnEffort".

    tbl_Area would not have PortID in it.....


    Quote Originally Posted by chelonidae View Post
    Does this mean using look-up tables?
    Yep. That is normally the way it is done.


    Quote Originally Posted by chelonidae View Post
    In the form, would I use a cascading combobox
    You could be... but I would use a Main form / Sub form arraignment.



    It would help to see some data for Areas, Ports and the what you expect in table Effort.... (Areas, Ports, Counts, times)
    Also, maybe an tell us what you are doing - no computer jargon. Sounds kinda like you are setting up to count fish captures...

  6. #6
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30
    You are right, Steve! We are conducting a "creel" survey where we collect counts of clammers/crabbers at different locations to assess effort, and then interview them at these different areas to assess their catch. So, each survey day should have one entry in an effort table (and analogous entry in Interview table). The datasheet looks something like this:

    Date
    Port
    Sampler

    Area
    Time
    Count
    (this repeated for however many areas are visited for a particular Port)

    As a spreadsheet view, the tbl_Effort looks like this:
    Area1 AA Time1 08:42 Count1 12 Area2 BB Time2 08:46 Count2 3 Area3 CC Time3 08:51 Count3 0, etc.

  7. #7
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30
    Steve, I'm trying to see how to apply your table design.
    Fields EffortID_FK and PortID_FK are linked to tables with the unique codes for each. To me, these are look up tables, except that we've linked them many-to-many as FKs instead of using them as lookups for the fields. Does that mean the area, port are entered in these tables rather than in this table via a form?

    I'm not sure how sequence works here. It seems to me that it would be a new row for every area visited, even by the same sampler at the same port on the same day.
    Does this mean way more data entry in a form? (one entry per area visit?)

    Maybe I'm not understanding. I'm googling more about repeated measures/fields for database design but not finding much.

    Thanks for hanging in there.

  8. #8
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30

    New Relationships

    This is my mock up of the database. tbl_Effort incorporates the suggested changes to avoid the repeated fields problem.

    I haven't figured out how to make forms yet, but I assume the Area and Port in this table will be added to tbl_Area and tbl_Port, respectively, with counts and times in tbl_Effort. For each area, the data will be a new row in each field (correct?). I'll have to figure out how to do this in forms so the Port doesn't need to be added each time.

    tbl_Interviews and tbl_Biodata (no repeated measures like Area1, Area2 for each record) call Area and Port using look ups.

    One thing I didn't mention is that I wanted to eliminate data entry errors (there were sooo many in my inherited database) so I wanted combobox or list boxes to select for almost everything except for number values. I have a feeling I'm not normalizing correctly. Feedback?

    Click image for larger version. 

Name:	Relationships_v1.png 
Views:	21 
Size:	128.7 KB 
ID:	33297
    Attached Thumbnails Attached Thumbnails Relationships_v1.jpg  

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've got to study your design (in Post #8) for a while.

    I used a main form/sub form design to enter ports, areas and count.....
    From what you posted earlier, I created this example
    Attached Files Attached Files

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The design looks pretty good to me... Just a few issues..


    In table "tbl_Effort", "TIME" is a reserved word (and a built in function) and shouldn't be used as an object (field) name.
    In tables "tbl_Effort" and "tbl Bio_Data", "COUNT" is a reserved word and shouldn't be used as an object (field) name.


    Do the "Time" fields include the date??


    In "tbl_Interviews", I would change field name "Interview_No" to "Interview_Num".
    In "tbl_Interviews", the field "Start_Time" refers to what? Interview start time? Trip start time? Does the time include the date?


    What is the difference between tables "tbl_Port" and "luPort"? (other than a field name) Is the data the same?
    What is the difference between tables "tbl_Area" and "luArea"? Is the data the same?


    It looks like table "tbl_Interviews" has data (clam names) as field names.
    If you had a table of employees, would you have field names "Al, Bob, Chris, Don, Ed,....???
    What happens if you want to add "Razor"? (see post #5 re "Area1, Area 2, Area 3,....")
    What about a table for clams or sea life or... what all do you inspect/track??
    And you would need another table (like "tbl_Boidata") for the clam names.

    Click image for larger version. 

Name:	Design1.png 
Views:	23 
Size:	148.8 KB 
ID:	33299


    I prefer to have suffixes for the primary key and foreign key fields: "_PK" and "_FK". Makes it easier to see relationships (at least for me )

  11. #11
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30
    First, only the Date used is in tbl_Main (Creel_Date). Times for all other fields are in format 17:34.

    Quote Originally Posted by ssanfu View Post
    What is the difference between tables "tbl_Port" and "luPort"? (other than a field name) Is the data the same?
    What is the difference between tables "tbl_Area" and "luArea"? Is the data the same?
    I'm clearly not understanding how creating the two new tables (tbl_Port and tbl_Area) were supposed to work to help me get rid of these repeated columns.
    Are they supposed to act as lookups or store data?
    I need the values for Port and Area to be list or combobox in a form to minimize entry errors.

    My conventions: I use lu_ for look up and tbl_ for tables where data are stored.
    I adopted your conventions of _PK, _FK.

    Lastly, tbl_Interviews are counts of clams for each species of interest. A proportion of these clams are measured (length, width, etc.) and recorded in tbl_Biodata

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If I've offended you, I apologize.
    The example dB I created using the image in Post #1. There were only 3 tables, so I didn't know about the "lu" and other tables.

    Quote Originally Posted by chelonidae View Post
    I'm clearly not understanding how creating the two new tables (tbl_Port and tbl_Area) were supposed to work to help me get rid of these repeated columns.
    Are they supposed to act as lookups or store data?
    If the data contained in the tables are the same, you only need one pair of the tables.


    Quote Originally Posted by chelonidae View Post
    My conventions: I use lu_ for look up and tbl_ for tables where data are stored.
    I was able to figure that out. Not a bad idea... I might have to steal that prefix from you.


    Quote Originally Posted by chelonidae View Post
    Lastly, tbl_Interviews are counts of clams for each species of interest. A proportion of these clams are measured (length, width, etc.) and recorded in tbl_Biodata
    In Post #6 you said "we collect counts of clammers/crabbers at different locations to assess effort". Not sure what "effort" means/refers to, but do you ever measure/collect data on crabs?


    I created the tables and set relationships. What do you think of this design?
    Click image for larger version. 

Name:	Design2.png 
Views:	18 
Size:	118.0 KB 
ID:	33312

  13. #13
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30
    Quote Originally Posted by ssanfu View Post
    If I've offended you, I apologize.
    No offense taken! I was describing what I wasn't understanding. Thanks for hanging in there with me.

    Quote Originally Posted by ssanfu View Post
    The example dB I created using the image in Post #1. There were only 3 tables, so I didn't know about the "lu" and other tables.
    I've learned that full disclosure is best. I was trying to simplify to get at the heart of the problem, but it didn't work. Sorry!


    Quote Originally Posted by ssanfu View Post
    In Post #6 you said "we collect counts of clammers/crabbers at different locations to assess effort". Not sure what "effort" means/refers to, but do you ever measure/collect data on crabs?
    -Effort is the #people doing the activity (ie clamming or crabbing).
    -During interviews, we count the number of each species (ie their "catch"). Zeros are data. So each species has a value. These data are different than biodata.
    -Biodata in tbl_Interviews is really "Biodata?", a Yes/No field. It is something checked off in the field on a datasheet and is something users want.
    -Biodata, or biological data, are measurements like length and weight that we take on a subset of interviews. Different data so different table.


    Quote Originally Posted by ssanfu View Post
    I created the tables and set relationships. What do you think of this design?
    Other than moving SpeciesCount to tbl_Interview, and linking luSpecies to both tables, I think I'll try it.

    Lookups
    I didn't know you could/should link lookup tables using a primary key. Maybe I've been doing it wrong the entire time! My biggest concern (confusion?) is how to set up forms so that it is easy to put in the data like a spreadsheet. I'll post the pics of the old forms next so you can see what I mean.

  14. #14
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30

    Translating new DB design to user friendly forms.

    Here are the data entry forms from the previous not normalized DB.
    Click image for larger version. 

Name:	OldFormEffort.png 
Views:	17 
Size:	235.9 KB 
ID:	33322 Click image for larger version. 

Name:	OldFormInt.jpg 
Views:	19 
Size:	147.6 KB 
ID:	33323

    This is my most recent database design.
    Click image for larger version. 

Name:	Relationships_v3.jpg 
Views:	16 
Size:	118.3 KB 
ID:	33324

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Observations:

    Table Interviews:
    IMO, field "Species_FK" should NOT be in this table. Presumably, there could/would be more than 1 species in this interview.
    I can understand the count of clams (field "NumOfClams") which might be the total number of all clam species in this interview, so having one species listed in the interview record doesn't make sense to me.

    I would name the field "Area" to "Area_FK".

    You have a field named "#Clams" and a field named "BioData?"
    Better would be "NumClams" or "CountOfClams" or "ClamCounts" and "blnBioData" or "ynBioData".

    Some suggestions on naming objects (applied especially to fields):
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not use look up FIELDS, multi-value fields or calculated fields in tables.
    Do not begin object names with a number.


    Table tblBioData:
    What is the field "Batch?"
    I would have a count field in this table.
    Do you need an AGE field?


    Table tblEffort:
    Don't need field "Port_FK" in this table since you can get the port from table "tblMain".



    There are still issues with this example dB, but could demo a few things.
    The interview sub form still needs buttons to move first, last, prev and next.
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 3
    Last Post: 03-06-2017, 02:13 PM
  2. Replies: 5
    Last Post: 12-09-2014, 01:36 PM
  3. Replies: 4
    Last Post: 10-09-2013, 03:44 PM
  4. Replies: 3
    Last Post: 01-30-2012, 09:43 AM
  5. Update Tables from Combo Boxes in a Form
    By RedWolf3x in forum Access
    Replies: 3
    Last Post: 11-03-2011, 08:07 AM

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