Need help with if statement in query and deleting a row

[Scott]

I have a table that has info and some of the phone #'s are
duplicated. The numbers that are duplicated each have its
own code and I need to delete one whole row if this shows
up..

Here is what the table kind of looks like

code phone # state city
ddd 5678 ny albany
eee 5678 ny albany
eee 4312 ma boston
ddd 1234 ma boston

5678 is a duplicate so I want it to remove the row with
the DDD code so this would come back and change the EEE to
say something else like this.

ADD 5678 ny albany
eee 4312 ma boston
ddd 1234 ma boston

I am at a loss where to even start. I am still a novice at
access

Thanks
Scott

 

[Michel Walsh]

Hi,


why removing (eee 5678 ... ...) and not (ddd 5678 ... ... ) ?


Anyhow, you can try:

SELECT FIRST(code), phoneNumber, FIRST(state), FIRST(city)
FROM myTable
GROUP BY phoneNumber



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Anything with a duplicate, that line is invalid.

I used the code below and its pulling out the wrong
code..its pulling out the first line and not the second.

Any ideas how I can change this?

Thanks
Scott

 

[Michel Walsh]

Hi,


What differentiate the "first" line from the "second" line...you don't
mean the way they are displayed? In a table, there is no display, there is
"record" either, there is no "this is the first", "this is the second". If
the DATA cannot tell you which is which, I am afraid any solution is just
"lucky" to work, and may not work next time you compact the data... but if
that may (look like it) do the job, try to replace the FIRST with LAST.



Hoping it may help,
Vanderghast, Access MVP