Combine Tables?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SKB - 15 Nov 2005 17:21 GMT

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

Reply to this Message

Tom Ellison - 15 Nov 2005 18:23 GMT

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

>I asked this question in the 'getting started' forum. Maybe the wrong
>place, thought I should try here also.
[quoted text clipped - 32 lines]
>
> SKB

Reply to this Message

SKB - 15 Nov 2005 19:18 GMT

> Dear S. Burnett:
>
[quoted text clipped - 61 lines]
>>
>>SKB

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

Reply to this Message

Tom Ellison - 15 Nov 2005 21:13 GMT

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:
>>
[quoted text clipped - 89 lines]
>
> SKB

Reply to this Message

SKB - 15 Nov 2005 22:19 GMT

> Dear S. Burnett:
>
[quoted text clipped - 6 lines]
>
> Tom Ellison

Worked great!!!

Thank you Tom,

SKB

Reply to this Message

Jeff Boyce - 15 Nov 2005 18:48 GMT

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>

>I asked this question in the 'getting started' forum. Maybe the wrong
>place, thought I should try here also.
[quoted text clipped - 32 lines]
>
> SKB

Reply to this Message