Combine Tables?

[SKB]

I asked this question in the 'getting started' forum. Maybe the wrong
place, thought I should try here also.

I have three tables that represent three separate mailing lists
(converted from text files). Many of the people are in all three lists.
Currently each table contains name and address information with a yes/no
field for their list.

Is there an easy way to combine the three tables into one table showing
which lists they are in avoiding duplicates in the Name information.

for example

This;
Table1
Name| list1 |list2 |list3
yes no no

Table2
Name| list1 |list2 |list3
no no no

Table3
Name| list1 |list2 |list3
no no yes

To this;

Table
Name| list1| list2| list3
yes no yes

If I can just get pointed in the right direction it would be great!
Thanks in advance for any help,

SKB

 

[Tom Ellison]

Dear S. Burnett:

You can certainly create such a list from your 3 tables using a UNION query:

SELECT Name, -1 AS List1, 0 AS List2, 0 AS List3
FROM Table1
UNION ALL
SELECT Name, 0 AS List1, -1 AS List2, 0 AS List3
FROM Table2
UNION ALL
SELECT Name, 0 AS List1, 0 AS List2, -1 AS List3
FROM Table3

Save this query. For reference below, I call it qryUnion

Now, you need to combine the rows having the same name (assuming this was
unique in the original tables).

SELECT Name, SUM(List1) AS List1,
SUM(List2) AS List2, SUM(List3) AS List3
FROM qryUnion
GROUP BY Name

I used values of -1 for "true" following Access Jet's standard for booleans.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

 

[Jeff Boyce]

If you are looking for a way to come up with a list of unique folks, Tom's
suggestion (UNION query) will work.

If you are looking for a way to come up with a single table, holding only
unique folks/rows, you could either create a make-table query based on Tom's
UNION query, or you could create your new table, identify which fields would
guarantee unique-ness and index that combination, then append records from
each of your three imports into that new table. Any "duplicate" records
(based on your "unique" index) will be rejected during the appends.

Regards

Jeff Boyce
<Office/Access MVP>

 

[SKB]

Dear S. Burnett:

You can certainly create such a list from your 3 tables using a UNION query:

SELECT Name, -1 AS List1, 0 AS List2, 0 AS List3
FROM Table1
UNION ALL
SELECT Name, 0 AS List1, -1 AS List2, 0 AS List3
FROM Table2
UNION ALL
SELECT Name, 0 AS List1, 0 AS List2, -1 AS List3
FROM Table3

Save this query. For reference below, I call it qryUnion

Now, you need to combine the rows having the same name (assuming this was
unique in the original tables).

SELECT Name, SUM(List1) AS List1,
SUM(List2) AS List2, SUM(List3) AS List3
FROM qryUnion
GROUP BY Name

I used values of -1 for "true" following Access Jet's standard for booleans.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

This is your union query with my actual field and table names...

SELECT Name,[address line 1],[address line 2],city,st,zip, -1 AS pet, 0
AS equine, 0 AS [wild bird]
FROM [Pet List]
UNION ALL
SELECT Name,[address line 1],[address line 2],city,st,zip, 0 AS pet, -1
AS equine, 0 AS [wild bird]
FROM [equine list]
UNION ALL SELECT Name,[address line 1],[address line 2],city,st,zip, 0
AS pet, 0 AS equine, -1 AS [wild bird]
FROM [wild bird list];

It ran fine, however the select query stops with an error...

SELECT Name, [address line 1], [address line 2], city, st, zip, SUM(pet)
AS pet, SUM(equine) AS equine, SUM([wild bird]) AS [wild bird]
FROM qryunion
GROUP BY Name, [address line 1], [address line 2], city, st, zip;

Circular reference caused by alias 'pet' in query definitions SELECT
list. Is the error. Any idea how I messed it up?

SKB

 

[Tom Ellison]

Dear S. Burnett:

It doesn't like the alias being the same as an existing column name. Change
these:

AS pet AS petCt
AS equine AS equineCt
AS [wild bird] AS [wild birdCt]

Tom Ellison

Dear S. Burnett:

You can certainly create such a list from your 3 tables using a UNION
query:

SELECT Name, -1 AS List1, 0 AS List2, 0 AS List3
FROM Table1
UNION ALL
SELECT Name, 0 AS List1, -1 AS List2, 0 AS List3
FROM Table2
UNION ALL
SELECT Name, 0 AS List1, 0 AS List2, -1 AS List3
FROM Table3

Save this query. For reference below, I call it qryUnion

Now, you need to combine the rows having the same name (assuming this was
unique in the original tables).

SELECT Name, SUM(List1) AS List1,
SUM(List2) AS List2, SUM(List3) AS List3
FROM qryUnion
GROUP BY Name

I used values of -1 for "true" following Access Jet's standard for
booleans.

Please let me know if this helped, and if I can be of any other
assistance.

Tom Ellison

This is your union query with my actual field and table names...

SELECT Name,[address line 1],[address line 2],city,st,zip, -1 AS pet, 0 AS
equine, 0 AS [wild bird]
FROM [Pet List]
UNION ALL
SELECT Name,[address line 1],[address line 2],city,st,zip, 0 AS pet, -1 AS
equine, 0 AS [wild bird]
FROM [equine list]
UNION ALL SELECT Name,[address line 1],[address line 2],city,st,zip, 0 AS
pet, 0 AS equine, -1 AS [wild bird]
FROM [wild bird list];

It ran fine, however the select query stops with an error...

SELECT Name, [address line 1], [address line 2], city, st, zip, SUM(pet)
AS pet, SUM(equine) AS equine, SUM([wild bird]) AS [wild bird]
FROM qryunion
GROUP BY Name, [address line 1], [address line 2], city, st, zip;

Circular reference caused by alias 'pet' in query definitions SELECT list.
Is the error. Any idea how I messed it up?

SKB

 

[SKB]

Dear S. Burnett:

It doesn't like the alias being the same as an existing column name. Change
these:

AS pet AS petCt
AS equine AS equineCt
AS [wild bird] AS [wild birdCt]

Tom Ellison

Worked great!!!

Thank you Tom,

SKB