Page 5 of 5 FirstFirst 12345
Results 61 to 74 of 74
  1. #61
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43


    Quote Originally Posted by orange View Post
    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

    Code:
    '---------------------------------------------------------------------------------------
    ' 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:

    Code:
    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

    Code:
    '---------------------------------------------------------------------------------------
    ' 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
    Code:
    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
    Created the database but I am stumped...... had Numbers Numbers Numbers3 ...in the database you had me created there is no reference to that? Where must I input the data for the 70 Daily nrs. I am stumped Your samples are WOW.....thanks Orange. Another thing your choice data is in row format. In my case I had Column B and Column C and Column D with a heading Numbers Numbers2 Numbers3 with the 70 numbers below the heading

  2. #62
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As I said, I'm not an Excel whiz.
    Post a copy of your sample excel xls file in zip format, and I'll look at it.

    Why do you put the data into Excel? Could you put them in Access?
    Could move all your current data to Access, then just use Access for the whole thing in future?

    I am attaching the xlsx file I used in my demo.
    Attached Files Attached Files
    Last edited by orange; 02-11-2017 at 06:11 PM.

  3. #63
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    AHaaaa making more sense now.......so you import the numbers under a drawdate. So I must redesign my spreedsheet in a row sheet basically. Thanks for clearing that up.So I can alltogether ignore Numbers Numbers Numbers3.Thank you Orange.I will hoping to close this Sunday will report back to you



    Uploaded my data in the same file.....sheet Mydata as requested

    I can possible look into that Orange......I will be able to set up and query the database from out of Excel correct ? Thats possible I know.So your suggestion is good
    Attached Files Attached Files

  4. #64
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    As you will see I have lots of data....the current draw gave me over 140 numbers......this can also happen but its rare. Just from a viewing point of view a column is better.A row setup works if you dont have lots of data. I will have to redesign.....But thank you it starting to make sense to me now

  5. #65
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    Just a question.....The drawdate I must change from Date/Time to Text as you are importing Numbers and a date field ??? The dtabase design I have setup is as follows (Just want to double check)

    Pick3ID PK .....Autonumber
    Drawdate.......Date/Time
    DrawSequence...Number
    WinningNumber Text

  6. #66
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What do numbers1, numbers2, numbers3 mean to you. How do you know which draw date is involved??

    You can not use a number data type because you need to allow for a leading 0 eg 098. Numbers don't have leading 0's

    Code:
    Pick3ID PK .....Autonumber
    Drawdate.......Date/Time <<<<<---------Date/Time in Access to show which Date this winning number applies to
    DrawSequence...Number<------------------------you don't need this only 1 draw per day
    WinningNumber Text
    In my spreadsheet I put in Drawdate (in Date format) and left the other column headers blank.
    When I imported the xls data to Access it built a table with DRawDate and all F2..F3...field names.
    I changed the first one to DrawDate as Date/time. But it imported as a number. I could change it to a "readable" date
    using Format(DrawDate,"Medium Date")

  7. #67
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    Quote Originally Posted by orange View Post
    What do numbers1, numbers2, numbers3 mean to you. How do you know which draw date is involved??

    You can not use a number data type because you need to allow for a leading 0 eg 098. Numbers don't have leading 0's

    Code:
    Pick3ID PK .....Autonumber
    Drawdate.......Date/Time <<<<<---------Date/Time in Access to show which Date this winning number applies to
    DrawSequence...Number<------------------------you don't need this only 1 draw per day
    WinningNumber Text
    In my spreadsheet I put in Drawdate (in Date format) and left the other column headers blank.
    When I imported the xls data to Access it built a table with DRawDate and all F2..F3...field names.
    I changed the first one to DrawDate as Date/time. But it imported as a number. I could change it to a "readable" date
    using Format(DrawDate,"Medium Date")
    I could have put in Column A1 this formula =TODAY() .....that would give you
    2017/02/12
    But ok I wonder now I have went and made a manual database with those fields. Should I rather do exactly the same...import spreadsheet so that access create it ? What would you recommend.Thanks I hope to close this thread today as I starting to see light.Thanks for your help Orange you are a master in access

  8. #68
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In your xls you have column of data, but I don't see a date.
    How do you know which Date/Draw your column of values apply to?

    Do you still need the old data? Why?

    If you are going to keep historic data, you need to record:
    --Your choices, and the Date these choices apply to
    --Separately, you need to record DrawDate and Winning number.

    Do you want to do it all in Access- you don't need both Access and Excel?

    I hope to close this thread today as I starting to see light
    .
    ??? Hmmm? We're still trying to get the details/requirements> ???

  9. #69
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43

    A way

    Quote Originally Posted by orange View Post
    In your xls you have column of data, but I don't see a date.
    How do you know which Date/Draw your column of values apply to?

    Do you still need the old data? Why?

    If you are going to keep historic data, you need to record:
    --Your choices, and the Date these choices apply to
    --Separately, you need to record DrawDate and Winning number.

    Do you want to do it all in Access- you don't need both Access and Excel?
    I see what you say I could delete numbers numbers2 numbers3 and replaced all 3 by =Today() will have a the date asigned to it so that will be a an easy fix. For now I just want it to work I dont mind using a macro to split the cells in a row....I can do that too. the method I use use excel so I only want access for the backtesting query to see if I had matches and storing of data...and like you said Access is the way.So I wont be able to throw away the method which is excel. Hope it make sense.Thanks Orange

  10. #70
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    I have succesfully got this to work.....Orange thank you very much for your help and solution. Bulzie Ajax too for helping the Noob. Thank you its time for me to learn access.

  11. #71
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    well done, what was the final solution? Might help others who have a similar requirement

  12. #72
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  13. #73
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    Quote Originally Posted by Ajax View Post
    well done, what was the final solution? Might help others who have a similar requirement
    From excel I have used a macro to sort the data into rows...so basically it feeded the data into the rows. Believe me I had a few grey hairs as it was tricky

  14. #74
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    Quote Originally Posted by orange View Post
    I agree with ajax --well done. You could post your solution to show others.
    Sure Macro in Excel
    Code:
    Option Explicit
    Sub copy_data()
    
    Dim ws1 As Worksheet 'the mydata sheet
    Dim ws2 As Worksheet 'the destination sheet
    Dim myDate As Range 'the cell matching the date
    Dim myRow As Long 'this will be the row number where you want to paste the data
    
    Set ws1 = Worksheets("Mydata")
    Set ws2 = Worksheets("Sheet1")
    Set myDate = ws2.Range("A:A").Find(ws1.Range("H1").Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows) '
    If myDate Is Nothing Then
        MsgBox "The date was not found in Sheet1, no data will be copied."
        Exit Sub
    End If
    myRow = myDate.Row 'myRow is now a variable with the row number where you want to paste your data.
    
    'code here to actually copy/paste the necessary data
    
    End Sub

Page 5 of 5 FirstFirst 12345
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Newbie needs help
    By Donatello in forum Access
    Replies: 5
    Last Post: 06-09-2016, 01:36 PM
  2. A newbie here...looking for some help
    By ryanna1978 in forum Forms
    Replies: 2
    Last Post: 03-28-2013, 03:57 PM
  3. newbie needs help
    By ianhaney28 in forum Access
    Replies: 1
    Last Post: 04-18-2012, 10:09 AM
  4. Newbie needs help
    By Daryl2106 in forum Access
    Replies: 8
    Last Post: 01-24-2012, 09:12 PM
  5. can you help a newbie out
    By jayjayuk in forum Access
    Replies: 1
    Last Post: 10-10-2010, 07:42 PM

Posting Permissions

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