return all records, regardless of fields matching

D

David Pelizzari

Ok, apparently I have lost a good chunk of my brain in the last 6 months. I
am trying to merge 4 tables together that have at least one field that should
match (tables 1 & 2 join, 2 &3 join, 3 & 4 join). I want to mash these four
tables together based on those joins, but I want ALL the records, regardless
if there is a matched value. I can do this in Access 2003 or 2007.
 
R

Rob Parker

More specifically, since the OP says he wants "all records, regardless if
there is a matched value", it sounds like he needs a UNION ALL query, since
a standard Union query will eliminate duplicates.

Rob
 
D

David Pelizzari

Karl,

Maybe I am reading the Help wrong, it says that the fields in both tables
must be the same in order to use a Union. All four of my tables have
different data, with only one field possibly common between them. I want to
take the data from all four tables and mush it together. I am going to try
the Union All, but Microsoft doesn't write the examples to give me a warm
fuzzy about this working :)
 
D

David Pelizzari

Maybe this will help...

I have two tables, Active Directory and SMS.
Active directory has two fields, ADName and ADDescription. SMS has 4
fields, SMSModel, SMSLastLoggedOnUserName, SMSComputerName, SMSSerialNumber.
I want to take these two tables and merge them into a new table, say called
AD-SMS, where all the records from both tables are merged on
adname=smscomputername, I do however, want the records that don't match as
well in the new table. I am doing this by using an inner join, then using a
mismatched recoards query to make the table, but I still don't trust it.
 
K

KARL DEWEY

The datatype of the field must match like this ---
SELECT Text, Integer, DateTime
FROM 1st_Table
UNION ALL SELECT Text, Integer, DateTime
FROM 2nd_Table;

You can add aliases for additional fields that do not follow each other in
type --
SELECT Text, Integer, DateTime, Double, "" AS Text2
FROM 1st_Table
UNION ALL SELECT Text, Integer, DateTime, 0 AS Double, Text2
FROM 2nd_Table;

Here Double is in one table but not the other the same for Text2.
 
K

KARL DEWEY

Ok, create a temp table with a field called [adname] and make that field a
primary key. Append from [Active Directory].[adname] and
[SMS].[smscomputername]. Ignore error message that says it can not append all
due to duplicates.
Create your AD-SMS table having all fields needed to contain both [Active
Directory] and [SMS] tables.
Create first append left joining the temp table to [Active Directory] and
append to AD-SMS tablle. Do the same with temp table and [SMS] to end up
with a complete list from both tables.
 
D

David Pelizzari

Sounds like a plan, ty for your help!
KARL DEWEY said:
Ok, create a temp table with a field called [adname] and make that field a
primary key. Append from [Active Directory].[adname] and
[SMS].[smscomputername]. Ignore error message that says it can not append
all
due to duplicates.
Create your AD-SMS table having all fields needed to contain both [Active
Directory] and [SMS] tables.
Create first append left joining the temp table to [Active Directory] and
append to AD-SMS tablle. Do the same with temp table and [SMS] to end up
with a complete list from both tables.
--
KARL DEWEY
Build a little - Test a little


David Pelizzari said:
Maybe this will help...

I have two tables, Active Directory and SMS.
Active directory has two fields, ADName and ADDescription. SMS has 4
fields, SMSModel, SMSLastLoggedOnUserName, SMSComputerName,
SMSSerialNumber.
I want to take these two tables and merge them into a new table, say
called
AD-SMS, where all the records from both tables are merged on
adname=smscomputername, I do however, want the records that don't match
as
well in the new table. I am doing this by using an inner join, then
using a
mismatched recoards query to make the table, but I still don't trust it.
 
R

Rob Parker

Hi David,

I notice that Karl has followed this up earlier with a method that sounds
OK, but it can also be done using using a Union All query to simulate the
full outer join that you're wanting (and which Access/Jet does not provide).
A message thread a few days ago covered this; the only problem in that case
was that the poster was not joining on all matching fields and was losing
records. See the thread at
http://groups.google.com.au/group/m..._frm/thread/d9a9591c46c4efd1/acd404ed6cbc925e
for details.

Essentially, you Union All the results of an Inner Join query, and two Outer
Join (Right or Left) queries which show unmatched records from each table.

HTH,

Rob
 

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

Top