Solved

Forming a query using multiple sub queries

Posted on 2013-01-10
5
316 Views
Last Modified: 2013-01-10
Hi Everyone,

Can someone assist me in forming a query.  I've included a sample database.

My table has multiple fields however I want to use the fields BI_Part_nr, Competitor, Comp_Item_number, comp_price to create a query.

The table has multiple records which I want to consolidate to

BI_Part_nr (Group), competitor (Where Camozzi or Cmatic), field[comp_price_Camozzi]],field[comp_price_Cmatic]

I was able to create sub queries, however I coudn't get all the data to populate in one record tied to BI_Part_Nr.

Thanks!

MC
sample.accdb
0
Comment
Question by:MCaliebe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38764621
can you post a sample result that youwant.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38764667
try this query


SELECT xref.BI_Part_nr, Sum(IIf([Competitor]="Camozzi",[comp_price],0)) AS comp_price_Camozzi, Sum(IIf([Competitor]="Cmatic",[comp_price],0)) AS comp_price_Cmatic
FROM xref
GROUP BY xref.BI_Part_nr;

or this one

SELECT xref.BI_Part_nr, xref.Comp_Item_nr, Sum(IIf([Competitor]="Camozzi",[comp_price],0)) AS comp_price_Camozzi, Sum(IIf([Competitor]="Cmatic",[comp_price],0)) AS comp_price_Cmatic
FROM xref
GROUP BY xref.BI_Part_nr, xref.Comp_Item_nr;
0
 

Author Comment

by:MCaliebe
ID: 38764680
For instance, record 1368 and 2981have the same BI PN, PCNB2405-04-04, however a one has a price for Camozzi (1.98)  and one price for Cmatic (1.89)

I'd like to see the result as

BI_Part_nr, CamozziPrice, CMaticPrice
PCNB2405-04-04, 1.98, 1.89
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38764692
did you try the query i posted?
0
 

Author Closing Comment

by:MCaliebe
ID: 38764699
Thanks Cap.  The first query is what I wanted.

I'll pick it apart and figure out how to do it next time!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question