Solved

Select with 5 different options

Posted on 2006-06-29
7
236 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…

860 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