Solved

Forming a query using multiple sub queries

Posted on 2013-01-10
5
315 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

734 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