Duplicate records?

[David Fawn via AccessMonster.com]

Hi,
I have this problem:
I have an Access database which looks like this:
<PRE>
Value 1 Value 2
Text1 blabla
Text1 blabla2
Text2 xxxxxx
Text2 yyyyyy
Text2 ffffff
Text3 aaaaaa
Text4 kkkkkk
</PRE>

There are "Values 1" which repeat one or more time, but each of these
repeating values has different "Value 2". What I need is to do this:

It should look like this:
<PRE>
Value 1 Value 2 Value 3 Value 4
Text1 blabla blabla2
Text2 xxxxxx yyyyyy ffffff
Text3 aaaaaa
Text4 kkkkkk
</PRE>

I want to add "Value 2" of the rows with the same "Value 1" to the first
row of this value and to delete the others...

Thanks for your ideas

 

[Michel Walsh]

Hi,


Not quite sure what you mean, but I would start with




SELECT Value1, MIN(Value2)
FROM myTable
GROUP BY Value1




Hoping it may help,
Vanderghast, Access MVP

 

[David Fawn via AccessMonster.com]

Yes, thank you a lot for your help! I tried this but I don't know which
function does the "MIN" - I need to have there all records, and I can't
delete any... Thanks for your help

 

[Michel Walsh]

Hi,


I don't understand, mainly when I read the first post (where you speak
of "to the first
row of this value and to delete the others..."), and your last message,
where you say you can't delete any (record) ...


What I assumed is that a "record" is a "row" of data. I also assume you
meant to keep each DISTINCT value of field1 (and NOT each DISTINCT RECORD).
I assumed you wish to keep ANY of the value of field2, for each distinct
value of field1.


If my assumption is right, then

SELECT Field1, MIN(Field2)
FROM myTable
GROUP BY Field1



does the work. MIN just take ONE value under field2, for each distinct value
of Field1.




Hoping it may help,
Vanderghast, Access MVP