Need feedback on database

[Neo003]

I am kinda new to MS Access, and don't have very much knowledge on VB and SQL.
There are 54 box of scratch-off tickets, everynight I scan the ticket barcodes and dB will separate the ticket number and pack no from the scanned barcode.

Scratch off Dollar value = Total Number of tickets in a pack.
$1 = 300
$2 = 150
$3 = 100
$5 = 60
$10 = 30
$20 = 15
$25 = 24
$30 = 20

What my need was

1. Wanted a software where I can store sold lottery scratch off ticket numbers and have calculation based on # of ticket sold every night.
2. Also have data on which pack numbers were activated.
3. Organized data storage
4. Needed to have a software which is customizable to my need.
5. Needed to be free.
6. Learning experience

So after few back and forth and some(lot) trail and error, I created this db. It's perfect for my day to day use. But here is what I'm having problem and need help with,

1. If I activate a new ticket it shows negative on that day's sales report,
2. Can't find a way to show ticket numbers for lets say last 7 days or for a month if I want a report where it shows how many tickets sold per day per box.

I'm attaching the db., please review and let me know of all pros and cons, and what can I do to make it better.

 

[LarryE]

1. You are attempting to use ACCESS like a spreadsheet. The way you have constructed the application will never work properly.
2. You have four tables that have no relationship to one another what-so-ever. A relational system relies on tables and fields that relate to one-another.
3. Your tables have multiple fields that have the same data in them. Relational systems don't have that. That's why they call them RELATIONAL database systems.
4. I know nothing about lotto tickets or their sale or how they are stored or accounted for, but based on the contents of your tables, I have taken a guess at what you tried to do. Please look at this structure tell me if it is close to correct.

The only original table that is useful is tbl_Games which I kept intact, although TotalTickets, StartNumber and EndNumber should probably be calculted on forms and reports but not stored in a field in a table. The other three tables I created new.
In this data structure:

