Run multiple queries in one go (1 Viewer)

chezza

New member
Local time
Today, 03:49
Joined
Aug 5, 2020
Messages
5
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.

SQL:
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

Super Moderator
Staff member
Local time
Yesterday, 22:49
Joined
Feb 19, 2002
Messages
30,036
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

New member
Local time
Today, 03:49
Joined
Aug 5, 2020
Messages
5
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.
 

Attachments

  • coronavirus-copy.accdb
    744 KB · Views: 14
Last edited:

chezza

New member
Local time
Today, 03:49
Joined
Aug 5, 2020
Messages
5
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!
 

Attachments

  • coronavirus-copy.accdb
    596 KB · Views: 16

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:49
Joined
Feb 19, 2002
Messages
30,036
You're welcome :)

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

Users who are viewing this thread

Top Bottom