|
 |
asp_databases thread: Expert help required for non-profit site.
Message #1 by "Alan Coleman" <colemans@m...> on Wed, 27 Jun 2001 20:03:29
|
|
Hi,
How do I query a Access database using more then 40 OR searches. The idea
is simple, it's just if aa='aa' then it shows
that row. The OR searches goes up to 'ht' (aa,ab,ac, ad ...). I've got
upto 30 but now I need 40+.
The database coloums are called the same as the form names subitted using
POST. Has it got anything to with 'loop'. I've tried to read up on it but
to no luck.
Thanks
Alan
Message #2 by "Peter Foti (PeterF)" <PeterF@S...> on Wed, 27 Jun 2001 15:30:37 -0400
|
|
Alan,
It's unclear by your post exactly what it is that you are trying to do.
What type of information are you searching on? What does your database
design look like? Based on what you wrote, it sounds to me like your
database might be designed in some strange, inefficient way. But
without knowing more, it's hard to provide any useful comments. Do you
have any code you could post?
Peter Foti
> -----Original Message-----
> From: Alan Coleman [mailto:colemans@m...]
> Sent: Wednesday, June 27, 2001 8:03 PM
> To: ASP Databases
> Subject: [asp_databases] Expert help required for non-profit site.
>
>
> Hi,
> How do I query a Access database using more then 40 OR
> searches. The idea
> is simple, it's just if aa=3D'aa' then it shows
> that row. The OR searches goes up to 'ht' (aa,ab,ac, ad ...).
> I've got
> upto 30 but now I need 40+.
> The database coloums are called the same as the form names
> subitted using
> POST. Has it got anything to with 'loop'. I've tried to read
> up on it but
> to no luck.
> Thanks
> Alan
Message #3 by John Pirkey <mailjohnny101@y...> on Wed, 27 Jun 2001 12:27:32 -0700 (PDT)
|
|
are there 40 columns, or are yuo checking one column for 40 different values? if
it's just one column, then try the IN clause
SELECT ... FROM Table
WHERE aa in ('aa', 'ab', 'ac', 'ad'...)
as for the POST thing you mentioned - you could loop through the request.querystring
objects and build a string that way.
for each itm in request.querystring
sqlwhere = sqlwhere & itm & " = '" & request.querystring(itm) & "' OR "
next
'trim off the remaining "or"
sqlwhere = left(sqlwhere, len(sqlwhere) - 3)
sfullsql = "select ... from table " & sqlwhere
and execute that.
hope this helps,
john
--- Alan Coleman <colemans@m...> wrote:
> Hi,
> How do I query a Access database using more then 40 OR searches. The idea
> is simple, it's just if aa='aa' then it shows
> that row. The OR searches goes up to 'ht' (aa,ab,ac, ad ...). I've got
> upto 30 but now I need 40+.
> The database coloums are called the same as the form names subitted using
> POST. Has it got anything to with 'loop'. I've tried to read up on it but
> to no luck.
> Thanks
> Alan
>
----------------------------
John Pirkey
MCSD
John@S...
http://www.stlvbug.org
Message #4 by "Alan Coleman" <colemans@m...> on Wed, 27 Jun 2001 22:48:57
|
|
Thanks for the help.
In the Access db their are 40 coloums that match 40 text fields. Form
field 'aa' = the db coloum 'aa' Or 'ab' = the db coloum 'ab'... this goes
upto 'ht'!!!. The database was not planned out but is too large to redo.
As you know this does not work: Recordset1.Source = "SELECT * FROM units
WHERE aa = 'MMaa' OR ab = 'MMab' OR ac = 'MMac ' OR ad = 'MMad ' OR ae
= 'MMae ' OR af = 'MMaf ' OR ag = 'MMag ' OR ah = 'MMah ' OR ai = 'MMai '
OR aj = 'MMaj ' OR ak = 'MMak ' OR al = 'MMal ' OR am = 'MMam ' OR an
= 'MMan ' OR ao = 'MMao ' OR ap = 'MMap ' OR aq = 'MMaq ' OR ar = 'MMar '
OR as = 'MMas ' OR at = 'MMat ' OR au = 'MMau ' OR av = 'MMav ' OR aw
= 'MMaw ' OR ax = 'MMax ' OR ay = 'MMay ' OR az = 'MMaz ' OR ba = 'MMba'
OR bb = 'MMbb' OR bc = 'MMbc ' OR bd = 'MMbd ' OR be = 'MMbe ' OR bf
= 'MMbf ' OR bg = 'MMbg ' OR bh = 'MMbh ' OR bi = 'MMbi ' OR bj = 'MMbj '
OR bk = 'MMbk ' OR bl = 'MMbl ' OR bm = 'MMbm ' OR bn = 'MMbn ' OR bo
= 'MMbo ' OR bp = 'MMbp ' OR bq = 'MMbq ' OR br = 'MMbr ' OR bs
= 'MMbs '"..
All your help is very welcome.
Al
> Alan,
>
> It's unclear by your post exactly what it is that you are trying to do.
> What type of information are you searching on? What does your database
> design look like? Based on what you wrote, it sounds to me like your
> database might be designed in some strange, inefficient way. But
> without knowing more, it's hard to provide any useful comments. Do you
> have any code you could post?
>
> Peter Foti
>
>
> > -----Original Message-----
> > From: Alan Coleman [mailto:colemans@m...]
> > Sent: Wednesday, June 27, 2001 8:03 PM
> > To: ASP Databases
> > Subject: [asp_databases] Expert help required for non-profit site.
> >
> >
> > Hi,
> > How do I query a Access database using more then 40 OR
> > searches. The idea
> > is simple, it's just if aa=3D'aa' then it shows
> > that row. The OR searches goes up to 'ht' (aa,ab,ac, ad ...).
> > I've got
> > upto 30 but now I need 40+.
> > The database coloums are called the same as the form names
> > subitted using
> > POST. Has it got anything to with 'loop'. I've tried to read
> > up on it but
> > to no luck.
> > Thanks
> > Alan
>
Message #5 by "Peter Foti (PeterF)" <PeterF@S...> on Wed, 27 Jun 2001 18:14:02 -0400
|
|
Hi Alan,
1. What is this data that is being stored in the table? That is, if
column aa contains "aa", will the remaining columns in this row be
empty?
2. As far as the database being too large to redo... I say a database
is NEVER too large to redo, especially if it's to the point where it's
causing problems. Depending on what exactly this data is that is being
stored, I think you should be able to somewhat easily redo at least
this
table so that the data is stored in a more logical manner. I don't
know
what aa, ab, ac, ... stands for, but my guess is that it's all the same
kind of data.
3. Why does that SELECT statement not work? Is that how you got up to
30?
-Peter
> -----Original Message-----
> From: Alan Coleman [mailto:colemans@m...]
> Sent: Wednesday, June 27, 2001 10:49 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Expert help required for non-profit
site.
>
>
> Thanks for the help.
> In the Access db their are 40 coloums that match 40 text fields. Form
> field 'aa' =3D the db coloum 'aa' Or 'ab' =3D the db coloum
> 'ab'... this goes
> upto 'ht'!!!. The database was not planned out but is too
> large to redo.
> As you know this does not work: Recordset1.Source =3D "SELECT *
> FROM units
> WHERE aa =3D 'MMaa' OR ab =3D 'MMab' OR ac =3D 'MMac ' OR ad =3D
> 'MMad ' OR ae
> =3D 'MMae ' OR af =3D 'MMaf ' OR ag =3D 'MMag ' OR ah =3D 'MMah ' OR
> ai =3D 'MMai '
> OR aj =3D 'MMaj ' OR ak =3D 'MMak ' OR al =3D 'MMal ' OR am =3D 'MMam
' OR an
> =3D 'MMan ' OR ao =3D 'MMao ' OR ap =3D 'MMap ' OR aq =3D 'MMaq ' OR
> ar =3D 'MMar '
> OR as =3D 'MMas ' OR at =3D 'MMat ' OR au =3D 'MMau ' OR av =3D 'MMav
' OR aw
> =3D 'MMaw ' OR ax =3D 'MMax ' OR ay =3D 'MMay ' OR az =3D 'MMaz ' OR
> ba =3D 'MMba'
> OR bb =3D 'MMbb' OR bc =3D 'MMbc ' OR bd =3D 'MMbd ' OR be =3D 'MMbe
' OR bf
> =3D 'MMbf ' OR bg =3D 'MMbg ' OR bh =3D 'MMbh ' OR bi =3D 'MMbi ' OR
> bj =3D 'MMbj '
> OR bk =3D 'MMbk ' OR bl =3D 'MMbl ' OR bm =3D 'MMbm ' OR bn =3D 'MMbn
' OR bo
> =3D 'MMbo ' OR bp =3D 'MMbp ' OR bq =3D 'MMbq ' OR br =3D 'MMbr ' OR
bs
> =3D 'MMbs '"..
>
> All your help is very welcome.
> Al
> > Alan,
> >
> > It's unclear by your post exactly what it is that you are
> trying to do.
> > What type of information are you searching on? What does
> your database
> > design look like? Based on what you wrote, it sounds to me
> like your
> > database might be designed in some strange, inefficient way. But
> > without knowing more, it's hard to provide any useful
> comments. Do you
> > have any code you could post?
> >
> > Peter Foti
> >
> >
> > > -----Original Message-----
> > > From: Alan Coleman [mailto:colemans@m...]
> > > Sent: Wednesday, June 27, 2001 8:03 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] Expert help required for non-profit
site.
> > >=3D20
> > >=3D20
> > > Hi,=3D20
> > > How do I query a Access database using more then 40 OR=3D20
> > > searches. The idea=3D20
> > > is simple, it's just if aa=3D3D'aa' then it shows=3D20
> > > that row. The OR searches goes up to 'ht' (aa,ab,ac, ad
...).=3D20
> > > I've got=3D20
> > > upto 30 but now I need 40+.
> > > The database coloums are called the same as the form names=3D20
> > > subitted using=3D20
> > > POST. Has it got anything to with 'loop'. I've tried to read=3D20
> > > up on it but=3D20
> > > to no luck.
> > > Thanks
> > > Alan
Message #6 by "Alan Coleman" <colemans@m...> on Wed, 27 Jun 2001 23:51:02
|
|
Thanks Peter,
The data started with coloum name 'Description' Then coloum
name 'Effect' the coloums 'aa' 'ab' 'ac' ...'ht'. When it was first set up
with just 'aa' 'ab'..upto...'an' in the description it products we sell.
¦ID¦ ¦Description¦ ¦Effect¦ ¦aa¦ ¦ab¦ ¦ac¦
1 Thing a one a 1
2 Thing a two a(diffent then 2x thinga) 2
3 Thing a three a("" 3x thinga") 3
THIS GOES ON FOR ABOUT 7 things: then this
10 Thing b thing b (diffent then 2x thing b) 1
11 Thing b thing b (diffent then 3x thing b) 2
Get the driff?
I know it is very un effecit
The form is text fields with name 'aa' and the next one 'ab'..etc.. The
form asks the database to show the record when form value'aa'etc =db
coloum 'ab'etc.
Thanks
Alan
> Hi Alan,
>
> 1. What is this data that is being stored in the table? That is, if
> column aa contains "aa", will the remaining columns in this row be
> empty?
> 2. As far as the database being too large to redo... I say a database
> is NEVER too large to redo, especially if it's to the point where it's
> causing problems. Depending on what exactly this data is that is being
> stored, I think you should be able to somewhat easily redo at least
> this
> table so that the data is stored in a more logical manner. I don't
> know
> what aa, ab, ac, ... stands for, but my guess is that it's all the same
> kind of data.
> 3. Why does that SELECT statement not work? Is that how you got up to
> 30?
>
> -Peter
>
> > -----Original Message-----
> > From: Alan Coleman [mailto:colemans@m...]
> > Sent: Wednesday, June 27, 2001 10:49 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Expert help required for non-profit
> site.
> >
> >
> > Thanks for the help.
> > In the Access db their are 40 coloums that match 40 text fields. Form
>
> > field 'aa' =3D the db coloum 'aa' Or 'ab' =3D the db coloum
> > 'ab'... this goes
> > upto 'ht'!!!. The database was not planned out but is too
> > large to redo.
> > As you know this does not work: Recordset1.Source =3D "SELECT *
> > FROM units
> > WHERE aa =3D 'MMaa' OR ab =3D 'MMab' OR ac =3D 'MMac ' OR ad =3D
> > 'MMad ' OR ae
> > =3D 'MMae ' OR af =3D 'MMaf ' OR ag =3D 'MMag ' OR ah =3D 'MMah ' OR
> > ai =3D 'MMai '
> > OR aj =3D 'MMaj ' OR ak =3D 'MMak ' OR al =3D 'MMal ' OR am =3D 'MMam
> ' OR an
> > =3D 'MMan ' OR ao =3D 'MMao ' OR ap =3D 'MMap ' OR aq =3D 'MMaq ' OR
> > ar =3D 'MMar '
> > OR as =3D 'MMas ' OR at =3D 'MMat ' OR au =3D 'MMau ' OR av =3D 'MMav
> ' OR aw
> > =3D 'MMaw ' OR ax =3D 'MMax ' OR ay =3D 'MMay ' OR az =3D 'MMaz ' OR
> > ba =3D 'MMba'
> > OR bb =3D 'MMbb' OR bc =3D 'MMbc ' OR bd =3D 'MMbd ' OR be =3D 'MMbe
> ' OR bf
> > =3D 'MMbf ' OR bg =3D 'MMbg ' OR bh =3D 'MMbh ' OR bi =3D 'MMbi ' OR
> > bj =3D 'MMbj '
> > OR bk =3D 'MMbk ' OR bl =3D 'MMbl ' OR bm =3D 'MMbm ' OR bn =3D 'MMbn
> ' OR bo
> > =3D 'MMbo ' OR bp =3D 'MMbp ' OR bq =3D 'MMbq ' OR br =3D 'MMbr ' OR
> bs
> > =3D 'MMbs '"..
> >
> > All your help is very welcome.
> > Al
> > > Alan,
> > >
> > > It's unclear by your post exactly what it is that you are
> > trying to do.
> > > What type of information are you searching on? What does
> > your database
> > > design look like? Based on what you wrote, it sounds to me
> > like your
> > > database might be designed in some strange, inefficient way. But
> > > without knowing more, it's hard to provide any useful
> > comments. Do you
> > > have any code you could post?
> > >
> > > Peter Foti
> > >
> > >
> > > > -----Original Message-----
> > > > From: Alan Coleman [mailto:colemans@m...]
> > > > Sent: Wednesday, June 27, 2001 8:03 PM
> > > > To: ASP Databases
> > > > Subject: [asp_databases] Expert help required for non-profit
> site.
> > > >=3D20
> > > >=3D20
> > > > Hi,=3D20
> > > > How do I query a Access database using more then 40 OR=3D20
> > > > searches. The idea=3D20
> > > > is simple, it's just if aa=3D3D'aa' then it shows=3D20
> > > > that row. The OR searches goes up to 'ht' (aa,ab,ac, ad
> ...).=3D20
> > > > I've got=3D20
> > > > upto 30 but now I need 40+.
> > > > The database coloums are called the same as the form names=3D20
> > > > subitted using=3D20
> > > > POST. Has it got anything to with 'loop'. I've tried to read=3D20
> > > > up on it but=3D20
> > > > to no luck.
> > > > Thanks
> > > > Alan
>
Message #7 by David Cameron <dcameron@i...> on Thu, 28 Jun 2001 09:19:33 +1000
|
|
I really think that you may need to rebuild the database. I'm not sure that
I understand entirely what the point of the columns aa-ht are but it really
extremely bad database design to build something that means you need to add
more fields in the way that these fields are being added. Designing for
scalability is an incredibly important part of db design. Any time spent
rebuilding the db will be gained in time not spent trying to work with the
current design. In this case I think that you might need another table
joined to the current one to store the aa-ht fields:
|ID| |ProductID| |Letter| |value|
1 1 aa 1
2 2 aa 2
3 3 aa 3
4 10 ab 1
5 11 ab 2
Even then I think that the db design is flawed. I am hoping that this isn't
a 1 table database and that you have some relationships.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Alan Coleman [mailto:colemans@m...]
Sent: Thursday, 28 June 2001 9:51 AM
To: ASP Databases
Subject: [asp_databases] RE: Expert help required for non-profit site.
Thanks Peter,
The data started with coloum name 'Description' Then coloum
name 'Effect' the coloums 'aa' 'ab' 'ac' ...'ht'. When it was first set up
with just 'aa' 'ab'..upto...'an' in the description it products we sell.
|ID| |Description| |Effect| |aa| |ab| |ac|
1 Thing a one a 1
2 Thing a two a(diffent then 2x thinga) 2
3 Thing a three a("" 3x thinga") 3
THIS GOES ON FOR ABOUT 7 things: then this
10 Thing b thing b (diffent then 2x thing b) 1
11 Thing b thing b (diffent then 3x thing b) 2
Get the driff?
I know it is very un effecit
The form is text fields with name 'aa' and the next one 'ab'..etc.. The
form asks the database to show the record when form value'aa'etc =db
coloum 'ab'etc.
Thanks
Alan
> Hi Alan,
>
> 1. What is this data that is being stored in the table? That is, if
> column aa contains "aa", will the remaining columns in this row be
> empty?
> 2. As far as the database being too large to redo... I say a database
> is NEVER too large to redo, especially if it's to the point where it's
> causing problems. Depending on what exactly this data is that is being
> stored, I think you should be able to somewhat easily redo at least
> this
> table so that the data is stored in a more logical manner. I don't
> know
> what aa, ab, ac, ... stands for, but my guess is that it's all the same
> kind of data.
> 3. Why does that SELECT statement not work? Is that how you got up to
> 30?
>
> -Peter
>
> > -----Original Message-----
> > From: Alan Coleman [mailto:colemans@m...]
> > Sent: Wednesday, June 27, 2001 10:49 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Expert help required for non-profit
> site.
> >
> >
> > Thanks for the help.
> > In the Access db their are 40 coloums that match 40 text fields. Form
>
> > field 'aa' =3D the db coloum 'aa' Or 'ab' =3D the db coloum
> > 'ab'... this goes
> > upto 'ht'!!!. The database was not planned out but is too
> > large to redo.
> > As you know this does not work: Recordset1.Source =3D "SELECT *
> > FROM units
> > WHERE aa =3D 'MMaa' OR ab =3D 'MMab' OR ac =3D 'MMac ' OR ad =3D
> > 'MMad ' OR ae
> > =3D 'MMae ' OR af =3D 'MMaf ' OR ag =3D 'MMag ' OR ah =3D 'MMah ' OR
> > ai =3D 'MMai '
> > OR aj =3D 'MMaj ' OR ak =3D 'MMak ' OR al =3D 'MMal ' OR am =3D 'MMam
> ' OR an
> > =3D 'MMan ' OR ao =3D 'MMao ' OR ap =3D 'MMap ' OR aq =3D 'MMaq ' OR
> > ar =3D 'MMar '
> > OR as =3D 'MMas ' OR at =3D 'MMat ' OR au =3D 'MMau ' OR av =3D 'MMav
> ' OR aw
> > =3D 'MMaw ' OR ax =3D 'MMax ' OR ay =3D 'MMay ' OR az =3D 'MMaz ' OR
> > ba =3D 'MMba'
> > OR bb =3D 'MMbb' OR bc =3D 'MMbc ' OR bd =3D 'MMbd ' OR be =3D 'MMbe
> ' OR bf
> > =3D 'MMbf ' OR bg =3D 'MMbg ' OR bh =3D 'MMbh ' OR bi =3D 'MMbi ' OR
> > bj =3D 'MMbj '
> > OR bk =3D 'MMbk ' OR bl =3D 'MMbl ' OR bm =3D 'MMbm ' OR bn =3D 'MMbn
> ' OR bo
> > =3D 'MMbo ' OR bp =3D 'MMbp ' OR bq =3D 'MMbq ' OR br =3D 'MMbr ' OR
> bs
> > =3D 'MMbs '"..
> >
> > All your help is very welcome.
> > Al
Message #8 by "Peter Foti (PeterF)" <PeterF@S...> on Wed, 27 Jun 2001 19:46:45 -0400
|
|
Ok, let me see if I follow you...
There are several different products with the description "Thing a".
These are different. Could you give me an example of one of the
products?
I am guessing that this is something like:
Description Effect aa
----------- ------ --
Pepsi 8oz. 1
Pepsi 12oz. 2
Pepsi 16oz. 3
But I could be wrong.
Anyway, it seems as though you should be using your ID field instead of
searching all these aa, ab, ... fields. Know what I mean?
I'm still not 100% sure I understand your database & HTML form layout.
Is there a URL that you could post that shows the current form?
-Pete
> -----Original Message-----
> From: Alan Coleman [mailto:colemans@m...]
> Sent: Wednesday, June 27, 2001 11:51 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Expert help required for non-profit
site.
>
>
> Thanks Peter,
> The data started with coloum name 'Description' Then coloum
> name 'Effect' the coloums 'aa' 'ab' 'ac' ...'ht'. When it was
> first set up
> with just 'aa' 'ab'..upto...'an' in the description it
> products we sell.
> =A6ID=A6 =A6Description=A6 =A6Effect=A6
> =A6aa=A6 =A6ab=A6 =A6ac=A6
> 1 Thing a one a 1
> 2 Thing a two a(diffent then 2x thinga) 2
> 3 Thing a three a("" 3x thinga") 3
> THIS GOES ON FOR ABOUT 7 things: then this
> 10 Thing b thing b (diffent then 2x thing b) 1
> 11 Thing b thing b (diffent then 3x thing b) 2
>
>
> Get the driff?
> I know it is very un effecit
>
> The form is text fields with name 'aa' and the next one
> 'ab'..etc.. The
> form asks the database to show the record when form value'aa'etc
=3Ddb
> coloum 'ab'etc.
> Thanks
> Alan
> > Hi Alan,
> >
> > 1. What is this data that is being stored in the table?
> That is, if
> > column aa contains "aa", will the remaining columns in this row be
> > empty? =3D20
> > 2. As far as the database being too large to redo... I say
> a database
> > is NEVER too large to redo, especially if it's to the point
> where it's
> > causing problems. Depending on what exactly this data is
> that is being
> > stored, I think you should be able to somewhat easily redo
> at least =3D
> > this
> > table so that the data is stored in a more logical manner.
> I don't =3D
> > know
> > what aa, ab, ac, ... stands for, but my guess is that it's
> all the same
> > kind of data.
> > 3. Why does that SELECT statement not work? Is that how
> you got up to
> > 30?
> >
> > -Peter
> >
> > > -----Original Message-----
> > > From: Alan Coleman [mailto:colemans@m...]
> > > Sent: Wednesday, June 27, 2001 10:49 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] RE: Expert help required for non-profit
=3D
> > site.
> > >=3D20
> > >=3D20
> > > Thanks for the help.
> > > In the Access db their are 40 coloums that match 40 text
> fields. Form =3D
> >
> > > field 'aa' =3D3D the db coloum 'aa' Or 'ab' =3D3D the db
coloum=3D20
> > > 'ab'... this goes=3D20
> > > upto 'ht'!!!. The database was not planned out but is too=3D20
> > > large to redo.=3D20
> > > As you know this does not work: Recordset1.Source =3D3D "SELECT
*=3D20
> > > FROM units =3D20
> > > WHERE aa =3D3D 'MMaa' OR ab =3D3D 'MMab' OR ac =3D3D 'MMac ' OR
> ad =3D3D=3D20
> > > 'MMad ' OR ae=3D20
> > > =3D3D 'MMae ' OR af =3D3D 'MMaf ' OR ag =3D3D 'MMag ' OR ah =3D3D
> 'MMah ' OR=3D20
> > > ai =3D3D 'MMai '=3D20
> > > OR aj =3D3D 'MMaj ' OR ak =3D3D 'MMak ' OR al =3D3D 'MMal ' OR
> am =3D3D 'MMam =3D
> > ' OR an=3D20
> > > =3D3D 'MMan ' OR ao =3D3D 'MMao ' OR ap =3D3D 'MMap ' OR aq =3D3D
> 'MMaq ' OR=3D20
> > > ar =3D3D 'MMar '=3D20
> > > OR as =3D3D 'MMas ' OR at =3D3D 'MMat ' OR au =3D3D 'MMau ' OR
> av =3D3D 'MMav =3D
> > ' OR aw=3D20
> > > =3D3D 'MMaw ' OR ax =3D3D 'MMax ' OR ay =3D3D 'MMay ' OR az =3D3D
> 'MMaz ' OR=3D20
> > > ba =3D3D 'MMba'=3D20
> > > OR bb =3D3D 'MMbb' OR bc =3D3D 'MMbc ' OR bd =3D3D 'MMbd ' OR
> be =3D3D 'MMbe =3D
> > ' OR bf=3D20
> > > =3D3D 'MMbf ' OR bg =3D3D 'MMbg ' OR bh =3D3D 'MMbh ' OR bi =3D3D
> 'MMbi ' OR=3D20
> > > bj =3D3D 'MMbj '=3D20
> > > OR bk =3D3D 'MMbk ' OR bl =3D3D 'MMbl ' OR bm =3D3D 'MMbm ' OR
> bn =3D3D 'MMbn =3D
> > ' OR bo=3D20
> > > =3D3D 'MMbo ' OR bp =3D3D 'MMbp ' OR bq =3D3D 'MMbq ' OR br =3D3D
> 'MMbr ' OR =3D
> > bs=3D20
> > > =3D3D 'MMbs '"..
> > >=3D20
> > > All your help is very welcome.
> > > Al
Message #9 by "Blake, Shane" <Shane.Blake@p...> on Thu, 28 Jun 2001 09:03:37 -0400
|
|
like everyone else, i'm stabbing in the dark here without a good
understanding of what you're exactly trying to do, but...
you might want to make a category table
id name description
=3D=3D =3D=3D=3D=3D =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
1 aa this is what aa is
2 ab this is what ab is
3 ac this is what ac is
you could use this table to build a select box off of in your forms...
your main table could be like this
id description effect category
subCategory
1 Thing a one a 1
1
2 Thing a two a(diffent then 2x thinga) 1
2
3 Thing a three a("" 3x thinga") 1
3
10 Thing b thing b (diffent then 2x thing b) 2
1
11 Thing b thing b (diffent then 3x thing b) 2
2
this would be a lot easier to search on, imho
shane
-----Original Message-----
From: Alan Coleman [mailto:colemans@m...]
Sent: Wednesday, June 27, 2001 7:51 PM
To: ASP Databases
Subject: [asp_databases] RE: Expert help required for non-profit site.
Thanks Peter,
The data started with coloum name 'Description' Then coloum
name 'Effect' the coloums 'aa' 'ab' 'ac' ...'ht'. When it was first set
up
with just 'aa' 'ab'..upto...'an' in the description it products we
sell.
=A6ID=A6 =A6Description=A6 =A6Effect=A6
=A6aa=A6 =A6ab=A6 =A6ac=A6
1 Thing a one a 1
2 Thing a two a(diffent then 2x thinga) 2
3 Thing a three a("" 3x thinga") 3
THIS GOES ON FOR ABOUT 7 things: then this
10 Thing b thing b (diffent then 2x thing b) 1
11 Thing b thing b (diffent then 3x thing b) 2
Get the driff?
I know it is very un effecit
The form is text fields with name 'aa' and the next one 'ab'..etc.. The
form asks the database to show the record when form value'aa'etc =3Ddb
coloum 'ab'etc.
Thanks
Alan
> Hi Alan,
>
> 1. What is this data that is being stored in the table? That is, if
> column aa contains "aa", will the remaining columns in this row be
> empty? =3D20
> 2. As far as the database being too large to redo... I say a
database
> is NEVER too large to redo, especially if it's to the point where
it's
> causing problems. Depending on what exactly this data is that is
being
> stored, I think you should be able to somewhat easily redo at least
=3D
> this
> table so that the data is stored in a more logical manner. I don't
=3D
> know
> what aa, ab, ac, ... stands for, but my guess is that it's all the
same
> kind of data.
> 3. Why does that SELECT statement not work? Is that how you got up
to
> 30?
>
> -Peter
>
> > -----Original Message-----
> > From: Alan Coleman [mailto:colemans@m...]
> > Sent: Wednesday, June 27, 2001 10:49 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Expert help required for non-profit
=3D
> site.
> >=3D20
> >=3D20
> > Thanks for the help.
> > In the Access db their are 40 coloums that match 40 text fields.
Form =3D
>
> > field 'aa' =3D3D the db coloum 'aa' Or 'ab' =3D3D the db
coloum=3D20
> > 'ab'... this goes=3D20
> > upto 'ht'!!!. The database was not planned out but is too=3D20
> > large to redo.=3D20
> > As you know this does not work: Recordset1.Source =3D3D "SELECT
*=3D20
> > FROM units =3D20
> > WHERE aa =3D3D 'MMaa' OR ab =3D3D 'MMab' OR ac =3D3D 'MMac ' OR ad
=3D3D=3D20
> > 'MMad ' OR ae=3D20
> > =3D3D 'MMae ' OR af =3D3D 'MMaf ' OR ag =3D3D 'MMag ' OR ah =3D3D
'MMah ' OR=3D20
> > ai =3D3D 'MMai '=3D20
> > OR aj =3D3D 'MMaj ' OR ak =3D3D 'MMak ' OR al =3D3D 'MMal ' OR am
=3D3D 'MMam =3D
> ' OR an=3D20
> > =3D3D 'MMan ' OR ao =3D3D 'MMao ' OR ap =3D3D 'MMap ' OR aq =3D3D
'MMaq ' OR=3D20
> > ar =3D3D 'MMar '=3D20
> > OR as =3D3D 'MMas ' OR at =3D3D 'MMat ' OR au =3D3D 'MMau ' OR av
=3D3D 'MMav =3D
> ' OR aw=3D20
> > =3D3D 'MMaw ' OR ax =3D3D 'MMax ' OR ay =3D3D 'MMay ' OR az =3D3D
'MMaz ' OR=3D20
> > ba =3D3D 'MMba'=3D20
> > OR bb =3D3D 'MMbb' OR bc =3D3D 'MMbc ' OR bd =3D3D 'MMbd ' OR be
=3D3D 'MMbe =3D
> ' OR bf=3D20
> > =3D3D 'MMbf ' OR bg =3D3D 'MMbg ' OR bh =3D3D 'MMbh ' OR bi =3D3D
'MMbi ' OR=3D20
> > bj =3D3D 'MMbj '=3D20
> > OR bk =3D3D 'MMbk ' OR bl =3D3D 'MMbl ' OR bm =3D3D 'MMbm ' OR bn
=3D3D 'MMbn =3D
> ' OR bo=3D20
> > =3D3D 'MMbo ' OR bp =3D3D 'MMbp ' OR bq =3D3D 'MMbq ' OR br =3D3D
'MMbr ' OR =3D
> bs=3D20
> > =3D3D 'MMbs '"..
> >=3D20
> > All your help is very welcome.
> > Al
> > > Alan,
> > >=3D20
> > > It's unclear by your post exactly what it is that you are=3D20
> > trying to do.
> > > What type of information are you searching on? What does=3D20
> > your database
> > > design look like? Based on what you wrote, it sounds to me=3D20
> > like your
> > > database might be designed in some strange, inefficient way. But
> > > without knowing more, it's hard to provide any useful=3D20
> > comments. Do you
> > > have any code you could post?
> > >=3D20
> > > Peter Foti
> > >=3D20
> > >=3D20
> > > > -----Original Message-----
> > > > From: Alan Coleman [mailto:colemans@m...]
> > > > Sent: Wednesday, June 27, 2001 8:03 PM
> > > > To: ASP Databases
> > > > Subject: [asp_databases] Expert help required for non-profit
=3D
> site.
> > > >=3D3D20
> > > >=3D3D20
> > > > Hi,=3D3D20
> > > > How do I query a Access database using more then 40 OR=3D3D20
> > > > searches. The idea=3D3D20
> > > > is simple, it's just if aa=3D3D3D'aa' then it shows=3D3D20
> > > > that row. The OR searches goes up to 'ht' (aa,ab,ac, ad =3D
> ...).=3D3D20
> > > > I've got=3D3D20
> > > > upto 30 but now I need 40+.
> > > > The database coloums are called the same as the form
names=3D3D20
> > > > subitted using=3D3D20
> > > > POST. Has it got anything to with 'loop'. I've tried to
read=3D3D20
> > > > up on it but=3D3D20
> > > > to no luck.
> > > > Thanks
> > > > Alan
>
Message #10 by "Alan Coleman" <colemans@m...> on Thu, 28 Jun 2001 23:51:14
|
|
Thanks for much needed help.
I could rebuild the database like suggested above, but how would I
search say for a record that has "LETTER 'aa' and VALUE '3'"
OR "LETTER 'ab' and VALUE '1'" for example. The search form is just text
fields named aa-ht that surfers enter the value of each one they want.
This has to be done with text boxes or radio boxes, pull-dows fail.
Thanks
AL
> I really think that you may need to rebuild the database. I'm not sure
that
> I understand entirely what the point of the columns aa-ht are but it
really
> extremely bad database design to build something that means you need to
add
> more fields in the way that these fields are being added. Designing for
> scalability is an incredibly important part of db design. Any time spent
> rebuilding the db will be gained in time not spent trying to work with
the
> current design. In this case I think that you might need another table
> joined to the current one to store the aa-ht fields:
> |ID| |ProductID| |Letter| |value|
> 1 1 aa 1
> 2 2 aa 2
> 3 3 aa 3
> 4 10 ab 1
> 5 11 ab 2
>
> Even then I think that the db design is flawed. I am hoping that this
isn't
> a 1 table database and that you have some relationships.
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
>
> -----Original Message-----
> From: Alan Coleman [mailto:colemans@m...]
> Sent: Thursday, 28 June 2001 9:51 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Expert help required for non-profit site.
>
>
> Thanks Peter,
> The data started with coloum name 'Description' Then coloum
> name 'Effect' the coloums 'aa' 'ab' 'ac' ...'ht'. When it was first set
up
> with just 'aa' 'ab'..upto...'an' in the description it products we sell.
> |ID| |Description| |Effect| |aa| |ab| |ac|
> 1 Thing a one a 1
> 2 Thing a two a(diffent then 2x thinga) 2
> 3 Thing a three a("" 3x thinga") 3
> THIS GOES ON FOR ABOUT 7 things: then this
> 10 Thing b thing b (diffent then 2x thing b) 1
> 11 Thing b thing b (diffent then 3x thing b) 2
>
>
> Get the driff?
> I know it is very un effecit
>
> The form is text fields with name 'aa' and the next one 'ab'..etc.. The
> form asks the database to show the record when form value'aa'etc =db
> coloum 'ab'etc.
> Thanks
> Alan
> > Hi Alan,
> >
> > 1. What is this data that is being stored in the table? That is, if
> > column aa contains "aa", will the remaining columns in this row be
> > empty?
> > 2. As far as the database being too large to redo... I say a database
> > is NEVER too large to redo, especially if it's to the point where it's
> > causing problems. Depending on what exactly this data is that is being
> > stored, I think you should be able to somewhat easily redo at least
> > this
> > table so that the data is stored in a more logical manner. I don't
> > know
> > what aa, ab, ac, ... stands for, but my guess is that it's all the same
> > kind of data.
> > 3. Why does that SELECT statement not work? Is that how you got up to
> > 30?
> >
> > -Peter
Message #11 by David Cameron <dcameron@i...> on Fri, 29 Jun 2001 10:04:43 +1000
|
|
You could generate SQL queries in the page to deal with this or if you are
using SQL Server you could do something in a stored proc. To generate the
SQL query:
<%
Dim rsProd
Dim strSQL
Dim intCnt1, intCnt2
Dim blnOr
blnAnd = False
strSQL = "SELECT Product.ID, Product.Description, Product.Effect FROM " & _
" Product INNER JOIN Category ON Category.ProductID = Product.ID " & _
"WHERE "
For intCnt1 = 1 To 26
For intCnt2 = 1 To 26
If Request.Form(Chr(intCnt1 + 96) & Chr(intCnt2 + 96)) <> "" Then
If blnOr Then
strSQL = strSQL & " OR (Category.Letter AND Category.value)"
Else
strSQL = strSQL & " (Category.Letter AND Category.value)"
End If
End If
Next
Next
strSQL = strSQL & " ORDER BY Product.Description"
Set rsProd = Server.CreateObject("ADODB.Recordset")
rsProd.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
%>
This code is off the top of my head and completely untested.
I am not sure I completely understand what you are trying to do. I might be
able to offer better help if I understood more of what you are trying to do.
Am I correct assuming that there are 176 fields in the form labeled aa-ht
and people enter numbers into them? It sounds a little user a unfriendly. I
am also not quite sure I understand the function of the value field. It
seems that you are using a combination of that and the Letter field as an
ID.
hope this helps
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Alan Coleman [mailto:colemans@m...]
Sent: Friday, 29 June 2001 9:51 AM
To: ASP Databases
Subject: [asp_databases] RE: Expert help required for non-profit site.
Thanks for much needed help.
I could rebuild the database like suggested above, but how would I
search say for a record that has "LETTER 'aa' and VALUE '3'"
OR "LETTER 'ab' and VALUE '1'" for example. The search form is just text
fields named aa-ht that surfers enter the value of each one they want.
This has to be done with text boxes or radio boxes, pull-dows fail.
Thanks
AL
> I really think that you may need to rebuild the database. I'm not sure
that
> I understand entirely what the point of the columns aa-ht are but it
really
> extremely bad database design to build something that means you need to
add
> more fields in the way that these fields are being added. Designing for
> scalability is an incredibly important part of db design. Any time spent
> rebuilding the db will be gained in time not spent trying to work with
the
> current design. In this case I think that you might need another table
> joined to the current one to store the aa-ht fields:
> |ID| |ProductID| |Letter| |value|
> 1 1 aa 1
> 2 2 aa 2
> 3 3 aa 3
> 4 10 ab 1
> 5 11 ab 2
>
> Even then I think that the db design is flawed. I am hoping that this
isn't
> a 1 table database and that you have some relationships.
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
>
> -----Original Message-----
> From: Alan Coleman [mailto:colemans@m...]
> Sent: Thursday, 28 June 2001 9:51 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Expert help required for non-profit site.
>
>
> Thanks Peter,
> The data started with coloum name 'Description' Then coloum
> name 'Effect' the coloums 'aa' 'ab' 'ac' ...'ht'. When it was first set
up
> with just 'aa' 'ab'..upto...'an' in the description it products we sell.
> |ID| |Description| |Effect| |aa| |ab| |ac|
> 1 Thing a one a 1
> 2 Thing a two a(diffent then 2x thinga) 2
> 3 Thing a three a("" 3x thinga") 3
> THIS GOES ON FOR ABOUT 7 things: then this
> 10 Thing b thing b (diffent then 2x thing b) 1
> 11 Thing b thing b (diffent then 3x thing b) 2
>
>
> Get the driff?
> I know it is very un effecit
>
> The form is text fields with name 'aa' and the next one 'ab'..etc.. The
> form asks the database to show the record when form value'aa'etc =db
> coloum 'ab'etc.
> Thanks
> Alan
> > Hi Alan,
> >
> > 1. What is this data that is being stored in the table? That is, if
> > column aa contains "aa", will the remaining columns in this row be
> > empty?
> > 2. As far as the database being too large to redo... I say a database
> > is NEVER too large to redo, especially if it's to the point where it's
> > causing problems. Depending on what exactly this data is that is being
> > stored, I think you should be able to somewhat easily redo at least
> > this
> > table so that the data is stored in a more logical manner. I don't
> > know
> > what aa, ab, ac, ... stands for, but my guess is that it's all the same
> > kind of data.
> > 3. Why does that SELECT statement not work? Is that how you got up to
> > 30?
> >
> > -Peter
|
|
|
View Cart
