Need your help ....Newbie

[CJ_London]

am still a bit confused

So am I.



I really don't think I can help any more. I clearly do not understand how the game works and failing to make myself understood. Suggest you start a new thread, reference this one for background. In the new thread give a clear description of how the game works - you get the numbers on line, you buy different numbers for different games, you play the same numbers multiple times, how they get matched etc whatever the rules of the game are.

[Accessnewbie008]

So am I.

I really don't think I can help any more. I clearly do not understand how the game works and failing to make myself understood. Suggest you start a new thread, reference this one for background. In the new thread give a clear description of how the game works - you get the numbers on line, you buy different numbers for different games, you play the same numbers multiple times, how they get matched etc whatever the rules of the game are.

Not your fault you are trying to help but its not how to understand pick3.....it should be a straightforward lookup on only exact straight matches.......460 must be matched in Copy as 460 if 460 was todays draw results. Where I dont understand your logic is where Table Copy dont have a date assigned to it.....there is only Numbers Numbers2 Numbers3 . If I upload todays data then the previous draws data will be mixed together. There is no date.......example copy table

9 Feb 17 >>>>>>>>>>70 combos splitted in 3 columns Numbers Numbers2 Numbers3
10 Feb>>>>>>>>>70 combos splitted in 3 columns Numbers Numbers2 Numbers3


Every days 70 tickets must be kept assigned under a date......then queried to ClarityData to check for a straight match >>460 >>460 ......Match/No Match

This is all I wanted Thanks Ajax

I am no database expert but shouldnt the date in copy be queried on the data table which will contain a date and the winning number for the day

[orange]

Accessnewbie008,

I am not talking about understanding Ajax's best guess to your issue. I am asking you to describe to us in plain, simple terms WHAT you are trying to do. And to provide a clear example in a step by step format.
It isn't whether or not you understand database or Access or programming, first you have to be clear in WHAT you are trying to automate and/or record.

To be clear Ajax's proposed method, as you called it, represents a possible HOW you might do something. Readers need to understand
the something which is the WHAT. If you were hiring someone to build a solution for you, they would want to know your requirements; what is the process(es) involved; what do you need to record/store. Only you know WHAT you want. It is up to you to tell readers if they are to provide help/advice.

[Bulzie]

I've read it all again and also still not clear on the process. Probably should start a new thread as suggested but if not, Accessnewbie can you read what I wrote below and give us a clear process. Correct any steps below that are not accurate and answer the question I posed at the end.

1. You buy a pick3 ticket. This ticket has 3 numbers, each number is from 0-9 so only 3 digits on a ticket.
2. Lottery does a Draw and picks 3 numbers and your ticket 3 numbers mush match their 3 numbers exactly to win?
3. Lottery does a Pick3 Draw 4 times a day where they choose 3 numbers each draw?
4. Your ticket can win any of those 4 draws if they match? Or if you buy a ticket at 8am and they draw at 9am, that ticket after 9am is now used and cannot win the other 3 draws for the day? Or not?
5. You want to have a process where you can enter the draw tickets and your purchased tickets into separate tables where you can see if the draw ticket matches your purchased tickets? So lets say you buy 1 ticket and they do 4 draws a day, in your system you will have a table with 3 fields holding your 3 numbers and another table that will have the 4 draws (3 numbers each) so will have 4 rows with 3 numbers each along with the date of the draw?

6. Questions for you to answer in detail:
a. What do you mean in your previous posts by there could be 70+ numbers? Are these tickets, the individual number on each ticket(3 per ticket)?
b. How many tickets do you buy a day or for a draw?
c. Are you importing these ticket values from a file into the Access table or are you manually adding all the numbers needed in both tables into your database?

[Accessnewbie008]

Accessnewbie008,

I am not talking about understanding Ajax's best guess to your issue. I am asking you to describe to us in plain, simple terms WHAT you are trying to do. And to provide a clear example in a step by step format.
It isn't whether or not you understand database or Access or programming, first you have to be clear in WHAT you are trying to automate and/or record.

