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
Solved

How would you rewrite a query to yield 2 records which are the only 2 values for one of the fields selected ?

Posted on 2008-09-29
3
253 Views
Last Modified: 2013-12-05
I am developing an Access application using an ADP file with Access as the front end and SQL Server as the back end database.

I use the following query to extract records from a table and insert the result set into another table.

Do you know how I could rewrtie this query to yield ONLY 2 records based on the field [Legal Entity} ?

The field [Legal Entity] has only 2 distinct values.

For example: [Legal Entity] will have a value of either "Class E" or "Class O".

INSERT INTO ztbl_Source_Funds_Ungrouped_SSFIII (masterID, Fund, reportClientName, originalCommitment,
Redemptions, Reinvestments, Nav, Market_app_depre, Distributions, sourceid, reportDt, sourceFund, [Gross_Sales],
[Investor Name], GP, B.[Legal Entity], ExtractType)
SELECT A.id AS masterID, 'Special Situations Fund III','MS Employees' As reportClientName,
SUM(ISNULL(A.[original commitment],0)) As [Original Commitment],
SUM(ISNULL(B.Redemptions,0)) AS Redemptions,
0.0, 0.0, 0.0,
SUM(ISNULL(B.[Income Distributions],0)) + SUM(ISNULL(b.[Capital Distributions],0)) As Distributions,
[Account Name],
Max(filedate) As [Reporting Date],
'SSFIII' AS fundsource,
SUM(ISNULL(B.Contributions,0)) AS Contributions,
B.[Investor Name], B.GP, B.[Legal Entity], 'CLASSEO'
FROM ztbl_Master_Template A
INNER JOIN ztbl_Source_SSFIII B ON  
B.[Investor Name] = A.[Account Name] AND
A.[Legal Entity]    LIKE '%' + B.[Legal Entity] + '%'
AND B.GP NOT IN('MS GP','MS LP') AND (B.[Legal Entity] LIKE '%CLASS E%' OR B.[Legal Entity] LIKE '%CLASS O%')
GROUP BY B.[Legal Entity],B.[Investor Name], A.id, A.[Fund Name], A.[Account Name],GP

The output from this query could be used as input into another table, if that is easier.
0
Comment
Question by:zimmer9
3 Comments
 
LVL 10

Expert Comment

by:calpurnia
ID: 22601683
Please can you give us a sample of the data in your tables, and state what output you want from your query for that data.
0
 

Author Comment

by:zimmer9
ID: 22601690
Great idea. That's the way I like to work. Give me some time. Thanks.
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 22605041
if you want only 2 records based on field "legal entity", then "legal entity" must be the only GROUP BY field in your quesry; all others have to be aggregates. That is, now you have

select ...
GROUP BY B.[Legal Entity],B.[Investor Name], A.id, A.[Fund Name], A.[Account Name],GP

it should become

GROUP BY B.[Legal Entity]

of course you have to decide what to do in case

legal entity           investor name
class e                 john
class e                 bob

i.e. how to aggregate investor name into one record. The same also applies to  A.id, A.[Fund Name], A.[Account Name],GP
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

790 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