Run multiple queries in one go (1 Viewer)

[chezza]

Situation: I'm tracking coronavirus deaths across 216 countries, updated daily via an append query on an Excel spreadsheet which I import. Although I've only been doing this for 5 months, the Access table containing all the records is already getting a bit twitchy. It only has 30,000 records so far. I'm not sure why it's getting slow, but I can't reasonably split the data (there are 216 countries for each date, and all the dates for each country).

Thought it might help to query the daily import six different times, once for each continent. This will be pain in the neck to do on a daily basis, though! I'm hoping I can build something - anything: a form, a module, an SQL procedure, or some sort of meta-query - where all I have to do is change the name of the source table and hit 'go'.

I don't really know how to do VBA or even build a non-standard form, but am willing to learn if it will make this task easier for the next year and a half!
I'm also open to alternative suggestions on how to solve my "table getting too big" issue.

SQL for a single-continent append query below. What I think I need is to have six of these running automatically, with a new table instead of the [04 Aug 2020] one.

INSERT INTO [South America] ( CountryID, DateID, Country, [Date], [Total deaths], [New deaths], [Deaths /1m pop], Population )
SELECT DISTINCTROW Countries.CountryID, Dates.DateID, [04 Aug 2020].Country, [04 Aug 2020].Date, [04 Aug 2020].[Total deaths], [04 Aug 2020].[New deaths], [04 Aug 2020].[Deaths /1m pop], [04 Aug 2020].Population
FROM Dates INNER JOIN (Countries INNER JOIN [04 Aug 2020] ON Countries.Country = [04 Aug 2020].Country) ON Dates.Date = [04 Aug 2020].Date
WHERE ((([04 Aug 2020].Country)="Brazil" Or ([04 Aug 2020].Country)="Colombia" Or ([04 Aug 2020].Country)="Argentina" Or ([04 Aug 2020].Country)="Peru" Or ([04 Aug 2020].Country)="Venezuela" Or ([04 Aug 2020].Country)="Chile" Or ([04 Aug 2020].Country)="Ecuador" Or ([04 Aug 2020].Country)="Bolivia" Or ([04 Aug 2020].Country)="Paraguay" Or ([04 Aug 2020].Country)="Uruguay" Or ([04 Aug 2020].Country)="Guyana" Or ([04 Aug 2020].Country)="Suriname" Or ([04 Aug 2020].Country)="French Guiana" Or ([04 Aug 2020].Country)="Falkland Islands"));

 

[Pat Hartman]

You should not have 1 table per continent. You should have only 1 table and include a column that identifies the continent, or keep a country table and put the continent in the country table.

30,000 rows is tiny in the world of relational databases. Try compacting your database. Then take a look at the indexes. You may have too many (makes insert/update very slow) or too few (makes queries too slow).

Post the db if you can.

To answer your question, your code can just run one append query followed by another but this isn't your solution anyway.

 

[chezza]

Thank you for understanding my post, Pat!

I've just been tidying up the indexes. Not sure it's made any difference. I agree about the continents, and was just about to make another 'master' table to perform that task.

Post the db if you can.

Sure thing I removed some superfluous stuff; hope I haven't left it too large.

 

[chezza]

I've just been tidying up the indexes. Not sure it's made any difference. I agree about the continents, and was just about to make another 'master' table to perform that task.

In the process of doing that, I found some of my field types were less than optimal. After changing them, it doesn't seem to be struggling as before.
New, cleaner version attached in case anyone's interested.

I'm always open to suggestions, though!

 

[Pat Hartman]

You're welcome

Without the spreadsheet, it isn't possible to test the app.