Active TopicsActive Topics  Display List of Forum MembersMemberlist  Search The ForumSearch  HelpHelp
  RegisterRegister  LoginLogin
Lotto Number Analysis Using Excel, Access and Visual Basic
 LottoPoster Forums : LOTTO PROBABILITY, COVERAGE AND PROGRAMMING : Lotto Number Analysis Using Excel, Access and Visual Basic
Message Icon Topic: Store Lotto draws in Access database using Excel Post Reply Post New Topic
Author Message
Colin F
Lotto Systems Tester Creator & Analyst
Lotto Systems Tester Creator & Analyst
To dream the impossible dream ...

Joined: September 30 2004
Location: Australia
Online Status: Offline
Posts: 678
Quote Colin F Replybullet Topic: Store Lotto draws in Access database using Excel
    Posted: December 04 2009 at 10:01pm

Lotto number enthusiasts overwhelmingly use Microsoft Excel as their analysis program and a common task is to examine a Lotto game's history of draws to quantify the occurrence of the integers over a given number of draws and the recency or absence of an integer, for whatever reason. Commonly, this information is given by the Lotto operators on their websites but what if you want to do some further calculations?

If you exclude VBA code because the Lotto people using Excel either don't understand it or maybe don't want to and prefer something they can follow, then how does one go about it with the proviso that the draws are stored in a database?

These are the objectives: -

  1. Create an Access database using MSQRY32.exe to store the draw data. Add draws to a table in the database using an updateable query. (It is quite easy to provide  some text boxes on the worksheet or a user form to enter the draw and add another record to the database table using VBA but we have excluded that for this exercise.) 
  2. Due to the inability to do a Group/Count/Sort query on a previously done Union query in MSQRY32 (it doesn't store queries in the Access database) use the IIF function in an update query to flag the integers picked per draw or either use the IIF function on the fly to calculate the values in the query or simply return the draw data and use the Excel CountIf function on the woksheet.
  3. Use a parameter select query to link the data and bring in a prescibed number of most recent draws to an Excel spreadsheet or alternatively a Top N query. The user can opt to refresh this data in a number of ways.

The steps to achieve this: -

1   If you have Microsoft Excel you should have MSQRY32.exe; if not you need to reload your Office installation disk and make sure to include it. (Your files etc are unaffected for your current installation - it simply adds some extra programs which are not necessarily included in a standard installation. After finding MSQRY32.exe create a shortcut on your desktop (along with one for Microsoft Excel).

2   Start MSQRY32 and from the menu click File then New to bring up the Choose Data Source dialog. Making sure <New Data Source> is highlighted click OK and then in the Create New Datasource dialog enter Lotto as the data source name and Microsoft Access Driver (*.mdb) as the driver then click the connect button.

3   From the ODBC Microsoft Access Setup dialog click the Create button. Enter in the New Database dialog UK649.mdb for the Database Name and make sure in the Directories that it will be located in the root directory ie C:\ and not in some lengthy default location. Click OK to the messagebox informing that the database has been successfully created and in turn OK to the other dialogs. Close the add tables dialog (don't have any yet) and close the query.

4   From the menu click on File then Table definition. In the Choose Data Source dialog select Lotto which was created in step 2 and then OK. In the Select Table dialog click on New and enter tblUK649 for the table name and then the following 8 fields with the type: -
1 field    DrawDate   Date/Time

1 field    DrawID       Integer
6 fields  B1 to B6     Byte
After entering all fields click create and then in the Select Table dialog highlight tblUK649 and click on Index. Enter PK as the Index Name and select DrawID as the index field.

5   Now we enter some draw data in the table tblUK649 created in step 4 by using an updateable query as unlike Access you cannot enter data directly into a table. From the menu click New then in the Choose Data Source select Lotto then OK. From the Add Tables dialog highlight tblUK649, click Add then Close. Drag in turn the fields DrawDate, DrawID and B1 to B6 on to the first row of the grid. From the menu click Records then Allow Editing. Click the bang ! or Query Now button and enter some draw data lines. Save the query as EnterDraws. This query can now be accessed to run from File then Open.

6   To create the parameter query that provides the data for Excel from the menu click file then New. In the Choose Data Source dialog select Lotto and make sure Use the Query Wizard ... is unchecked. In the Add Tables dialog C:\UK649 should be showing as the database. Highlight tblUK649 click Add then Close. From tblUK649 drag in turn DrawDate, DrawID and B1 to B6 to the first row of the results pane. Click Show Results (!) and the draws you have entered should appear. From the menu click View and check Criteria. In the Criteria pane select DrawID from the dropdown in Criteria Field and for the Value type >=[Enter start DrawID]. Clicking on ! now should bring up a prompt asking for the start DrawID. From the menu click Records then Sort. Select DrawID for the column and the Descending option button. Save the query as qryUK649DrawsOnly.

7   To set up your worksheet open Excel and enter the 49 integers leaving a free adjacent cell. Use the screenshots below as a guide. Locate your cursor in the starting cell for the data you will bring in from MSQry32. From the menu click Data then Run saved Query. Select qryUK649DrawsOnly and enter a start DrawID in the prompt dialog. For the value cell associated with each integer enter the following formula =CountIf(C2:H1000,1) which you will need to change for each integer. C2 is the top left start cell for the range and H1000 is the lower right limit cell which remain the same and 1 corresponds to the integer up to 49.

8   The routine for using is to first open MSQry32.exe if you need to add a draw clicking File from the menu, Open and then the EnterDraws query. To view and analyze the data open Excel the relevant Workbook and worksheet. To refresh the data make sure your cursor is in a data cell associated with your query then from the menu click Data then Refresh Data or simply click on the bang (!) button on the toolbar if displayed.

Using a parameter query to return specified number of draws (51 in this case):

 Using a parameter or top n query to return draws and occurrence data: -


Top 50 query including calculated Occurrence fields and Absence calculated in Excel

As a relational database person using a flat file Excel worksheet for data storage is anathema to me. If you are an Excel user this article may serve as an introduction to the benefits of using a database to store data that can be brought into Excel. In this example they are: -

  1. The latest draw will always be at the top of the spreadsheet.
  2. More than one spreadsheet can use the same database with different queries.
  3. Number of draws not limited to sheet size.
  4. SQL can be used to create different queries
  5. Multi-table queries can be created.

Colin Fairbrother


IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

This page was generated in 0.104 seconds.