Extracting common ids across 12 tables and assoc data

[Max]

I have 12 tables named as: Sep06, Oct06, .. Aug07
(each table is ~ 30k lines)

In each table there are 2 fields of interest: CINU, AUM
(the 2 fields are named identically in all tables)

CINU contains ids (alphanumerics). Ids are unique within each month's table,
but each month's table may contain slightly different ids (some common ids,
some different ids)

AUM contains numbers: negatives, zeros, positives

I would like to query out a col list of distinct CINU which is present
across each of the 12 tables (ie the common ids) for which AUM is <> zero,
and to extract the AUMs from each of the 12 tables into cols next to the
distinct CINU, placed in chrono sequence: Sep06's AUM, Oct06's AUM, ...
Aug07's AUM

Any insights appreciated. Thanks.
I'm new to Access (using Access 2003)

 

[Dale Fye]

Max,

This would be significantly easier if your data was normallized and instead
of 12 tables you had a single table with 4 columns (YearField, MonthField,
CINU, and AUM). You can develop a query that will present your data in this
format by creating a UNION query. Unfortunately, this type of query is only
available in the SQL view, but it looks like:

SELECT 2006 as YearField, 9 as MonthField, CINU, AUM
FROM [Sep06]
UNION ALL
SELECT 2006 as YearField, 10 as MonthField, CINU, AUM
FROM [Oct06]
UNION ALL
....

Union queries require that each element contains the same number of fields,
and the fields have to be listed in the same order. Once you have this
union query working, you should save it as qry_NormalData or something like
that.

Next, create a query to identify the unique CINU values that are present in
all 12 months. This query includes a nested subquery which selects records
where AUM <> 0, and then groups them by CINU, YearField, and MonthField. At
this point, in includes all of the possible combinations of CINU, YearField,
and Month field. In order to identify only those records where the CINU
value exists in all 12 months, you have to use the subquery as the source
for another query. You could do ths by saving the subquery and using it as
the datasource for a third query, or you could just wrap it in parenthesis,
name the subQuery (I named mine "SubQuery") and then group it just by the
CINU value and count the number of months that have that value. If it is
not present in all 12 months, then exclude it. This query (lets call it
qry_CINU_values) will look something like:

SELECT CINU
FROM (
SELECT CINU, YearField, MonthField
FROM qry_NormalData
WHERE AUM <> 0
GROUP BY CINU, YearField, MonthField) as SubQuery
GROUP BY CINU
HAVING COUNT(MonthField) = 12

It looks like you are going to want some sort of crosstab query using CINU
as a row header and some combination of YearField, MonthField, and AUM as
the column headers. However, at this point your explaination gets a little
fuzzy, so you will probably need to provide a little more detail about what
you expect the column headers to look like, and what value you want to show
up in the matrix. It might be helpful if you provided a sample of data for
a single CINU and what you want the output to look like.

HTH
Dale

 

[Max]

Many thanks for your comprehensive insights, Dale.
Illustrated below is a sample of what I have and need


Sep06
CINU AUM
xx1234 100
xx1235 0
xx1236 200

Oct06
CINU AUM
xx1235 100
xx1236 500
xx1234 200

Nov06
CINU AUM
xx1236 100
xx1234 300
xx1235 200
xx1237 500

Desired query output
CINU Sep06 Oct06 Nov06
xx1234 100 200 300
xx1236 200 500 100


Notes:
xx1235
should not appear as it has aum =0 in Sep06,
albeit it is common in all month tables

xx1237
should not appear as it is not found in every month table,
although aum <>0

As to your comment:

Union queries require that each element contains the same number of
fields, and the fields have to be listed in the same order.

The 12 tables (Sep06, Oct06,..) may contain different number of fields, but
each table will have CINU and AUM amongst others. The 2 fields CINU, AUM
may not be in the same position.

Thanks

 

[Michel Walsh]

Make a first query to normalize your data, into one single table (which will
be a query)

SELECT "Sep06" AS dt, cinu, aum FROM Sep06
UNION ALL
SELECT "Oct06", cinu, aum FROM Oct06
UNION ALL
....



If that query is saved under the name qu, then, use a crosstab to
denormalize the data, as your desired result:

TRANSFORM LAST(aum)
SELECT cinu
FROM qu
GROUP BY cinu
PIVOT dt



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

If the CINU has to be present in every table then you just need to join all
twelve tables in one query based on CINU and then test AUM to see if it is
not zero in each table (using and criteria).

The SQL of such a query would look like the following for the first three
months
SELECT Sep06.CINU
, Sep06.AUM as SeptAum
, Oct06.AUM asOctAum
, Nov06.AUM as NovAum
FROM (Sep06 INNER JOIN Oct06
ON Sep06.CINU = Oct06.CINU)
INNER JOIN Nov06
ON Oct06.CINU = Nov06.CINU
WHERE Sep06.AUM <> 0 AND Oct06.Aum <> 0 AND Nov06.AUM<> 0



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Max]

Thanks for your thoughts, Michel.
I'll try out all suggestions offered.

 

[Max]

Thanks for your thoughts, John.
I'll try out all suggestions offered.