Solved

Forming a query using multiple sub queries

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

Expert Comment

by:Rey Obrero
ID: 38764621
can you post a sample result that youwant.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now