Swipe and Win Promotion

[BruceWil15]

Hello All,

I am trying to write and access program for our small company that will reward customers with a random prize. I am not really sure on how to begin this project. I have done some random generators before, and a program where someone could enter a thier member number and it would track them.

What I am trying to do is the following.

I have about 1700 people in a list to allow a chance to win. Only those 1700 people would be eligible.

I want them the enter their member number, then hit enter. They would either be awarded $10.00 or a random gift.

There would only be about 26 random gifts.

Then I would want it to print out what they won.

Example: Customer steps up to computer, enters thier member number and it would say they either won $10 or a random gift (it would generate what that random gift was - say $500 off of tires).

Can this be done?

Any advise to get started would greatly help.

 

[Golom]

Need a few more details.

- Is there a limit of how many times each "random" gift can be issued? For example, could every customer get the $10.00 or do you allow only some limited number of them?

- Will EVERY customer (provided they are in the 1,700) get something or is there a "Sorry ... you're outta luck" option?

Yes ... it certainly can be done but you need to be explicit about what the rules are.

Check out the Rnd function.

 

[BruceWil15]

Thanks Golom

To answer your questions:

Yes every person would get at least $10 but 26 (number of radmon prizes) would get something else (like $50).

There is not a "Sorry didnt win message".

A random gift can only be issued once. So if I had 1 $50 random prize, it could only be issued once then no more.

Does that answer they questions.

I am figuring I need a customer number table and a prize table. From there I am pretty lost.

 

[Golom]

First ... the prizes
[tt]tblPrizes
PrizeDesc (PK)
[/tt]
and it would be populated with
[tt]PrizeDesc
Aircraft Carrier
Trip to Hong Kong
Fridge Magnet
Coffee Mug
50 bucks
etc.
[/tt]
Then

[COLOR=black cyan]' On average we will give a prize only 1.5% of the time. That is[/color]
[COLOR=black cyan]' we don't want to issue prizes to the first 26 people and then everybody[/color]
[COLOR=black cyan]' else is outta luck.[/color]
Public Function AwardAPrize (CustID As String) As String
Dim GiveAPrize As Boolean
Dim Prizes As DAO.Recordset
Dim PrizeAwarded As String
Dim SelectPrize As Integer

GiveAPrize = (Rnd() * 100) <= 1.5

Set Prizes = CurrentDb.Openrecordset ("Select * From tblPrizes")

If GiveAPrize and Not Prizes.EOF then
   [COLOR=black cyan]' This one gets a Prize!![/color]
   Prizes.MoveLast
   Prizes.MoveFirst
   If Prizes.Recordcount = 1 Then
      [COLOR=black cyan]' Last Prize ... issue it[/color]
      PrizeAwarded = Prizes![PrizeDesc]
      Prizes.Delete
   Else
      [COLOR=black cyan]' More than one prize left.[/color]
      SelectPrize = Int(Rnd * Prizes.RecordCount)
      Do While SelectPrize < 0 or SelectPrize > Prizes.Recordcount
         SelectPrize = Int(Rnd * Prizes.RecordCount)
      Loop
      Prizes.Move SelectPrize
      PrizeAwarded = Prizes![PrizeDesc]
      Prizes.Delete
   End If

Else

   [COLOR=black cyan]'Didn't qualify or there are no prizes left ... give him 10 bucks[/color]
   PrizeAwarded = "10 Bucks"
   
End If

CurrentDb.Execute _
      "INSERT INTO Awards (Customer, PrizeDesc) " & _
      "VALUES('" & CustID & "','" & PrizeAwarded & "')"

Prizes.Close
Set Prizes = Nothing
AwardAPrize = PrizeAwarded

End Function

That's the general idea. You'll probably need to play with it a bit to be sure that "Rnd" isn't returning zero and giving you errors when positioning the recordset.

 

[BruceWil15]

Golom

Thanks much, this is awesome. However, I am not sure where to put the code at? Does this go in a form or on something like a GENERATE button?

 

[PWise]

customernumbertable Fields
Customerid
Prizeid

Prize Table Fields
Prizeid
Prizename
RandomPrize (yes/no)

prizetable info
prizeid prizename RandomPrize
1 $10.00 no
2 $500 off of tires yes
3 $50 random prize yes

your random generator would genarate numbers from 1 to 27

use this query to check if this prize was selected alredey

Select prizeid from prizetable where RandomPrize=0
union 
Select prizeid from prizetable 
left join customernumbertable as cnt on cnt.prizeid=prizetable.prizeid
where RandomPrize=-1 and cnt.prizeid is null

this will return the $10.00 prize and all random prizes not selected

 

[BruceWil15]

Golom,

You lost me on the last one with:

customernumbertable Fields
Customerid
Prizeid

Not sure where this goes? Also I wanted to make sure that if someone else walked up and entered a code it would generate something like - not valid - it that possible to add?

 

[BruceWil15]

pwise,

I am getting "The specified field 'prizeid' could refer to more than one table listed from the FROM clause of your SQL statement."

 

[Golom]

You would place the code in a module and invoke it with something like

Sub Command1_Click()
   Dim rs As DAO.Recordset
   Dim CustID As String
   Dim SQL as String
   Dim PrizeAwarded As String

   CustID = Text1.Text [COLOR=black cyan]' Assume that the customer ID is entered in a Text Box[/color]

   SQL = "Select * From Customers Where CustID = '" & CustID & "'"

   Set rs = CurrentDb.Openrecordset(SQL)

   If rs.EOF then
      MsgBox CustID & " is an invalid customer ID"
   Else
      PrizeAwarded = AwardAPrize(CustID)
      MsgBox "CONGRATULATIONS!", & vbCrLf & _
             "You Won " & PrizeAwarded , _
             vbExclamation, "Lucky SOB!"
      [COLOR=black cyan]' Print the Prize here[/color]
   End If 
End Sub

Note that, since the only information being entered is some identifying number, there is no assurance that the person doing the entering is actually the customer with that number. You may want to think about how to verify their identity (e.g. enter your date of birth; mother's maiden name, etc. ... or whatever info you have about them.


You will need to build the "Customers" table an populate it. That is left as an exercise for the reader.

 

[PWise]

Select prizeid from prizetable where RandomPrize=0
union 
Select [COLOR=red]prizetable.[/color]prizeid from prizetable 
left join customernumbertable as cnt on cnt.prizeid=prizetable.prizeid
where RandomPrize=-1 and cnt.prizeid is null

 

[BruceWil15]

Golom,

Thanks,

Yes, they actually have cards with the their member number encoded on the back. I can setup a card swipper that will capture the number perfectly.

 

[BruceWil15]

Golen,

I have setup the Customer table with CustId field. When I put the code on a command box I am getting a syntax error when I try to complile the code, the error is on:

MsgBox "CONGRATULATIONS!", & vbCrLf & _
"You Won " & PrizeAwarded , _
vbExclamation, "Lucky SOB!"

Thanks so much thus far, and advise?

 

[BruceWil15]

pwise,

When I run the querry all I am getting is 3 rows:
1
2
3

 

[PWise]

did you add all 26 prizes?

 

[BruceWil15]

Yes I did, now is just goes through 1 - 27.
1
2
3
4
5
6
7
8
etc...