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

    Need your help ....Newbie

    Hi everyone



    I am a complete access noobie so please forgive me and I would like to learn access more. I have a database that I created called clarity1 with 2 tables claritydata and copy. I have two tables -the import failed and I had create another table named copy. My aim is to update my pick3 result numbers in the database. I need your help only to get the setup correct....I am totally a noobie. My aim is have 3 columns with numbers linked to a draw or date. So if I click on draw it must list all the numbers from Numbers Numbers2 Numbers3 for this specific date/draw....So those 3 columns must be linked to the draw and date. Every day there is 80 numbers so it must be linked for draw and date. I hope someone that have something similiar setup can help me out. If you would be so kind to help a noobie out it will be highly appreciated.I have attached the database ...hope I have explained it correctly. I would also if possible have a query to check if the winning number is in my +70 odd numbers....thats the aim to save the data in access for a lookup. The winning numbers is updated in Claritydata table. Thank you for any assistance and rep given for your help
    Attached Files Attached Files

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I would probably just use 1 table:
    tblDraw(DrawID: autonumber, DrawDate:datetime, Nbr1:numeric, Nbr2:numeric, Nbr3:numeric).
    Search the table for the winning numbers.

    If you want to use 2 tables:
    tblDraw(DrawID: autonumber, DrawDate:datetime)
    tblNumbers(NbrID:autoNumber, DrawID:numeric, Nbr1:numeric, Nbr2:numeric, Nbr3:numeric)
    Link the 2 tables in a query by DrawID and search query for winning numbers.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I think I understand what you want - you have bought 70 lottery tickets. Each lottery ticket has 3 numbers and you want to find out if you have a match for any particular draw. The issue is that a number could be in any column e.g.

    ticket
    3...4...5

    draw
    4...3...5

    since the numbers match overall, this would be a winning ticket

    Is that a correct interpretation? if not, please try explaining again

  4. #4
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    Quote Originally Posted by Ajax View Post
    I think I understand what you want - you have bought 70 lottery tickets. Each lottery ticket has 3 numbers and you want to find out if you have a match for any particular draw. The issue is that a number could be in any column e.g.

    ticket
    3...4...5

    draw
    4...3...5

    since the numbers match overall, this would be a winning ticket

    Is that a correct interpretation? if not, please try explaining again
    Hi Ajax thank you so much for your reply....you are 100% correct thats is exactly what I want. I would also like to have it setup the easiest way....Can you help as I am clueless how to achieve that...I will want to learn access .....Its very powerfull what I have heard.

  5. #5
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    Hi Ajax thank you so much for your reply....You have it 100% correct I would like to see if the draw number is in my 70 choices...so you are correct. I agree for having it in 1 database...can you help ? Sorry I dont know much your help will be greatly appreciated

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    You can use what is called a Cartesian query - i.e. no joins

    I've added a primary key (called ticketPK, autonumber) to your copy table (I'm assuming these are your tickets), and made the Draw field the primary key for your clarity table.

    If you do the same, you should just be able to copy and paste this query


    SELECT Draw, ticketPK, Numbers, Numbers2, Numbers3
    FROM ClarityData, Copy
    WHERE Numbers In ([P1],[P2],[P3]) AND Numbers2 In ([P1],[P2],[P3]) AND Numbers3 In ([P1],[P2],[P3])
    I presume your data isn't real - there are blanks and repeat numbers

  7. #7
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    Yes sorry it was only a test....I will sort the data for no spaces.You will also notice funny charachters......I will clean that before I add them

  8. #8
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    Quote Originally Posted by Ajax View Post
    You can use what is called a Cartesian query - i.e. no joins

    I've added a primary key (called ticketPK, autonumber) to your copy table (I'm assuming these are your tickets), and made the Draw field the primary key for your clarity table.

    If you do the same, you should just be able to copy and paste this query




    I presume your data isn't real - there are blanks and repeat numbers
    I lost you there.....will have to to google Cartesian query.....quite correct duplicates I will remove too. I will be very greatfull only to get it setup correctly for data entry .....the query to check the icing on the cake as that is exactly what I am after to check if the winning number is between the 80

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I will be very greatfull only to get it setup correctly for data entry
    normally if you have fields named 'number1', 'number2' this would flag that the data is not normalised (google 'normalisation' to find out what that means), however in this situation your tables are fine in this respect.

  10. #10
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    Quote Originally Posted by Ajax View Post
    normally if you have fields named 'number1', 'number2' this would flag that the data is not normalised (google 'normalisation' to find out what that means), however in this situation your tables are fine in this respect.
    I have just googled it thank you Ajax you are so right....atleast good news though. Are you a pick3 player ? I am sure that helps if you can help yourself. I need to to learn from the start as I dont know access but heard great things about it.Long way to go suppose

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Are you a pick3 player
    ......Nope

  12. #12
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    Quote Originally Posted by Bulzie View Post
    I would probably just use 1 table:
    tblDraw(DrawID: autonumber, DrawDate:datetime, Nbr1:numeric, Nbr2:numeric, Nbr3:numeric).
    Search the table for the winning numbers.

    If you want to use 2 tables:
    tblDraw(DrawID: autonumber, DrawDate:datetime)
    tblNumbers(NbrID:autoNumber, DrawID:numeric, Nbr1:numeric, Nbr2:numeric, Nbr3:numeric)
    Link the 2 tables in a query by DrawID and search query for winning numbers.
    Bulzie thank you !!!! Apologies for sounding dumb but how do you do this......

  13. #13
    Accessnewbie008 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2017
    Posts
    43
    Bump for any help please

  14. #14
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    As Ajax asked but maybe missing answer, do the numbers have to match in the exact order on the ticket or do they just have to be one of the 3 numbers in any order?

    What I described is the table structure, which is the most important part of any Access database. How you set up your tables will dictate how you design your forms, queries, etc. You really should look at some of the tutorials on this site to help get an understanding of MS Access.

    To get you started, when you open Access, go to the top and click on Create, Table Design, then start adding those fields and datatypes I described and save the table. Click on th left of the DrawID field and look at the top for the key symbol and click that to make that field the key field of the table.

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    what was wrong with the answer in post#6? - or is there another question I've missed?

Page 1 of 5 12345 LastLast
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