Need help with delete query

[Adam]

Hello, I have a 2 column file that looks like this:

VALUE1 | VALUE2
12 | 7
10 | 7
9 | 8
10 | 8
11 | 8
6 | 9
8 | 9
9 | 9
What I need to do is delete any row when there is a more than one value in
VALUE2 that is the same and only keep the incidence with the highest value
in VALUE1.

Hmm.. that doesn't explain very well. Maybe this will help..

For example the result on the above should wind up being:

VALUE1 | VALUE2
12 | 7
11 | 8
9 | 9

So I wind up with only unique values in VALUE2 and the ones that are tossed
are the duplicates with VALUE1 values lower than the highest one found.

Does that make sense? And if so how can I do that?

Thanks.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

2 ways:

Way 1:

Run a make-table query that gets the data you want, then drop the
original table & rename the new table the old table.

Make-table query:

SELECT Max(Value1) As MaxValue1, Value2 INTO NewTableName
FROM OldTableName
GROUP BY Value2

DDL statement to drop the old table:

DROP TABLE OldTable

Rename the table using VBA:

DoCmd.Rename "OldTableName", acTable, "NewTableName"

====================

Way 2 (untested):

Run query like this:

DELETE *
FROM TableName
WHERE NOT EXISTS (SELECT Max(Value1) As MaxValue1, Value2
FROM TableName
GROUP BY Value2

To see if this works substitute SELECT for DELETE, run it & see if the
results are what you want deleted.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDPGaoechKqOuFEgEQLiigCfT1cAlWsdarZDvrxT1mjLQz oK2JgAoOvh
91SgP58XdPJMyXfXWjMPMpDL
=W8Ug
-----END PGP SIGNATURE-----
Adam wrote:

Hello, I have a 2 column file that looks like this:

VALUE1 | VALUE2
12 | 7
10 | 7
9 | 8
10 | 8
11 | 8
6 | 9
8 | 9
9 | 9
What I need to do is delete any row when there is a more than one value in
VALUE2 that is the same and only keep the incidence with the highest value
in VALUE1.

Hmm.. that doesn't explain very well. Maybe this will help..

For example the result on the above should wind up being:

VALUE1 | VALUE2
12 | 7
11 | 8
9 | 9

So I wind up with only unique values in VALUE2 and the ones that are tossed
are the duplicates with VALUE1 values lower than the highest one found.

Does that make sense? And if so how can I do that?

[Mark]

Along similar lines, can you suggest a way to merge 5 records in a table into
one master record containing all the data in the fields of the five records?

Each of the 5 records contains firstname, lastname, city, state and zip in
separate fields. There is no primary key in the records. All the records contain
the same 15 other fields. In each of these 15 fields (for example SSN) one or
more records may contain data or that field could be blank in all records. For
the moment we will assume that if multiple records contain data in a field,
there are no conflicts between the records. Do you have any suggestion on how to
merge all five records into one master record where each of the 15 fields would
contain data if the data for that field existed in any of the 5 records?

Thanks for any help you can give me!

Mark
"MGFoster" <[e-mail]> wrote in message
news:rD*****************@newsread1.news.pas.earthl ink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

2 ways:

Way 1:

Run a make-table query that gets the data you want, then drop the
original table & rename the new table the old table.

Make-table query:

SELECT Max(Value1) As MaxValue1, Value2 INTO NewTableName
FROM OldTableName
GROUP BY Value2

DDL statement to drop the old table:

DROP TABLE OldTable

Rename the table using VBA:

DoCmd.Rename "OldTableName", acTable, "NewTableName"

====================

Way 2 (untested):

Run query like this:

DELETE *
FROM TableName
WHERE NOT EXISTS (SELECT Max(Value1) As MaxValue1, Value2
FROM TableName
GROUP BY Value2

To see if this works substitute SELECT for DELETE, run it & see if the
results are what you want deleted.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDPGaoechKqOuFEgEQLiigCfT1cAlWsdarZDvrxT1mjLQz oK2JgAoOvh
91SgP58XdPJMyXfXWjMPMpDL
=W8Ug
-----END PGP SIGNATURE-----
Adam wrote:

Hello, I have a 2 column file that looks like this:

VALUE1 | VALUE2
12 | 7
10 | 7
9 | 8
10 | 8
11 | 8
6 | 9
8 | 9
9 | 9
What I need to do is delete any row when there is a more than one value in
VALUE2 that is the same and only keep the incidence with the highest value
in VALUE1.

Hmm.. that doesn't explain very well. Maybe this will help..

For example the result on the above should wind up being:

VALUE1 | VALUE2
12 | 7
11 | 8
9 | 9

So I wind up with only unique values in VALUE2 and the ones that are tossed
are the duplicates with VALUE1 values lower than the highest one found.

Does that make sense? And if so how can I do that?

[Rich P]

DoCmd.RunSQL "Delete * From tbl1 Where Exists " _
& "(Select * From tbl1 As t Where val2 = tbl1.val2 " _
& "And val1 > tbl1.val1)"
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!