I've been sub-query challenged lately and I believe this is another example. My sample DB has a XRef table with a number of fields. Field 3, [BI_Part_nr] needs to be grouped, and then I need to run through the table multiple times for every competitor (8 are included) and pull out a comp_price where the BI_Part_nr is equal to the items listed in the grouped field.
My query should look like
BI_Part_Nr, EAT_Price, FITU_Price, HOR_Price, ect...
I tried doing it with multipe table, however I couldn't have a "where" statement for each table. Perhaps this is done with multiple sub queries, however I didin't know how to tie them back to the grouped BI_Part_nr.