Solved

Select with 5 different options

Posted on 2006-06-29
7
233 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now