I've been working on this problem for a long while now and can't seem to find an acceptable solution. The problem is that I have many different sources of data (at least 20) that I've boiled down to have the same six or seven columns of data that I want. My goal is to create a "universe" that includes all the items listed on each source of data. Most of the data is listed on multiple sources, and some only on one or two sources. I need to keep track of what sources have what items as well. Ultimately my goal is a table with every item in the "universe" in one column, followed by a series of columns marking each source that it was identified in (i.e. the column for Source 12 gets an "x" if Source 12 contains the current item). In addition, there are columns that have some information about the items (their official name). All of the items have a unique number but they have varying names, so in the final table I produce I need to list the name from the source with highest priority. So, if the item is on Source 1, use the name listed in Source 1; if not, but it is on Source 2, use the name listed for the item in Source 2; and so on.
I'm working on this in Microsoft Access and until recently have been able to get away with joining all of my many source tables on the unique number, and doing a big nested Iif statement to get the name in order of the sources' priority, e.g.,
Iif(Not Null(Source1.ItemName), Source1.ItemName, Iif(NotNull(Source2.ItemName), Source2.ItemName, Iif(...... ))))
However I've now reached a problem as I've been adding more sources. Access limits how nested the Iif statements can be. So, I tried several different solutions. I tried using VBA to run a select query on each source table in order of priority until it found a record that matched the current unique number and returned the item's name listed in the source of highest priority in which it was found. The problem is that this function needs to run several times for each unique number, as there are a few properties in addition to the item name. This results in a very slow query.
Another alternative I tried that worked well (but failed later for another reason I'll explain) was to do a self join. I had one query (query1) do a union of the unique number, item name, and other properties from all the sources as well as add a column for the source rank. Then another query (query2) had a subquery which grouped by the unique number and chose the minimum source rank, which was inner joined onto query1 to get the rest of the record listed in query 1 for the highest ranked source. The problem though is that in my final query I need to take that information, and add in the flags that note what sources the item is found in, and this requires about 20 left joins. In addition, some sources have data that others do not, so I can't just add those columns to the query1 union. So, while I solved the multiple nested Iif problem, I still have a problem with requiring too many joins.
What can I do to get a final table with all of the item properties as well as source columns?
In order to condense columns together you can use the SQL standard function
Access doesn't support
COALESCE, but if does have a
NZ function. Sadly the latter only takes 1 parameter, not an unlimited number like
Still if you want everything in one column, you can do a large
UNION like so.
SELECT a AS OneColumnToRuleThemAll FROM ( SELECT col1 as a FROM a UNION SELECT col2 as a FROM b UNION SELECT ....) S
This will put everything in one column.