problem with update query

[Tim]

Very new to access 2003 - I am trying to update a table based on
corresponding new data in a second table. When I have only the old table in
Access...if I set up a query without having the new update table imported
yet....I get all 4041 records when I hit the run button.

However...when I import the new update table...and setup the same query with
both tables included...I am only getting 187 results after the trial run
attempt. This is BEFORE I setup the actual update field parameters

I have linked the appropriate fields in the two databases (tables) prior to
the initial trial 'run'

When I worked with my first update query experiment a while ago...when I ran
the trial 'run' before setting up the update query...I always got then
entire 4041 records even though both tables were included in the 'run' test.
When I ran the update query...all 4041 reecords updated

Hope this makes since...can any one suggest what I am doing wrong here...or
why I am only getting 187 records instead of the total 4041 records on the
trial 'run' before I set up the update query

Thanks, Tim R

 

[Andy Hull]

Hi Tim

So you're doing a select query, with the same join as you will use in the
update query, so you can check which / how many records will be updated?

Your results show that not every record in the old table has a matching one
in the new table. If there can be 1 and only one matching row then there are
only 187 that do match.

If you have reason to think there should be more then investigate your data
a bit by considering things like...

Does the new table only have 187 records?
Check the join is correct
Find a record in the new table that doesn't match when you though it would -
then check the data in the columns you are joining

This should show up an error somewhere or explain why only some match

Failing that, post back the query you've got so far.

hth

Andy Hull

Ps If you are joining datetime fields that are supposed to store just dates
(ie time is irrelevant) then make sure rows with matching dates don't have
different times in these fields

 

[John Spencer]

My guess is that the import table (source) has 187 records that match those
that already exist in the table to be updated (target table).

When you join tables the default is to show only the records that exist in
BOTH tables based on the fields you use to join the tables. If you are
updating existing data this makes sense - you don't update what does not
exist.

If you want to append (add) new records from the source to the target, you
would use an Append query. You would need to screen out records that were
in both tables in this case. To do that you would need to change the query
so you were only going to append records in the source that don't already
exist in the target.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Tim]

Thanks guys...I more or less thought that was the case...but felt I should
have many more than 187 out of 640 in the update table that matched ?
Actually...I thought all 640 should be updated/matched with the old table

Is there a way to run the test "run" to see how many records would 'append'
without actually running an append query that would permanently change the
data base "A"

 

[John Spencer]

If you swtich the view from design to datasheet the query doesn't execute
the update, it just shows the records that would be updated. If you use the
Query: Run from the menu or the Run button (Red Exclamation mark!) then the
query will execute.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..