Forum Discussion
Access - how to joining two queries with different data without doubling data
I'm not sure how to phrase my issue, which makes finding a solution also difficult
I'm trying to combine two different queries so I can run a report that shows all of the crops FarmerID is growing as well as the number of livestock . One query is labeled qryCrops and the other qryLivestock. Both queries have the same FarmerID (the unique key). The problem is I have a variety of scenarios: some farmers have multiple crops and only one type of livestock, other farmers have one crop and multiple types of livestock, some have multiples of both, etc. Whenever I try connecting the data, Access automatically doubles the information (see example below).
I've tried joining data which hasn't worked. A query with a left join won't duplicate the crops, but will duplicate livestock. A query with a right join won't duplicate the livestock, but will duplicate the crops. I've tried running a left and right join query separately, and then combining both queries into a Union query, and my data still is getting doubled (I suspect that's not the right option as I'm not comparing apples to apples). Nothing seems to work! I've tried searching the internet and have been unsuccessful at finding a solution.
Here is what I want; I want access to leave fields blank if there is no data:
FarmerID | Acres | CropType | LivestockNum | LivestockType |
Farmer A | 6 | Pasture | 3 | Cows |
Farmer A | 10 | Hay |
|
|
Farmer B | 15 | Pasture | 2 | horses |
Farmer B |
|
| 100 | chickens
|
This is what I get when I combine the data:
FarmerID | Acres | CropType | LivestockNum | LivestockType |
Farmer A | 6 | Pasture | 3 | Cows |
Farmer A | 10 | Hay | 3 | Cows |
Farmer B | 15 | Pasture | 2 | horses |
Farmer B | 15 | Pasture | 100 | chickens |
Any thoughts?
Our reports are currently set up to show the farmers and all of their crops, then the next section has the farmers and all of their livestock. I want a report that's grouped by the farmer so it shows all of their information in one place (without doubling data), then moves on to the next farmer. I'm hoping the query solution mentioned above will solve that.
Thanks in advance!
- XPS35Iron Contributor
nicoleethier You posted the same question on an other forum and got multiple replies. See HERE. You responded to non of them. That in itself is not nice, but to then ask the question again on another forum is very rude.
- nicoleethierCopper ContributorI couldn't figure out how to find my question on the other forum. I thought I'd get e-mails if anyone replied, but I didn't get any. I had no intentions of being rude or being a pain. Thank you for the link, but please don't be too quick to make assumptions.
- XPS35Iron Contributor
nicoleethier There are problems sending notifications on that forum:
Please note, the notification system in the Community is partially broken
You might not receive an email informing you that there is a new reply in a thread you are following. We are working on fixing this as fast as possible. Please make sure to periodically check the conversation to see if there are any updates.
- nicoleethierCopper Contributorand now I can't figure out how to delete the post to get rid of the clutter. ARG.
- arnel_gpSteel Contributor
nicoleethier , you need to post some screen shots of the result of two queries.