To be clear Ajax's proposed method, as you called it, represents a possible HOW you might do something. Readers need to understand
the something which is the WHAT. If you were hiring someone to build a solution for you, they would want to know your requirements; what is the process(es) involved; what do you need to record/store. Only you know WHAT you want. It is up to you to tell readers if they are to provide help/advice.

Thank you Orange and I fully understand


In my previous post it describes exactly what I am trying to achieve Organge. I am looking for a facility to do backtesting ...I can check today if I had winners. I can check 29 January if I had winners on January's 29ths data. So I dont know how I can explain it more simply. The solution I am looking for is plain forward and simple. It doent have to anything about pick3 positioning - I have mentioned ignore and look for straight wins....460 must be 460. I am looking for a straight forward way to check 9 Feb here was my 70 combos.....those combos get checked on the master table Claritydata which contain the winning number.Every day I will export 70 numbers which should be under a day...There is no way to do backtesting if the data is all lying in numbers Numbers2 Numbers3......There should be a field for dateI will export every days 70 numbers but it should be in under a day for backtesting purposes. My numbers for today will not be the same as yesterdays numbers so thats why it should be kept seperate and for backtesting purposes. I am sure its a database we are talking about here as someone told me to do this it should be done with a database. I hope someone can understand and possible give me pointers in achieving this.....or tell me straight its not possible. Lets not complicate the matter in 3 way 6 way......What I am looking for is a straight forward way 460 was the winning numbers for today....it must be checked under 10 Feb 70 Numbers Numbers2 Numbers3 in table Copy.....so fully able to perform backtesting is what I am looking for. If I missed a weeks back draw....I can run a query on the week backs draw to see if I had a match.The solution Ajax proposed all the numbers in copy will be copied into Copy.....It can ONLY check for 1 Draw and sorry thats not the solution I am looking for. But thanks Ajax for your help and your time I am sure your an expert but you did not understand my logik ......Maybe someone should just tell me what I am looking for is not possible. I know more of excell but excell is not a database

[orange]

My previous post it describes exactly what I am trying to achieve

not to me, and many others, else you wouldn't be at 50 posts and readers/responders still unclear and confused.

It sounds to me, after a brief scan of the thread (and post 49 by Bulzie)

-You want to record the winning numbers for each Pick3 draw for each day the draw occurs/occurred.
-There are 4 draws per day
-You have your own numbers for each/some draws
-You want to check whether your numbers match the winning numbers for a specific draw
-You want to be able check your numbers against winning numbers for any draw.

Please revise as appropriate until we agree on what you're trying to do.

Possible structure to support my guess.

tblPick3Info
Pick3ID PK
DrawDate
DrawSeq 'if there is an order to multiple draws on a given Date
WinningNum text '

If you use the same combination (3 digits) whether 1 or 70, you could make it a constant in Access

Dim qrySQL as string
Dim MyCmb as string
MyCmb ="'023','048','049','050','111','121','122'.... ." '<---you combination/selections

Then in query

qrySQL = "Select * from tblPick3Info Where WinningNum In ( '" & MyCmb & "')"

Good luck

Mock up:

PickId	PickDate	WinningNum
1	14-Dec-2016	341
2	20-Dec-2017	298
3	21-Dec-2016	075
4	29-Dec-2016	111
5	09-Jan-2017	273
6	16-Jan-2017	102
7	18-Jan-2017	049

Select * from tblPick3Info Where WinningNum In ('023','048','049','050','111','121','122')

Result of query

PickId	PickDate	WinningNum
4	29-Dec-2016	111
7	18-Jan-2017	049

[Accessnewbie008]

I've read it all again and also still not clear on the process. Probably should start a new thread as suggested but if not, Accessnewbie can you read what I wrote below and give us a clear process. Correct any steps below that are not accurate and answer the question I posed at the end.

