Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Forming a query using multiple sub queries

Posted on 2013-01-10
5
Medium Priority
?
318 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
  • 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 2000 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

916 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