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:
[qryTOP10NoviceA vg] UNION [qryTOP10OpenAvg] UNION [qryTOP10Utility]
Each of these queries works individually.
HELP!!! Any ideas?
Regards,
SueB
*** Sent via Developersdex http://www.developersdex.com *** 12 2899
Susan Bricker wrote: I've tried looking for help on UNIONs but the following just won't get past the query builder:
[qryTOP10NoviceA vg] UNION [qryTOP10OpenAvg] UNION [qryTOP10Utility]
Each of these queries works individually.
HELP!!! Any ideas?
Try
Select * from [qryTOP10NoviceA vg] UNION select * from [qryTOP10OpenAvg]
UNION select * from [qryTOP10Utility]
or
Table [qryTOP10NoviceA vg] UNION Table [qryTOP10OpenAvg] UNION table
[qryTOP10Utility]
For your testing, with only 3 records, test it using select top 2 or 1
--
[OO=00=OO]
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 ***
Susan Bricker <sl*****@verizo n.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:
[qryTOP10NoviceA vg] 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
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]
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 qryTOP10NoviceA vg UNION SELECT * FROM qryTOP10OpenAvg
UNION SELECT * FROM qryTOP10Utility Avg
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 qryTOP10NoviceA vg 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 ***
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 qryTOP10NoviceA vg UNION SELECT * FROM qryTOP10OpenAvg
UNION SELECT * FROM qryTOP10Utility Avg
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 qryTOP10NoviceA vg 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 ***
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 ***
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 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] This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: KENNY L. CHEN |
last post by:
Dear experts,
I have two tables in my Oracle 8i database: TEST (COL1,COl2,REC_NO)
and TEST1 (COL1,COL2,REC_NO).
Both tables are unique-indexed on (COL1,COL2,REC_NO).
I think the following SQL commands will return the same result but one
of my friends don't think so. He said "QUERY 1" will return 1 unsorted
record (ROWNUM < 2 ) first then sort the result (ORDER BY COL1 ASC,
|
by: d2r2 |
last post by:
Hi,
I'm trying to run a nested (UNION) query against a MSAccessXP database
(version 2002; build 10.6501.6714; SP3)
In Access the SQL-statement executes just fine. When I run it in a asp-page
I get the following error:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.
Can a nested union-query be used at all or should I use an intermediate
|
by: lyn.duong |
last post by:
Hi,
I have a large table (about 50G) which stores data for over 7 years. I
decided to split this table up into a yearly basis and in order to
allow minimum changes to the applications which access this table, I
created a union all view over the 7 yearly tables.
What I have noticed is that queries against the union all view is
considerably slower than queries against the original table. When I ran
db2batch, I noticed cpu usage was higher...
|
by: s_gregory |
last post by:
The mdb is considerable size 70 +- mb. A complex union query was
working well, but when an additional union select... was added into
the query, selecting identical fields from a different source, it
began to have problems.
Failure was observed only in a few PC's at first. For example, in an
NT 4.0 SP6 PC, it continued to work OK. But in my Win 2k laptop, it
failed. As the union query was gradually simiplified in testing, the
failure...
|
by: Arvin Portlock |
last post by:
I can't come up with a query that works. Can anyone help?
Conceptually the relationships are easy to describe. I have
a table for books (Entries), a table for authors (Authors),
and a linking table between the two because books often
have more than one author (OA_Link). This situation is
simple and common. A query to list each title and all
the authors associated with that title looks like this:
SELECT Entries.TitleStatement,...
| |
by: shaun palmer |
last post by:
when or Where do you use a union query ?
how can I wright sql,
tblPopulation,* tblcustomer,* one to one with
all the appropriate attributes(field).
Your help would be greatly
appreciated. Thank you.
|
by: marco |
last post by:
Dear List,
as it seems, MS SQL as used in Access does not allow a select INTO
within a UNION query. Also, it seems that a UNION query can not be used
as a subquery.
Maybe my (simplified) problem can avoid these technicalities: the
original table has columns
A1, A2, B1, B2, C1, C2.
|
by: Robert |
last post by:
How do you get an accurate count of the number of records returned from
a query when using linked tables.
I have an access 2003 database as a front end to another access 2003
database that contains only tables. I have linked the tables for the
front end to the back end database.
I am trying to set the recordsource of a form to a query established by
the user to narrow the scope but I don't want to display the form if
there are no...
|
by: bgreenspan |
last post by:
Hi Everyone, I'm back for some more expert help. Here's what I am
doing and what I tried.
My database has entries with Contract Names and Expiry Dates, among
other fields. I have a form designed to show the expiring contracts.
To do this I use a straight forward query in my form's ON LOAD code
strwhere = " BETWEEN #" & Now() & "# AND #" &
DateAdd("m", 6, Now()) & "#"
Set MyQueryDef = MyDatabase.CreateQueryDef("qryMattersQuery",...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |