473,240 Members | 1,579 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,240 software developers and data experts.

Lots of queries for my db

Hello all,

I have a database in SQL Server that should save data from a CRM-like
application.

The database consists of tables like products, services, customers,
partners etc. Problem is that the users should be able to find these
items on different properties and with or without substring finding
(SQL: LIKE). Example: I want the users to be able to find a customer,
providing a customerID, but also providing a customername, zipcode or
just a part of those strings.

This will result in a lot of queries. I bet there are some nice
solutions to this, since I will not be the first with this situation.

If anyone can help, please.
Thank you in advance.

Regards,

Freek Versteijn
Jul 20 '05 #1
3 1713
This seems like the article for you:
http://www.algonet.se/~sommar/dyn-search.html

--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=d...blic.sqlserver
"Versteijn" <ve*******@538mail.nl> wrote in message
news:4d**************************@posting.google.c om...
Hello all,

I have a database in SQL Server that should save data from a CRM-like
application.

The database consists of tables like products, services, customers,
partners etc. Problem is that the users should be able to find these
items on different properties and with or without substring finding
(SQL: LIKE). Example: I want the users to be able to find a customer,
providing a customerID, but also providing a customername, zipcode or
just a part of those strings.

This will result in a lot of queries. I bet there are some nice
solutions to this, since I will not be the first with this situation.

If anyone can help, please.
Thank you in advance.

Regards,

Freek Versteijn

Jul 20 '05 #2

One of the solutions is not a technical solution : it's a deeper analysis.
A great advantage of this solution is that the technical implementation will
be, we hope, simpler than an exhaustive research across all the columns of
the database.

The risk of an exhaustive implementation is an research that the users
will use only at 5% of its capacities (always quering on the 4 same
columns...).

I had implemented this kind of research program : it worked fine when it
returned 2 result lines, but too much often, it had returned thousand lines
!

For information, the main algorithm was (if my memory do not fail !) :
- ask to the user the value to search, and for string value only, if it is
an exact search or an "LIKE %s%" search ;
- detection of the type of value to search (char, interger, float, ...) ;
- research of the columns of this type (1 query in
INFORMATION_SCHEMA.COLUMNS) ;
- for each table with at least one matched column, 1 SELECT query :
- SELECT PK, fld1, fld2
- FROM the table
- WHERE fld1='value' OR fld2='value' OR ...
(or WHERE fld1 LIKE '%value%' OR ...)

* analyse the result recordset and display the results in a array, with
these columns : TableName, PKValueOfTheMatchedRecord, FieldName, FieldValue.

This program gives the right results, but it talks about "Table", "PK",
"Field", ... and it's not the user's language. So, the difficulty is to
transform these "data oriented" results in "users oriented" results !

I'm interested in an different approach.

Jacques.
"Versteijn" <ve*******@538mail.nl> a écrit dans le message de news:
4d**************************@posting.google.com...
Hello all,

I have a database in SQL Server that should save data from a CRM-like
application.

The database consists of tables like products, services, customers,
partners etc. Problem is that the users should be able to find these
items on different properties and with or without substring finding
(SQL: LIKE). Example: I want the users to be able to find a customer,
providing a customerID, but also providing a customername, zipcode or
just a part of those strings.

This will result in a lot of queries. I bet there are some nice
solutions to this, since I will not be the first with this situation.

If anyone can help, please.
Thank you in advance.

Regards,

Freek Versteijn

Jul 20 '05 #3
> The database consists of tables like products, services, customers,
partners etc. Problem is that the users should be able to find these
items on different properties and with or without substring finding
(SQL: LIKE). Example: I want the users to be able to find a customer,
providing a customerID, but also providing a customername, zipcode or
just a part of those strings.


Hi,

Without knowing the details about your DB, the following should work.
Assuming that there are no nulls in your key fields and they are set
as varchar/char. If they're integer, the query will do an implicit
conversion. Of course, the hard part is actually testing and
optimizing the queries. -- Louis

-- All the parameters should default to '%'
(to return all rows, unless the user passes a value)

select *
from mytable
where
customerID like @customerID
and customerID like '%'+ @customerIDfragment +'%'
and customername like @customername
and customername like '%'+ @customernamefragment +'%'
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
1
by: Roger Green | last post by:
I have inherited a complex database that has many dozens of queries that derive data from a people table. I now need to be able to run these queries (from within a significant number of forms)...
3
by: NeilAnderson | last post by:
I'm a fairly new user of access & I've never had any training, so I'm wondering if I'm doing the right thing here, or if it matter at all. I'm building a database for room booking purposes and I'm...
2
by: Darryl Kerkeslager | last post by:
As the subject above hopefully makes clear, I want to do several reports, "with lots of fields not otherwise in database". These reports also have variable-length text. I have defined the...
44
by: Greg Strong | last post by:
Hello All, Is it better to create a query in DAO where a report has 4 sub-reports each of whose record source is a query created at runtime and everything is in 1 MDB file? From what I've...
6
by: Jack | last post by:
Hi there, I'm not sure if this the appropriate group so apologies if it lies outside the boundary. Senario: I have a customer table with contains a bunch of different bit values that represent...
2
by: saran | last post by:
I am having a problem with MySQL consuming a lot of memory and eventually throwing an Out of Memory error and restarting itself. The symptoms are that swap usage continues to rise until some...
2
by: TD | last post by:
Hello All- We are in the process of converting the back end of our large MS Access application to PostgreSQL. My question today concerns linked tables and how their connections are managed by...
5
by: listerofsmeg01 | last post by:
Hi, Pretty new to PHP and MySQL. I have a page on my site that displays a lot of information from various tables. Currently I have lots of small PHP wrapper functions around SQL queries to...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.