Share via

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

Anonymous
2025-01-22T15:48:43+00:00

Hello all,

I am looking to understand how best to link an excel spreadsheet (that contains a list of courses) to Access and where I can add additional 'comment' type columns to that linked data and eventually query it all or view bits of it in a form.

When the linked excel spreadsheet is updated (Usually daily i.e. some courses are removed from the list, some added, most remain and data for each course may change), I would like the additional columns that I have added in Access (and the comments that have been added in those columns) to remain 'attached' to courses where those courses are still present in the linked data as it changes.

I've tried to do this in Excel with a self-referencing power query and managed to get this to work in a fashion, but as the number of columns I am using is 90+, hiding and unhiding columns after refreshing the data, breaks the query so I'm reverting back to Access to see if there is/is an easier way to do this.

I appreciate any suggestions or pointers :-)

Thanks,

C

Microsoft 365 and Office | Access | For education | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

11 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-01-23T09:18:22+00:00

    Thanks, some useful ideas and lots to explore :-)

    0 comments
  2. Anonymous
    2025-01-23T09:19:05+00:00

    Thanks for the extra ideas :-)

    0 comments
  3. Anonymous
    2025-01-23T09:21:47+00:00

    Thanks for the suggestions, the course ids are unique - I think I'm going to muddle on with Excel for the moment and learn to live with the query errors - most of it works if I don't mess about with the visual aspects :-) I can then follow the Access suggestions provided in the feedback from everyone when the urgency is less - much appreciated!

    0 comments
  4. ScottGem 68,775 Reputation points Volunteer Moderator
    2025-01-23T13:14:17+00:00

    When you say that the course ID is unique do you mean that Fred's Flowers is always A12345 whenever its given or that Fred's Flowers starting on 1.2.22025 is A12345 and Fred's Flowers starting on 2.2.2025 is A12567? That is what I was asking and its key to designing your Access database.

    It's clear that this is not a process well suited for Excel. So, the sooner you can get it into Access, the easier your life will be.

    0 comments
  5. Anonymous
    2025-01-23T14:04:03+00:00

    Hi Scott, yes the course ID is always unique to the individual course, even if the same course name is used again for other courses on other dates. I had intended using that course id as the primary key in any eventual linked tables (linking to other tables as appears to be the sane solution put forward)

    Thanks,

    C

    0 comments