Database Tutorial and Free VB code

This is a v1.0 draft of a quick introduction to the tables, queries and VB code section of the Access database used as a lotto data-mining tool.  Although I would agree with the sentiment that there are no shortcuts in learning programming (like learning music or Math you see it or you don’t) there are a few automation shortcuts in the Access database, such as the use embedded SQL (Structured Query Language) in the VB editor to enable getting things done quickly. 
 
The modern database is built around the relational model, which groups pieces of data into similar entities as a means to reduce redundant data (an oversimplification, therefore the "1^st draft" label to this bloogie).  Your table(s) will generally model or be defined by what you're trying to track. In this case it will be a Pick 5 game.  At the most granular level, you are generally asking questions about certain metrics, when did this # last show up? Or how many times in the last 7 10, 20, … 100 draws?  Once you have built one test, you build another…and so on.  Perhaps a new unexpected insight leads to yet another (15 is picked 5x in the last 6 draws… what other things happen when that occurs?).  I like the following metaphor to describe the most used parts of the Database: the tables are the chassis of the car, the queries are the engine, the forms are the doors and/or windows and the reports are the chrome.  This write up will focus on tables and queries and hopefully work its way to moving the queries to code so folks can build, test and win.  I’ll call it a draft because if there are questions, I’ll fill in some of the blanks and because this draft is by no means exhaustive to the topics.
 
Table Design The rules of table design can be thought of as a blend of Art and Science, but they “are really just formalized common sense” as famously quoted by Database guru and author C.J. Date.  Although the most often books list normalization rules up to “third normal form” we’ll concern ourselves with the first two when modeling/tracking attributes of a pick 5 lotto game.  First Normal Form states that data in the tables’ fields must be atomic, that is broken down to the smallest unit (example: store someone’s name as last name and first name separately, so if you have to specify a criteria on a name it is easier to do when one field contains one value only).  Second Normal Form has to be with not allowing redundant fields in the relational table structure.  One will want to employ the primary key concept, which enforces uniqueness in a column that in turn protects against duplicate data and/or missing values.
 
When you create fields for your DB tables, do not include spaces in field names "FirstName" (NOT "First Name") because when you use the field in code you'll have to bracket it  "[First Name]" otherwise you'll trigger an error.  Similarly don't start your fields with a digit or those other non Alpha-numeric fields eg. "-" because your code will think it is the subtraction operator which is likely not your intention.
 
Basic Queries  Most of requested uses of SQL (Structured Query Language) are straightforward, though there are many esoteric parts that can get tricky.  The basic structure is:
 
SELECT fieldname1, fieldname2
FROM YourTableName
WHERE fieldname1 = 123
 
When joining tables in a query, there are different kinds of joins that can be done that mirror some of the different kinds of relationships among tables and other queries that typically exist in a database (one to one, one to many, many to many is not supported directly; it requires an intermediate table for a lookup work-around).  
 
There are basic SELECT queries and Action queries (INSERT, APPEND, UPDATE, DELETE).  You're not required to capitalize the Keywords, it is just good practice for the sake of readability; you are doing yourself a favor down the line when you return to the code and you want to quickly figure out its meaning without draining your brain of horsepower at 3PM or 3AM.  There are also Aggregate queries, which allow you to easily get Sums, Averages, Min, Max, Variance, Standard Dev; your basic "Descriptive Statistics" functions built in.  You can call your own user defined function, provided that it is already saved in your Module window of your database.
 
When learning how to work with queries, I'd strongly recommend going to the pull down "View" menu and select "SQL" view so you can read what you are doing, it is pretty close to standard English.  Once familiar with SQL it can be placed in the VB Editor as embedded SQL, which can be a fast method for automating various routines and functions.  Below is an example that can be strung together as part of a larger program to suit your needs.
 
' a brief example of SQL embedded in the VB editor; an update query is shown
Docmd.RunSQL "UPDATE tblData SET D1 = 2 WHERE D1=1;"
 
The UNION query is also a very useful construct.  It allows you to append one recordset to another, provided that both recordsets have the same fields, in the same order and are of the same datatype. 
UNION ALL will include duplicates in the resulting recordset, where UNION will quietly not include duplicate records.  You could add a WHERE clause to filter this by Date range, for example.
'save this query as "UNION_ALLDraws_1col"
SELECT tblData.D1
FROM tblData
UNION ALL
SELECT tblData.D2
FROM tblData
UNION ALL
SELECT tblData.D3
FROM tblData
UNION ALL
SELECT tblData.D4
FROM tblData
UNION ALL
SELECT tblData.D5
FROM tblData;
 
' this returns the frequency count of all 39 numbers , when query above has been named as instructed.
SELECT UNION_ALLDraws_1col.D1, Count(UNION_ALLDraws_1col.D1) AS CountOfD1
FROM UNION_ALLDraws_1col
GROUP BY UNION_ALLDraws_1col.D1;
 
 

Variables in your code  For this introductory article, I think we’ll keep the variables inside the function (“at the procedure level”) in which they operate to be able to skim over global variables.  The time during which a variable retains its value is known as its lifetime.  The value of a variable may change over its lifetime, but it retains some value.  When a variable loses scope, it no longer has a value.A procedure-level variable declared with the Dim statement retains a value until the procedure is finished running. If the procedure calls other procedures, the variable retains its value while those procedures are running as well. If a procedure-level variable is declared with the Static keyword, the variable retains its value as long as code is running in any module. When all code has finished running, the variable loses its scope and its value. Its lifetime is the same as a module-level variable.  Also always have "Option Explicit" at the top of each code or form module. Otherwise you will think that a variable is in scope because the code compiles, whereas what is acually happening is that a new (local) variable has been created on the fly.