UNION Query

J

James C

Hello How can I do a UNION Query with teh following SQL
Code and the Follwoing additional fields from diffrent
Tables. I need this as I am creating a search form.

The current SQL I have is:

SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support From] FROM [SQT Live CISCO
Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
NULL,Base,Username,Password,[Type of Dialin],[Support
From] FROM[live CISCO Accounts];

Here are the two tables and there fields:

Table1 Name: External SIMS/USER/E-Mail Passwords

Username
passwords
base
current user
sims id
sims password
ems online id
ems online password

All are text fields.


Table 2 Name: SIMS/USER/E-Mail Passwords

username
password
e-mail password
sims id
sims password
current user
e-mail address
department
epix user id
epix password
dip system user id
dip system group
dip system password
mainframe lun number
mainframe systems access

All text fields and no primary key in either of them.

Can Anyone help me...

I need a query which places all teh fields side by side to
make like a table so that I may have access to all this
information on one form...

Many Thanks

James
 
J

Jeff Boyce

James

Here's one way to "build" a UNION query...

Create a query against the first table, using the query grid. Run it. Does
it return the rows/info you want?

Create a query against the second table, ... (same as above).

When both are working correctly, and both have the same number of and order
of fields, you're ready to build the UNION query.

Open a new query and click the SQL design button to get the SQL view window.
Open the first query above, click the SQL view design button, copy the SQL,
and paste into your new query. Open the second (above), copy/paste into
your new query.

Finally, a little cleanup -- remove the ";" after the first query's SQL, hit
<enter> and add the word "UNION" and <enter>. Make sure you have a space
before and after UNION. Now try running it!

Good luck!

Jeff Boyce
<Access MVP>
 
J

James

Thanks for that but where would I have my other UNION
query?

I have the following code I need to add into this New
UNION Query:

SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support From] FROM [SQT Live CISCO
Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
NULL,Base,Username,Password,[Type of Dialin],[Support
From] FROM[live CISCO Accounts];

So when I have the valid code from the two queries I will
make where do I put the above code below the two sets I
have copied into my new query?

Many Thanks

James
 
J

James

Hello Again I have done the following SQL and I get alot
of Exp 1008, 1009 etc fields and things in the wrong
places is there any easy way of correcting this?

Here is the SQL code:

SELECT [External SIMS/USER/E-Mail Passwords].[User ID],
[External SIMS/USER/E-Mail Passwords].Password, [External
SIMS/USER/E-Mail Passwords].Base, [External SIMS/USER/E-
Mail Passwords].[Current User], [External SIMS/USER/E-Mail
Passwords].[SIMS ID], [External SIMS/USER/E-Mail
Passwords].[SIMS Password], [External SIMS/USER/E-Mail
Passwords].[EMS Online User ID], [External SIMS/USER/E-
Mail Passwords].[EMS Online
Password],null,null,null,null,null,null,null
FROM [External SIMS/USER/E-Mail Passwords]
UNION
SELECT [SIMS/USER/E-Mail Passwords].[User Name],
[SIMS/USER/E-Mail Passwords].Password, [SIMS/USER/E-Mail
Passwords].[E-Mail Password], [SIMS/USER/E-Mail Passwords].
[SIMS ID], [SIMS/USER/E-Mail Passwords].[SIMS Password],
[SIMS/USER/E-Mail Passwords].[Current User], [SIMS/USER/E-
Mail Passwords].[E-Mail Address], [SIMS/USER/E-Mail
Passwords].Department, [SIMS/USER/E-Mail Passwords].[EPIX
User ID], [SIMS/USER/E-Mail Passwords].[EPIX Password],
[SIMS/USER/E-Mail Passwords].[DIP System User ID],
[SIMS/USER/E-Mail Passwords].[DIP System Group],
[SIMS/USER/E-Mail Passwords].[DIP System Password],
[SIMS/USER/E-Mail Passwords].[Mainframe LUN Number],
[SIMS/USER/E-Mail Passwords].[Mainframe Systems Access]
FROM [SIMS/USER/E-Mail Passwords]
UNION
SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support
From],null,null,null,null,null,null,null FROM [SQT Live
CISCO Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
NULL,Base,Username,Password,[Type of Dialin],[Support
From],null,null,null,null,null,null,null FROM[live CISCO
Accounts];

Many Thanks

James
-----Original Message-----
Thanks for that but where would I have my other UNION
query?

I have the following code I need to add into this New
UNION Query:

SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support From] FROM [SQT Live CISCO
Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
NULL,Base,Username,Password,[Type of Dialin],[Support
From] FROM[live CISCO Accounts];

So when I have the valid code from the two queries I will
make where do I put the above code below the two sets I
have copied into my new query?

Many Thanks

James
-----Original Message-----
James

Here's one way to "build" a UNION query...

Create a query against the first table, using the query grid. Run it. Does
it return the rows/info you want?

Create a query against the second table, ... (same as above).

When both are working correctly, and both have the same number of and order
of fields, you're ready to build the UNION query.

Open a new query and click the SQL design button to get the SQL view window.
Open the first query above, click the SQL view design button, copy the SQL,
and paste into your new query. Open the second (above), copy/paste into
your new query.

Finally, a little cleanup -- remove the ";" after the first query's SQL, hit
<enter> and add the word "UNION" and <enter>. Make sure you have a space
before and after UNION. Now try running it!

Good luck!

Jeff Boyce
<Access MVP>

.
Click to expand...
.
Click to expand...
 
J

John Viescas

PMJI. Move the second query to first in the set of UNIONs to get valid
field names or add AS clauses after all your Null literals in the first
SELECT statement.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
James said:
Hello Again I have done the following SQL and I get alot
of Exp 1008, 1009 etc fields and things in the wrong
places is there any easy way of correcting this?

Here is the SQL code:

SELECT [External SIMS/USER/E-Mail Passwords].[User ID],
[External SIMS/USER/E-Mail Passwords].Password, [External
SIMS/USER/E-Mail Passwords].Base, [External SIMS/USER/E-
Mail Passwords].[Current User], [External SIMS/USER/E-Mail
Passwords].[SIMS ID], [External SIMS/USER/E-Mail
Passwords].[SIMS Password], [External SIMS/USER/E-Mail
Passwords].[EMS Online User ID], [External SIMS/USER/E-
Mail Passwords].[EMS Online
Password],null,null,null,null,null,null,null
FROM [External SIMS/USER/E-Mail Passwords]
UNION
SELECT [SIMS/USER/E-Mail Passwords].[User Name],
[SIMS/USER/E-Mail Passwords].Password, [SIMS/USER/E-Mail
Passwords].[E-Mail Password], [SIMS/USER/E-Mail Passwords].
[SIMS ID], [SIMS/USER/E-Mail Passwords].[SIMS Password],
[SIMS/USER/E-Mail Passwords].[Current User], [SIMS/USER/E-
Mail Passwords].[E-Mail Address], [SIMS/USER/E-Mail
Passwords].Department, [SIMS/USER/E-Mail Passwords].[EPIX
User ID], [SIMS/USER/E-Mail Passwords].[EPIX Password],
[SIMS/USER/E-Mail Passwords].[DIP System User ID],
[SIMS/USER/E-Mail Passwords].[DIP System Group],
[SIMS/USER/E-Mail Passwords].[DIP System Password],
[SIMS/USER/E-Mail Passwords].[Mainframe LUN Number],
[SIMS/USER/E-Mail Passwords].[Mainframe Systems Access]
FROM [SIMS/USER/E-Mail Passwords]
UNION
SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support
From],null,null,null,null,null,null,null FROM [SQT Live
CISCO Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
NULL,Base,Username,Password,[Type of Dialin],[Support
From],null,null,null,null,null,null,null FROM[live CISCO
Accounts];

Many Thanks

James
-----Original Message-----
Thanks for that but where would I have my other UNION
query?

I have the following code I need to add into this New
UNION Query:

SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support From] FROM [SQT Live CISCO
Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
NULL,Base,Username,Password,[Type of Dialin],[Support
From] FROM[live CISCO Accounts];

So when I have the valid code from the two queries I will
make where do I put the above code below the two sets I
have copied into my new query?

Many Thanks

James
-----Original Message-----
James

Here's one way to "build" a UNION query...

Create a query against the first table, using the query grid. Run it. Does
it return the rows/info you want?

Create a query against the second table, ... (same as above).

When both are working correctly, and both have the same number of and order
of fields, you're ready to build the UNION query.

Open a new query and click the SQL design button to get the SQL view window.
Open the first query above, click the SQL view design button, copy the SQL,
and paste into your new query. Open the second (above), copy/paste into
your new query.

Finally, a little cleanup -- remove the ";" after the first query's SQL, hit
<enter> and add the word "UNION" and <enter>. Make sure you have a space
before and after UNION. Now try running it!

Good luck!

Jeff Boyce
<Access MVP>

.
Click to expand...
.
Click to expand...
Click to expand...
 
J

Jeff Boyce

James

Sorry I wasn't clearer -- I was suggesting a replacement for the SQL you
already have. What happens if you only do what I suggested, rather than
trying to add in your existing SQL statement?

Good luck

Jeff Boyce
<Access MVP>
 
J

James

PMJI?? What does this mean?

Also I have created teh following SQL to be added into my
current SQL...

SELECT [SIMS/USER/E-Mail Passwords].[User Name],
[SIMS/USER/E-Mail Passwords].Password,
[SIMS/USER/E-Mail Passwords].[E-Mail Password],
[SIMS/USER/E-Mail Passwords].[SIMS ID],
[SIMS/USER/E-Mail Passwords].[SIMS Password],
[SIMS/USER/E-Mail Passwords].[Current User],
[SIMS/USER/E-Mail Passwords].[E-Mail Address],
[SIMS/USER/E-Mail Passwords].Department,
[SIMS/USER/E-Mail Passwords].[EPIX User ID],
[SIMS/USER/E-Mail Passwords].[EPIX Password],
[SIMS/USER/E-Mail Passwords].[DIP System User ID],
[SIMS/USER/E-Mail Passwords].[DIP System Group],
[SIMS/USER/E-Mail Passwords].[DIP System Password],
[SIMS/USER/E-Mail Passwords].[Mainframe LUN Number],
[SIMS/USER/E-Mail Passwords].[Mainframe Systems Access],
NULL,
NULL
FROM [SIMS/USER/E-Mail Passwords]

UNION

SELECT [External SIMS/USER/E-Mail Passwords].[User ID],
[External SIMS/USER/E-Mail Passwords].Password,
NULL,
[External SIMS/USER/E-Mail Passwords].[SIMS ID],
[External SIMS/USER/E-Mail Passwords].[SIMS Password],
[External SIMS/USER/E-Mail Passwords].[Current User],
NULL,
NULL,
[External SIMS/USER/E-Mail Passwords].Base,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
[External SIMS/USER/E-Mail Passwords].[EMS Online User ID],
[External SIMS/USER/E-Mail Passwords].[EMS Online Password]
FROM [External SIMS/USER/E-Mail Passwords];

Would this Work?

Many Thanks

James
-----Original Message-----
PMJI. Move the second query to first in the set of UNIONs to get valid
field names or add AS clauses after all your Null literals in the first
SELECT statement.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Hello Again I have done the following SQL and I get alot
of Exp 1008, 1009 etc fields and things in the wrong
places is there any easy way of correcting this?

Here is the SQL code:

SELECT [External SIMS/USER/E-Mail Passwords].[User ID],
[External SIMS/USER/E-Mail Passwords].Password, [External
SIMS/USER/E-Mail Passwords].Base, [External SIMS/USER/E-
Mail Passwords].[Current User], [External SIMS/USER/E- Mail
Passwords].[SIMS ID], [External SIMS/USER/E-Mail
Passwords].[SIMS Password], [External SIMS/USER/E-Mail
Passwords].[EMS Online User ID], [External SIMS/USER/E-
Mail Passwords].[EMS Online
Password],null,null,null,null,null,null,null
FROM [External SIMS/USER/E-Mail Passwords]
UNION
SELECT [SIMS/USER/E-Mail Passwords].[User Name],
[SIMS/USER/E-Mail Passwords].Password, [SIMS/USER/E-Mail
Passwords].[E-Mail Password], [SIMS/USER/E-Mail Passwords].
[SIMS ID], [SIMS/USER/E-Mail Passwords].[SIMS Password],
[SIMS/USER/E-Mail Passwords].[Current User], [SIMS/USER/E-
Mail Passwords].[E-Mail Address], [SIMS/USER/E-Mail
Passwords].Department, [SIMS/USER/E-Mail Passwords]. [EPIX
User ID], [SIMS/USER/E-Mail Passwords].[EPIX Password],
[SIMS/USER/E-Mail Passwords].[DIP System User ID],
[SIMS/USER/E-Mail Passwords].[DIP System Group],
[SIMS/USER/E-Mail Passwords].[DIP System Password],
[SIMS/USER/E-Mail Passwords].[Mainframe LUN Number],
[SIMS/USER/E-Mail Passwords].[Mainframe Systems Access]
FROM [SIMS/USER/E-Mail Passwords]
UNION
SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support
From],null,null,null,null,null,null,null FROM [SQT Live
CISCO Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
NULL,Base,Username,Password,[Type of Dialin],[Support
From],null,null,null,null,null,null,null FROM[live CISCO
Accounts];

Many Thanks

James
-----Original Message-----
Thanks for that but where would I have my other UNION
query?

I have the following code I need to add into this New
UNION Query:

SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support From] FROM [SQT Live CISCO
Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
NULL,Base,Username,Password,[Type of Dialin],[Support
From] FROM[live CISCO Accounts];

So when I have the valid code from the two queries I will
make where do I put the above code below the two sets I
have copied into my new query?

Many Thanks

James
-----Original Message-----
James

Here's one way to "build" a UNION query...

Create a query against the first table, using the query
grid. Run it. Does
it return the rows/info you want?

Create a query against the second table, ... (same as
above).

When both are working correctly, and both have the same
number of and order
of fields, you're ready to build the UNION query.

Open a new query and click the SQL design button to get
the SQL view window.
Open the first query above, click the SQL view design
button, copy the SQL,
and paste into your new query. Open the second (above),
copy/paste into
your new query.

Finally, a little cleanup -- remove the ";" after the
first query's SQL, hit
<enter> and add the word "UNION" and <enter>. Make sure
you have a space
before and after UNION. Now try running it!

Good luck!

Jeff Boyce
<Access MVP>

.

.
Click to expand...
Click to expand...


.
Click to expand...
 
J

John Viescas

"Pardon My Jumping In" - often used when one MVP jumps into a thread already
being handled by another.

I was trying to answer why you were getting "Expnnn" for some of the field
names. Access uses the field names from the first SELECT statement that it
encounters in a UNION. When you add "dummy" fields to make the columns line
up with subsequent SELECT statements, you should add an AS clause to give
the field a name. If you don't do that, Access generates a name for you.

For example, if you want to UNION rows from a table that has FirstName and
LastName with a table that has FirstName, LastName, and email, you need to
do:

SELECT FirstName, LastName, Null As Email
FROM Table1
UNION
SELECT FirstName, LastName, Email
FROM Table2

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
James said:
PMJI?? What does this mean?

Also I have created teh following SQL to be added into my
current SQL...

SELECT [SIMS/USER/E-Mail Passwords].[User Name],
[SIMS/USER/E-Mail Passwords].Password,
[SIMS/USER/E-Mail Passwords].[E-Mail Password],
[SIMS/USER/E-Mail Passwords].[SIMS ID],
[SIMS/USER/E-Mail Passwords].[SIMS Password],
[SIMS/USER/E-Mail Passwords].[Current User],
[SIMS/USER/E-Mail Passwords].[E-Mail Address],
[SIMS/USER/E-Mail Passwords].Department,
[SIMS/USER/E-Mail Passwords].[EPIX User ID],
[SIMS/USER/E-Mail Passwords].[EPIX Password],
[SIMS/USER/E-Mail Passwords].[DIP System User ID],
[SIMS/USER/E-Mail Passwords].[DIP System Group],
[SIMS/USER/E-Mail Passwords].[DIP System Password],
[SIMS/USER/E-Mail Passwords].[Mainframe LUN Number],
[SIMS/USER/E-Mail Passwords].[Mainframe Systems Access],
NULL,
NULL
FROM [SIMS/USER/E-Mail Passwords]

UNION

SELECT [External SIMS/USER/E-Mail Passwords].[User ID],
[External SIMS/USER/E-Mail Passwords].Password,
NULL,
[External SIMS/USER/E-Mail Passwords].[SIMS ID],
[External SIMS/USER/E-Mail Passwords].[SIMS Password],
[External SIMS/USER/E-Mail Passwords].[Current User],
NULL,
NULL,
[External SIMS/USER/E-Mail Passwords].Base,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
[External SIMS/USER/E-Mail Passwords].[EMS Online User ID],
[External SIMS/USER/E-Mail Passwords].[EMS Online Password]
FROM [External SIMS/USER/E-Mail Passwords];

Would this Work?

Many Thanks

James
-----Original Message-----
PMJI. Move the second query to first in the set of UNIONs to get valid
field names or add AS clauses after all your Null literals in the first
SELECT statement.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Hello Again I have done the following SQL and I get alot
of Exp 1008, 1009 etc fields and things in the wrong
places is there any easy way of correcting this?

Here is the SQL code:

SELECT [External SIMS/USER/E-Mail Passwords].[User ID],
[External SIMS/USER/E-Mail Passwords].Password, [External
SIMS/USER/E-Mail Passwords].Base, [External SIMS/USER/E-
Mail Passwords].[Current User], [External SIMS/USER/E- Mail
Passwords].[SIMS ID], [External SIMS/USER/E-Mail
Passwords].[SIMS Password], [External SIMS/USER/E-Mail
Passwords].[EMS Online User ID], [External SIMS/USER/E-
Mail Passwords].[EMS Online
Password],null,null,null,null,null,null,null
FROM [External SIMS/USER/E-Mail Passwords]
UNION
SELECT [SIMS/USER/E-Mail Passwords].[User Name],
[SIMS/USER/E-Mail Passwords].Password, [SIMS/USER/E-Mail
Passwords].[E-Mail Password], [SIMS/USER/E-Mail Passwords].
[SIMS ID], [SIMS/USER/E-Mail Passwords].[SIMS Password],
[SIMS/USER/E-Mail Passwords].[Current User], [SIMS/USER/E-
Mail Passwords].[E-Mail Address], [SIMS/USER/E-Mail
Passwords].Department, [SIMS/USER/E-Mail Passwords]. [EPIX
User ID], [SIMS/USER/E-Mail Passwords].[EPIX Password],
[SIMS/USER/E-Mail Passwords].[DIP System User ID],
[SIMS/USER/E-Mail Passwords].[DIP System Group],
[SIMS/USER/E-Mail Passwords].[DIP System Password],
[SIMS/USER/E-Mail Passwords].[Mainframe LUN Number],
[SIMS/USER/E-Mail Passwords].[Mainframe Systems Access]
FROM [SIMS/USER/E-Mail Passwords]
UNION
SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support
From],null,null,null,null,null,null,null FROM [SQT Live
CISCO Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
NULL,Base,Username,Password,[Type of Dialin],[Support
From],null,null,null,null,null,null,null FROM[live CISCO
Accounts];

Many Thanks

James
-----Original Message-----
Thanks for that but where would I have my other UNION
query?

I have the following code I need to add into this New
UNION Query:

SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support From] FROM [SQT Live CISCO
Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
NULL,Base,Username,Password,[Type of Dialin],[Support
From] FROM[live CISCO Accounts];

So when I have the valid code from the two queries I will
make where do I put the above code below the two sets I
have copied into my new query?

Many Thanks

James
-----Original Message-----
James

Here's one way to "build" a UNION query...

Create a query against the first table, using the query
grid. Run it. Does
it return the rows/info you want?

Create a query against the second table, ... (same as
above).

When both are working correctly, and both have the same
number of and order
of fields, you're ready to build the UNION query.

Open a new query and click the SQL design button to get
the SQL view window.
Open the first query above, click the SQL view design
button, copy the SQL,
and paste into your new query. Open the second (above),
copy/paste into
your new query.

Finally, a little cleanup -- remove the ";" after the
first query's SQL, hit
<enter> and add the word "UNION" and <enter>. Make sure
you have a space
before and after UNION. Now try running it!

Good luck!

Jeff Boyce
<Access MVP>

.

.
Click to expand...


.
Click to expand...
Click to expand...
 
J

James

Ah right thanks for that...

Do you have any comments on the SQL part of my query as I
am really getting frustrated with it now as everything I
seem to try with the damn thing does not work...

Many Thanks

James
-----Original Message-----
"Pardon My Jumping In" - often used when one MVP jumps into a thread already
being handled by another.

I was trying to answer why you were getting "Expnnn" for some of the field
names. Access uses the field names from the first SELECT statement that it
encounters in a UNION. When you add "dummy" fields to make the columns line
up with subsequent SELECT statements, you should add an AS clause to give
the field a name. If you don't do that, Access generates a name for you.

For example, if you want to UNION rows from a table that has FirstName and
LastName with a table that has FirstName, LastName, and email, you need to
do:

SELECT FirstName, LastName, Null As Email
FROM Table1
UNION
SELECT FirstName, LastName, Email
FROM Table2

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
PMJI?? What does this mean?

Also I have created teh following SQL to be added into my
current SQL...

SELECT [SIMS/USER/E-Mail Passwords].[User Name],
[SIMS/USER/E-Mail Passwords].Password,
[SIMS/USER/E-Mail Passwords].[E-Mail Password],
[SIMS/USER/E-Mail Passwords].[SIMS ID],
[SIMS/USER/E-Mail Passwords].[SIMS Password],
[SIMS/USER/E-Mail Passwords].[Current User],
[SIMS/USER/E-Mail Passwords].[E-Mail Address],
[SIMS/USER/E-Mail Passwords].Department,
[SIMS/USER/E-Mail Passwords].[EPIX User ID],
[SIMS/USER/E-Mail Passwords].[EPIX Password],
[SIMS/USER/E-Mail Passwords].[DIP System User ID],
[SIMS/USER/E-Mail Passwords].[DIP System Group],
[SIMS/USER/E-Mail Passwords].[DIP System Password],
[SIMS/USER/E-Mail Passwords].[Mainframe LUN Number],
[SIMS/USER/E-Mail Passwords].[Mainframe Systems Access],
NULL,
NULL
FROM [SIMS/USER/E-Mail Passwords]

UNION

SELECT [External SIMS/USER/E-Mail Passwords].[User ID],
[External SIMS/USER/E-Mail Passwords].Password,
NULL,
[External SIMS/USER/E-Mail Passwords].[SIMS ID],
[External SIMS/USER/E-Mail Passwords].[SIMS Password],
[External SIMS/USER/E-Mail Passwords].[Current User],
NULL,
NULL,
[External SIMS/USER/E-Mail Passwords].Base,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
[External SIMS/USER/E-Mail Passwords].[EMS Online User ID],
[External SIMS/USER/E-Mail Passwords].[EMS Online Password]
FROM [External SIMS/USER/E-Mail Passwords];

Would this Work?

Many Thanks

James
-----Original Message-----
PMJI. Move the second query to first in the set of UNIONs to get valid
field names or add AS clauses after all your Null literals in the first
SELECT statement.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Hello Again I have done the following SQL and I get alot
of Exp 1008, 1009 etc fields and things in the wrong
places is there any easy way of correcting this?

Here is the SQL code:

SELECT [External SIMS/USER/E-Mail Passwords].[User ID],
[External SIMS/USER/E-Mail Passwords].Password, [External
SIMS/USER/E-Mail Passwords].Base, [External SIMS/USER/E-
Mail Passwords].[Current User], [External
Click to expand...
SIMS/USER/E-
Mail
Passwords].[SIMS ID], [External SIMS/USER/E-Mail
Passwords].[SIMS Password], [External SIMS/USER/E- Mail
Passwords].[EMS Online User ID], [External SIMS/USER/E-
Mail Passwords].[EMS Online
Password],null,null,null,null,null,null,null
FROM [External SIMS/USER/E-Mail Passwords]
UNION
SELECT [SIMS/USER/E-Mail Passwords].[User Name],
[SIMS/USER/E-Mail Passwords].Password, [SIMS/USER/E- Mail
Passwords].[E-Mail Password], [SIMS/USER/E-Mail Passwords].
[SIMS ID], [SIMS/USER/E-Mail Passwords].[SIMS Password],
[SIMS/USER/E-Mail Passwords].[Current User], [SIMS/USER/E-
Mail Passwords].[E-Mail Address], [SIMS/USER/E-Mail
Passwords].Department, [SIMS/USER/E-Mail Passwords]. [EPIX
User ID], [SIMS/USER/E-Mail Passwords].[EPIX Password],
[SIMS/USER/E-Mail Passwords].[DIP System User ID],
[SIMS/USER/E-Mail Passwords].[DIP System Group],
[SIMS/USER/E-Mail Passwords].[DIP System Password],
[SIMS/USER/E-Mail Passwords].[Mainframe LUN Number],
[SIMS/USER/E-Mail Passwords].[Mainframe Systems Access]
FROM [SIMS/USER/E-Mail Passwords]
UNION
SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support
From],null,null,null,null,null,null,null FROM [SQT Live
CISCO Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
NULL,Base,Username,Password,[Type of Dialin],[Support
From],null,null,null,null,null,null,null FROM[live CISCO
Accounts];

Many Thanks

James
-----Original Message-----
Thanks for that but where would I have my other UNION
query?

I have the following code I need to add into this New
UNION Query:

SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support From] FROM [SQT Live CISCO
Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
NULL,Base,Username,Password,[Type of Dialin], [Support
From] FROM[live CISCO Accounts];

So when I have the valid code from the two queries I will
make where do I put the above code below the two sets I
have copied into my new query?

Many Thanks

James
-----Original Message-----
James

Here's one way to "build" a UNION query...

Create a query against the first table, using the query
grid. Run it. Does
it return the rows/info you want?

Create a query against the second table, ... (same as
above).

When both are working correctly, and both have the same
number of and order
of fields, you're ready to build the UNION query.

Open a new query and click the SQL design button to get
the SQL view window.
Open the first query above, click the SQL view design
button, copy the SQL,
and paste into your new query. Open the second (above),
copy/paste into
your new query.

Finally, a little cleanup -- remove the ";" after the
first query's SQL, hit
<enter> and add the word "UNION" and <enter>. Make sure
you have a space
before and after UNION. Now try running it!

Good luck!

Jeff Boyce
<Access MVP>

.

.



.
Click to expand...
Click to expand...


.
Click to expand...
 
J

John Viescas

Hmm. Looks like the first query has 17 columns, and the second has 18.
Does this query run? If not, what error messages are you seeing?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
James said:
Ah right thanks for that...

Do you have any comments on the SQL part of my query as I
am really getting frustrated with it now as everything I
seem to try with the damn thing does not work...

Many Thanks

James
-----Original Message-----
"Pardon My Jumping In" - often used when one MVP jumps into a thread already
being handled by another.

I was trying to answer why you were getting "Expnnn" for some of the field
names. Access uses the field names from the first SELECT statement that it
encounters in a UNION. When you add "dummy" fields to make the columns line
up with subsequent SELECT statements, you should add an AS clause to give
the field a name. If you don't do that, Access generates a name for you.

For example, if you want to UNION rows from a table that has FirstName and
LastName with a table that has FirstName, LastName, and email, you need to
do:

SELECT FirstName, LastName, Null As Email
FROM Table1
UNION
SELECT FirstName, LastName, Email
FROM Table2

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
PMJI?? What does this mean?

Also I have created teh following SQL to be added into my
current SQL...

SELECT [SIMS/USER/E-Mail Passwords].[User Name],
[SIMS/USER/E-Mail Passwords].Password,
[SIMS/USER/E-Mail Passwords].[E-Mail Password],
[SIMS/USER/E-Mail Passwords].[SIMS ID],
[SIMS/USER/E-Mail Passwords].[SIMS Password],
[SIMS/USER/E-Mail Passwords].[Current User],
[SIMS/USER/E-Mail Passwords].[E-Mail Address],
[SIMS/USER/E-Mail Passwords].Department,
[SIMS/USER/E-Mail Passwords].[EPIX User ID],
[SIMS/USER/E-Mail Passwords].[EPIX Password],
[SIMS/USER/E-Mail Passwords].[DIP System User ID],
[SIMS/USER/E-Mail Passwords].[DIP System Group],
[SIMS/USER/E-Mail Passwords].[DIP System Password],
[SIMS/USER/E-Mail Passwords].[Mainframe LUN Number],
[SIMS/USER/E-Mail Passwords].[Mainframe Systems Access],
NULL,
NULL
FROM [SIMS/USER/E-Mail Passwords]

UNION

SELECT [External SIMS/USER/E-Mail Passwords].[User ID],
[External SIMS/USER/E-Mail Passwords].Password,
NULL,
[External SIMS/USER/E-Mail Passwords].[SIMS ID],
[External SIMS/USER/E-Mail Passwords].[SIMS Password],
[External SIMS/USER/E-Mail Passwords].[Current User],
NULL,
NULL,
[External SIMS/USER/E-Mail Passwords].Base,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
[External SIMS/USER/E-Mail Passwords].[EMS Online User ID],
[External SIMS/USER/E-Mail Passwords].[EMS Online Password]
FROM [External SIMS/USER/E-Mail Passwords];

Would this Work?

Many Thanks

James
-----Original Message-----
PMJI. Move the second query to first in the set of
UNIONs to get valid
field names or add AS clauses after all your Null
literals in the first
SELECT statement.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?
pg=personal&fr_id=1090&px=1434411
message
Hello Again I have done the following SQL and I get alot
of Exp 1008, 1009 etc fields and things in the wrong
places is there any easy way of correcting this?

Here is the SQL code:

SELECT [External SIMS/USER/E-Mail Passwords].[User ID],
[External SIMS/USER/E-Mail Passwords].Password,
[External
SIMS/USER/E-Mail Passwords].Base, [External SIMS/USER/E-
Mail Passwords].[Current User], [External SIMS/USER/E-
Mail
Passwords].[SIMS ID], [External SIMS/USER/E-Mail
Passwords].[SIMS Password], [External SIMS/USER/E- Mail
Passwords].[EMS Online User ID], [External SIMS/USER/E-
Mail Passwords].[EMS Online
Password],null,null,null,null,null,null,null
FROM [External SIMS/USER/E-Mail Passwords]
UNION
SELECT [SIMS/USER/E-Mail Passwords].[User Name],
[SIMS/USER/E-Mail Passwords].Password, [SIMS/USER/E- Mail
Passwords].[E-Mail Password], [SIMS/USER/E-Mail
Passwords].
[SIMS ID], [SIMS/USER/E-Mail Passwords].[SIMS Password],
[SIMS/USER/E-Mail Passwords].[Current User],
[SIMS/USER/E-
Mail Passwords].[E-Mail Address], [SIMS/USER/E-Mail
Passwords].Department, [SIMS/USER/E-Mail Passwords].
[EPIX
User ID], [SIMS/USER/E-Mail Passwords].[EPIX Password],
[SIMS/USER/E-Mail Passwords].[DIP System User ID],
[SIMS/USER/E-Mail Passwords].[DIP System Group],
[SIMS/USER/E-Mail Passwords].[DIP System Password],
[SIMS/USER/E-Mail Passwords].[Mainframe LUN Number],
[SIMS/USER/E-Mail Passwords].[Mainframe Systems Access]
FROM [SIMS/USER/E-Mail Passwords]
UNION
SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support
From],null,null,null,null,null,null,null FROM [SQT Live
CISCO Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS Surname,
NULL,Base,Username,Password,[Type of Dialin],[Support
From],null,null,null,null,null,null,null FROM[live CISCO
Accounts];

Many Thanks

James
-----Original Message-----
Thanks for that but where would I have my other UNION
query?

I have the following code I need to add into this New
UNION Query:

SELECT
Forname,Surname,Location,Department,Username,Password,
[Type of Dialin], [Support From] FROM [SQT Live CISCO
Accounts]
UNION ALL SELECT
IIF(Instr(Trim([Name] & "")," ") >0,
Left([Name], Instr([Name], " ") -1), [Name]) AS
Forname,
IIF(Instr(Trim([Name] & "")," ") >0,
Mid([Name], Instr([Name], " ") + 1), [Name]) AS
Surname,
NULL,Base,Username,Password,[Type of Dialin], [Support
From] FROM[live CISCO Accounts];

So when I have the valid code from the two queries I
will
make where do I put the above code below the two sets I
have copied into my new query?

Many Thanks

James
-----Original Message-----
James

Here's one way to "build" a UNION query...

Create a query against the first table, using the
query
grid. Run it. Does
it return the rows/info you want?

Create a query against the second table, ... (same as
above).

When both are working correctly, and both have the
same
number of and order
of fields, you're ready to build the UNION query.

Open a new query and click the SQL design button to
get
the SQL view window.
Open the first query above, click the SQL view design
button, copy the SQL,
and paste into your new query. Open the second
(above),
copy/paste into
your new query.

Finally, a little cleanup -- remove the ";" after the
first query's SQL, hit
<enter> and add the word "UNION" and <enter>. Make
sure
you have a space
before and after UNION. Now try running it!

Good luck!

Jeff Boyce
<Access MVP>

.

.



.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

UNION Again (cant get my head round it) 1
Uniting Tables and Queries 9
Union Query - Select Distinct 2 fields 7
Union Query Used in Select Query Not Working 4
Incorrect syntax near '-'.Must declare the scalar variable "@UserName 0
Hotmail in Windows Mail etc 21
newbie: Problem with CreateUserWizard 1
Windows mail 1

Top