how to consolidate or merge two rows of data into one row in query result

[tdovertonjr]

After i run the query below i have two rows of data. The two rows arise because [CATEGORY DESCRIPTION] has many different values depending on TYPE_DESCRIPTION and other fields. A portion of the output is

Account    Category Description     ADJ_CD 
123        Average                   .82
123        B+                       1.41

I NEED

Account    Category Description    ADJ_CD 
123        B+                        .82

I also tried other queries and TRANSFORM with PIVOT.

The SQL View for the query is:

SELECT real_acct.ACCOUNT, structural_elem1.[CATEGORY_DESCRIPTION] AS Expr1, real_acct.SITE_ADDR_1
   , [NEIGHBORHOOD_CODE] & "/ " & [NEIGHBORHOOD_GROUP] AS [NEIGHBORHOOD CODE/GR], structural_elem1.STATE_CLASS_CODE, real_acct.YR_IMPR, real_acct.TOTAL_BUILDING_AREA, structural_elem1.CATEGORY_DESCRIPTION, structural_elem1.ADJ_CD, real_acct.LAND_VALUE, real_acct.IMPROVEMENT_VALUE, real_acct.EXTRA_FEATURES_VALUE, real_acct.TOTAL_MARKET_VALUE
FROM real_acct INNER JOIN structural_elem1 ON real_acct.ACCOUNT = structural_elem1.ACCOUNT
WHERE (([structural_elem1]![ACCOUNT]=[real_acct]![ACCOUNT] And [TYPE_DESCRIPTION]="Grade Adjustment") AND ((real_acct.YR_IMPR)>"1997" And (real_acct.YR_IMPR)<"2007") AND ((real_acct.TOTAL_BUILDING_AREA)>"2100" And (real_acct.TOTAL_BUILDING_AREA)<"2300") AND ((structural_elem1.STRUCTURE_TYPE)="GRD" Or (structural_elem1.STRUCTURE_TYPE)="CDU") AND ((structural_elem1.TYPE_DESCRIPTION)="Grade Adjustment" Or (structural_elem1.TYPE_DESCRIPTION)="Cond / Desir / Util") AND ((structural_elem1.CATEGORY_DESCRIPTION)="B" Or (structural_elem1.CATEGORY_DESCRIPTION)="A-" Or (structural_elem1.CATEGORY_DESCRIPTION)="B+" Or (structural_elem1.CATEGORY_DESCRIPTION)="Very Poor" Or (structural_elem1.CATEGORY_DESCRIPTION)="Poor" Or (structural_elem1.CATEGORY_DESCRIPTION)="Average" Or (structural_elem1.CATEGORY_DESCRIPTION)="Fair" Or (structural_elem1.CATEGORY_DESCRIPTION)="Very Good" Or (structural_elem1.CATEGORY_DESCRIPTION)="Excellent" And [STRUCTURE_TYPE]="CDU" Or (structural_elem1.CATEGORY_DESCRIPTION)="GRD" And "«Expr» [structural_elem1]![TYPE_DESCRIPTION]"="Cond / Desir / Util" Or (structural_elem1.CATEGORY_DESCRIPTION)="Grade Adjustment")) OR (((real_acct.YR_IMPR)>"1997" And (real_acct.YR_IMPR)<"2007") AND ((real_acct.TOTAL_BUILDING_AREA)>"2100" And (real_acct.TOTAL_BUILDING_AREA)<"2300") AND ((structural_elem1.STRUCTURE_TYPE)="GRD" Or (structural_elem1.STRUCTURE_TYPE)="CDU") AND ((structural_elem1.TYPE_DESCRIPTION)="Grade Adjustment" Or (structural_elem1.TYPE_DESCRIPTION)="Cond / Desir / Util") AND ((structural_elem1.CATEGORY_DESCRIPTION)="B" Or (structural_elem1.CATEGORY_DESCRIPTION)="A-" Or (structural_elem1.CATEGORY_DESCRIPTION)="B+" Or (structural_elem1.CATEGORY_DESCRIPTION)="Very Poor" Or (structural_elem1.CATEGORY_DESCRIPTION)="Poor" Or (structural_elem1.CATEGORY_DESCRIPTION)="Average" Or (structural_elem1.CATEGORY_DESCRIPTION)="Fair" Or (structural_elem1.CATEGORY_DESCRIPTION)="Very Good" Or (structural_elem1.CATEGORY_DESCRIPTION)="Excellent" And [STRUCTURE_TYPE]="CDU" Or (structural_elem1.CATEGORY_DESCRIPTION)="GRD" And "«Expr» [structural_elem1]![TYPE_DESCRIPTION]"="Cond / Desir / Util" Or (structural_elem1.CATEGORY_DESCRIPTION)="Grade Adjustment"));

[jforbes]

If you are getting multiple rows for each real_acct.ACCOUNT, then it means that there are multiple rows available in structural_elem1 that have an ACCOUNT that match against real_acct.ACCOUNT

If there is a One-to-Many relationship for FROM real_acct INNER JOIN structural_elem1 ON real_acct.ACCOUNT = structural_elem1.ACCOUNT and you are expecting a One-to-One, there is probably a problem with the Data/Data Structure and not the SQL Statement.

If there is really a One-to-Many and you are attempting to only select a single record from structural_elem1 for each ACCOUNT, then you'll need to re-evaluate what it is that you are trying to accomplish. Mainly because, how is SQL supposed to know which record to return of the many available?

[tdovertonjr]

Thank you. You are correct the structural_elem1 table contains several records for each account. I would have used a different structure, but the table was created by a tax appraisal district and it contains about 2 million accounts.

