Need your help ....Newbie

[Accessnewbie008]

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

[Bulzie]

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.

[CJ_London]

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

[Accessnewbie008]

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.

[Accessnewbie008]

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

[CJ_London]

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

[Accessnewbie008]

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

[Accessnewbie008]

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

[CJ_London]

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.

[Accessnewbie008]

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

[CJ_London]

Are you a pick3 player

......Nope

[Accessnewbie008]

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......

[Accessnewbie008]

Bump for any help please

[Bulzie]

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.

[CJ_London]

what was wrong with the answer in post#6? - or is there another question I've missed?