Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Forming a query using multiple sub queries

Posted on 2013-01-10
5
Medium Priority
?
319 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
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 …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

572 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