Counting Updated Records

[Guest]

on a daily basis, I receive a csv file that I import into my access database.
and update my existing data to match. I can and have created a basic update
query to look at all the records that match between the two tables and update
them to the new values.
However - I would like to know how many records actually had a change made
to them. On a typical day, out of 400 records, 5-10 records would have data
to be updated. My boss would like to track the quantity of changes made for
statistical purposes.

Is there a way to either limit the records my query retrieves to those that
have differences between the two tables in ANY field (30 fields)...

or to count just the records that are changed?

Any/All input is greatly appreciated!

 

[Van T. Dinh]

If you run the Query by VBA code using DAO, check the RecordsAffected
Property.

If you run the Query by the user interface, I think you get a message box
asking you to confirm the update which also states how many Records wil be
updated.

 

[Guest]

I'll be running the query through VBA. The quantity of records changed is
going to be stored in a table, along with the number of records affected from
3 other events ... the idea being that after six months or a year's worth of
data, we can do some statistical analysis and see where our production demand
is heaviest, lightest, etc....


Right now, the code reads

docmd.openquery "Update1Qry"

My education in VBA has been self-taught, and I know there are gaps in what
I understand, so I imagine there's a better way to do it - notably - in a way
that will allow me to check the records affected property.

I'll look into this myself - but if you have a more detailed description of
how to go about setting this up with DAO - I would be very grateful.

-Amanda

 

[Van T. Dinh]

Use the Execute Method in lieu of OpenQuery then you can use the
RecordsAffected Property

There are example codes in VBA / DAO Help on the Execute Method and the
RecordsAffected Property.

 

[Guest]

I found how to use the Execute Method and the RecordsAffected property.

HOWEVER... this doesn't quite give me what I want. Today, for example... I
have 340 total records in both tables. 1 to 1 ratio. I manually counted,
and out of those 340 records, only the data in 15 records had any changes to
post.

The value I WANT returned is 15. What I got was 340. Perhaps I don't have
my filters set right on my query... The only thing I can think of to do is
set up something where every value is compared between each table, and the
query only includes records where those values are different... Unfortunately
- I have more than 40 fields... and Access 97 draws the line at 40 "OR"
statements. (tried that already....)

 

[Van T. Dinh]

I think you guessed correctly. A normal Update Query will update all
Records (regardless of whether the source and the destination are already
exactly the same or not) *unless* you specify in the criteria to update only
Records that have something different (to update).

You can switch to SQL of the Query and continue typing the OR clauses. The
limit is on the GUI / grid, not on the actual SQL that will be passed to the
JET engine for processing. There are limts on the actual SQL, e.g. max of
32 Tables & max length of 64K characters but they are so big that you never
reach these limits. I get absolutely confused by the time the SQL get to
about 4K characters.