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:
- Farmers (FarmerID, last name, first name, city, region, license#)
- Region (RegionID, region name) - I need a separate table for this in case region borders change
- 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)
- 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