How do I create a table of unique records from two or more tables

[Guest]

I saw some similar type questions posted -- I was able to make things work
using an append query, but it seems I should have a more efficient way to
solve my issue. I have about 20 tables -- 4 of the fields together in each
table make a unique record. A unique record in one table may be in all 20 of
the other tables or only in one of the tables. I want to create one table
that has all unique records and then puts info that is unique in each table
in a separate field in my "master table." I have been able to acccomplish
this using some append queries to create all of my unique records (w/o data
that is unique to each table) and then using a huge make table query that
uses the table created by my append query with one to many relationships
defined to each of the 20 separate tables to create my huge master table --
problem is that it takes my query about 45 minutes to run.

If that wasn't all that clear above then let me illustrate with the
following basic type example ...


Table 1
Person Color
Jeff Red
Jim Blue
Sheri Green
Ted Yello

Table 2
Person Cereal
Ed Cornflakes
Jeff Cheerios
Sheri Sugar Pops
Sam Oatmeal

Desired Master Table
Person Color Cereal
Jeff Red Cheerios
Jim Blue
Sheri Green Sugar Pops
Ted Yellow
Ed Cornflakes
Sam Oatmeal


Is there an easy answer? or are my append queries and update/make table
queries the only answers?

Thank you in advance ...

 

[Michel Walsh]

Hi,



Sure. First, make the mother of all person table:


SELECT Person FROM table1
UNION
SELECT Person FROM table2
UNION
....
SELECT Person FROM table20



Save that query, or make a table out of it, and, in that case, index its
field.



Next, it is a matter of outer join starting from this previous query/table.


SELECT a.Person, Table1.*, Table2.*, Tale3.*, ...
FROM motherOfAll As a LEFT JOIN table1 ON a.Person=table1.Person
LEFT JOIN table2 ON a.Person = table2.Person
LEFT JOIN ...
LEFT JOIN table20 ON a.Person = table20.Person


(You may need parentheses)



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi --

Thank you for your response. I really like the union query to come up with
unique values -- and I like the idea of using the query in my subsequent
query as opposed to creating a table since my data is somewhat dynamic.

On the right joins -- is there a more efficient way of doing that part? I
think I may be able to use multiple crosstab queries and then an update query
to add the multiple columns. I have 4 different fields that make up the
primary key so by the time it goes through 20 tables and 4 fields it ends up
taking about 45 minutes to process all of the inner joins. Any further
guidance

Thank you --

 

[Michel Walsh]

Hi,


Using a table, rather that embedding the union-query, could accelerate the
process, mainly if you index the field in the table (thing that we cannot do
on a query, since no index can be built on a query).


A crosstab create new fields based on DATA stored under a given field or in
a derived expression (the pivot). Since we already have the field name, and
we don't want to create field name from data, crosstab does not sound like
matching our problem "pattern".


dataField ' field name
jan
feb
mar ' data under the field name


then a crosstab with dataField as Pivot will create the new fields jan,
feb, mar.


Your problem already have all its required fields, right at the start. No
crosstab.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


Well, you can use a crosstab too, if first, we union all your tables.


First query:
------------------------
SELECT Person, Color As mText, "Color" as mPivot FROM table1
UNION ALL
SELECT Person, Cereal, "Cereal" FROM table2
UNION ALL
....
SELECT Person, Param20, "Param20" FROM table20
--------------------------

Having saved this query, say under the name qu, then

------------------------
TRANSFORM LAST(mText)
SELECT Person
FROM qu
PIVOT mPivot
---------------------------


should produce the desired result.


The first query "assimilate" the original fields as data under a field
called mPivot... and the crosstab "un-assimilate" those fields again. The
'data' to be displayed in the "grid" has also been reassembled under a
common field, mText. This process assumes that there is no duplicated Person
in any table (as does the first solution based on the concept of a full
outer join).



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,

Missing the GROUP BY in the last query


------------------------
TRANSFORM LAST(mText)
SELECT Person
FROM qu
GROUP BY Person
PIVOT mPivot