UNION these?

[Guest]

I have been struggling with a way to combine these queries and wondered if
there was a way to Union them somehow. As you can see, one of them is a
UNION query.

Query1 (crosstab):
TRANSFORM Sum(qryEOYShowPointsStep3UNION.Points) AS SumOfPoints
SELECT qryEOYShowPointsStep3UNION.HorseName,
qryEOYShowPointsStep3UNION.NWHATRNumber, qryEOYShowPointsStep3UNION.Title,
qryEOYShowPointsStep3UNION.Titles, qryEOYShowPointsStep3UNION.Owner,
qryEOYShowPointsStep3UNION.NWHAHP, Sum(qryEOYShowPointsStep3UNION.Points) AS
[Total Of Points]
FROM qryEOYShowPointsStep3UNION
WHERE (((qryEOYShowPointsStep3UNION.NWHAHP)<>"NA"))
GROUP BY qryEOYShowPointsStep3UNION.HorseName,
qryEOYShowPointsStep3UNION.NWHATRNumber, qryEOYShowPointsStep3UNION.Title,
qryEOYShowPointsStep3UNION.Titles, qryEOYShowPointsStep3UNION.Owner,
qryEOYShowPointsStep3UNION.NWHAHP
PIVOT qryEOYShowPointsStep3UNION.Entries;

Query2 (select):
SELECT qryHorseWORKEDtest.FullName, qryHorseWORKEDtest.HorseName,
Count(qryHorseWORKEDtest.AddField) AS CountOfAddField
FROM qryHorseWORKEDtest
GROUP BY qryHorseWORKEDtest.FullName, qryHorseWORKEDtest.HorseName
HAVING (((Count(qryHorseWORKEDtest.AddField))>=3));

I need the information provided by the 1st query as well as I need to know
how many shows the horse went to that was at least 3 or more. I'm in the
testing phase right now and will clean up query names once it is working.

Any assistance would be appreciated.

 

[Jeff Boyce]

For a UNION query to work (properly), you need to have the same number of
fields in each of the UNION phrases. If you wish to combine data from one
query with another, but they have differing fields (or different numbers of
fields), do they have a common field you can use to join them?

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

Thanks Jeff.
I had tried that earlier today. But, after reading your reply, I went back
and tried combining them on a common field again. I think my problem was
trying to use the common field in Query 1 instead of the common field in
Query 2. When I used the common field in Query 2 and included the fields I
needed, IT WORKED!!!
Thanks for your help.
DN

For a UNION query to work (properly), you need to have the same number of
fields in each of the UNION phrases. If you wish to combine data from one
query with another, but they have differing fields (or different numbers of
fields), do they have a common field you can use to join them?

Regards

Jeff Boyce
<Office/Access MVP>

I have been struggling with a way to combine these queries and wondered if
there was a way to Union them somehow. As you can see, one of them is a
UNION query.

Query1 (crosstab):
TRANSFORM Sum(qryEOYShowPointsStep3UNION.Points) AS SumOfPoints
SELECT qryEOYShowPointsStep3UNION.HorseName,
qryEOYShowPointsStep3UNION.NWHATRNumber, qryEOYShowPointsStep3UNION.Title,
qryEOYShowPointsStep3UNION.Titles, qryEOYShowPointsStep3UNION.Owner,
qryEOYShowPointsStep3UNION.NWHAHP, Sum(qryEOYShowPointsStep3UNION.Points)
AS
[Total Of Points]
FROM qryEOYShowPointsStep3UNION
WHERE (((qryEOYShowPointsStep3UNION.NWHAHP)<>"NA"))
GROUP BY qryEOYShowPointsStep3UNION.HorseName,
qryEOYShowPointsStep3UNION.NWHATRNumber, qryEOYShowPointsStep3UNION.Title,
qryEOYShowPointsStep3UNION.Titles, qryEOYShowPointsStep3UNION.Owner,
qryEOYShowPointsStep3UNION.NWHAHP
PIVOT qryEOYShowPointsStep3UNION.Entries;

Query2 (select):
SELECT qryHorseWORKEDtest.FullName, qryHorseWORKEDtest.HorseName,
Count(qryHorseWORKEDtest.AddField) AS CountOfAddField
FROM qryHorseWORKEDtest
GROUP BY qryHorseWORKEDtest.FullName, qryHorseWORKEDtest.HorseName
HAVING (((Count(qryHorseWORKEDtest.AddField))>=3));

I need the information provided by the 1st query as well as I need to know
how many shows the horse went to that was at least 3 or more. I'm in the
testing phase right now and will clean up query names once it is working.

Any assistance would be appreciated.