all records from 2 tables

  • Thread starter Thread starter G.
  • Start date Start date
G

G.

OK, there is probably a simple answer to my question:

I have 2 tables with some of the fields being the same. I am trying to make
a query that includes ALL information from both tables. The information in
the tables do not match so when I create a relationship, I get no results in
the query.

I'm just not getting it. Any help is appreciated. Thank you.
 
Ummmm - let me explain a bit more. When I saif the fields were the same, I
actaully meant the headings - the information in the fields is different.
Hope that clarifies things. Really, I just want all information from 2 tables
to show up in my query - not just the information that matches. Thanks again.
 
Dont create a relationship, just put both tables in the query. Then the
query will analize both tables indavidually. Do all the Field Names match?
 
Thanks. Let me explain more. I have a Last_Name field in both tables (I am
measuring information from different populations so I am using 2 tables). I
have 11 people in population #1 and 3 in population #2 - but they are all
different people. I need a query that combines both tables to give me 15
names.

If I create a query with no relationship I can do Last_Name and get 10 names
from the first table, but then the Last_Name from the second table will be
another field so they are not listed in one column. Also, I get a total of 33
records instead of 13.

Thanks again.
 
Thanks. Yes, I suspected that a union query may do the trick so this is what
I need. However, I am not great (or even good) at SQL yet. Is there a way to
do a union query outside of SQL in design view? The union query does not look
very sql intensive so if the answer is no, I can go through it - just not
extremely comfortable with it yet (but it may be good exercise). Thanks for
all your help.
 
A Union query does not have a designer, you have to write it in SQL view.
The instructions on the link that I sent you are pretty straight foward and
not that hard to follow. Let me know if you need help with the SQL
statement. I would need the table names and fields you wish to use if you
need help.
 
While you've received a response pointing you toward a UNION query, I need
to ask "why?", as in "why are you using two tables to hold information about
"people" from two different populations?"

This sounds like a spreadsheet, not a relational database.

In a relational database (e.g., ACCESS!), you'd use one table for your
person-oriented information, and if you needed to know the
source/population, you might add a single field to hold the
source/population indicator. I mention "might" because I don't understand
your situation well enough to offer more specific suggestions yet...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Ummmm - let me explain a bit more. When I saif the fields were the same, I
actaully meant the headings - the information in the fields is different.
Hope that clarifies things. Really, I just want all information from 2 tables
to show up in my query - not just the information that matches. Thanks again.
Click to expand...

Say you have 2 records in T1 and 3 records in T2, you want the result
to have 5 records where all fields from T1 and T2 are shown, right?

Then you need a union query. The easiest way to do it is to create
two select queries, one for each table, T1 and T2, and then put the
columns in order so that the each column in your result corresponds to
a column in the other table. This is really confusing without an
example... so, say I have T1, and T2

T1(PersonID, FirstName, MI, LName, Address, City, State, ZipCode)
T2(PersonID, FirstName, LastName, Address1, City, Region, PostalCode,
PhoneNo)

basically, the only difference is T1 contains MI and T2 contains
PhoneNo. The rest of the differences are basically synonyms.

SELECT T1.PersonID, T1.FirstName, T1.MI, T1.LName AS LastName,
T1.Address, T1.City, T1.State, T1.ZipCode, Null As PhoneNo
FROM T1
UNION ALL
SELECT T2.PersonID, T2.FirstName, Null, T2.LastName, T2.Address1,
T2.City, T2.Region, T2.PostalCode, T2.PhoneNo
FROM T2;

Since for the columns that exist in one table but not in the other, I
had to alias them in the first select statement so the column counts
match in the two SQL statements.

HTH
 
Back
Top