Uniting Tables and Queries

  • Thread starter Thread starter James
  • Start date Start date
J

James

Hello what I need is to unite 2 tables and one SQL query
together and then base a form on that one query.

I have an SQL query which has a UNION ALL statement in it
and I need to add to a new query this one query and the
other two tables and only show the records that they hold.

Is there a way to do this?
Click to expand...

I'm sure there is, but without knowing anything about the
nature of
the queries nor how you want them to be joined, I really
can't make
any suggestions, other than that it sounds like you want
another UNION
query unioning this first query and the other two tables.
Care to post
the SQL and the table definitions?

-----------------------------------------------------------
Hello,

Sorry for the late reply had lots of stuff to do...

What do you mean by table definitions?

And the SQL is as follows:

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

Many Thanks

James
 
As this is the second time that you have posted this, I will implore that
you ask a specific question, or reformat the post such that it contains a
question that can be answered.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

James said:
Hello what I need is to unite 2 tables and one SQL query
together and then base a form on that one query.

I have an SQL query which has a UNION ALL statement in it
and I need to add to a new query this one query and the
other two tables and only show the records that they hold.

Is there a way to do this?
Click to expand...

I'm sure there is, but without knowing anything about the
nature of
the queries nor how you want them to be joined, I really
can't make
any suggestions, other than that it sounds like you want
another UNION
query unioning this first query and the other two tables.
Care to post
the SQL and the table definitions?

-----------------------------------------------------------
Hello,

Sorry for the late reply had lots of stuff to do...

What do you mean by table definitions?

And the SQL is as follows:

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

Many Thanks

James
Click to expand...
 
I have been asked to provide the Table definitions (what
ever they are) and the SQL code for my problem so here is
the SQL:

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


And when I know what the table definitions are I will post
these as well..

I would like to combine this SQL query to two more tables
and it doesnot seem to be working for me.

Many Thanks

James
-----Original Message-----
As this is the second time that you have posted this, I will implore that
you ask a specific question, or reformat the post such that it contains a
question that can be answered.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hello what I need is to unite 2 tables and one SQL query
together and then base a form on that one query.

I have an SQL query which has a UNION ALL statement in it
and I need to add to a new query this one query and the
other two tables and only show the records that they hold.

Is there a way to do this?
Click to expand...

I'm sure there is, but without knowing anything about the
nature of
the queries nor how you want them to be joined, I really
can't make
any suggestions, other than that it sounds like you want
another UNION
query unioning this first query and the other two tables.
Care to post
the SQL and the table definitions?

-------------------------------------------------------- ---
Hello,

Sorry for the late reply had lots of stuff to do...

What do you mean by table definitions?

And the SQL is as follows:

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

Many Thanks

James
Click to expand...


.
Click to expand...
 
And when I know what the table definitions are I will post
these as well..
Click to expand...

Table definitions are just the list of fields and datatypes in the
table. It helps if you note which is the Primary Key.
I would like to combine this SQL query to two more tables
and it doesnot seem to be working for me.
Click to expand...

And it would help also if you could explain how you want to "combine"
this. UNION two more tables? Just add

UNION
SELECT <whatever> FROM <third table>

at the end of the query, then again with the fourth table; the number
and datatype of the fields must match.

Join? Just save the UNION query and create a new Query, joining the
saved UNION query to the new table; or, include the join in each of
the SELECT statements.
 
Here are the table definitions of one table:

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

All are text fields.

The next table is like this:

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.

HTH

Many Thanks

James
 
Here are the table definitions of one table:
Click to expand...

Thanks, James. However you didn't clearly answer the critical
question: What are you trying to accomplish? The term "Uniting" does
not have any meaning in Access, and I do not understand what you want
as a result.
 
Ah right sorry...

I am trying to accomplish the joining of two tables and a
query as I am creating a search page from three forms so
when they go on one form (which runs from one of the
tables) they need to return the results in the relevant
txt fields... So I need to base the form on this query I
am trying to create.

Hope this helps you...

Cheers

James
 
Ah right sorry...

I am trying to accomplish the joining of two tables and a
query as I am creating a search page from three forms so
when they go on one form (which runs from one of the
tables) they need to return the results in the relevant
txt fields... So I need to base the form on this query I
am trying to create.
Click to expand...

James... please try to understand.

We are not magicians. We are not telepathic. We are volunteers, with
businesses of our own to run.

We cannot see your tables. We cannot read your mind.

You say that you want to "accomplish the joining of..." several times,
but you have never yet explained HOW YOU WANT TO JOIN THEM. How are
they related? Do you want to UNION them into a recordset with more
records (end to end)? Do you want to JOIN them into a recordset with
more fields (side by side)? If the latter, what fields do you have to
make the join? What have you tried? What didn't work?
 
Ok well I need to make like another table which has all
the fields in the two tables and the query running side by
side so that its like one big table...

I have tried making an SQL query using UNION and it does
not seem to work... I have deleted it but I tried a UNION
ALL statement and then tried adding the other tables below
it with which I had UNION statements...

I have tried making a simple query where I just slam all
the fields of each table and query and it just seems to
give me millions and millions of records so that failed
mirably...

Any more information?

Will that do on a bases for some assistance on this matter?

Cheers

James
 
I have tried making a simple query where I just slam all
the fields of each table and query and it just seems to
give me millions and millions of records so that failed
mirably...
Click to expand...

This is called a "Cartesian join" - if you don't specify which field
in each table is used to match a record in the first table (or query)
with a corresponding record in the other table, it will give you all
possible combinations. You need to specify a joining field by dragging
it from your UNION query to the table you're trying to join. Since you
don't have a Primary Key, there is no way to uniquely identify any
record in any of your tables (this is what primary keys are for!!!!),
so it's going to be difficult; I'm GUESSING (bear in mind I don't know
the logic of your tables) that you can join on USERNAME.
Any more information?

Will that do on a bases for some assistance on this matter?
Click to expand...

I *really* suggest that you spend some time reading up about how
relational databases work. You are making your job much harder than it
would be if you had a better grounding in how queries work! See

http://support.microsoft.com/default.aspx?scid=kb;en-us;289533

for some pointers.
 
Back
Top