Is there an easy way to resolve this please

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

Hi

Have almost finished amalgamating a whole load of different databases into
one.

I have a main table which has four related tables on a one to many basis -
each kinked by the field RecordId.

I have cleaned up all the data apart from one issue. Within my main table I
have about 300 instances where we have two or more records for a single
company. These records all have related information in the other tables.

What I am trying to automate is to look at the first record for the company
and get the RecordId. I then need to look at the second record for the same
company get the Record Id number, then look at all the elated tables for the
second RecordID number, and update it to the first Record ID number, so that
they get linked to the first Record. i than then delete the second record
(which shouldn't have a ny related records)

Does that make sense.

Is there any easy way to do this please?

Thanks

Alex
 
Dear Alex:

The need to "merge" two records into one is a feature we are now
including in all new applications we produce. We have a user
interface for this that is now standard. The user selects the "merge
target row" into which the rows from dependent tables will be merged.
Then the user selects another row which will be deleted, with all its
dependent rows being moved to the target row.

I don't think you're asking about the user interface, are you? You
want to see the SQL to move the rows.

Say the primary table is called PrimaryTable and one of the dependent
tables is DepTable. We have already determined the RecordId of the
target row (TargetId) and the RecordId of the row to be deleted
(DeleteId). Before you can delete this row in the PrimaryTable you
must move all the dependent rows from having DeleteId to having
TargetId:

UPDATE DepTable SET ForeignId = TargetId WHERE ForeignId = DeleteId

We would usually generate the SQL for this in code and run it there at
the appropriate time. After doing this for all dependent tables you
can:

DELETE PrimaryTable WHERE RecordId = DeleteId

Again, we generate the SQL in code and run it there. The form will
need Refreshed.

Just what kind of additional detail might you be seeking?

I wrote this up in this forum a couple of weeks ago, but no one seemed
to know what I was talking about!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom - thanks a load for your input - I will study it closely in morning.

I tyhink that you are probably right in including it as a standard within
applications. We have needed it many times in past, but its always been
just a few records, and some poor secretary has been delegated to do it the
hard way...

With applications becoming more and more complex, and with more tables being
related to each other.. moving data around between records is becoming an
issue, especially in the accounting sector.

Thanks again for your input - it will save me hours

Cheers

Alex
 
I don't think people are accustomed to anything but "help me"
questions here. I probably shocked everybody by trying to open a
discussion rather than ask a question.

Or, are you being sarcastic, saying it's no surprise that people find
me impossible to understand? <grin> That, too, would be no surprise!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
: in message
I don't think people are accustomed to anything but "help me"
questions here. I probably shocked everybody by trying to open a
discussion rather than ask a question.
Click to expand...

It's like Patrick Henry said, "Give me the answer or give me nothing".
Or, are you being sarcastic, saying it's no surprise that people find
me impossible to understand? <grin> That, too, would be no surprise!
Click to expand...

It's like Descartes said, "I think, therefore I am at risk on this ng".

:~)

RTF
www.rac4sql.net
 
Back
Top