Question - duplicate records

[learning_codes]

Hi,

I'm trying to figure it out but I don't know if there is a way to do
this or not.

In the database, I have duplicated record (ID Number).

ID Number Date Reason
Completed
123 2008/01/03 Snowstorm NO
123 2008/03/31 Terminated YES

I want to collapse two records into one. The result I want to get:

123 2008/01/03 Terminated
YES
(a)
(b) (c)

a) I want to use 2008/01/03 instead of 2008/03/31

b) I want to use Terminated instead of Snowstorm

c) I want to use YES instead of NO

Is there a way to make it work or not ?

Your feedback and help would be much appreciated.
Thanks

 

[John W. Vinson]

Hi,

I'm trying to figure it out but I don't know if there is a way to do
this or not.

In the database, I have duplicated record (ID Number).

ID Number Date Reason
Completed
123 2008/01/03 Snowstorm NO
123 2008/03/31 Terminated YES

I want to collapse two records into one. The result I want to get:

123 2008/01/03 Terminated
YES
(a)
(b) (c)

a) I want to use 2008/01/03 instead of 2008/03/31

b) I want to use Terminated instead of Snowstorm

c) I want to use YES instead of NO

Is there a way to make it work or not ?

Your feedback and help would be much appreciated.
Thanks

Well, *as posted*, sure; run a Delete query to delete the record with
2008/01/03 and an Update query to update the other one.

But I'm sure this is an example of a larger more general question, to which I
can only say "I don't know; it depends on how you determine which of the pair
to update and what it should be updated to".

What's the actual, real-life problem? How and when should this update be run?

 

[John Spencer]

For that specific set of values you could use

SELECT [ID Number]
, Min([Date])
, Min(Reason]
, Min(YesNoTextField)
FROM YourTable
GROUP BY [Id Number]

Since you haven't told us what the rules are for deciding which value in
each column to display, you could get a number of answers. And once you
tell us how to decide (in words) which value to keep we may not have a
solution.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================