Improve company productivity with a Business Account.Sign Up

x
?
Solved

Select with 5 different options

Posted on 2006-06-29
7
Medium Priority
?
247 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 750 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 300 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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 450 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

595 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