1. You buy a pick3 ticket. This ticket has 3 numbers, each number is from 0-9 so only 3 digits on a ticket.
2. Lottery does a Draw and picks 3 numbers and your ticket 3 numbers mush match their 3 numbers exactly to win?
3. Lottery does a Pick3 Draw 4 times a day where they choose 3 numbers each draw?
4. Your ticket can win any of those 4 draws if they match? Or if you buy a ticket at 8am and they draw at 9am, that ticket after 9am is now used and cannot win the other 3 draws for the day? Or not?
5. You want to have a process where you can enter the draw tickets and your purchased tickets into separate tables where you can see if the draw ticket matches your purchased tickets? So lets say you buy 1 ticket and they do 4 draws a day, in your system you will have a table with 3 fields holding your 3 numbers and another table that will have the 4 draws (3 numbers each) so will have 4 rows with 3 numbers each along with the date of the draw?

6. Questions for you to answer in detail:
a. What do you mean in your previous posts by there could be 70+ numbers? Are these tickets, the individual number on each ticket(3 per ticket)?
b. How many tickets do you buy a day or for a draw?
c. Are you importing these ticket values from a file into the Access table or are you manually adding all the numbers needed in both tables into your database?

Bulzie thanks for your response. It sound way too complicated.....its actually simple. No I dont have 4 draws a day. We have 1 draw a day at Night. So we are talking of 1 draw a day and not 4 draws a day. Hope thats clear as I know some US states have more than 1 draw a day. In my case ONLY 1 draw in the evening.

I have a spreadsheet that work out 70-90 numbers a day. I want to store that in the database under a draw/date.
The amount of tickets is irrelevant I have 70-90 combos in the form of 532....thats a sample 1 combo nr.To comply it need to have 3 digits next to each other.. I normally only play 6 number combos a day on the ticket I fill in.
YESS I am importing the numbers from an excell spreadsheet.....3 columns which I named Numbers Numbers2 Numbers3. For this example I have manually imported the data via a wizzard but I know this can be done via VBA if its setup correctly. Thank you Bulzie hope this is clearer now. I apologize like I said I dont fully understand the access database setup and queries thats why I am asking your help. Once again thanks for any help. I am more clued up in Excell....can help myself there better

[Accessnewbie008]

My previous post it describes exactly what I am trying to achieve

not to me, and many others, else you wouldn't be at 50 posts and readers/responders still unclear and confused.

It sounds to me, after a brief scan of the thread (and post 49 by Bulzie)

-You want to record the winning numbers for each Pick3 draw for each day the draw occurs/occurred. YES
-There are 4 draws per day. NO ONLY 1
-You have your own numbers for each/some draws.GET WORKED OUT FROM SPREEDSHEET A DAY YESS
-You want to check whether your numbers match the winning numbers for a specific draw. CORRECT
-You want to be able check your numbers against winning numbers for any draw.INCORRECT NO DATE SPECIFIC. THE RESULT FOR 8FEB MUST BE CHECKED ON 8FEB DATA ONLY THATS WHY IT MUST BE ORGANIZED. BUT I WANT TO CHECK A YEARS BACK DATA FOR THAT SPECIFICS DATE DATA

Please revise as appropriate until we agree on what you're trying to do.

Possible structure to support my guess.

tblMyPick3Info
Pick3ID PK
DrawDate
DrawSeq 'if there is an order to multiple draws on a given Date
MyNum Text 'have to allow for leading 0
WinningNum text '

Good luck

-You want to record the winning numbers for each Pick3 draw for each day the draw occurs/occurred. YES
-There are 4 draws per day. NO ONLY 1
-You have your own numbers for each/some draws.GET WORKED OUT FROM SPREEDSHEET A DAY YESS
-You want to check whether your numbers match the winning numbers for a specific draw. CORRECT
-You want to be able check your numbers against winning numbers for any draw.INCORRECT NO DATE SPECIFIC. THE RESULT FOR 8FEB MUST BE CHECKED ON 8FEB DATA ONLY THATS WHY IT MUST BE ORGANIZED. BUT I WANT TO CHECK A YEARS BACK DATA FOR THAT SPECIFICS DATE DATA....20 MAY 2011 the results was 511.....That must be checked for draw 542 which was 20MAY 2011. So 20 MAY 2011 70 numbers must be checked

[orange]

I modified my previous response while you were typing.
I tried to mock up some sample data---see it for info. I assumed you used a constant set of choices, but that was just a sample.

