Populate a second table with info from first table

[Guest]

I've imported an Excel spreadsheet and named it Donations. This table contains multiple donations from some donors. The Donations table contains an auto number Donation ID field and a blank Donor ID field. I also created a Donor table with an auto number Donor ID field for each donor. How do I now populate the Donations table with the automatically created Donor ID number?

 

[Guest]

Deb,

simple answer is don't use the auto-number as the link between the two sets of data. This number will change if you add/delete an entry - hence it is called 'auto'. Assign yr own unique Donor ID and enter into the tables.

A whizzy idea that you may like. You can 'link' from Access to Excel rather than import. This means that you can edit/add information in Excel AND have the power of relating the tables together in Access. Everyone is a Wiz in Excel but fewer in Access.

regards
Laurie Eaton

 

[Guest]

Oops.

from MSAccess help says 'Microsoft Access provides the AutoNumber data type to create fields that automatically enter a unique number when a record is added. Once a number is generated for a record, it can't be deleted or changed.". My comment below on Autonumbers is wrong. But you will have to enter the number manually since Access has no way of knowing which records should be linked to which.

 

[John Spencer (MVP)]

Well, it might be.

You have two tables. Ignoring the order of the records in the two tables, how
would you as a human say that a record in table one matches a particular record
or set of records in table two. If you can't do it, the computer can't do it.

The easiest way to do this might be to import ALL the data from the Excel table
into the Donations Table and then create a column for DONOR ID.

Now create the Donors table with the unique Donor information.

Now link the Donors Table to the Donations table in a select query on the Donor
Name fields in each table.

Turn that into an update query, and use it to UPDATE the DonorID in the
Donations table.

Now DELETE the extra columns in the Donations table.

The UPDATE query SQL statement might look something like:

UPDATE Donations Inner Join Donors
ON Donations.LastName = Donors.LastName AND
Donations.FirstName = Donors.FirstName
SET Donations.DonorId = Donors.DonorID

This still could lead to problems if you have identical donor names for
different individuals. You know - John Spencer at 12134 Main St and John Spencer
at 1210 Oak Avenue.

If that is the case you _MIGHT_ be able to add the additional criteria to the
join clause. Another problem, will be data entry on any of the repeating data.
Jon Spencer = John Spencer = John Specner Although to you they might be the
same, to the computer there are three different names.

Good luck, and be prepared to do a lot of manual corrections before you get a
clean set of records.