Rationalising Union Query output

[Roger]

I have merged data from three tables containing similar data into one using
a Union query. However, the data is not always identical and many fields
have no data:

tblOne
1 AAA CCC DDD
2 EEE FFF HHH
3 III JJJ KKK LLL

tblTwo
1 AAA BBB CCC DDD
2 EEE FFF GGG HHH
3 III KKK LLL

tblThree
1 AAA BBB CCC DDD
2 FFF HHH
3 III KKK LLL

The Union query has joined all the data together, but has regarded records
with one or more null fields as separate records (i.e. different to records
with populated fields). I need all the data from populated fields to be
returned, replacing the null fields where applicable.

Any ideas on how to do this. TIA.

Roger

 

[Tom Ellison]

Dear Roger:

In order to do what you say, there needs to be a column that is common
to the 3 sets. Your post shows a column with 1, 2, or 3 in it. Is
that a real column in the tables?

Are you working in a typical MDB using Jet?

I'm thinking you would not create the UNION, but rather JOIN the 3
tables on the first column. I'll call the columns Col1, Col2, Col3,
Col4, and Col5.

SELECT T1.Col1,
IIf(T1.Col2 IS NULL(IIfT2.Col2 IS NULL, T3.Col2, T2.Col2),
T1.Col2) AS Col2,
IIf(T1.Col3 IS NULL(IIfT2.Col3 IS NULL, T3.Col3, T2.Col3),
T1.Col3) AS Col3,
IIf(T1.Col4 IS NULL(IIfT2.Col4 IS NULL, T3.Col4, T2.Col4),
T1.Col4) AS Col4
FROM tblOne T1
INNER JOIN tblTwo T2 ON T2.Col1 = T1.Col1
INNER JOIN tblThree T3 ON T3.Col1 = T1.Col1

Are all the rows (as identified in Col1) in all 3 tables? If not, we
must construct a 3 way FULL OUTER JOIN, a bit of a mess for Jet to be
sure. If this is not a problem, the above may be pretty close.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Spencer (MVP)]

First question, how can you identify the record in table one that goes with the
record in table two that goes with the record in table three?

Second, what rules do you want to apply to combine the records? Lets say record
1 table one was
1 AAA KKK DDD

Then which would you want
1 AAA BBB KKK DDD
1 AAA BBB CCC DDD
1 AAA KKK DDD

How about if table three had record 1 were
1 AAA JJJ CCC DDD

Then what would you want returned?
1 AAA JJJ KKK DDD
1 AAA JJJ CCC DDD
1 AAA BBB KKK DDD
1 AAA BBB CCC DDD
1 AAA KKK DDD

 

[Tom Ellison]

Dear Roger:

John's post brings up an additional concern I did not address. If the
values for a column across the 3 tables are not the same, the code I
porposed would take the value from the first, lowest numbered table
and ignore the other two. As you example did not show this, perhaps
we are to assume that, when there is a value in more than one table
for the joined row and in the same column, the values are always the
same. You could test for this assumption with some query code now,
but that would not guarantee this fact for future data entry.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Roger]

I have merged data from three tables containing similar data into one
using

a Union query. However, the data is not always identical and many fields
have no data:

tblOne
1 AAA CCC DDD
2 EEE FFF HHH
3 III JJJ KKK LLL

tblTwo
1 AAA BBB CCC DDD
2 EEE FFF GGG HHH
3 III KKK LLL

tblThree
1 AAA BBB CCC DDD
2 FFF HHH
3 III KKK LLL

Thanks for the responses Tom and John.
The data is in a standard Access mdb database, with data from the three
original tables also now combined into one table via a union query.
To clarify, the numbers in column 1 are simply autonumber ID fields which
are not related to the data. Each table has a different number of records,
although all the fields types are identical.
There is no common keyfield between the three tables as the only unique ID
is a string containing all fields for each table. However, any of the
records can have some blank fields, which are not necessarily the same blank
fields as in (almost) matching records in the other tables.
I want to show a single record for each unique string (all fields) but fill
blank fields where data is available in the other table(s). If actual data
is different then a new record should be shown.

To use John's example:
Second, what rules do you want to apply to combine the records? Lets say
record
1 table one was
1 AAA KKK DDD

Then which would you want
1 AAA BBB KKK DDD *Yes*
1 AAA BBB CCC DDD *Yes*
1 AAA KKK DDD *No*

How about if table three had record 1 were
1 AAA JJJ CCC DDD

Then what would you want returned?
1 AAA JJJ KKK DDD *Yes*
1 AAA JJJ CCC DDD *Yes*
1 AAA BBB KKK DDD *Yes*
1 AAA BBB CCC DDD *Yes*
1 AAA KKK DDD *No*

Sorry if this is confusing but I am finding it difficult to explain exactly
what I require. Put simply, I want to rationalise the data as much as
possible by eliminating null fields where a similar field exists with data
in another table. However, I take the point that there may be logical
difficulties where data is slightly different between records.
Unfortunately, I have been supplied with this data from an external source
so was not responsible for its creation, I just have to try to crunch it
down in the most logical way possible! It's the old "don't start from here"
syndrome :-(

Hope this makes some sense!

Roger

 

[John Spencer (MVP)]

OK, I am stuck. I really don't understand what you are trying to do. At one
point, I thought you were trying to replace blanks in existing rows with data
from matching records. I was attempting to determine your matching schema and
if there were multiple matches what you would do to break the tie. Now you seem
to be creating additional records if there are "Ties".

Any solution I could come up with seems to involve some complex coding and quite
a bit of time to do. Perhaps Tom Ellison will see an easy solution to the problem.

Sorry, wish I could help.

 

[Roger]

OK, I am stuck. I really don't understand what you are trying to do. At one
point, I thought you were trying to replace blanks in existing rows with data
from matching records. I was attempting to determine your matching schema and
if there were multiple matches what you would do to break the tie. Now you seem
to be creating additional records if there are "Ties".

Any solution I could come up with seems to involve some complex coding and quite
a bit of time to do. Perhaps Tom Ellison will see an easy solution to the

problem.

Thanks for the reply, John.
You are right in that I would probably want to create additional records if
there are ties, although there would only be a small number of these in
reality - in most cases it is a matter of "filling the blanks".
Anyway, I have now processed the data semi-manually to try to resolve the
problem and crunched down the original 150,000 records in three tables to
68,000 records in one table.
My end-user seems happy with the output so don't think any further work is
needed on this.
Must admit that I initially thought this would be a fairly straightforward
problem to resolve in Access but this experience has taught me otherwise :-(
Thanks again to both you and Tom for your efforts on this - really
appreciated.

Best regards,

Roger