union query

  • Thread starter Thread starter Jeff Boeker
  • Start date Start date
J

Jeff Boeker

I have two queries I would like to combine into one query and I'm not
able to find the proper syntax (see below). Additionally I would like
to get the total number of records retrieved in each query and be able
to limit the results of the second query to a set number of records
(say 10). Anybody give me some pointers?

Another question is how efficient union queries with different files
are relative to doing the query on each database separately (assume
can just append each recordset with no sorting).

Thanks,

Jeff
__________
qryEvents:
Select *
From tblEventHistory In 'f:\Database\Events\Events_20040705.mdb'
UNION ALL Select *
From tblEventHistory In 'f:\Database\Events\Events_20040706.mdb'
ORDER BY 3;

qryAlarms:
SELECT qryEvents.Parameters, Count(*) AS EventCount
FROM qryEvents
WHERE (((qryEvents.SeverityTypeID)=8))
GROUP BY qryEvents.Parameters
ORDER BY 2 DESC , 1;
 
Hi,

Have your tried something like:

qryEvents:
==========
Select "1stQuery" As Descr, * From tblEventHistory In
'f:\Database\Events\Events_20040705.mdb'

UNION ALL

Select TOP 10 "2ndQuery" As Descr, * From tblEventHistory In
'f:\Database\Events\Events_20040706.mdb'

ORDER BY 3;


qryAlarms:
============
SELECT Descr, Count(*) AS EventCount
FROM qryEvents
GROUP BY Descr;


In answer to your question what's more efficient? Using Linked Tables
would be more efficient than this approach.


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| From: (e-mail address removed) (Jeff Boeker)
| Newsgroups: microsoft.public.access.queries
| Subject: union query
| Date: 18 Jul 2004 22:42:14 -0700
| Organization: http://groups.google.com
| Lines: 27
| Message-ID: <[email protected]>
| NNTP-Posting-Host: 64.171.190.200
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1090215751 4215 127.0.0.1 (19 Jul 2004
05:42:31 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Mon, 19 Jul 2004 05:42:31 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwi
x.com!newsfeed.cwix.com!border1.nntp.dca.giganews.com!nntp.giganews.com!news
.glorb.com!postnews2.google.com!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:207258
| X-Tomcat-NG: microsoft.public.access.queries
|
| I have two queries I would like to combine into one query and I'm not
| able to find the proper syntax (see below). Additionally I would like
| to get the total number of records retrieved in each query and be able
| to limit the results of the second query to a set number of records
| (say 10). Anybody give me some pointers?
|
| Another question is how efficient union queries with different files
| are relative to doing the query on each database separately (assume
| can just append each recordset with no sorting).
|
| Thanks,
|
| Jeff
| __________
| qryEvents:
| Select *
| From tblEventHistory In 'f:\Database\Events\Events_20040705.mdb'
| UNION ALL Select *
| From tblEventHistory In 'f:\Database\Events\Events_20040706.mdb'
| ORDER BY 3;
|
| qryAlarms:
| SELECT qryEvents.Parameters, Count(*) AS EventCount
| FROM qryEvents
| WHERE (((qryEvents.SeverityTypeID)=8))
| GROUP BY qryEvents.Parameters
| ORDER BY 2 DESC , 1;
|
 
Jeff,

The key to using a union query is ensuring that the field sequence is
exactly the same between the two parts of the UNION. That is why I prefer
to explicitly state the field names to be pulled from each SELECT statement,
rather than using the *. Sometimes, the field sequence gets changed, even
though the fields are identical, if they are not in the same sequence, the
query either will not work, or will produce results that are different than
expected.

HTH
Dale
 
Back
Top