New Access db from existing Excel workbook

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

New Access db from existing Excel workbook

Post by Jeff H »

The database I’ve been working on in Excel for a while works fine. I’ve been able to get it to do everything I need it to. But it really is more suited to Access and I’ve long wanted to gain more proficiency in Access, so I’m going to use this as a bridge.

The Excel version is based on three main tables: Volunteers, Patients, and Activities. In Excel I’m using primary key fields for the first two tables called VID and PID which contain unique values consisting of the person’s two initials followed by a consecutive, 2-digit number determined by how many others have the same initials. In Access I’ve added an auto-numbered primary key field to both of those tables (VolunteerID and PatientID).

Question: The Activities table shows individual meetings between Volunteers and Patients identified by the VIDs and PIDs. I’ve added a VolunteerID and PatientID foreign key to the Activities table, but I don’t know how to populate them based on the existing VID/PID combinations shown for each Activity record.

I've been checking out several online tutorials, and I've tried constructing a query, but I'm getting nowhere.

- Jeff

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: New Access db from existing Excel workbook

Post by HansV »

On the Create tab of the ribbon, select Query Design.
Add the Volunteers and Activities tables, then close the Show Table dialog.
If Access has joined the tables automatically on VolunteerID, select the join line and press Delete.
Then join the tables on VID.
Add the VolunteerID field of the Activities table to the query grid.
In the Query Type group of the Design tab of the ribbon, select Update.
In the Update To row of the VolunteerID column, enter [Volunteers].[VolunteerID]
Click the Run button and confirm that you want to update ... records.
You can discard this query now.

Then do the same but with the Patients and Activities tables.
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: New Access db from existing Excel workbook

Post by Jeff H »

Smooth!! Easy by the numbers.
So the logic, which completely escaped me, is that Update To means updating the selected field to the values found in the designated field on the linked table. I was thinking of it more like Copy To, where the selected range is deposited in another range. It's the reverse. Good to know.

As always, Hans, thanks very much.

- Jeff