I have created a small DB in Access, I then created the datasource and
got the data through to the app in detail and it shows me 6 columns and
each of the 6 columns holds a number.
I then have a further 6 text boxes, I want to enter a number in each of
the textboxes and search against all of the entries that I have in the
DB and if there are 4 or more matches I need to to display that row.
The number in textbox1 can be any of the columns in the table
So far I have created a variable for each of the textboxes when I click
the search button so it will take the number in there and I can use it
to compare against the columns in the table but I am having no luck
because I do not know the syntax to get this done.
All I want to do is create a small database that hols 6 numbers per a
line and if I give the app any six numbers then I need it to search
through the DB and give me back all the strings that had 3 or more
matches.
Please, Please can someone help me. 7 1118
Hello...
It sounds like you want to implement some "Lotto" logic. I think the layout
of the data you described is a bit tough to querry. Since you want to match
any of the 6 input values against every column and only return the result if
4 or more columns match. The only querry i am comming up with against your
structure would be kind of ugly... (Ok... VERY Ugly)
eg:
Select max(ID) ID, Field1,Field2,...,Field6 from (
Select * from table where Field1 in (1,2,3,4,5,6)
union
Select * from table where Field2 in (1,2,3,4,5,6)
union
Select * from table where Field3 in (1,2,3,4,5,6)
.....
) Group by Field1,Field2,...,Field6 having count>=4
Now I would think about 2 possible solutuions... One would involve
determining which rows to show in your .Net Code (But only since you said
"small DB"... How many records are we talking here?
How about splitting each "row" into N seperate rows which belong to one
"Set" of numbers
Basically
TableSet
---------
ID
Name
TableTip
ID
Set
Tip
and then do a query along the lines of
Select Set,count(*) from tableTip
where Tip in (1,2,3,4,5,6)
group by Set
having count(*) >= 4
Well
Hope that helps
"Trevor" <tr************@za.syspro.comwrote in message
news:11*********************@h40g2000cwb.googlegro ups.com...
>I have created a small DB in Access, I then created the datasource and
got the data through to the app in detail and it shows me 6 columns and
each of the 6 columns holds a number.
I then have a further 6 text boxes, I want to enter a number in each of
the textboxes and search against all of the entries that I have in the
DB and if there are 4 or more matches I need to to display that row.
The number in textbox1 can be any of the columns in the table
So far I have created a variable for each of the textboxes when I click
the search button so it will take the number in there and I can use it
to compare against the columns in the table but I am having no luck
because I do not know the syntax to get this done.
All I want to do is create a small database that hols 6 numbers per a
line and if I give the app any six numbers then I need it to search
through the DB and give me back all the strings that had 3 or more
matches.
Please, Please can someone help me.
I think the question, "What is the data?" needs to be asked here.
So, Trevor, what is the data? Maybe there's a different way to
structure it that will allow you to get the results you need.
Robin S.
------------------------------------------
"rdrunner" <No**@your.comwrote in message
news:uN**************@TK2MSFTNGP03.phx.gbl...
Hello...
It sounds like you want to implement some "Lotto" logic. I think the
layout of the data you described is a bit tough to querry. Since you
want to match any of the 6 input values against every column and only
return the result if 4 or more columns match. The only querry i am
comming up with against your structure would be kind of ugly... (Ok...
VERY Ugly)
eg:
Select max(ID) ID, Field1,Field2,...,Field6 from (
Select * from table where Field1 in (1,2,3,4,5,6)
union
Select * from table where Field2 in (1,2,3,4,5,6)
union
Select * from table where Field3 in (1,2,3,4,5,6)
....
) Group by Field1,Field2,...,Field6 having count>=4
Now I would think about 2 possible solutuions... One would involve
determining which rows to show in your .Net Code (But only since you
said "small DB"... How many records are we talking here?
How about splitting each "row" into N seperate rows which belong to
one "Set" of numbers
Basically
TableSet
---------
ID
Name
TableTip
ID
Set
Tip
and then do a query along the lines of
Select Set,count(*) from tableTip
where Tip in (1,2,3,4,5,6)
group by Set
having count(*) >= 4
Well
Hope that helps
"Trevor" <tr************@za.syspro.comwrote in message
news:11*********************@h40g2000cwb.googlegro ups.com...
>>I have created a small DB in Access, I then created the datasource and got the data through to the app in detail and it shows me 6 columns and each of the 6 columns holds a number. I then have a further 6 text boxes, I want to enter a number in each of the textboxes and search against all of the entries that I have in the DB and if there are 4 or more matches I need to to display that row. The number in textbox1 can be any of the columns in the table
So far I have created a variable for each of the textboxes when I click the search button so it will take the number in there and I can use it to compare against the columns in the table but I am having no luck because I do not know the syntax to get this done.
All I want to do is create a small database that hols 6 numbers per a line and if I give the app any six numbers then I need it to search through the DB and give me back all the strings that had 3 or more matches.
Please, Please can someone help me.
Trevor,
Six columns of numbers sounds suspiciously like a repeating group. Please
tell us that is not the case.
Kerry Moorman
"Trevor" wrote:
I have created a small DB in Access, I then created the datasource and
got the data through to the app in detail and it shows me 6 columns and
each of the 6 columns holds a number.
I then have a further 6 text boxes, I want to enter a number in each of
the textboxes and search against all of the entries that I have in the
DB and if there are 4 or more matches I need to to display that row.
The number in textbox1 can be any of the columns in the table
So far I have created a variable for each of the textboxes when I click
the search button so it will take the number in there and I can use it
to compare against the columns in the table but I am having no luck
because I do not know the syntax to get this done.
All I want to do is create a small database that hols 6 numbers per a
line and if I give the app any six numbers then I need it to search
through the DB and give me back all the strings that had 3 or more
matches.
Please, Please can someone help me.
Yes it is something to do with lotto but I don't want to get random
numbers or anything.
I want to input a set of 6 numbers that I have selected then take that
set and hold it in a DB....I was also looking at maybe holding it in an
xml file if possible.
I then want to be able to say that any six number did come up and I
want to search my DB to see how many times I got more than 3 numbers.
It sounds simple but has proved to be a bit irritating.
I have a collection of about 20 000 sets of six number sets and that
should not be a big DB.
Thanks for the suggestions so far though.
Given a table (main) with a unique column (id) and 6 value columns (val1
thru val6) where each column is Int32 nd each row represents a single set of
numbers, the following query will return those rows where the criteria of 4
or more from 6 is satisfied:
Dim _con As New OleDbConnection(connectionstring)
Dim _com As new OleDbCommand("select * from " _
"main where id in (select id from (select id," _
"count(*) from (select id,val1 as val from main " _
"union all select id,val2 from main union all " _
"select id,val3 from main union all select id," _
"val4 from main union all select id,val5 from " _
"main union all select id,val6 from main) a " _
"where val=? or val=? or val=? or val=? or " _
"val=? or val=? group by id having count(*)>3) b)", _con)
_com.Parameters.Add("p1", OleDbType.Integer).Value = TextBox1.Text
_com.Parameters.Add("p2", OleDbType.Integer).Value = TextBox2.Text
_com.Parameters.Add("p3", OleDbType.Integer).Value = TextBox3.Text
_com.Parameters.Add("p4", OleDbType.Integer).Value = TextBox4.Text
_com.Parameters.Add("p5", OleDbType.Integer).Value = TextBox5.Text
_com.Parameters.Add("p6", OleDbType.Integer).Value = TextBox6.Text
_con.Open()
Dim _drdr As OleDbDataReader = _com.ExecuteReader()
While _drdr.Read
' Do something with the row
End While
_drdr.Close()
_con.Close()
"Trevor" <tr************@za.syspro.comwrote in message
news:11*********************@i12g2000cwa.googlegro ups.com...
Yes it is something to do with lotto but I don't want to get random
numbers or anything.
I want to input a set of 6 numbers that I have selected then take that
set and hold it in a DB....I was also looking at maybe holding it in an
xml file if possible.
I then want to be able to say that any six number did come up and I
want to search my DB to see how many times I got more than 3 numbers.
It sounds simple but has proved to be a bit irritating.
I have a collection of about 20 000 sets of six number sets and that
should not be a big DB.
Thanks for the suggestions so far though.
"Trevor" <tr************@za.syspro.comwrote in message
news:11*********************@i12g2000cwa.googlegro ups.com...
Yes it is something to do with lotto but I don't want to get random
numbers or anything.
I want to input a set of 6 numbers that I have selected then take that
set and hold it in a DB....I was also looking at maybe holding it in
an
xml file if possible.
I then want to be able to say that any six number did come up and I
want to search my DB to see how many times I got more than 3 numbers.
It sounds simple but has proved to be a bit irritating.
I have a collection of about 20 000 sets of six number sets and that
should not be a big DB.
Thanks for the suggestions so far though.
I'm probably going to be lambasted for this, but here's another idea.
What if you stored the data like this:
ID, Value, Seq
1 10 1
1 11 2
1 24 3
1 46 4
1 32 5
1 44 6
2 9 1
2 11 2
2 12 3
2 13 4
2 55 5
2 43 6
(and so on)
So you have them put numbers into 6 textboxes, and you
want to know which ID#'s have more than 4 matches, right?
You could query it like this:
select ID, count(ID) from BigTable where value IN (textbox1.text,
textbox2.text, textbox3.text, textbox4.text, textbox5.text,
textbox6.text) GROUP BY ID WHERE count(ID) >= 4
This will give you a list of ID's with 4 or more matches.
YOu could use that list of ID's to query back and get the
data for display.
If it wasn't millions of records, I'd be tempted to keep the
data in both formats -- one for searching, and one for display.
Then you could do the second query like this:
select * from OriginalTable WHERE ID IN (1,2,3)
(where 1, 2, 3) are the id's returned from the first query
and display the rows easily that way.
If it was SQLServer, you could do this:
select * from OriginalTable WHERE ID IN (
select ID, count(ID) from BigTable where value IN (textbox1.text,
textbox2.text, textbox3.text, textbox4.text, textbox5.text,
textbox6.text) GROUP BY ID WHERE count(ID) >= 4)
You can try that with Access, but I think it converts it to
a join "underneath" rather than processing it as a subquery,
and won't let you do a join with a query containing aggregate
functions.
Just another idea.
Robin S.
Thank you very much for all the suggestions I will now attempt to put
this together and see if I get it working. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jeremy |
last post by:
I was working on an asp and it was working fine. I came back to it a
few days later and it was no longer working... When I submit a form
through the ASP page, it only spits back the raw code, but...
|
by: Susan Bricker |
last post by:
Hi. Does anyone have a clue why my mouse wheel stopped working while I
was working on the VB behind a form in MS/Access? I would swear that
the mouse wheel was working a short time ago. I've...
|
by: worldbadger |
last post by:
When I moved my Access 2000 program to Win XP Home, none of the wizards
are working. Anyone know why?
Since I know it will come up (at least it was the first question
Microsoft asked) I do have...
|
by: Max Riedel |
last post by:
Hi!
In the company where I'm working there are 5 machines setup with Access
2003 and some with 2002. On all but one access isn't functioning
properly.Everytime someone tries to query or create a...
|
by: Nikhil Patel |
last post by:
Hi all,
I installed an asp.net application on a client's machine on Friday. It
uses a Sql Server database as backend. Database server and Web server are on
different machines but same domain. The...
|
by: Mark B |
last post by:
Our webhost (www.usbusinessweb.net) had a W2K IIS5 server crash after a
scheduled hard-boot occurred during a ms-security patch install overnight.
They couldn't get the server working again so they...
|
by: David |
last post by:
Hi,
I have taken some SQL from MS Access and got it working.
I have since updated the SQL in Access but cannot get the additional
part working in ASP.
The working SQL I have in asp is:-
...
|
by: JT |
last post by:
Here is the overall structure I will be referring to:
End-program
ProvideWorkFlow.dll
Forms and methods that properly manipulate calls to methods in
AccessUtils
AccessUtils (a web service)...
|
by: ApexData |
last post by:
The standard NavigationButtonBar at the bottom of my form was working
fine.
Now, without recognizable reason, the AutoRepeat capability of the
Access
forward and back buttons are not working. If...
|
by: =?Utf-8?B?U3RlcGhhbmU=?= |
last post by:
Hi,
I have a problem with Integrated Windows Authentication on one server (Win
Server 2003 SP2 IIS 6.0 ASP.Net 1.4). Let's say I want to disable anonymous
connections to an admin directory, I...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |