lottery database 4

[JamesMcG]

I have two tables, one contains names and address of 'players' in my lottery database the other contains the lottery information. The lottery information contains in ID which is linked to the&nbsp;&nbsp;'players' ID which is the primary key. Each player is asigned 4 sets of lottery numbers. And a player can have as many as he likes. This means there is 4 columns on my lottery table each of which contain<br>a lottery number. <br><br>I need to be able to search for a particular number in all of the four colums. I also need 1 combo box to display<br>all of the numbers contained in the 4 columns. <br><br>Is it possible to display them so the look like 1 colum?n<br><br><br>Thanks for your help.<br><br>James

 

[WaltLukeIII]

Yes in the form's design view use the wizard and insert a list box containing all four columns. <br><br>as for searching for a value use a while loop in the code of the form or right a separate module to go through each column and search for the number you are looking for. <p>Walt III<br><a href=mailto:SAElukewl@netscape.net>SAElukewl@netscape.net</a><br><a href=

http://www.muc.edu/~lukewl>homepage</a><br>

 

[JamesMcG]

I need all four columns to be displayed as one column. Using this list box method all four are selected at the same time.

 

[WaltLukeIII]

I don't lunderstand can you give an example<br> <p>Walt III<br><a href=mailto:SAElukewl@netscape.net>SAElukewl@netscape.net</a><br><a href=

http://www.muc.edu/~lukewl>homepage</a><br>

 

[DougP]

So we understand what you want give us a diagram like so:<br>So you have this in your database???<br><br>Sample lottery numbers in 4 columns<br>Number1&nbsp;&nbsp;&nbsp;Number2&nbsp;&nbsp;&nbsp;&nbsp;Number3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Number4<br>12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;18&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6<br>14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br>15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5<br><br>And you want them in one column<br>12<br>18<br>3<br>6<br>14<br>etc.<br><br>???????<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[JamesMcG]

exactly!

 

[WaltLukeIII]

there is a command something like (Object.AddItem) but I have not used it and do not know much about it. Sorry I can not be of more help <br><br>To get each number use a recordset<br><br>dim db as database, recset as recordset<br>dim value as integer, value2 as integer<br><br>set db = dbengine.workspaces(0).databases(0)<br>set recset = db.openrecordset(&quot;name of table&quot;, db_open_snapshot)<br><br>recset.movefirst<br><br>value = recset![name of first column]<br>value2 = recset![name of second column]<br>...<br><br>then if you can figure out the addItem use it to add Value <p>Walt III<br><a href=mailto:SAElukewl@netscape.net>SAElukewl@netscape.net</a><br><a href=

http://www.muc.edu/~lukewl>homepage</a><br>

 

[JamesMcG]

How do I attach code like Walt III's to a combobox?<br><br>

 

[DreewToo]

I could see using a union query for this...something like<br><br>SELECT UserID, Column1<br>FROM tblLottery<br>UNION<br>SELECT UserID, Column2<br>FROM tblLottery<br>UNION<br>SELECT UserID, Column3<br>FROM tblLottery<br>UNION<br>SELECT UserID, Column4<br>FROM tblLottery;<br><br>Recordsource for the combobox would be this SQL.<br><br>HTH,<br>Drew<br>

 

[kathryn]

Good morning all,<br><br>I am coming to this late, but I have to add my two cents here.&nbsp;&nbsp;Is there any reason that your table can't look like this?<br><br>Player#&nbsp;&nbsp;&nbsp;Group#&nbsp;&nbsp;Lottery#&nbsp;&nbsp;LotteryValue<br>&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12<br>&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;18<br>&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3<br>&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6<br>&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;14<br>&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9<br>&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8<br>&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br>&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15<br>&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7<br>&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9<br>&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5<br>&nbsp;&nbsp;&nbsp;<br>In this example, Player #1 has two sets of numbers, while player #2 has only one set.<br><br>This design makes searching much easier and allows you to show data one column.<br><br>It usually isn't two hard to transform existing data into a form like this, if you choose to do so.<br><br>Sorry for butting in, buy I am in the middle of teaching a class on database normalization and your email struck a chord.&nbsp;&nbsp;If not on this project, I hope this helps on some future project.<br><br>Kathryn<br> <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[JamesMcG]

That worked great Drew! thanks!<br><br>No onto my next little problem.<br><br>I want this combobox to control a query which searchs through the all the numbers (from the four colums) and displays any matches.<br><br>The numbers are actuall entered as text in the for 11.22.33.44.55.66. Each player has a 4 sets of numbers. Is is possible that the combobox could display a player if one of his/her sets of numbers matches.<br><br>I have tried setting the masterfield to &lt;name of combobox&gt; and the child fields to &lt;set_of_numbers1&gt;;&lt;set_of_numbers2&gt;;&lt;set_of_numbers3&gt;;&lt;set_of_numbers4&gt;<br><br>but no records are displayed at all.<br><br>I have my tables set up as follows, 1 table is players names the other is the lotterydata. Each player can have many entrys in the lottery table. i.e.<br><br><br><br>ID-----\&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ID <br>Name&nbsp;&nbsp;&nbsp;&nbsp;\&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Num_set1<br>Address&nbsp;&nbsp;\&nbsp;&nbsp;&nbsp;&nbsp;Num_set2<br>PH&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\&nbsp;&nbsp;&nbsp;Num_set3<br>E-mail&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\&nbsp;&nbsp;Num_set4<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\-Player_ID<br><br><br>Any help appreciated<br><br>Thanks<br>

 

[DreewToo]

I'm happy the Union query tip helped, James...It's an awesome tool and has solved many of my duct-tape patches once I learned about it.<br><br>I'd probably use something like the Instr() function to walk through the results of the Union query, in a nested If statement...I'm not sure how I'd code it, but it seems reasonable.<br><br>Drew

 

[JamesMcG]

I understant that I will have to write VB to find the search through my query. Is is possible that it filters out the results that dont match what is in my combo box?<br><br>Thanks very much,<br><br>James

 

[MichaelRed]

James,<br><br>Just my two cents - go back and <b><i><u>READ</u></i></b> Kathryn's post.&nbsp;&nbsp;Rember the motto (&quot;There's never time to do it right, but there is always time to do it over&quot<br><br>Michael<br>

 

[JamesMcG]

I did READ Kathryn's post and her suggestion was very intelligent.<br><br>I also tried implementing it. I found it difficult to figure out an easy way for the four actual Lotto numbers were going to be entered into the database without moving to a new record manually. I couldne see a way of implementing it with some trouble. Kathryn's method was definitly the easiest way to search though.<br><br>I am the database nearly completed now, so I do not like the thought of going back to the start.<br><br>Thanks for your suggestion.

 

[malcolmk]

I have a similar app. Could you send a copy of your mdb? [sig][/sig]

 

[JamesMcG]

Malcom,

I can send you a copy, but the four sets of numbers thing was scrapped because I didnt have the time or patience to become an access expert.

What your email?

James [sig][/sig]

 

[malcolmk]

kim.huston@med.va.gov [sig][/sig]