Adding additional fields to a linked excel table in Access where the data in those additional fields is retained as the Excel data source is updated

90+ columns suggests an unnormalized application. You are possibly still thinking “spreadsheet” when you need to model your data in a relational solution.

You can create a table in Access that has a column that matches a unique value in your linked Excel table. Your Access table could be updated as a subform on a from bound to your Excel table.

Bottom line, we need more information about your existing table/fields to provide more quality assistance.

Hi Duane,

Thanks for responding and I will try and clarify further.

The data I am working with, is exported from a course management system in either excel, csv or xml - I have chosen excel format in this instance as I am familiar with excel and that's what Access seems to link to, but I'm not wed to that format of course :-)

Each row of the exported spreadsheet contains a course with a unique course ID, along with an additional 30+ columns each relating to other course information such as start/end dates, tutor etc. Something like this:

I would like to link the spreadsheet (which is updated daily) to Access but also add some additional 'fields' where I can 'check off' that certain activities for each course has been done i.e. Has the contract been paid? Has the venue been booked? and so on, ending up with something like this:

When the excel data is refreshed each day (Downloaded, Copy and paste job for now) I would like Access to update with any new courses/removed courses but still retain the additional fields I have added in (as well as the data that has been added to those fields) for courses that still remain in the linked data. Something like this:

As each course goes through different stages (planning, live, completion) there are a number of different 'checks' for each stage and that is where the 90+ columns (or fields in Access) will come in. I had in mind that I would bring the data into Access and then link that data to three different tables within Access, each containing the various checks (fields) that were required for each stage and that I could then populate. I'd then be able to explore queries and reports.

Hope that is a bit clearer?

Thanks!

C

As Duane pointed out, Access is very different from Excel. So different that users moving from Excel to Access can find themselves struggling to unlearn a lot of Excel principles and learning new relational database principles in order to make the transition.

Ironically, in fact, the more you know Excel, the harder it is to make the transition.

Take a break from trying to force the current tables to work in a relational database application.

Instead, invest time in learning how relational database applications, like those created in Access, work.

Search topics on Database Normalization. Search topics on converting Excel spreadsheets to properly designed database tables.

For starters, here are a few references.

https://rogersaccessblog.blogspot.com/2017/03/what-is-normalization-part-i.htmlhttps://support.microsoft.com/en-us/office/move-data-from-excel-to-access-90c35a40-bcc3-46d9-aa7f-4106f78850b4

https://youtu.be/GFQaEYEc8_8?si=rjuGgwmBzgfsGEEN

In a relational database, each of the "different stages" would create a new record in a table of course stages. This would easily allow for the modification of stages in the future. A number of years decades ago, I created an application that tracked refinancing tasks. Rather than adding each task as a field/column, I created a table of all possible tasks and a junction table that related the tasks to the refinance record. This allowed my client to add tasks as needed without a contract programmer to modify tables, forms, reports, etc.

I'm sure George's link will explain this.

Is the CourseID unique? Or does the same CourseID exist with different dates venues, etc.? For example: Can there be multiple records for A12345, Fred's Flowers with different start date and venues?

If not, then I would NOT add fields. I would link the spreadsheet and then add tables in a relational manner that contain the additional info you want to track. In addition having fields for the "checks" is not the way to go. Relational tables should be tall and thin, not wide. For example, you have a table like this

tblCheckStatus

CheckStatusID (PK autonumber)

CheckTypeID (FK)

Status

So you have a record, linked back to the Course, that contains all the additional info you want to track.