combining queries with different data

Question

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 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.

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 an example of what I want:

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

If there is data missing, I want Access to leave it blank instead of filling in the blanks itself, if that makes sense.

What do I do? Any help or advise is greatly appreciated!

Answer 1

As far as I can see, all you need to do is set the LinkMasterFields and LinkChildFields properties of the two subreports to PlanID.  The parent report’s RecordSource must return the PlanID of course.  The parent report should be grouped on PlanID, with the data per plan returned in a group header.  The two subreports, and only the two subreports, should be in the Detail section.  For each plan the livestock and crop data will then be returned together below the plan data.

If you don’t want the report to return the data in PlanID order, you should add a sort level on the column (field) in which you want the data to be returned above the PlanID group level.

Answer 2

Can you also show us how you would like the report laid out.

Off the top of my head I would envision a report grouped by Farmer with 2 subreports for crops and livestock within the grouping. Would that satisfy your needs?

Answer 3

RE:One query is labeled qryCrops and the other qryLivestock.

Please provide datas of your source table.

Answer 4

A UNION of the two queries, rather than a JOIN, should not produce any inappropriate duplication unless data is already duplicated in the individual queries' result tables:

SELECT FarmerID, Acres, CropType, LivestockNum, LivesyockType

FROM qryCrops

UNION ALL

SELECT FarmerID, Acres, CropType, LivestockNum, LivestockType

FROM qryLivestock

ORDER BY FarmerID;  

However, we really need to see what rows are in the base tables to produce the incorrect result table which you posted. You may need to amend the original queries to restrict their result tables more closely. Normally, in situations like this, the two queries would be projected over different columns, and NULL would be returned at the inapplicable column positions in each part of the UNION operation, but the first and third rows in your desired result table rules that out, having no NULL column positions.

Answer 5

Let's start by showing us the tables involved so we know exactly what we are going to have to work with.

Thanks.