Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Select with 5 different options

Posted on 2006-06-29
7
Medium Priority
?
245 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

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…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

578 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