You could import you daily choices from Excel and store in Access. You would need to record DrawDate and Combination.
If you make 50 selections on Feb 27 2017, you would have 50 records in Access. Each record would have DrawDate = Feb 27 2017 and the combination/choice.

A routine could be created to select your combinations/choices for a date and construct and execute a query.

[Accessnewbie008]

Fabulous thank you Orange can you perhaps upload it for me please to test......Yes I would love to learn.

[Accessnewbie008]

My previous post it describes exactly what I am trying to achieve

not to me, and many others, else you wouldn't be at 50 posts and readers/responders still unclear and confused.

It sounds to me, after a brief scan of the thread (and post 49 by Bulzie)

-You want to record the winning numbers for each Pick3 draw for each day the draw occurs/occurred.
-There are 4 draws per day
-You have your own numbers for each/some draws
-You want to check whether your numbers match the winning numbers for a specific draw
-You want to be able check your numbers against winning numbers for any draw.

Please revise as appropriate until we agree on what you're trying to do.

Possible structure to support my guess.

tblPick3Info
Pick3ID PK
DrawDate
DrawSeq 'if there is an order to multiple draws on a given Date
WinningNum text '

If you use the same combination (3 digits) whether 1 or 70, you could make it a constant in Access

Dim qrySQL as string
Dim MyCmb as string
MyCmb ="'023','048','049','050','111','121','122'.... ." '<---you combination/selections

Then in query

qrySQL = "Select * from tblPick3Info Where WinningNum In ( '" & MyCmb & "')"

Good luck

Mock up:

PickId	PickDate	WinningNum
1	14-Dec-2016	341
2	20-Dec-2017	298
3	21-Dec-2016	075
4	29-Dec-2016	111
5	09-Jan-2017	273
6	16-Jan-2017	102
7	18-Jan-2017	049

Select * from tblPick3Info Where WinningNum In ('023','048','049','050','111','121','122')

Result of query

PickId	PickDate	WinningNum
4	29-Dec-2016	111
7	18-Jan-2017	049

This look fantastic!!!!!!!! Thank you Orange

[Accessnewbie008]

I modified my previous response while you were typing.
I tried to mock up some sample data---see it for info. I assumed you used a constant set of choices, but that was just a sample.

You could import you daily choices from Excel and store in Access. You would need to record DrawDate and Combination.
If you make 50 selections on Feb 27 2017, you would have 50 records in Access. Each record would have DrawDate = Feb 27 2017 and the combination/choice.

A routine could be created to select your combinations/choices for a date and construct and execute a query.

100% fully agree that is it......give and take 70-80 combos.......some days its less it can be 30. I have seen I normally get approx 70-80 combinations give and take...some days I can get less so your statement is 100% valid thank you Orange. Ballpark to go on is 70 combos as that is the average

[Accessnewbie008]

This look fantastic!!!!!!!! Thank you Orange

Should I create a new database with these fields......so only 1 Table ?

tblPick3Info
Pick3ID PK
DrawDate
DrawSeq 'if there is an order to multiple draws on a given Date
WinningNum text '

[Bulzie]

never mind

[orange]

Accessnewbie,

I don't know what your xls file looks like, but I mocked up a sheet that contains a DrawDate with 11 choice fields. I am not an Excel person.
You can adjust the code to allow for as many fields as you need.

I have a routine that imports the your choices data from Excel to an Access table.
Also I'm using the tblPick3Info info to show winning numbers by Drawdate .

The basic logic of the routine is:
-read the list of your choices
-for each Date, assemble your choices to create a list
-the list completes query SQL to compare your choice info with winning number info
-any matches result in a print out to immediate window showing the Draw ID, Drawdate and winning number.

Here is the mock up data and results:

PickId	PickDate	WinningNum
1	14-Dec-16	341
2	20-Dec-17	298
3	21-Dec-16	075
4	29-Dec-16	111
5	09-Jan-17	273
6	12-Jan-17	102
7	18-Jan-17	049

The routine to compare the choices and winning numbers by Date

