Option other than multiple unmatched queries (1 Viewer)

[cfp76]

Is there anyway to join 15 queries into 1 (all have the same data from a different source but need merged into 1 large query) rather than doing 12 unmatched queries?

Let's say I have 5 records in the first query, 5 records in the 2nd, and 3 records in the third - I want to end up with 1 query that has 13 records.

?

 

[plog]

A UNION query (http://www.techonthenet.com/sql/union.php) will definitely do this, but my guess is that those 15 queries can be combined into 1 that gives you what you want. Can you post the SQL for those 15 queries, or your database itself?

 

[cfp76]

Actually, I can't but here is the idea (and you may think I'm on the wrong path so PLEASE help me)

I have 1 table that has been imported

policynumber,data1,data2,data3

I need to report it as:
policynumber, data1
policynumber, data2
policynumber, data3

(provided the write string is listed in the data field - where the data lands is not consistant)

I broke it out into 3 queries

report policynumber and data1 if detail in data1 is present
report policynumber and data2 if ...
report policynumber and data3

so now I have 15 queries, ALL with the same fields that need to now be recombined to report the correct data.

Example:
original table

1234,LIFE14,AUTO10,LIFE3
4567,LIFE12
7894,AUTO30
6549,LIFE15
6541,AUTO10,LIFE34,AUTO56


Since I'm only looking to separate out the Life items, My queries would show the following

Query 1:
1234,LIFE14
4567,LIFE12
6549,LIFE15

Query2:
6541,LIFE34

Query 3:
1234,LIFE3

I think there has to be a cleaner way to break them out, but right now, this has been my only solution.

Now, with these 3 different queries, I want to create a query that returns all values found in Query 1, 2, and 3

Final Query:
1234,LIFE14
4567,LIFE12
6549,LIFE15
6541,LIFE34
1234,LIFE3

 

[plog]

I gotcha. You are basically trying to normalize an unnormalized table. For this you should use a UNION query (http://www.techonthenet.com/sql/union.php) to bring all those individual queries together.

 

[cfp76]

HA! Was just coming back here to say I figured it out *face palm*

It has been YEARS since I've had to do this....
found my instruction here: http://office.microsoft.com/en-us/a...-a-union-query-HA010341541.aspx#_Toc264361444

Thanks for walking through it with me though - I appreciate it!