The query returns two rows for each account that include the data or values that I need.

I just need to get the values to one row. Maybe I can concatenate the two fields to a new table.

[zmbd]

What is it that you really need from the field [CATEGORY_DESCRIPTION] ?

In the example You have [CATEGORY_DESCRIPTION]="Average" which is in
WHERE ... Or (structural_elem1.CATEGORY_DESCRIPTION)="Average"
Yet you say you only want [CATEGORY_DESCRIPTION]="B-"
WHERE ... Or (structural_elem1.CATEGORY_DESCRIPTION)="B+

Brain storming here - nothing tried nor even closly evaluated for feasibility:

1st thought, in your very long WHERE clause eliminate the conditions returning the undesired [CATEGORY_DESCRIPTION] values. However, from what I am reading here, it appears that there are two separate paradigms being used for the [CATEGORY_DESCRIPTION] values.

2nd thought, build a second query based on the first query that returns only the desired values

SELECT (fields here) 
FROM (your Query) 
WHERE ([CATEGORY_DESCRIPTION] 
   IN (list your values); 

You could also use the OR and build a series of

WHERE(([CATEGORY_DESCRIPTION]="A+") OR
   [CATEGORY_DESCRIPTION]="B-" OR ...

Not sure which would be better for speed against that many records...

3rd thought, "two separate paradigms being used for the [CATEGORY_DESCRIPTION] values"
Use a cross reference table relating the values against a standard value then returning the standard value. Not sure if this is any better than the 2nd thought.

[tdovertonjr]

The query returns two rows for each account. Only one of values for ADJ_CD and CATEGORY_DESCRIPTION valid for what I need. I don't know or understand how to combine the sub-queries or create a new table with the correct results in one row.


For example, this subquery will return the correct ADJ_CDU, but the wrong CATEGORY_DESCRIPTION.

SELECT RA_SUB.[ACCOUNT /ADDRESS], RA_SUB.YR_IMPR, RA_SUB.ADJ_CD AS [Cdu], RA_SUB.CATEGORY_DESCRIPTION AS [Grade Adjustment]
FROM RA_SUB
WHERE RA_SUB.TYPE_DESCRIPTION IN ('Cond / Desir / Util');

For example, this subquery will return the correct CATEGORY_DESCRIPTION, but the wrong ADJ_CDU.

SELECT RA_SUB.[ACCOUNT /ADDRESS], RA_SUB.YR_IMPR, RA_SUB.ADJ_CD AS [Cdu], RA_SUB.CATEGORY_DESCRIPTION AS [Grade Adjustment]
FROM RA_SUB
WHERE RA_SUB.TYPE_DESCRIPTION IN ('Grade Adjustment');

I need the correct value of ADJ_CD and CATEGORY_DESCRIPTION in the same row.

I also shortened the query to:

SELECT [real_acct]![ACCOUNT] & " / " & [SITE_ADDR_1] AS [ACCOUNT /ADDRESS], [NEIGHBORHOOD_CODE] & " / " & [NEIGHBORHOOD_GROUP] & " / " & [STATE_CLASS_CODE] AS [NEIGHBORHOOD CODE/GR/CLASS], real_acct.YR_IMPR, real_acct.TOTAL_BUILDING_AREA, structural_elem1.ADJ_CD, structural_elem1.CATEGORY_DESCRIPTION, real_acct.LAND_VALUE, real_acct.IMPROVEMENT_VALUE, real_acct.EXTRA_FEATURES_VALUE, real_acct.TOTAL_MARKET_VALUE, structural_elem1.TYPE_DESCRIPTION, structural_elem1.STRUCTURE_TYPE
FROM real_acct INNER JOIN structural_elem1 ON real_acct.ACCOUNT = structural_elem1.ACCOUNT
WHERE (((real_acct.YR_IMPR)>"1997" And (real_acct.YR_IMPR)<"2007") AND ((real_acct.TOTAL_BUILDING_AREA)>"2100" And (real_acct.TOTAL_BUILDING_AREA)<"2300") AND ((structural_elem1.CATEGORY_DESCRIPTION)="A-" Or (structural_elem1.CATEGORY_DESCRIPTION)="B+" Or (structural_elem1.CATEGORY_DESCRIPTION)="B") AND ((structural_elem1.TYPE_DESCRIPTION)="Grade Adjustment" Or (structural_elem1.TYPE_DESCRIPTION)="Cond / Desir / Util") AND ((structural_elem1.STRUCTURE_TYPE)="GRD" Or (structural_elem1.STRUCTURE_TYPE)="CDU")) OR (((real_acct.YR_IMPR)>"1997" And (real_acct.YR_IMPR)<"2007") AND ((real_acct.TOTAL_BUILDING_AREA)>"2100" And (real_acct.TOTAL_BUILDING_AREA)<"2300") AND ((structural_elem1.CATEGORY_DESCRIPTION)="Very Poor" Or (structural_elem1.CATEGORY_DESCRIPTION)="Poor" Or (structural_elem1.CATEGORY_DESCRIPTION)="Fair" Or (structural_elem1.CATEGORY_DESCRIPTION)="Average" Or (structural_elem1.CATEGORY_DESCRIPTION)="Good" Or (structural_elem1.CATEGORY_DESCRIPTION)="Very Good" Or (structural_elem1.CATEGORY_DESCRIPTION)="Excellent") AND ((structural_elem1.TYPE_DESCRIPTION)="Grade Adjustment" Or (structural_elem1.TYPE_DESCRIPTION)="Cond / Desir / Util") AND ((structural_elem1.STRUCTURE_TYPE)="GRD" Or (structural_elem1.STRUCTURE_TYPE)="CDU"));