to copy data from an access file to another

[razvan via AccessMonster.com]

Dear All,

I need help to copy data from a file as the one below

Reference name
100 john
101 marc
102 helen
....
the file has over 10000 records

to the file( in to the filed ''name'')

Ref name

100
100
100.01
102
102.02

the name should be the same as the first file and copied no matter how many
times the reference appear on the second file and no matter if the reference
is followed by .01 or .02

thank you all in advance

 

[Steve Schapel]

Razvan,

Is the value in the Ref field a Text data type? Does the Reference
field have a unique index?

Try this idea, on a backup copy of your database...

First of all, make a query based on the second table. Add both fields,
and then in the Field row of a blank column in the query design grid,
enter like this:
BaseRef: CInt([Ref])

Then, make another query, which includes the first table, plus the above
query, joined on the Reference<->BaseRef fields.
Make this an Update Query (select Update from the Query menu). In the
Update To row of the Name field (2nd table), enter the equivalent of:
[NameOfFirstTable].[Name]

Run the query, should do what you want.

By the way, Name is a Reserved Word (i.e. has a special meaning) in
Access, and as such should not be used as the name of a field or control.