UNION Again (cant get my head round it)

[James]

Hello I have the following SQL (Which Works):

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 AS [EMS Online User ID],
NULL AS [EMS Online Password]
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,
[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];

I would like to add the following into that same SQL
statment (Which again on its own works):

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];

How do I go about putting them together to form one query?
I know I have to make up the number of fields and mix and
match but how do I do this and what the hell do I do with
the IIF statments... How can I include them in the
previous SQL statments?

Many Thanks in advance

James

 

[John Viescas]

James-

Here's the procedure I recommend for building a UNION query.

1) Build a query with all the columns you need from one of the tables.
2) Build a second query with all the columns. If some of the columns don't
exist in the second table, insert a blank column *in the exact same position
in this second query as in the first one*, enter the field name (use
brackets around the name if it contains blanks), a colon, and type the
keyword NULL. Switch to Datasheet view to make sure the query works and
returns the same number of columns in the same sequence as the first one.
3) Switch to SQL View in the first query and remove the semicolon at the end
of the SQL. Press Enter and type the word UNION and press enter again.
4) Switch to SQL View in the second query and copy everything except the
ending semicolon to the clipboard.
5) Go back to the first query and paste the SQL on the new line after the
UNION keyword.

You should be able to follow these steps to insert additional SELECT
statements to UNION.

--
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 I have the following SQL (Which Works):

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 AS [EMS Online User ID],
NULL AS [EMS Online Password]
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,
[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];

I would like to add the following into that same SQL
statment (Which again on its own works):

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];

How do I go about putting them together to form one query?
I know I have to make up the number of fields and mix and
match but how do I do this and what the hell do I do with
the IIF statments... How can I include them in the
previous SQL statments?

Many Thanks in advance

James