• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

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
0
MCaliebe
Asked:
MCaliebe
  • 2
  • 2
1 Solution
 
karunamoorthyCommented:
you can convert your db into mdb and resend pl
0
 
Rey Obrero (Capricorn1)Commented:
test this query, see if this is what you want


SELECT xref.BI_Part_nr, Sum(IIf([Competitor]="Eat",[Comp_Price],0)) AS EAT_Price, Sum(IIf([Competitor]="FITU",[Comp_Price],0)) AS FITU_Price, Sum(IIf([Competitor]="HOR",[Comp_Price],0)) AS HOR_Price, Sum(IIf([Competitor]="PAR",[Comp_Price],0)) AS PAR_Price, Sum(IIf([Competitor]="PC",[Comp_Price],0)) AS PC_Price, Sum(IIf([Competitor]="TOM",[Comp_Price],0)) AS TOM_Price, Sum(IIf([Competitor]="SSP",[Comp_Price],0)) AS SSP_Price, Sum(IIf([Competitor]="WW",[Comp_Price],0)) AS WW_Price
FROM xref
GROUP BY xref.BI_Part_nr;
sampleRev4.accdb
0
 
MCaliebeAuthor Commented:
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?
0
 
Rey Obrero (Capricorn1)Commented:
<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
0
 
MCaliebeAuthor Commented:
<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 >>
I thought using SUM would be dangerous if two records exists, then the each price would be incorrect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now