Combine records in query

[thart21]

I'm completely lost on this one. I am querying from linked Oracle tables and cannot change any of the data.
I am grouping by "Prod_Code". The problem is that I have one field in my table that may or may not have data, "Country".
What I am getting is one record for each "Prod_Code" that does have data in "Country" and another for those where the field "Country" is blank.
I am required to show only one record in my query per "Prod_Code" and need to ignore the "Country" field in order to combine the records.
Trying to do something like
IIf "Prod_Code" is duplicate, THEN ignore where "Country" Is Null.
Thanks!

 

[Golom]

Maybe a UNION query

Select ... some fields ...
From SomeTable
Group By Prod_Code
Having Count(*) = 1 AND Country IS NULL

UNION

Select ... some fields ...
From SomeTable
Group By Prod_Code
Having Count(*) > 1 AND NOT IsNull(Country)

 

[thart21]

Thanks, trying it out and getting "Syntax error in HAVING clause". I've used my new field names, different from original post but same result desired.

This should combine all of the records between the 2 queries resulting in only on record per DEV_CD, correct?

SELECT DEV_CD, DEV_PROJ_ALIAS, STY_GRP_CD,DEVL_NM,Retail,Whsl,[New Or Carry],GRP_CAT_DESC, SAP_CAT_LONG_DESC,SAP_SUB_CAT_LONG_DESC, GBL_STATUS,[MLP FLAG], GFP_PRODUCT.GNDR_AGE_DESC, GFP_PRODUCT.PROD_TYP_GRP_DESC, GFP_PRODUCT.PROD_TYP_DESC, P_SILO_DESC,SPORT_ACTVTY_DESC,[PRIMARY PLATFORM GRP DESC],[SELL SEASON], DEV_SEASON, FCTY_CD
FROM qSourceData_AllSeasons_P_Silo_NULL
Having Count(*) = 1 AND P_SILO_DESC IS NULL
GROUP BY DEV_CD

UNION ALL

SELECT DEV_CD, DEV_PROJ_ALIAS, STY_GRP_CD,DEVL_NM,Retail,Whsl,[New Or Carry],GRP_CAT_DESC, SAP_CAT_LONG_DESC,SAP_SUB_CAT_LONG_DESC, GBL_STATUS,[MLP FLAG], GFP_PRODUCT.GNDR_AGE_DESC, GFP_PRODUCT.PROD_TYP_GRP_DESC, GFP_PRODUCT.PROD_TYP_DESC, P_SILO_DESC,SPORT_ACTVTY_DESC,[PRIMARY PLATFORM GRP DESC],[SELL SEASON], DEV_SEASON, FCTY_CD
FROM qSourceData_AllSeasons
Having Count(*) > 1 AND NOT IsNull(P_SILO_DESC)
GROUP BY DEV_CD

Thanks!

 

[dhookom]

The HAVING phrase comes after the GROUP BY in queries. Every field in the the SELECT should be in the GROUP BY unless it is an aggregate like Sum() , Count(), Min(), ...

Duane
Hook'D on Access
MS Access MVP