Help me create a database to give money away

January 16, 2008 2:41 PM

Help me design a small Access application to run a prize drawing. As I see it, the MC will open the application and click the Draw button, which will select from the available prizes for that month. Once drawn, that prize becomes unavailable. MC will then type in the winner's name and click Save, setting the form ready for the next contestant. Specs and examples follow.

Prizes will be one of the following dollar denominations: 5, 10, 20, 50, 100, or Consolation Prize.

There may be more than one prize of a particular denomination, that will change from month to month.

Prizes should be chosen randomly.

Prizes cannot be given more than once. (obvious)

As an example, this months drawing may have 12 $5 prizes, 9 $10 prizes, 5 $20 prizes, 2 $50 prizes, 1 $100 prize, and 50 consolation prizes.

The application will need to be opened and closed a few times before the month's drawing is complete. Freeware applicaitons that interface with Access or Excel will be considered.

posted by kc0dxh to computers & internet (8 comments total)

I'm not sure what your question is, but it seems like a straightforward project that can be done entirely in Access (or Excel) by someone with some VBA programming skill in less than a day. In Access you would want a table for the prizes and a table for winners. If the list of potential winners is known (e.g., if it is an employee roster), then you would have a table listing all the eligible people.

Here's what it could look like:

Table: Prizes
ID, Description, DateAwarded

Table: People
ID, Name

Table: PrizeWinners
ID, PrizeID, PeopleID, DateAwarded


The 'ID' field in each table would be a unique primary key.

You would then populate the Prizes and People tables, assigning a unique ID to each record.

Then, create a relation between the three tables so that the ID fields in the Prizes and People tables each are linked to the obvious fields in the PrizeWinners table.

Finally, create a form that has a button that first queries the Prize table with SQL like "SELECT * FROM Prizes WHERE DateAwarded IsNull;" (I'm not sure if you would use IsNull or something like ="" or something else). Using the returned recordset, you would select a random number between 1 and recordset.recordcount. The random number would be the record number, which would be updated by having its DateAwarded field updated.

If you have a People table, you could do the same thing to randomly pick a person if you'd like.

Then, append a new record to the PrizeWinners table with the PrizeID, the PeopleID and the DateAwarded fields.

Finally, display the prize (and winner).

Make sure that the computer is physically secured, and password protect the .mdb file (encrypt it on a thumbdrive that you keep in a safe place for even more security).
posted by i love cheese at 3:35 PM on January 16, 2008

I have an excel solution, but I have nowhere to put it up (as I am trapped behind a firewall), so check your email. Should be self explanatory. Press the button, basically.
posted by pompomtom at 4:18 PM on January 16, 2008

Sounds overly complicated.

Why not print the prizes on a piece of paper, cut the paper into strips and put them into a box? Write the names of the winners on the strips as you draw them out.
posted by mphuie at 4:19 PM on January 16, 2008

Doing it the way mphuie suggests would be much more fun and exciting, not to mention immune from allegations of fraud (electronic voting, anyone?).
posted by TedW at 6:32 PM on January 16, 2008

I think you will also want some code that will randomly select a record from your prizes table. I wrote something quickly:

Public Function GetWinningRecord(TableName As String, TestFieldName As String, IDFieldName)
Randomize
Dim MyDB As DAO.Database
Dim RSCount As DAO.Recordset
Dim RSTestRecord As DAO.Recordset
Dim RecID As Long
Dim I As Long
Set MyDB = CurrentDb
Set RSCount = MyDB.OpenRecordset("Select Count(" & IDFieldName & ") as TheCount " _
& "From " & TableName & " where " & TestFieldName & " = 0", dbOpenSnapshot)
RecID = Int((RSCount("thecount")) * Rnd + 1)
Set RSTestRecord = MyDB.OpenRecordset("Select " & IDFieldName & " " _
& "From " & TableName & " where " & TestFieldName & " = 0", dbOpenSnapshot)
For I = 1 To RecID - 1
RSTestRecord.MoveNext
Next
MyDB.Execute "update " & TableName & " set " & TestFieldName & " = 1 " _
& "where " & IDFieldName & " = " & RSTestRecord(IDFieldName)

GetWinningRecord = RSTestRecord(IDFieldName)

End Function

Passed into the function is the name of the table that contains the record record to choose, the name of the field that is the test field to determine whether the record has been selected already and the ID field for the record.

The procedure marks the record as selected when it is called and returns the ID of the winning record. Your prize table would need some numeric primary key and a numeric field that stores whether the record has been called already. A sample table could contain these fields:

Table Name: Prizes
Fields:
PrizeID AutoNumber
PrizeName Text
PrizeDesc Memo
BeenSelected Long

A sample call to the procedure would be:
MsgBox GetWinningRecord("Prizes", "BeenSelected", "PrizeID")

The procedure would fail if it was called after all prizes has been selecte.

Hope this helps!
posted by GregWithLime at 9:33 PM on January 16, 2008

Forgot to add that you need to reference DAO in your module.
posted by GregWithLime at 9:34 PM on January 16, 2008

The slips-of-paper-in-the-hat method has gotten old and they are asking for something more flashy. Not to mention that it's a lot of work.

Tom, I got your email and, while that's not Access, it does make it easy for others to manage it. It may end up being the core of what we use. Thanks!

GregWithLime, that seems slick, but I don't know where to put it. I wish I understood these things better.
posted by kc0dxh at 7:25 AM on January 17, 2008

The code would go into a module. Click on the Modules button/tab and add a new one. Then you can paste my code into a module. While in the module, you need to reference the Microsoft DAO library. I don't have Access on the machine I am at right now but I believe references is located under Tools\References.

Then, add a button to a form. Right click on the button and choose add code/procedure or whatever it says like that. Then you can place the call to the procedure there.

Hope that helps...
posted by GregWithLime at 3:01 PM on January 17, 2008