Hi Peter,
I'm not sure about your question. Are you looking to add additional fields
in the query result so that you have something like:
SELECT ......,Field1, Field2, Field3
UNION ALL
SELECT ......, Field1, Field2, Field3
UNION ALL
SELECT ......, Field1, Field2, Field3
Or are the fields unique in each select statement, like
Select........., xfield, yfield
UNION ALL
Select ......., afield, bfield, cfield
UNION ALL
Select ......, hfield
If it's the latter then you must note that it won't work because each
Select Statement should have the same number of columns returned. As a
workaround you could something like:
Select........., xfield, yfield, "Extra 3rd column"
UNION ALL
Select ......., afield, bfield, cfield
UNION ALL
Select ......, hfield, "Extra 2nd column", "Extra 3rd
column"
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) (Peter Knight)
| Newsgroups: microsoft.public.access.queries
| Subject: Re: Querying Multple Tables in Access with different fields
| Date: 15 Mar 2004 09:19:18 -0800
| Organization:
http://groups.google.com
| Lines: 111
| Message-ID: <
[email protected]>
| References: <
[email protected]>
<
[email protected]>
| NNTP-Posting-Host: 132.185.144.122
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1079371158 26175 127.0.0.1 (15 Mar 2004
17:19:18 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Mon, 15 Mar 2004 17:19:18 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!tiscali!newsfeed1.ip.tiscali.net!216.196.98.140.MISMATCH!border1.nntp.a
sh.giganews.com!nntp.giganews.com!news.glorb.com!postnews2.google.com!not-fo
r-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:193772
| X-Tomcat-NG: microsoft.public.access.queries
|
| Thank you very much for your help with this, I ran the query the other
| day and feel that it is definately running in the right direction,
| however I am still a little confused about UNION ALL. Now that I have
| these query results in a table which give me the genre ID and the
| table (e.g 3 ROT) they are from, how do I add the rest of the record
| information from the tables so that it could be displayed in a report
| or form?
|
| Thanks again for any help
|
| Regards
|
| Peter
|
| (e-mail address removed) (Eric Butts [MSFT]) wrote in message
| > Hi Peter,
| >
| > Have you consider using a Union Query?
| >
| > Note: UNION ignores duplicates while UNION ALL includes duplicates.
| >
| > Example:
| > =====================
| > SELECT Genre, "Clip" As TableName
| > FROM Clip
| > WHERE [Clip].[Genre]=[Forms]![Genre/Programme Names/Presenter]![Genre]
| >
| > UNION ALL
| >
| > SELECT Genre, "RoT" As TableName
| > FROM Clip
| > WHERE [RoT].[Genre]=[Forms]![Genre/Programme Names/Presenter]![Genre]
| >
| > UNION ALL
| >
| > SELECT Genre, "Live OB" As TableName
| > FROM [Live OB]
| > WHERE [Live OB].[Genre]=[Forms]![Genre/Programme
Names/Presenter]![Genre];
| >
| >
| > 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) (Peter Knight)
| > | Newsgroups: microsoft.public.access.queries
| > | Subject: Querying Multple Tables in Access with different fields
| > | Date: 9 Mar 2004 14:48:34 -0800
| > | Organization:
http://groups.google.com
| > | Lines: 26
| > | Message-ID: <
[email protected]>
| > | NNTP-Posting-Host: 81.178.216.61
| > | Content-Type: text/plain; charset=ISO-8859-1
| > | Content-Transfer-Encoding: 8bit
| > | X-Trace: posting.google.com 1078872515 8511 127.0.0.1 (9 Mar 2004
| > 22:48:35 GMT)
| > | X-Complaints-To: (e-mail address removed)
| > | NNTP-Posting-Date: Tue, 9 Mar 2004 22:48:35 +0000 (UTC)
| > | Path:
| >
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
| >
e.de!130.59.10.21.MISMATCH!irazu.switch.ch!switch.ch!news.tele.dk!news.tele.
| > dk!small.news.tele.dk!news.glorb.com!postnews1.google.com!not-for-mail
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:193039
| > | X-Tomcat-NG: microsoft.public.access.queries
| > |
| > | Hello,
| > |
| > | I have a Access 2000 database which contains three main tables. These
| > | main tables contain a number of similiar fields to each other, many of
| > | which are relationship lookup tables. I need to write a query which
| > | takes information from a combobox and then is activated from a button
| > | and searches all three of these tables. I have tried various options,
| > | but they don't give me any results. I have pasted the SQL of the query
| > | as it is at the moment as a starting point.
| > |
| > |
| > | SELECT Clip.Genre, RoT.Genre, [Live OB].Genre, *
| > | FROM Clip, RoT, [Live OB]
| > | WHERE ((([Clip].[Genre])=[Forms]![Genre/Programme
| > | Names/Presenter]![Genre])) OR ((([Live
| > | OB].[Genre])=[Forms]![Genre/Programme Names/Presenter]![Genre])) OR
| > | ((([RoT].[Genre])=[Forms]![Genre/Programme Names/Presenter]![Genre]));
| > |
| > | This query at the moment gives me about 6000 results (and there are
| > | not 6000 records to start with)
| > |
| > | Any help would be great.
| > |
| > | Thanks
| > |
| > | Peter
| > |
|
