Using UNION of 2 or more saved queries

[Susan Bricker]

For those of you who have been following my posts - they all pertain to
a Dog Competition Organization's Database.

There are three classes that the dogs can participate:
NOVICE, OPEN, and UTILITY.

I want to produce a report of the top 10 average scores for each class
for each year.
At first I had a single query and used the TOP predicate to get the top
10 thinking that it would get me the top 10 in each class in each year.
I don't have enough test data to actually test this (only three scores
per class in one year)...so I was sort of guessing.

However, after thinking about it I figured out that I would only get the
top 10 resultant records out of the entire resultant recordset.

So ... I created three queries to select TOP 10; one for each class.
Now I want to "merge" or union these three queries so that I can base
the report off this new TOP 10 Union query.

I've tried looking for help on UNIONs but the following just won't get
past the query builder:

[qryTOP10NoviceAvg] UNION [qryTOP10OpenAvg] UNION [qryTOP10Utility]

Each of these queries works individually.

HELP!!! Any ideas?

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Trevor Best]

Susan Bricker wrote:

I've tried looking for help on UNIONs but the following just won't get
past the query builder:

[qryTOP10NoviceAvg] UNION [qryTOP10OpenAvg] UNION [qryTOP10Utility]

Each of these queries works individually.

HELP!!! Any ideas?

Try
Select * from [qryTOP10NoviceAvg] UNION select * from [qryTOP10OpenAvg]
UNION select * from [qryTOP10Utility]

or

Table [qryTOP10NoviceAvg] UNION Table [qryTOP10OpenAvg] UNION table
[qryTOP10Utility]

For your testing, with only 3 records, test it using select top 2 or 1

--
[OO=00=OO]

[Susan Bricker]

Trevor,

Thanks so much for the quick reply. I tried your first suggestion and
it worked great.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Br]

Susan Bricker <sl*****@verizon.net> wrote:

For those of you who have been following my posts - they all pertain
to a Dog Competition Organization's Database.

There are three classes that the dogs can participate:
NOVICE, OPEN, and UTILITY.

I want to produce a report of the top 10 average scores for each class
for each year.
At first I had a single query and used the TOP predicate to get the
top 10 thinking that it would get me the top 10 in each class in each
year. I don't have enough test data to actually test this (only three
scores per class in one year)...so I was sort of guessing.

However, after thinking about it I figured out that I would only get
the top 10 resultant records out of the entire resultant recordset.

So ... I created three queries to select TOP 10; one for each class.
Now I want to "merge" or union these three queries so that I can base
the report off this new TOP 10 Union query.

I've tried looking for help on UNIONs but the following just won't get
past the query builder:

[qryTOP10NoviceAvg] UNION [qryTOP10OpenAvg] UNION [qryTOP10Utility]

Each of these queries works individually.

HELP!!! Any ideas?

Regards,
SueB

You shouldn't have to use seperate queries in the first place.

Something like this would select the top 0 results for each
category...... (it will return more than 10 records per category if
records have the same result).

SELECT A.CategoryID, A.Result
FROM tblResults A
WHERE (A.Result) In
(
SELECT TOP 10 Result
FROM tblResults B
WHERE A.CategoryID= B.CategoryID
)
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Trevor Best]

Br@dley wrote:

You shouldn't have to use seperate queries in the first place.

Something like this would select the top 0 results for each
category...... (it will return more than 10 records per category if
records have the same result).

SELECT A.CategoryID, A.Result
FROM tblResults A
WHERE (A.Result) In
(
SELECT TOP 10 Result
FROM tblResults B
WHERE A.CategoryID= B.CategoryID
)

Depends on complexity, it might be easier to manage the separate queries
using the query grid.

Can you imagine altering a union query where each of the 3 sections
fills the entire SQL edit box when Access tells you that you haven't got
the same number of output columns in each?

--
[OO=00=OO]

[Susan Bricker]

I didn't understand Bradley's suggestion. Maybe another explanation
would help. But I have another problem ... please read the entire post.
It's long so that I include as much information as possible to help you
help me.

In any event ... I got the UNION query to work with sorting working as
expected. However, I've lost precision in a calculated AVG field.

Here's the UNION query:
SELECT * FROM qryTOP10NoviceAvg UNION SELECT * FROM qryTOP10OpenAvg
UNION SELECT * FROM qryTOP10UtilityAvg
ORDER BY [Yr], [classID], [avgscore] DESC;

The field [avgscore] is calculated by each individual query based on
[score] in table [tblScores].

If you need to see the other queries let me know. I'll post them, but
they are long.

Problem example:

In qryTOP10NoviceAvg one of the [avgscore] values is: 192.9
In the UNION qryTOP10 the same value is: 192.8
In the Report based on qryTOP10 the value is: 192.0

HELP!!! I need the value to be displayed as 192.9 ... what did I do
wrong.

The field [score] in [tblScores] is defined as:
datatype = Number
fieldsize = Decimal
format = Fixed
precision = 4
scale = 1
decimal places = 1

The [avgscore] in qryTOPT0Novice is defined as:
format = Fixed
decimal places = 1

The report has the display field defined as:
format = Fixed
decimal places = 1
controlsource = avgscore
Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Susan Bricker]

I didn't understand Bradley's suggestion. Maybe another explanation
would help. But I have another problem ... please read the entire post.
It's long so that I include as much information as possible to help you
help me.

In any event ... I got the UNION query to work with sorting working as
expected. However, I've lost precision in a calculated AVG field.

Here's the UNION query:
SELECT * FROM qryTOP10NoviceAvg UNION SELECT * FROM qryTOP10OpenAvg
UNION SELECT * FROM qryTOP10UtilityAvg
ORDER BY [Yr], [classID], [avgscore] DESC;

