Results 1 to 9 of 9
  1. #1
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15

    New to Access - will I need a subquery?


    Hello,
    I wanted to learn Access and volunteered to create a database to catalog all of the different tasks our employees can do and will do, based on their own requirements (sorry, I have to be vague). I'm having a hard time creating a relationship between 2 tables, and my coworker who is more advanced with Access is also stumped. My end goal is to be able to bring up a list of which employee(s) does the specific task.

    Forgive me in advance for this weird example: let's say I have 40 "employees" who are all farmers, and 7 types of livestock, and each farmer might have only 1 livestock on their farm, or up to all 7. Now let's say the farmers that have cows - some of them milk the cows, while others only raise them for meat, and some of them do both. Within the farmers who milk their cows, there are a couple farmers who only milk cows with blue eyes because they are very particular about their process. I want to be able to pull up a list of farmers who "milk based on blue eyes." I don't want it to say "milk cows based on blue eyes" because that specific task could also apply to a goat farmer who only milks goats with blue eyes (my examples are getting weirder, sorry).

    The following tables that I have created:
    1. Farmers (FarmerID, last name, first name, city, region, license#)
    2. Region (RegionID, region name) - I need a separate table for this in case region borders change
    3. Primary livestock (PrimaryLivestockID, Primary livestock name [e.g. Cows, Goats, Pigs], livestock yes/no - in case I want to have other types of farmers that don't have livestock)
    4. Secondary livestock (SecondaryLivestockID, secondary livestock name [e.g. Cows - milk, Cows - milk blue eyes only, Cows - milk brown eyes only], required farming tools, primary livestock)


    I can create a relationship between Farmers and Region, and another relationship between Primary and Secondary Livestock tables, but I can't figure out the relationship between the farmers and the secondary livestock. Because again, each farmer might have more than just one type of livestock...

    In my real life scenario, I have 100+ "employees" and even though 50 of them might all have the same "livestock" like cows, I need to be able to pull up which 5 of them milk blue-eyed cows only! LOL

    I haven't tried creating any queries or reports yet because I want to figure out this link. Wondering if it will come down to a subquery? Any suggestions for building the missing relationship?

    If you made it through my whole blurb, thank you
    Emily

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    see the example db I have attached in post #21 of this thread https://www.accessforums.net/showthr...508#post448508. The orientation example is the one you need for secondary livestock, the title one for your primary livestock and region (I presume by definition a farmer can't have two primary livestocks)

    and another relationship between Primary and Secondary Livestock tables
    the example I provided works, but what you need is a what is called a cascading combo in your form - the first combo (primary livestock) will select 'cows', the second combo is based on secondary livestock which is filtered for cows by reference to the first combo.

    The alternative is for the user just to select secondary livestock from the complete list - your link in the secondary livestock table will then tell you which is the primary livestock (i.e. think from the other end)

    some tips to make your life easier

    1. don't have spaces and special characters (like #) in table and field names
    2. with fields used in relationships, use the same name for both fields and I recommend use PK/FK suffix so you know which end of a relationship a field is (see example)

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    One table per entity, so no to table for livestock and a table for blue eyed livestock. Nor should you have a field in livestock for every colour of eyes because colour is an attribute and you don't create fields for a single attribute (eye colour).
    You can normalize until it hurts, and without seeing my thoughts on paper (which is where a good design begins) I could be off a bit here. I'm going to say you can approach this 2 ways, neither of which involves a subquery (and shouldn't be the answer for this)

    tlbLivestock
    Species; EyeColour
    Cow; brown
    Cow; blue
    Cow; green
    Horse; brown
    Horse; blue
    Horse; green
    However, this is not the best approach, especially if you start mixing in other attributes besides eye colour. Do that and your records end up resembling a Cartesian product.

    Better to have a table for an entity and a table for an attribute, then you have a table to relate combinations thereof. This is known as a junction table. Thus if there are no cows with purple eyes, there's no record in the junction table, but you can have purple as an eye colour in the attribute table. Thus when new animal or eye colour values are added, you simply add records to these tables - not fields. As soon as you start adding fields or tables for basically the same thing, you are traveling down the wrong road.

    Thus you might not restrict the attribute to eye colour; maybe it's just values of green, brown, black, blue and the junction is for eye colour. Thus you might have a junction table for fur colour if that makes sense.

    Any of what I've written here is subject to the requirements of the real business you are into as I somehow doubt it's really about animal attributes as you've described. Therefore, you might be well served by reading up on normalization, especially where junction tables are dealt with. The concepts are more important than the examples I've given. Here's my whole list of bedtime reading for you. Make sure you understand normalization, then get some big sheets of paper, a pencil and a big fat eraser!

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    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
    About Multi Value Fields -http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

    EDIT - late to the party again, and maybe all for naught.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15
    Thank you for the quick reply Ajax. I will research cascading combos, I've just completed a basic online beginner's course, thinking my database would be simple, and then I hit a few roadblocks.

    By definition, a farmer could have more than one primary livestock, i.e. cows, goats and pigs all on the same farm. Does that mean the cascading combos would not work?

    I'll take a look at both methods you're suggesting, thanks again!

  5. #5
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15
    Hi Micron,
    Thank you too for your quick reply, you and Ajax are on the ball! I'm trying to avoid making a table with all the attributes that you referred to as eventually looking like a cartesian product, because some might be mixed. I'm anticipating having more individuals added to the database over time, and they may have new requirements that aren't already listed. I've already made a list in Word and it comes out to 10 pages of attributes :-S My team currently uses an Excel spreadsheet which is a pain because when you're looking for a specific attribute, it's difficult to find which individuals actually do it (we're talking 20+ worksheets in the Excel file as each worksheet is for a specific region).

    Thank you for all the links, I will read up on normalization and junctions. I am bound and determined to figure this out!

  6. #6
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    If you attach the excel file it is perhaps easier to understand what to do

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    By definition, a farmer could have more than one primary livestock, i.e. cows, goats and pigs all on the same farm. Does that mean the cascading combos would not work?
    they will, but you need a linking table between farmers and primary livestock

    you do need to design your tables and relationships carefully, considering all possible options and what you actually want to do with the data once you have it. The problem with example data is you can make it too simple. For example if your secondary livestock includes breeds - breeds are unique to a particular species. Include number of legs and that will apply to many species.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OP has started a new thread with the actual requirements and should close this one. On my phone so too difficult to post a link.

    EDIT - https://www.accessforums.net/showthread.php?t=79721
    there she be...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    thanks - had noticed, but nothing to add on the other thread at the moment

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

Similar Threads

  1. Replies: 4
    Last Post: 11-21-2015, 04:39 PM
  2. Subquery Access 2010
    By Computers in forum Queries
    Replies: 7
    Last Post: 04-30-2014, 07:27 PM
  3. Help on Access Subquery
    By cshannon in forum Queries
    Replies: 1
    Last Post: 02-01-2014, 05:40 PM
  4. Access WITH subquery
    By johnseito in forum Access
    Replies: 5
    Last Post: 10-15-2013, 08:41 PM
  5. Access SQL Subquery Problem
    By dfenton21 in forum Access
    Replies: 2
    Last Post: 07-26-2011, 07:54 AM

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