Forum Discussion

nicoleethier's avatar
nicoleethier
Copper Contributor
Oct 31, 2024

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!

  • XPS35's avatar
    XPS35
    Iron 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.

    • nicoleethier's avatar
      nicoleethier
      Copper Contributor
      and now I can't figure out how to delete the post to get rid of the clutter. ARG.
    • nicoleethier's avatar
      nicoleethier
      Copper Contributor
      I 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.
      • XPS35's avatar
        XPS35
        Iron 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. 

Resources