Sub-Query Madness

[cheuschober]

Sub-Query Madness - Addendum Help!

So, here is my problem--any help to point in the right direction would be appreciated...

I have a query taking a multitude of records from a table that SHOULD have a unique identifier in the OSS key (which is a three-field primary key).

However, due to bad data entry, OSS has been entered multiple times and now I need to clean some of the data.

Basically, my correct OSS would have the maximum value of all of any one unique OSS in the three fields attatched to the OSS.

So, Maxfield1perOSS, Maxfield2perOSS, and Maxfield3perOSS, respectively.

I know that subqueries have the answer, but I'm not getting the combination right!

Any help would be appreciated!

Thanks,
~Chad

ADDENDUM -- This is how far I've gotten and as far as I can tell, Logically it SHOULD work, but instead it actually manages to crash all of access every time it's run... PLS HELP!

SELECT
C1.OSS1,
C1.OSS2,
C1.OSS3,

(SELECT Max(field1) FROM qrySource AS C2 WHERE (C1.OSS1 = C2.OSS1) AND (C1.OSS2 = C2.OSS2) AND (C1.OSS3 = C2.OSS3)) AS Max_Field1,

(SELECT Max(field2) FROM qrySource AS C2 WHERE (C1.OSS1 = C2.OSS1) AND (C1.OSS2 = C2.OSS2) AND (C1.OSS3 = C2.OSS3)) AS Max_Field2,

(SELECT Max(field3) FROM qrySource AS C2 WHERE (C1.OSS1 = C2.OSS1) AND (C1.OSS2 = C2.OSS2) AND (C1.OSS3 = C2.OSS3)) AS Max_Field3

FROM qrySource AS C1;

 

[RV]

Could be that I misunderstand your question but seems to could do with a simple straight forward basic query:

SELECT Max(field1), MAX(field2), MAX(field3)
FROM qrySource
WHERE C1.OSS1 = C2.OSS1
AND C1.OSS2 = C2.OSS2
AND C1.OSS3 = C2.OSS3

RV