Solved

Select with 5 different options

Posted on 2006-06-29
7
235 Views
Last Modified: 2010-04-07
Hi Experts.

    Here is my problem.

   I have a database table with a lot of fields but the most relvenat fields are:

        Office
        User
       Operation
       Product
      Type.

   There is many users in many offices who make many operaitons over many products of many types.

   I am giving the user the choice of select One or all the offices with  one or all the users (There are many users in each office) with one or all the operations with  one or all the products with one or all the types.

  I need a solution to make a search the most efficient possible because the number of records in the table is thousands more than 500000 and this is for a simple consult that need to be as quickcly as possible.


Hope any one can help me, ask me for further information.

Thanks
0
Comment
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 15

Assisted Solution

by:JackOfPH
JackOfPH earned 250 total points
ID: 17014964
try using sql to generate requested information.
Try

Select * from yourTable where Operation Like `" & searchOperation & "` and Product Like `" & searchProduct & "` and Type Like `" & searchType & "`"
0
 
LVL 35

Assisted Solution

by:Raynard7
Raynard7 earned 100 total points
ID: 17015340
I agree with JackOfPh,
SQL is by far the fastest and easiest option.

You would want to dynamically create your query string so only the necessary elements would be searched on.

I am guessing that you are providing the client a list of exact values (ie distinct users, products, types, offices)

In which case I would run the query like:
********************************
Select
    *
From
    yourTable
Where
********************************
the above code would be in each string,

Then depending on what was in the selections I would add to a string (strSQL);
strSql = ""

If there is one office then insert
strSQL = strSQL & "Office = '" & officeValue & "'"
Otherwise If there were multiple I would use
strSQL = strSQL & "Office in ('" & officeValue1 & "', ' & officeValue2 & "')"
Enumerating through each office selected

I would then continue this choice for User, Operation, Product and Type. With the distinction between the in and = statements

For each of the other types I would also say if strSQL <> "" then strSQL = strSQL & " AND " 

I'd then collect the records using ADO and display them as relevant.
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 17015493
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 12

Author Comment

by:Alfredo Luis Torres Serrano
ID: 17021300
The use of SQL is not the best option because is it not just for Windows and i am looking the best performance not mater which database engine.

Still looking for solution
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17021952
You would be able to write this using an ANSI standard SQL and use this across database engines.  You should be able to use the same SQL for MySql, Oracle, Ms SQL and perhaps even access.

You mention that you have a "database table" so if you are not using SQL to retreive the data - how do you want to retreive it?

Are you looking for some sort of recursive sorting algorithm to go through your table and find the data?

Can you be more specific on what you are looking for?
0
 
LVL 3

Accepted Solution

by:
gafoorgk earned 150 total points
ID: 17022377
ok Masteraco, there is no way other than using SQL. SQL is compatible with any patform u might be using. i'll assume that u have combo or datacombo boxes for each field in the search form with first item being '<All>' following items of relevent field.  ADODB data combo would be the best choice. so the following code assumes that u r using adodb data combos with DisplayField set as values of each field and BoundColumn set as IDs of each field. all datacombos should have <All> item on top with 0 as ID. recordset for these combos can be generated using '<normal sql> union <custom sql selecting all and 0>'.

in the above scenario, your search sql would look something like following

"select * from <table> where ((office_id = " & office_combo.boundtext & ") or (" & office_combo.boundtext & " = 0)) and .........."

well, it'll seem to be little bit complicated. but once u understand what it does, u would like it's flexibility.
0
 
LVL 12

Author Comment

by:Alfredo Luis Torres Serrano
ID: 17023872
I have decided to use a mixed way using Logical Views and simple select with keys to thoe logicals ans sql whre needed.

Thsnka all, I Will split the points
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question