'---------------------------------------------------------------------------------------
' Procedure : PickSetUp
' Author    : mellon
' Date      : 10-Feb-2017
' Purpose   :
'---------------------------------------------------------------------------------------
'
Sub PickSetUp()
' uses this ImportXLSheetsAsTables to get pick3data from excel
'''need to include a reference to Excel !!!!!!!!!!!!

'This is a demo to bring my custom selections from Excel
'and to use those selections to see if I have any matching (winning numbers)
'that are recorded in tblPick3Info

'the table with excel data is tbl_MyPick3Selections
'it has DrawDate, followed by a variable number of selections
'the  selctions/choices fields are named F2....F11 (in this demo)

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rres As DAO.Recordset
    Dim i As Integer
    Dim InList As String
    Dim qrySQL As String


10    On Error GoTo PickSetUp_Error
20    Debug.Print "Started"
30    Set db = CurrentDb
40    Set rs = db.OpenRecordset("tbl_MyPick3Selections", dbOpenSnapshot)
50    Do While Not rs.EOF

60      For i = 2 To rs.Fields.Count - 1

70          If Not IsNull(rs.Fields(i)) Then
80              InList = InList & "'" & rs.Fields(i) & "',"
90          Else
100         End If
110     Next i
120     InList = Mid(InList, 1, Len(InList) - 1)
130     InList = "(" & InList & ")"
140     qrySQL = "SELECT * From tblPick3Info " _
                 & " WHERE PickDate = " & rs!Drawdate & "  and WinningNum In " & InList
        'Debug.Print qrySQL

150     Set rres = db.OpenRecordset(qrySQL, dbOpenSnapshot)
160     Do While Not rres.EOF
170         Debug.Print rres!PickID; rres!PickDate; rres!WinningNum
180         rres.MoveNext
190     Loop
200
210     InList = ""
220     rs.MoveNext
230   Loop
240       Debug.Print "Finished "
250   On Error GoTo 0
260   Exit Sub

PickSetUp_Error:

270   MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure PickSetUp of Module ImportExcelSheets"

End Sub

The Result in the immediate window:

Started
 4 29-Dec-2016 111
 6 12-Jan-2017 102
Finished

This is the code I used to bring the xls data into Access. I simply adjusted some old existing code. As I said I'm not an Excel person

'---------------------------------------------------------------------------------------
' Procedure : ImportXLSheetsAsTables
' Author    : jed
' Date      : 1/16/2009
' Purpose   : To import all worksheets in a specified workbook into
'             individual tables in Access.
' Tables get names: Tbl_ + name of the worksheet
'
' Modification Jan 16 2009 --
'             --- in Transferspreadsheet note the exclamation mark !!!
'
' NOTE: Must have a reference to the Microsoft Excel Object Library
'---------------------------------------------------------------------------------------
'
Sub ImportXLSheetsAsTables()
      Dim appexcel As Excel.Application
      Dim wb As Excel.Workbook
      Dim sh As Excel.Worksheet
      Dim strValue As String


10       On Error GoTo ImportXLSheetsAsTables_Error

20    Set appexcel = CreateObject("Excel.Application")
30    Set wb = appexcel.Workbooks.Open("C:\users\mellon\documents\MyPick3Choices.xlsx")  '"C:\test\jillian_2006-03-16.xls")
40    For Each sh In wb.Sheets
50    Debug.Print sh.name
      ' DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_" & sh.name, "C:\test\Jillian_2006-03-16.xls", True, sh.name & "!"
60    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_MYPick3Selections", "C:\users\mellon\documents\MyPick3Choices.xlsx"

70    Next

80    wb.Close
90    appexcel.Quit



100      On Error GoTo 0
110      Exit Sub

ImportXLSheetsAsTables_Error:

120       MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure ImportXLSheetsAsTables of Module ImportExcelSheets"

End Sub

This was my choices data

Drawdate
F2
F3
F4
F5
F6
F7
F8
F9
F10
F11

42732
012
023
036
123
127
126
345
456
678
789

42733
234
123
111
876
309











42747
002
003
004
100
104
102
222
333
456
111