The field [avgscore] is calculated by each individual query based on
[score] in table [tblScores].

If you need to see the other queries let me know. I'll post them, but
they are long.

Problem example:

In qryTOP10NoviceAvg one of the [avgscore] values is: 192.9
In the UNION qryTOP10 the same value is: 192.8
In the Report based on qryTOP10 the value is: 192.0

HELP!!! I need the value to be displayed as 192.9 ... what did I do
wrong.

The field [score] in [tblScores] is defined as:
datatype = Number
fieldsize = Decimal
format = Fixed
precision = 4
scale = 1
decimal places = 1

The [avgscore] in qryTOPT0Novice is defined as:
format = Fixed
decimal places = 1

The report has the display field defined as:
format = Fixed
decimal places = 1
controlsource = avgscore
Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Susan Bricker]

I got it! By using UNION ALL... I got the correct data in the report.
Funny thing ... without ALL, when I ran the UNION query by clicking on
the run icon (!) in the Query Builder, the UNION produced the correct
info in avgscore field. However, when the query was used as
RecordSource for the report, it only produced integer data for the
avgscore (no decimal places). I put Debug.Print in the report VB
Detail_Format routine. Then I put in a called routine in the
Report_Open routine to run the qryTOP10 UNION query. I then looped
through the recordset spewing out the avgscore values in Debug.Print
again. Again, the data was INTEGER (no decimal places). That's when I
went back the query and put in ALL.

I don't really understand (if anyone can explain this that would be
great) ... but it works and I'm happy. Thanks for listening.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Susan Bricker]

I got it! By using UNION ALL... I got the correct data in the report.
Funny thing ... without ALL, when I ran the UNION query by clicking on
the run icon (!) in the Query Builder, the UNION produced the correct
info in avgscore field. However, when the query was used as
RecordSource for the report, it only produced integer data for the
avgscore (no decimal places). I put Debug.Print in the report VB
Detail_Format routine. Then I put in a called routine in the
Report_Open routine to run the qryTOP10 UNION query. I then looped
through the recordset spewing out the avgscore values in Debug.Print
again. Again, the data was INTEGER (no decimal places). That's when I
went back the query and put in ALL.

I don't really understand (if anyone can explain this that would be
great) ... but it works and I'm happy. Thanks for listening.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Trevor Best]

Susan Bricker wrote:

I got it!
Glad I skipped down to read this post before trying to figure out the
previous one <g>
By using UNION ALL... I got the correct data in the report.
Funny thing ... without ALL, when I ran the UNION query by clicking on
the run icon (!) in the Query Builder, the UNION produced the correct
info in avgscore field. However, when the query was used as
RecordSource for the report, it only produced integer data for the
avgscore (no decimal places). I put Debug.Print in the report VB
Detail_Format routine. Then I put in a called routine in the
Report_Open routine to run the qryTOP10 UNION query. I then looped
through the recordset spewing out the avgscore values in Debug.Print
again. Again, the data was INTEGER (no decimal places). That's when I
went back the query and put in ALL.

I don't really understand (if anyone can explain this that would be
great) ... but it works and I'm happy. Thanks for listening.

That's a new one on me. I know union would do some grouping to eliminate
duplicate records, I was going to mention "union all" but I figured your
3 queries would show the class and would therefore not duplicate
anything with eachother.

As to why it would truncate non-integer data I don't know. Unless
someone can explain it's probably a bug.

--
[OO=00=OO]

[Trevor Best]

Susan Bricker wrote:

I got it!
Glad I skipped down to read this post before trying to figure out the
previous one <g>
By using UNION ALL... I got the correct data in the report.
Funny thing ... without ALL, when I ran the UNION query by clicking on
the run icon (!) in the Query Builder, the UNION produced the correct
info in avgscore field. However, when the query was used as
RecordSource for the report, it only produced integer data for the
avgscore (no decimal places). I put Debug.Print in the report VB
Detail_Format routine. Then I put in a called routine in the
Report_Open routine to run the qryTOP10 UNION query. I then looped
through the recordset spewing out the avgscore values in Debug.Print
again. Again, the data was INTEGER (no decimal places). That's when I
went back the query and put in ALL.

I don't really understand (if anyone can explain this that would be
great) ... but it works and I'm happy. Thanks for listening.

That's a new one on me. I know union would do some grouping to eliminate
duplicate records, I was going to mention "union all" but I figured your
3 queries would show the class and would therefore not duplicate
anything with eachother.

As to why it would truncate non-integer data I don't know. Unless
someone can explain it's probably a bug.

--
[OO=00=OO]

[Br]

Susan Bricker <sl*****@verizon.net> wrote:

I didn't understand Bradley's suggestion.
Instead of creating three seperate queries and then adding the resulting
recordsets together you can use an advanced SQL statement that will do
it in one query.
SELECT A.CategoryID, A.Result
FROM tblResults A
WHERE (A.Result) In
(
SELECT TOP 10 Result
FROM tblResults B
WHERE A.CategoryID= B.CategoryID
)

The query will return the top 10 results for each category.

<>
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Br]

Susan Bricker <sl*****@verizon.net> wrote:

I didn't understand Bradley's suggestion.
Instead of creating three seperate queries and then adding the resulting
recordsets together you can use an advanced SQL statement that will do
it in one query.
SELECT A.CategoryID, A.Result
FROM tblResults A
WHERE (A.Result) In
(
SELECT TOP 10 Result
FROM tblResults B
WHERE A.CategoryID= B.CategoryID
)

The query will return the top 10 results for each category.

<>
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response