Results 1 to 3 of 3
  1. #1
    ScottDar is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    1

    Keno Database


    Hello all!

    New to the forum here. I am developing a Keno database to track when/how often numbers are drawn. From the lottery website, the numbers drawn look like this: 01-02-55-80-26-22-36-45-63-12-04-06.
    My goal is to take this data into a query, to find out how many times each number is drawn. I have a query setup to count how many times "01" is drawn. I use a query with the criteria Like "*01*", and Totals turned on to count the results. This works for the number 01 or 02 or 03, etc individually, but I can't find a way for all of the counts to show up in 1 query.

    Ideally, I am looking for a way to make a query with the draw numbers 01 through 80 in 1 column, and then the counts in the next column for each number. Would this be a crosstab query? If so, I could use some help with the specifics. Thank you all soo much for your time and help with this!

    Scott

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Your table structure is not normalized. Each number drawn should be in its own record. Then a simple aggregate query (not a crosstab) should provide the count for each number.

    Drawings
    DrawID DrawDate
    1 1/1/2019
    2 1/2/2019

    DrawingNumbers
    ID DrawID_FK DrawNumber
    1 1 1
    2 1 2
    3 1 55
    4 1 80
    5 1 26
    6 1 22
    7 1 36
    8 1 45
    9 1 63
    10 1 12
    11 1 4
    12 1 6
    13 2 13
    ...
    SELECT DrawNumber, Count(*) AS CountNums FROM DrawingNumbers GROUP BY DrawNumber;

    Otherwise, a UNION query can parse the string of numbers and rearrange into the DrawingNumbers table structure should be in first place. Then use UNION query in the aggregation.

    SELECT ID, 1 AS Seq, Left([fieldname],2) AS DrawNumber FROM tablename
    UNION SELECT ID, 2, Mid([fieldname],4,2) FROM tablename
    UNION SELECT ID, 3, Mid([fieldname],7,2) FROM tablename
    UNION SELECT ID, 4, Mid([fieldname],10,2) FROM tablename
    UNION SELECT ID, 5, Mid([fieldname],13,2) FROM tablename
    UNION SELECT ID, 6, Mid([fieldname],16,2) FROM tablename
    UNION SELECT ID, 7, Mid([fieldname],19,2) FROM tablename
    UNION SELECT ID, 8, Mid([fieldname],22,2) FROM tablename
    UNION SELECT ID, 9, Mid([fieldname],25,2) FROM tablename
    UNION SELECT ID, 10, Mid([fieldname],28,2) FROM tablename
    UNION SELECT ID, 11, Mid([fieldname],31,2) FROM tablename
    UNION SELECT ID, 12, Right([fieldname],2) FROM tablename;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It's been a long time since I checked but for lotteries local to me, they had this info. If I wanted to know how many times 46 had been drawn in the entire history of the lottery, the web page told me. Did you check if that's available to you?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 6
    Last Post: 10-30-2018, 04:35 AM
  2. Replies: 9
    Last Post: 06-23-2018, 10:25 PM
  3. Replies: 2
    Last Post: 04-27-2016, 06:20 AM
  4. Replies: 1
    Last Post: 07-01-2015, 04:18 AM
  5. Replies: 1
    Last Post: 03-21-2015, 11:55 AM

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