asked on

MS Access query to separate consolidated data into a single record

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.

Thanks!
sampleRev.accdb

karunamoorthy

you can convert your db into mdb and resend pl

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MCaliebe

ASKER

Cap,

This appears to work. What is this query doing? Is it looking for all the EAT, FITU..ect prices first, and then grouping?

Is using SUM dangerous, or is it asssumed there is only one price/vendor record? BI_Part_nr field is not a Primary Key so, I suppose the chance does exisit that a duplicate BI_Part_nr/Vendor record could exists...and that would total the two prices, correct?

Rey Obrero (Capricorn1)

<Is using SUM dangerous, or is it asssumed there is only one price/vendor record? >
NO, why would it be?

<BI_Part_nr field is not a Primary Key so, I suppose the chance does exisit that a duplicate BI_Part_nr/Vendor record could exists...and that would total the two prices, correct? >

That is Correct

MCaliebe

ASKER