1. You have multiple Games of lotto
2. Each which may have several sales dates
3. Each sale date may have several "Boxes" associated with it (I don't know what a "box" is)
4. Each "Box" may have several individual lottery tickets sold

This structure will give you a better start at using ACCESS.

 

[Uncle Gizmo]

Relational systems don't have that. That's why they call them RELATIONAL database systems.

Further to Larry's comments:-

Extract:
The relational model is thus named, not because you can relate tables to one another (a popular misconception), but as a reference to the relationships between the columns in a table. These are the relationships that give the model its name; in other words, relational means that if several values belong to the same row in a table, they are related. The way columns are related to each other defines a relation, and a relation is a table (more exactly, a table represents one relation).

From:
The Art of SQL --- Authors: Stephane Faroult & Peter Robson
1.1. The Relational View of Data

 

[Neo003]

1. You are attempting to use ACCESS like a spreadsheet. The way you have constructed the application will never work properly.
2. You have four tables that have no relationship to one another what-so-ever. A relational system relies on tables and fields that relate to one-another.
3. Your tables have multiple fields that have the same data in them. Relational systems don't have that. That's why they call them RELATIONAL database systems.
4. I know nothing about lotto tickets or their sale or how they are stored or accounted for, but based on the contents of your tables, I have taken a guess at what you tried to do. Please look at this structure tell me if it is close to correct.

View attachment 102141
The only original table that is useful is tbl_Games which I kept intact, although TotalTickets, StartNumber and EndNumber should probably be calculted on forms and reports but not stored in a field in a table. The other three tables I created new.
In this data structure:

1. You have multiple Games of lotto
2. Each which may have several sales dates
3. Each sale date may have several "Boxes" associated with it (I don't know what a "box" is)
4. Each "Box" may have several individual lottery tickets sold

This structure will give you a better start at using ACCESS.

Thank you for the reply, I understand not everyone know about how we do things with lottery. I know my db is missing all the relations, but I couldn't figure a way to do it properly. Every information in this db is taken from a barcode from the back of the lottery, Like this the one at the bottom

433-561108-000
but when scanned it has different numbers like 4435611080003138763153, so I've to extract the Game Number (433), Pack Number (561108), Ticket Number (000) from all the tickets every night. If I can do it in the reports, I can't figure out how to. But the concept was to scan the tickets so it can extract the above numbers especially the ticket number for calculating number of ticket sold per day. The first ticket sell is the number 000 then 001 so on and on. till the end of the roll.

 

[Gasman]

So 443 is a typo?

 

[Uncle Gizmo]

I've looked at your database and I can see that you've done an immense amount of work! Unfortunately as others have stated you are thinking with your Excel head on, and MS Access just doesn't work like that.

I started modifying the data to try and bring it more in line with the correct way of working with MS Access, but I ran into this problem straight away which indicates to me that either there are some fundamental errors in your data, or I'm not understanding your goal.

I started looking at:- "tbl_Numbers" and I notice that you have a formula in column "Box1Ticket" which appears to extract a number from column:- "Box_1" with this formula:- "IIf(Len([Box_1])=22,Mid([Box_1],10,3),Mid([Box_1],11,3))"

This extraction appears to produce sensible results for the first 2 records:-

1 - 990506017 004 9220759246 --- Extracts 4
2 - 818207405 001 3666676355 --- Extracts 1

But then falls down because the numbers have a different number of places for example:-

3 - 112518054 600 47 --- Extracts ?
4 - 196079681 400 20 --- Extracts ?
5 - 112518054 700 76 --- Extracts ?

But then seems to rectify itself when you move to the next number:-
6 - 874208690 007 1775444209 --- Extracts 7

That's as far as I have got looking at that table.

So I'm thinking before I do anything further with your database I need more information about how it's put together.

 

[Uncle Gizmo]

Looking back through your other posts here on AWF, I see that you posted an image of your spreadsheet.

Need help creating a database for tracking lottery ticket sales.

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...

www.access-programmers.co.uk

To my mind that's where we need to start. You need to scrap your current database, talk to the guys here and ferret out the information into logical, proper MS Access tables and go from there.

It's too much work for one person to do. With the numerous experts here, all putting in their two bobs worth, I think you might come up with a successful outcome. But it will be quite a bit of work for you! On the other hand, you will learn something I'm sure!

 

[jdraw]

Just looked at the thread. You have gotten good advice re your current database efforts. It would be better for you and readers if you could describe in simple, plain English what is the purpose of the proposed database?
What are the processes involved in your activity with these lottery tickets?
eg, you find them; you record then; you do some analysis;....

 

[LarryE]

Are you attempting to keep an inventory of tickets you have received and then sold or just those you have sold? When do you scan the ticket? Do you scan it when you receive the ticket to be sold, or only when you actually sell it?

 

[Pat Hartman]

Aren't the scratch offs numbered sequentially for each game? Couldn't you calculate the number of sales by game by day by subtracting the ending number from yesterday from the ending number for today?

Do you really need to capture the values for the scratch off boxes for each winning ticket? To what end? I'm pretty sure you can't get the number from the boxes by using the bar code. That would be an easy way to identify winners if you knew the code. You would need private time with the scanner and the tickets to do the scanning but if cheating is possible, thieves will always find a way.

I would have a table to define the various games. It would include the sale price and your actual value as the seller. Then each day (in a second table, you would log the starting number for each game and at the end of the day, the ending number. How you do this will depend on your procedures. Lottery tickets are money so they are not left unattended and you must have procedures to keep them from being stolen by employees. Maybe you also need some reconciliation between your cash register and the number of tickets dispensed. So, if the register says you sold 100 tickets for game 234 but the ticket numbers show that 105 are sold, the discrepancy has to be accounted for.

 

[Neo003]

I have a store where we sell scratch tickets, there are 53 boxes with different tickets in each. So at night after closing we used to write down ticket number and subtract the ticket number from previous day to get the number of tickets sold today. Then multiplying it with the $ amount of those ticket to get total $ amount (We need 3 things daily beginning ,ending ticket number and total $ amount of tickets sold from each box).

Slowly I moved to excel to get the daily sold and sale amount, Which as "Uncle Gizmo" mentioned. But then I got familiar with access and wanted to move away from excel and into MS Access. Since we started hiring, all the new hires have barely any knowledge of how to operate computer other than POS. So wanted to make a simple form where they can just scan the ticket and have a report with how many tickets sold and what was the $ amount ready for that day. Also scanning barcode is so that they can't just make up number when writing it down since I've caught few of them doing it.

 

[Neo003]

I've looked at your database and I can see that you've done an immense amount of work! Unfortunately as others have stated you are thinking with your Excel head on, and MS Access just doesn't work like that.

I started modifying the data to try and bring it more in line with the correct way of working with MS Access, but I ran into this problem straight away which indicates to me that either there are some fundamental errors in your data, or I'm not understanding your goal.

I started looking at:- "tbl_Numbers" and I notice that you have a formula in column "Box1Ticket" which appears to extract a number from column:- "Box_1" with this formula:- "IIf(Len([Box_1])=22,Mid([Box_1],10,3),Mid([Box_1],11,3))"

This extraction appears to produce sensible results for the first 2 records:-

1 - 990506017 004 9220759246 --- Extracts 4
2 - 818207405 001 3666676355 --- Extracts 1

But then falls down because the numbers have a different number of places for example:-

3 - 112518054 600 47 --- Extracts ?
4 - 196079681 400 20 --- Extracts ?
5 - 112518054 700 76 --- Extracts ?

But then seems to rectify itself when you move to the next number:-
6 - 874208690 007 1775444209 --- Extracts 7

That's as far as I have got looking at that table.

So I'm thinking before I do anything further with your database I need more information about how it's put together.

Yes you're correct, there is a reason for have that formula, there are 2 different size barcodes. The Lottery started implementing new barcode number that has different length then previous one. So I've to have iff statement just in case they can scan old one but still get the correct pack and ticket number.

 

[Neo003]

Looking back through your other posts here on AWF, I see that you posted an image of your spreadsheet.

Need help creating a database for tracking lottery ticket sales.

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...

www.access-programmers.co.uk

To my mind that's where we need to start. You need to scrap your current database, talk to the guys here and ferret out the information into logical, proper MS Access tables and go from there.

It's too much work for one person to do. With the numerous experts here, all putting in their two bobs worth, I think you might come up with a successful outcome. But it will be quite a bit of work for you! On the other hand, you will learn something I'm sure!

I've tried it before but I think that not that many people are on this forum that are familiar or have any interest in the field I am in. Also I know that I am not much on how to explain what I want thing I want to do properly.

 

[Pat Hartman]

How many tickets do you sell each day? Let's say 200. You are asking each employee to REMEMBER to scan a ticket as it is sold. How much time will this take? How will it inconvenience the customer who has to wait for the clerk to scan the 10 tickets he purchased. What happens if the clerk forgets? Would there ever be a way to recover?

Why does each clerk have to enter the data if you do it the way you were originally doing it? Wouldn't this be done once in the morning when you open the register and then when you close the register at the end of the day or as a different clerk takes the sales position if that is your practice (which it should be).

 

[Neo003]

How many tickets do you sell each day? Let's say 200. You are asking each employee to REMEMBER to scan a ticket as it is sold. How much time will this take? How will it inconvenience the customer who has to wait for the clerk to scan the 10 tickets he purchased. What happens if the clerk forgets? Would there ever be a way to recover?

Why does each clerk have to enter the data if you do it the way you were originally doing it? Wouldn't this be done once in the morning when you open the register and then when you close the register at the end of the day or as a different clerk takes the sales position if that is your practice (which it should be).

This will be done only 1 time a day and that is at night. That's it, we only scan the ticket which is currently in the box at night. The tickets are sold using the different upc, and that's a breeze. That's not the issue here, also I'm not telling anyone to scan each ticket sold that's bonkers.

 

[Pat Hartman]

What was all this business about the boxes?

I don't think using a scanner makes this easier. I would use an append query that runs at the "closeout". This query copies a record from the active scratchoffs table and appends a record for today's date for each game. Then the user (two works best since one can call out the game and the number and the second can enter it.) enters the ending number for each game. Then a final check should look for any empty rows for today in case one game got missed. As each row is updated, your validation code should compare todays ending number to yesterday's and today's number must be >= yesterday's ending number.