Only return unique row and make value 0 for duplicates

[AndrewCorkery]

Hi,

I have the following data and need to return just the one record for 2 fields and if it does have a duplicate still return the record but have a zero value in the numeric field.

Company Field1 Field2 Field3 Field4
A 12 121 122 232
B 11 144 431 332
C 10 101 182 543
A 12 132 552 987
B 14 119 212 432

As you can see there is a duplicate for Company A in Field1. How can I return a 0 in Field1 if a company already has that value? This needs to be done in a query.

Thanks

Andrew

 

[TorF]

Add a primary key (named id) into your table and use the following query:

SELECT a.Compagny,
iif(isnull(b.Field1),a.Field1,0) AS Field1,
a.Field2,
a.Field3,
a.Field4
FROM Table1 AS a
LEFT JOIN Table1 AS b
ON (a.Field1=b.Field1)
AND (a.id<>b.id);

 

[AndrewCorkery]

I cannot add a primary key as I am doing a query from an ODBC linked tables- is there VBA code that will do this?