Help with school project...Comparing two recordsets, to determine field value differences.

[Brad Feldman]

Can anyone advise me as to the best technique to compare fields in two
identical recordsets. Let's say I RecA and I want to compare it to RecB, and
for any changes that I encounter in RecA, I want to update that filed in
RecB with the field value from RecA. If any one can provide me with a code
example, I would be most appreciative.

Thanks,

Carmine

 

[Carmine]

Yes, these RS are based on tables. How would I accomplish what you suggest?
How could I determine the differences in each field? I assume I can do this
with VBA, but what is the best technique?

Thanks,

 

[Douglas J. Steele]

How are the recordsets created? If they're based strictly on tables (or
queries), you're probably best off writing a number of Update queries, one
for each field, with each WHERE clause set appropriately.

 

[Douglas J. Steele]

In a choice between doing something via SQL or doing it via VBA, the SQL
approach will almost always be more efficient.

The SQL for the query would look something like:

UPDATE RecA LEFT JOIN RecB
ON RecA.ID = RecB.ID
SET RecB.Field1 = RecA.Field1
WHERE RecB.Field1 <> RecA.Field1

This particular query would make sure that Field1 in RecB is the same as
Field1 in RecA. It presupposes that ID is the Primary key for both tables.

Now, if your aim is to guarantee that the two are the same, you can even
ignore whether they're different, and automatically update RecB. In this
case, you can do it all in one statement:

UPDATE RecA LEFT JOIN RecB
ON RecA.ID = RecB.ID
SET RecB.Field1 = RecA.Field1,
RecB.Field2 = RecA.Field2,
RecB.Field3 = RecA.Field3

In fact, take a look at http://support.microsoft.com/?id=127977 That'll
explain how you can set up a query that will update the values in RecB if
they're different than RecA, and insert new records into RecB if they exist
in RecA but not RecB.

 

[Mike Painter]

For the benefit of future employers I would urge everybody to not assist
people who ask for homework help.

If the intend of the assignment was "open neighbor" then she should be in a
bull session with her classmates contributing as well as learning.

 

[Douglas J. Steele]

While I understand your sentiments, Mike, my view is that any reasonable
school is going to place a much higher percentage of the marks on exams
rather than assignments. Since they'll be on their own for the exams, it
isn't going to matter how they get the answers for their assignments: if
they don't know the material, they aren't going to pass the course.

At least, I hope most programs are still that way.

 

[Larry Linson]

. . . Since they'll be on their own for
the exams, it isn't going to matter how
they get the answers for their assignments:
if they don't know the material, they aren't
going to pass the course.

That's exactly why I won't _do_ students' homework if I realize that is what
it is -- because they aren't likely to learn anything from it. But, it's why
I am happy to participate in discussions of specifics (indicating they've
done enough to try something, and maybe just need some help past a stumbling
block) to help them along their learning path.

At least, I hope most programs are still
that way. (In re: exams counting more
than homework assignments).

It's difficult to determine how "most" programs are run these days. I've
observed wild swings in the competence of computer science graduates in
useful, practical knowledge over the years, from specific schools and in
general.

There was a time when it seemed that every new grad we hired thought he/she
was going to write the "compiler that would solve all problems" and was
devastated to learn that only a handful of IBM's developers were working in
the compiler area, for example.

Then, a few years later, I encountered several CS grads (from schools where
I rather expected to see *nix-heads and theorists) who were incredibly
well-oriented to practical business problems right from the very start of
their careers.

There has been time for several cycles of that kind since I was in the part
of the corporate world where I had much contact with new CS grads.