Results 1 to 12 of 12

Mini Casino...Please Help...Have Excel Data. Trouble creating tables, relations, and forms

  1. #1
    Noland is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8

    Mini Casino...Please Help...Have Excel Data. Trouble creating tables, relations, and forms

    Hello and welcome to my first post,

    I work at a VERY small (almost underground) mini casino/game-room. We only have 30 video slots and deal in all cash. We have been using an Excel Workbook and entering data twice a day at shift end (AM/PM) and matching the attendants written records with machine records to determine accuracy of the attendant and if there is any possible theft.

    I wanted to make a database that would keep a running total of the MASTER and PERIOD numbers Per Employee (for records and vendor purposes) and I would like the floor attendants to have a simple form that they can fill out which populates those records for use.

    I have been copying all the MASTER IN's and MASTER OUT's by hand and then entering them into the Excel database but human error is often costly
    I am going to try to upload a copy of the Excel Workbook We've been using and the Database I've started. PLEASE any and all suggestions are welcome, And appreciated

    In the Excel workbook the Period figure is calculated by subtracting the Prior from the Current and for each Shift the first Current, Prior, and Period are the In's and the second are the Out's and "Fish" is a high volume game from a different vendor we like to tally separately. The far Left column of calculations is pretty self explanatory...well..."Pulls" is the amount of cash they had to pull out of the machines throughout the shift to keep up with the tickets they had to payout. (All pays are hand-pays. When I say small game-room I mean smaahhhlll. Don't think Vegas, think more like trailer on a lonely Texas highway)



    I'm pretty sure I've added too many table to the database because they are redundant?? I think

    It's been years since I took the MS certification tests and I've been trying to watch videos to freshen up on Access to no avail

    I know its possible to have the Database open right to a login then a form Where they just have to fill in the Starting Bank, Pulls, Period_in&Period_Out per Machine, Matches (oh yeah, we match each customer the first $5 they spend) ,Expenses, and payroll and get a Report with pertinent info. I'd Like to thank you in advance for any help you can.gameroom_access.zipgameroom_excel.zip

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,075
    Interesting problem.......

    I've been looking at the spreadsheet, trying to understand what is being done. You use different terms in the post than what is on the worksheets. And some of the worksheets have different layouts.

    - You said there are 30 video slots (machines??) but the spreadsheet shows somewhere between 30 and 41??
    - Is "Current" (Friday!F2) the same as "AM_Current_Master_In" (Thursday!F2)??
    - Is "Fish" considered the same as a machine?? Or could it be?
    - It appears that the yellow background cells are for data entry - correct?

    - For an "AM" shift, there is a starting reading and an ending reading for each machine? Whole numbers only? (as in dollars?)
    - Does 1 Employee take all of the readings or are Employees assigned to specific machines?
    - For sheets Friday through Wednesday, what is FP (L2)?

    Looking at Thursday, if the AM_Master_In = 5267
    and the AM_Master_Prior_In = 5068, the difference is 199. Why would there be a difference?? Unless this has something to do with "Pulls"??

    OK, I just confused myself.
    Maybe you could explain a day/shift in the life of an Mini Casino employee. If I am working the AM shift, what is the process flow?



    And yes, you have too many tables. To start out, there would be a table for employees, a table for machines, a table for the daily info and a table for amounts per machine - I'm not quite sure what to call the columns F through K. (terminology)
    You don't want/need a table per machine.....( that would be a nightmare!)
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    Noland is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8
    Oh I'm so glad you replied I was about to just connect like terms as related and cross my fingers lol.

    OK so a day in the life of a game room attendant:

    Well shift starts at 7am. I Walk through a metal detector and ideally the night shift attendant hands me $500. This $500 is called the TURN(TRN)(or the money that he TURNS over to me at shift change that will become the first of my STARTING BANK of ordinarily $2500 ) when i arrive He should be about 1/2 way through with his FINAL PULL(FP) of the cash from the machines for his shift. On this last and FINAL PULL he will make a handwritten record of the METERS for each machine (aka the menu on each machine that shows how much cash has gone into that machine and how much its paid out since 1.)It's inception ( the MASTER_IN'S and OUT's) and 2.)Since the beginning of his shift(PERIOD_IN'S and OUT's)
    The MASTERS cannot be altered ...by anyone. However the PERIODS he can and will reset once recorded. They are then considered my METERS and my responsibility.
    (Note: that $500 I mentioned is not always $500 even. I might have to pay out that whole $500 to a winner during my first ten minutes there before hes done and he'd have to give me more to keep things rolling.)
    Each machine keeps both MASTERS and PERIODS simultaneously and so by subtracting the PRIOR shift ending MASTERS from CURRENTS shift ending MASTERS you can calculate a person's PERIODS. Owners like to go by MASTER numbers because they cannot be altered Attendants need the PERIODS so they can tally the profits.
    Throughout the day customers come in sign a sign-in sheet, find a machine and then they'll loudly call out "MATCH!!" Whereupon I go to where they are and as long as they got at least $5 to put in a machine then I'll MATCH them the first $5 they put in a machine. If someone wants to cash out they call TICKET! and as long as it's above $20 and ends in an increment of 5 then I'll clear the credits off there machine and pay them cash On average we see 200 customers a day and a little more at night. With PERIOD IN's from $5000-$10,000 and PERIOD_OUT'S are hopefully lees.
    I'll have to pull the cash from the machines severely times when I get low on bank and hopefully I'll have time to manually log all the money I pull. And 12hrs later I'll have $500 ready for the next shift when they walk in. Finish my FINAL PULL pay my security pay myself subtract my expenses, PERIOD_OUT'S leave the next shift 2500 minus the TURN and take the profit to the owner.

    A word about terminology

    The turn plus the final pull should equal all the money does that makes sense? Its termed MONEY ON THE TABLE or MOT of course there are several formulas that will get us to the money on the table or the profit for the the day. I think that the owner tries to make it the most complicated way he possibly can

  4. #4
    Noland is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8
    I just noticed I dont think I answered all of your questions.
    ok so yes by machines I mean video slots...the spreadsheet has extra rows because we've downsized and I just didn't take them out. I apologize. Some of the sheets have different layouts because back in march when those data sets were recorded I was having to fill in missing gaps from bad record keeping by working backward or piecing it together with incomplete sets of numbers
    On the Sheet That shows the full title AM_CURRENT_MASTER_IN etc...I added those titles last minute to try to help someone understand what they all meant. So for each sheet/day there are 2 shifts AM and PM.
    at the end of each shift attendants view and record the accounting data from each video slot machine as I detailed above.
    Also, oddly, Yes the "fish" is a game...If you've ever been to a chuck e. cheese or even at a Dave and Busters restaurant you'll see a video game the size of a small pool table seats about 6-10 players. Its just a video game where players collectively shoot at targets on the screen. Each shot costs anywhere from $.05 to $3.00. Players win by destroying targets and lose when they don't. And drop hundreds of dollars at a time. It's a fun and different way to play, and most people love it. Because so much money goes through it the owner likes to see the "Fish" numbers separately as well as in the grand total.

    Yes, yellow highlights are for data entry only

    For a single shift there is technically only one set of readings. But I guess you could say that the previous shift reading are My starting readings and my readings are the next shifts starting readings. But attendants generally dont see the prior shifts numbers.

    Everything they turn in is their own written paperwork. Which is exactly the problem. Cause everything they turn in is invariably missing something.

    I jump in and out of First Person when speaking of the attendants because I don't work as attendant full time. I also compile all the employees' paperwork into a spreadsheet. Hence the need to simplify and streamline, so I can stop banging my head on the wall.

    There are many other nuances to the gambling industry that mainly entail trying to protect those invested from costly human error, and since this is a cash business...theft.
    For instance we found one employee who would clear the PERIOD on a single machine half way through his shift pocket the proceeds. So I pay attention to the MASTERS. Common errors include miscalculating PERIOD totals, transposing numbers, or forgetting how much you gave the next shift as the TURN, and or mixing FINAL PULL money with BANK money.

    And lastly, yes one person does it all...tickets, matches, pulls, and paperwork. One security guy is present at all times though but attendants can and often do hire daily help as needed

    I hope I haven't come across as too overwhelming or stress-full it really is a fun job that few people ever have the joy of stressing over

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,075
    OK, here is where I am at.

    Here is the table design/relationships
    Click image for larger version. 

Name:	Relationships.png 
Views:	36 
Size:	68.6 KB 
ID:	36973


    This is what the main form looks like
    Click image for larger version. 

Name:	MainForm.png 
Views:	38 
Size:	170.4 KB 
ID:	36974


    I wasn't able to complete the design yet because I couldn't figure out what was data entry and what was a calculation.
    A lot of the calculations were different on different sheets.

    Is the "FP" (Final Pull column) data entry or calculations? Looks like data entry but it is not yellow, sooooo???

    The idea is to have a Data Entry form and a "Results of Calculations" form. You can have a very wide Excel sheet to display/enter/calculate data, but Access is not as flexible in this regard.

    Also, not having valid data to compare results makes it hard to ensure calculations are valid.
    Looking at the "Friday" worksheet, it looks like there might be a missing formula at cell K3 (the top cell in the period column). There are also missing/different calculations in different sheets.

    I just added a button to open a form to add/edit machine info......
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #6
    Noland is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8

    sorry for delay

    GAMEROOMNEW SYSTEMMOD1.zipgameroomDB mod3.zipGAMEROOM SPRDSHT MOD3 - Copy.zipO.k. First I want to apologize for how long it's taken me to say thank you for your help and assure you that it is VERY much appreciated. for the past few days I havent been able to open any document from the file explorer, and so could not zip to upload. I didn't want to send a reply with incomplete, or incongruent spreadsheets. Got it fixed finally. So I spoke with my boss to nail down something more concrete in terms of what he wants; and he wants something more individually oriented and restrictive for the attendant (i.e. periods). And he will then have another manager monitor the bigger picture who will have more freedom in terms of access to see previous (masters) meter readings, editing and so forth.
    I apologize again for the delay. I did take the time to update the spreadsheet to fix incongruencies and I switched up the formulas to reflect the switch of focus from the Masters to the periods.
    I was kind of confused looking over what you had so far. I'm posting a few powerpoint slides that me and the owner have agreed would be the most helpful

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,075
    No problem on the time.... I get busy also.

    You use Employee and Attendant: does it matter which you use? Would there be people other than Attendants in the table?


    About the table structures. You are thinking in spreadsheet, not database. For example, the tables "tbl_AM_Shift_Dates" and "tbl_PM_Shift_Dates" have the same structure (fields). These should be one table, with an additional field for AM or PM.

    Same with the tables "tblDaily_AM_MeterIN_Read", "tblDaily_AM_MeterOUT_Read", "tblDaily_PM_MeterIN_Read" and "tblDaily_PM_MeterOUT_Read". (The date field would need to be renamed)
    Either two fields need to be added (shift and In/Out) or 1 field (AM In, AM Out, PM In, PM Out).
    Otherwise, trying to create forms and reports are a real PITA..... but it could be done.


    Still looking at the spreadsheet..... still trying to understand "PERIOD_IN", "PERIOD_OUT" and "Final PULL" relationships.

    And what about "Fish" data?
    BTW, the PPT slides was a good idea to help design forms.

    About the cells in yellow. Those are inputs every day, so there needs to be fields to put the data in. See my Post #5 (table tblDailyMeterReads).
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #8
    Noland is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8
    Ok so yes Attendant and employee are interchangeable. I cant but agree that I'm stuck in spreadsheet mode. Tobe honest I took the Microsoft "Specialist" online test back in 2013 and Access was the only one I failed lol. But only by a small margin. Anyway Ill try and and incorporate the principles you mentioned. meanwhile I found the following guides online http://www.databaseanswers.org/data_...inos/index.htm I feel like this maybe should work if the right eye took a look at it.

    As for "PERIOD_IN", "PERIOD_OUT" and "Final PULL" relationships, PERIOD_IN is the total intake for that MACHINE_NUMBER for a particular shift. The total PERIOD_IN of all machines is all the cash for the day. The attendant starts the day with $2500 as that deplenishes he has to pull to replenish his cash flow Ideally he starts a pull before the cash in his pocket is gone. So the final pull of the day is done as the next shift isgetting there to clear them of any cash and reset the period meters for the upcoming shift. The money in the attendants pocket plus what he pulls from the machines is All the cash in the house or all the MONEY ON THE TABLE. 2500 is left for the next shift and the rest is profit. now if its the amount of profit that should be there or is it short is the process were trying to automate.

    Thank you ablout PPT slides

    I look forward to hammering this out.


  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,075
    OK, still processing "PERIOD_IN", "PERIOD_OUT" and "Final PULL".


    Q: Disregarding the yellow cells for the machines, do you want the rest if the yellow entry cells? (as in Column B and column O)


    Q: Do you consider "Fish" a machine? If not, how do you want to handle "Fish"?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  10. #10
    Noland is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8
    ok so I put together the most archaic and and basic powerpoint slide ever created that incorporates some photos of our actual paperwork, I try to explain our main "Cover Sheet", and I think it should help out If not youll at least get a good laugh at how basic it is. Notice how portions of the paperwork are left unfinished. I've also been trying to to revamp the spreadsheet and figure out formulas that only need the most basic info from the attendant.
    Also when you see the power point I think itll answer the questions you asked about columns "B" and "O".
    Well great now I cant get my files to upload

  11. #11
    Noland is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8
    I uploaded it to my google drive and got a shareable link
    https://drive.google.com/drive/folde...op?usp=sharing

  12. #12
    Noland is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    8
    I think machine #30 on the pictures deserves a word.
    Machine 30 is the fish game. It is from overseas and the menu on it is a little different. It doesn't have "PERIOD" meters. It only keeps a MASTER record. So we subtract the previously recorded MasterIn from the current MasterIn to get current PeriodIn. the same is done to get the PeriodOut.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Trouble normalizing data from excel to Access
    By NewUser2Database in forum Database Design
    Replies: 4
    Last Post: 10-20-2013, 01:27 PM
  2. Replies: 1
    Last Post: 05-23-2013, 09:00 AM
  3. Trouble with importing Excel Data
    By Monterey_Manzer in forum Access
    Replies: 2
    Last Post: 06-28-2012, 03:32 PM
  4. Trouble adding data with forms
    By chuck130 in forum Forms
    Replies: 3
    Last Post: 09-02-2010, 08:57 AM
  5. Replies: 1
    Last Post: 07-27-2010, 07:02 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums