Slow Application

[Kevin199]

I have a table 'tblData' with 3 fields ID, Animal, Rating:
ID Animal Rating
1 Dog 16
1 Cat 19
1 Horse 20
2 Rabbit 15
2 Lion 19
2 Dog 20
2 Zebra 26
2 Mouse 19
3 Cow 20
4 Cat 23
4 Lion 17
4 Dog 15
4 Rabbit 12
I would like to make another table 'tblSummary' that summarizes my original
table in three fields ID, Animals, AvgRate:
ID Animals Avg Rate
1 Dog, Cat, Horse 18.3333
2 Rabbit, Lion, Dog, Zebra, Mouse 19.8
3 Cow 20
4 Cat, Lion, Dog, Rabbit 16.75
I have tried converting tblData into a recordset then looping through each
record using nested loops and using an INSERT INTO statement to put it into
tblSummary. My problem is that tblData is over 243,000 records and my code
takes a long time to run, like half an hour. Also, on long runs I sometimes
get the message: "Cannot open database 'mydb'. It may not be a Database that
your application recognizes, or the file may be corrupt." After repairing
and compressing, my application runs again slowly and sometimes crashing as
just described. Is there a more efficient way to accomplish my task?

 

[Allen Browne]

To help Access make this more efficient for 240k records:

a) Make sure the data is in Access tables (not attached Excel spreadsheet or
text file for example.)

b) Make sure tblData has a primary key field, e.g. add an AutoNumber.

c) make sure the ID and Animal fields are indexed. (Ideally both should be
foreign keys to other tables, using relationships with enforced referential
integrity.)

You can now get the average quite quickly with a Totals query:

1. Create a query using tblData.
Output only the ID and Rating fields (not Animal.)

2. Depress the Totals icon on the toolbar.
Access adds a Total row to the query design grid.
In the Total row under ID accept Group By.
in the Total row under Rating, choose Average.

Now comes the problem of how to concatenate the animal names together. I'm
not really sure I followed the logic here, as some (e.g. Cat) turn up under
many IDs. Anyway, this is where the inefficient part is, so you may want to
Make Table with the results of the query above before you proceed.

You can concatenate the values together using code like this:
http://allenbrowne.com/func-concat.html
However, it will be slow: 1/2 an hour would be pretty quick for this.

Re the crashing problem make sure your code sets your recordset variable to
nothing and also your recordset variable (especially if you use CurrentDb.)

You could code this more efficiently by opening 2 recordsets (tblData as the
source, and another one as the target.) Loop through the source records
concatenating your string, and each time the ID changes output the combined
record to the target table.) This would probably be an order of magnitude
faster.