471,075 Members | 1,114 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

help trying to streamline my sql

347 100+
I have a website that uses asp and sql to access an access database and am trying to streamline the code, im not very good with access and sql and was wondering if someone could help?

i have sql code along the lines of this on all pages
Expand|Select|Wrap|Line Numbers
  1. Set yaketyConnection=Server.CreateObject("ADODB.Connection")
  2. DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/yaketynew.mdb") & ";"
  3. yaketyConnection.Open DatabaseDetails
  4. yaketyQuery="SELECT * FROM celebs WHERE voicetypea like '%"&voiceid&"%' and sex like 'm' or voicetypeb like '%"&voiceid&"%' and sex like 'm' or voicetypec like '%"&voiceid&"%' and sex like 'm' or voicetyped like '%"&voiceid&"%' and sex like 'm' or voicetypee like '%"&voiceid&"%' and sex like 'm' or voicetypef like '%"&voiceid&"%' and sex like 'm' or voicetypeg like '%"&voiceid&"%' and sex like 'm' or voicetypeh like '%"&voiceid&"%' and sex like 'm' or voicetypei like '%"&voiceid&"%' and sex like 'm' or voicetypej like '%"&voiceid&"%' and sex like 'm' or voicetypek like '%"&voiceid&"%' and sex like 'm' or voicetypel like '%"&voiceid&"%' and sex like 'm' or voicetypem like '%"&voiceid&"%' and sex like 'm'  or voicetypen like '%"&voiceid&"%' and sex like 'm' or voicetypem like '%"&voiceid&"%' and sex like 'm' or voicetypeo like '%"&voiceid&"%' and sex like 'm' or voicetypep like '%"&voiceid&"%' and sex like 'm' or voicetypeq like '%"&voiceid&"%' and sex like 'm' or voicetyper like '%"&voiceid&"%' and sex like 'm' or voicetypes like '%"&voiceid&"%' and sex like 'm' or voicetypet like '%"&voiceid&"%' and sex like 'm' or voicetypeu like '%"&voiceid&"%' and sex like 'm' or voicetypev like '%"&voiceid&"%' and sex like 'm' or voicetypew like '%"&voiceid&"%' and sex like 'm' order by surname,firstname"
  5. Set yaketyRecordset=yaketyConnection.execute(yaketyQuery)
i have attached a selection of pages and the access database to look at

I hope someone can assist i would like to speed up our website and im sure the code and database is messy
Attached Files
File Type: zip yaketynew1.zip (732.9 KB, 76 views)
File Type: zip pages.zip (41.1 KB, 69 views)
Apr 28 '11 #1
2 1470
3,406 Expert 2GB
The problem is you have over twenty columns with the same (almost) name and description. What's up with that? change it to a single memo field so you can enter a paragraph if you want, then you just have to search one field. The only other reasonable solution is to have a separate table with a list of voicetypes, and another table relating that to the celebs:
Expand|Select|Wrap|Line Numbers
  1. voicetypes table
  2. voicetype_id     voicetype_description
  3. 1                hearty
  4. 2                whiny
  5. 3                motherly
  6. etc
  8. celeb voicetypes table
  9. celebid     voicetypeid
  10. 1            1
  11. 1            3
  12. 1            17
  13. 2            6
  14. 3            5
  15. 3            19
  16. 3            8
That is the real power of a relational database even if you have an easy way to add new voicetypes. For example, when a celeb enters his voicetype, check to see if it's already in the db, if it is, add celeb 16 and voicetype 6 to the celeb voicetype table. If not, add a new voicetype and then use that new number to add to the celeb voicetype table, and the next time a celeb enters that voicetype it will already be in the db.

Jun 10 '11 #2
347 100+
Hi Jared

Thanks for that i now have a DB that is as above except the voicetypeid is worded and not numbered, i am trying to get my sql to work now and tried the following but i am going wrong somewhere

Expand|Select|Wrap|Line Numbers
  1. yaketyQuery="SELECT * FROM celebs INNER JOIN celebsvoicetypes ON celebs.idnumber = celebsvoicetypes.celebid where celebsvoicetypes.voicetype = '" &voiceid& "' and celebs.sex like 'f' order by celebs.surname,celebs.firstname"
Jun 10 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by dantan98 | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.