Access Database for managing raffle auctions

One of the annual events I help with is the SPCA Theme Basket auction, and year after year it has proven to be a challenge. At least as far as the logistics of managing the drawing. Different methods were tried but most years they ended in mistakes due to volunteers being stretched thin enough to make mistakes, and everyone blaming each other, which didn’t help either.

To help ease the burden, for the past couple of years I was working on developing an Access database to help manage auction, as the Excel spreadsheets used in the past required too much human input to generate the calling lists, it sometimes would end up shifting a line. The idea of the database was to minimize human input and errors from entering the winning numbers, to generating the calling sheets. But because the prior experiences with the Excel spreadsheets typically ended in disaster, it failed to garner support from the other volunteers.

The all paper method that was employed last year didn’t work much better either. It required longer hours from the volunteers, and mistakes were still made, probably even more so. That being said, this year ended up being the year of attempting this new method I’ve been working on, with the Access database.

For the most part, the database worked as designed, except for a couple of minor flaws. The first one was that using a simple file share with a split database file didn’t end up being the best method to allow multiple people to enter into the database at one time, especially if they were working off of a form based off of the same table. It would sometimes lock records out of its filter scope, and deter the other endpoint from being able to enter data. Going forward I’ll be experimenting with using an SQL backend for the databases.

The second issue that became apparent, was that the reports that I had setup, that were designed to group winners by the first letter of their last name, weren’t sorting correctly within each group. This one was attributed to me assuming that the group function for Access’s reports would automatically sort. It didn’t, and I should have manually specified an additional sort function in the report’s Group & Sort. I’m not perfect, and this one was kind of on me.

The third issue was more of an interpretation problem. To my understanding, someone putting quotes in a spreadsheet line means to carry the information from above down to that row. Apparently one of the other volunteers didn’t think the same way, and used quotes to specify when someone doesn’t specify a phone number. This goes to show that it’s always a good idea to make sure the people working with paper and the data entry folks are on the same page.

That being said, it was all a learning experience which I was glad to have, and to improve my methods even further.

Posted in Projects.

Leave a Reply

Your email address will not be published. Required fields are marked *