Need help creating a database for tracking lottery ticket sales.

[Neo003]

I am trying to create a daily lottery counting database, in which I can scan the lottery everynight and at the end it tells me how many tickets I sold and the dollar amount. I am using excel as of right now. I've got it nailed down in excel but I have to enter all the number manually.

Now for the scratch off barcode has some data in it like pack number and ticket number, I am able to extract the pack and data number from the scratch off. Also I have 54 box that needs to be calculated. Heres the way I did that tables.
This is where the box $ amount is


This is where the scanned ticket barcode goes

This is calculated field which extracts the ticket number



The formula for extracting the ticket number


Query for multiplying the daily numbers to the box $ amount


My questions and problems
1. I'm not able to do a sum in the daily total report (which multiplies the number of tickets sold to the box $ amount) I can see the correct amounts for each box but not able to do a sum text box in the report footer.
2. What can I do if I added a new ticket in to the box.
3. How can I keep track of the pack numbers.

Sorry my mind is going black right now but I'll add if any other things I need.

 

[theDBguy]

Hi. Before you proceed, I would suggest you look up "normalization" rules. Your table structure looks wrong to me.

 

[Neo003]

This is the way I have the tables

Table 1	Table 2	Table 3	Table 4
Box Number	Box Dollar Amount	Game Name & Game Number	Date & Daily Ticket Number & Calculated Ticket Number

 

[plog]

This is the way I have the tables

Normalization has nothing to do with how you name your tables. Although, you've not done that exactly correct either. You should avoid non-alphanumeric characters in table and field names (no spaces nor ampersands).

And I second the calls for reading up on normalization:

Database normalization - Wikipedia

en.wikipedia.org

Read up on that, google a tutorial or two, then apply what you learn to your data. Databases are not spreadsheets, you need a different mindset when setting up tables than you do a tab of data.

 

[Neo003]

Sorry the naming of table here in the form is just to explain. I don't have any "&" or anything like this but I do have "_".


I can't seems to think what is wrong in this database.

 

[MajP]

I can't seems to think what is wrong in this database.

It is hard to know if this is done correctly, but the structure screams that is not. In a database you normally have few columns and lots of rows. If I see a field repeated multiple times with a different number (Box1, Box2, etc) 99% of the time the database is designed wrong. Worse is to see fields that appear related to each other (box1 and box1Ticket). I do not really understand what is a box versus a ticket and what data is being stored. But more likely instead of 54 fields it is two fields and 54 records

BoxNumber
TicketNumber

1 9905061...1
....
54 9905061...2

The easiest to help would be to see a copy of the spreadsheet. The good news is that you can do a series of imports into the new structure once you get it correct. It is a common mistake to try to make a table match a spreadsheet. It is usually transposed in a way.

 

[Neo003]

This is what my excel file looks like, I'm manually putting the ticket numbers in.


So I have 54 boxes that has 54 different scratch offs, if you been to a gas station you might have seen those. So every night we take the number and subtract it from last night's number and figure out how many were sold, then we multiply it by the ticket amount (lets say it is $5 ticket and we sold 10 then it's $50).

The ticket roll has following amount of tickets in them.
Dollar amount = Number of tickets in a roll = Way tickets are numbered
$1 = 300 = 0 to 299
$2 = 150 = 0 to 149
$3 = 100 = 0 to 99
$5 = 60 = 0 to 59
$10 = 30 = 0 to 29
$20 = 15 = 0 to 14
$25 = 24 = 0 to 23
$30 = 20 = 0 to 19
We start selling at 0.

 

[moke123]

I can't seems to think what is wrong in this database.

To start I would think you need a table just describing each game.

GameID
GameName
GameDenomination
NumberInBook
Barcode_Identifier

Then a table for each book you activate

ActivatedID
GameID
BookNumber
DateActivated
BoxNumber

A table for Daily scans

ScanID
ActivatedID
ScanDateTime
TicketNumber

 

[Neo003]

To start I would think you need a table just describing each game.

GameID
GameName
GameDenomination
NumberInBook
Barcode_Identifier

Then a table for each book you activate

ActivatedID
GameID
BookNumber
DateActivated
BoxNumber

A table for Daily scans

ScanID
ActivatedID
ScanDateTime
TicketNumber

This is how the tickets get scanned
9905060170059983751013
18912261900032
It needs iif formula to get the pack number and ticket number. The tickets have two separate upc codes, one like above and another one(might be code128 or something like that).

 

[MajP]

Can you upload a real spreadsheet instead of screenshot?

 

[moke123]

This is how the tickets get scanned
9905060170059983751013
18912261900032
It needs iif formula to get the pack number and ticket number. The tickets have two separate upc codes, one like above and another one(might be code128 or something like that).

do you know how to parse out the various identifiers from the barcode?
for example, 1st 4 digits are the game number, next 10 are the book number, last 3 are the ticket numbers.