Excel and Access Do Not Play Well Together

I have been working on a spreadsheet that does some data analysis on what the antigunners now call "high-fatality mass shootings" (6 or more killed in one incident). Their expert submitted a declaration that plotted the number of incidents and deaths since 1974.  It was a scarily rising line but not adjusted for population growth (which was pretty substantial)  i have been using the data from my mass murder database to extend high-fatality mass shootings back to 1891. Adjusted for population growth, the trend lines for both incidents and dead slightly declined 1891-2023. 

This involves Excel querying the database and a bit of processing of data. I already had a spreadsheet that did this, but it was not entirely my work and if called to testify about the methodology, I might have had to admit that I was not as sure as I would like, so I am recreating it.

This has been a booger to do.  I have been Microsoft CoPilot to help me and I am glad that I did. There are so many aspects of the interaction that are error-prone as even CoPilot admits that i would likely never have gotten as far as I have. At least it is something that I understand, but I find the nature of its failings overwhelming. 

Yes, if a field contains 1890 is formatted as text not number, i can see why VLOOOKUP might be unable to use it to do a numeric match. The obvious solution would be for VLOOKUP to be smart enough recognize and make the conversion.

Worse, 1891 converted to Number formatted cell apparently automatically converts to floating point, so VLOOKUP does recognize that the lookup of an integer 1891 should match 1891.00000.

The way that Excel does Access queries is also stupid. If you change the query in Access and refresh the query, it does not run the query against the named query but refuses a cached copy of the SQL from the last time. Fixing this is not as simple as saying to clear the cache or just making a fresh call to Access with that named query. What a mess.