473,322 Members | 1,734 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,322 software developers and data experts.

Search all from one table

Hi,

I am trying to create a search form in Access 2000 that will allow the user to type in a string or multiple string, comma separated that will search one whole table, all columns and all records for whatever the user types. I have one table with 25 columns and 100 rows/records. Each cell contains numbers and letters. I need a "Google" style search command that will retrieve all records with the users input string. A one to many relationship.

So far I have: "select * from tblReports where " & txtSearchString...

Can someone please assist or advise on another way I can create a Search Command?

Thank you.
Mar 7 '08 #1
5 3038
JustJim
407 Expert 256MB
Hi,

I am trying to create a search form in Access 2000 that will allow the user to type in a string or multiple string, comma separated that will search one whole table, all columns and all records for whatever the user types. I have one table with 25 columns and 100 rows/records. Each cell contains numbers and letters. I need a "Google" style search command that will retrieve all records with the users input string. A one to many relationship.

So far I have: "select * from tblReports where " & txtSearchString...

Can someone please assist or advise on another way I can create a Search Command?

Thank you.
I suspect that your table structure is not perfect. Please feel free to post full details of the table you want to search. In the meantime read this for further information

Jim
Mar 7 '08 #2
Sorry I meant to click reply!
Mar 7 '08 #3
Thank you for your reply.

Here is the sample that I am working with, it only has 10 records in it right now, but there will be at least 100 records. This table will be a list of reports with the each record being a different report and the details of what the report displays in all the columns. The objective is too allow users to be able to type in a keyword or words from a report without having to specify which column or field they are in and the results displayed should be just the report names.

Columns:
ReportID ReportCode ReportName Group Formula1 Formula2 Formula3 Formula4 Formula5 Formula6 Formula7 Formula8 Formula9 Formula10 Description SelectExpert TblName1 TblName2 TblName3 TblName4 TblName5 TblName6 ColName1 ColName2 ColName3 ColName4 ColName5 ColName6 ColName7 ColName8 ColName9 ColName10 ColName11 ColName12 ColName13 ColName14 ColName15

Rows/Records:
1 chpd01, Funds Flow, aged investigations open or pending, Age CCYType, Range, Site, USDConvert, TransactionType, Mtcase, Mttran, Mttracs, Mtcomp, Mtqf, Assign To Case No, Counterparty, Ccy, Principal USDEquiv, Value Posted, Type, Dept, Error, Description
2 chpd02, ids, aged, compensation open or pending, Age, CCYType, Range Site, USDConvert, TransactionType, Mtcase, Mttran, Mttracs, Mtcomp, Mtqf, Case No, Counterparty, Ccy, Principal, USD Equiv, Value Posted, Type, Dept Error, Description, Age
3 chpd03, Funds Flow, open or pending, Age, CCY,Type, USDConvert, TransactionType, Mtcase, Mttran, Mttracs, Mtcomp, Mtqf, CURRENCY, Total, Cases per CCY, Total USD, Count of, Sum of
4 chpd04 Funds Flow, investigations open or pending, Age, CCYType, USDConvert TransactionType Mtcase Mttran Mttracs Mtcomp Mtqf Case No. Counterparty Ccy Principal USD Equiv. Value Posted Type Dept Error Description Age
5 chpd05 Funds Flow compensation open or pending Age CCYType USDConvert TransactionType Mtcase Mttran Mttracs Mtcomp Mtqf Case No. Counterparty Ccy Principal USD Equiv. Value Posted Type Dept Error Description Age
6 chpm01 Funds Flow case setups Age CCYType USDConvert TransactionType Mtcase Mttran Mttracs Mtcomp Mtqf CURRENCY Total Cases per CCY Total USD Count of Sum of
7 chpm02 Funds Flow closed invest summary Age Mtcase Mttran Mttracs Mtcomp Mtqf Assigned To Number of Cases Count of Sum of
8 chpm03 Funds Flow closed comp summary Age Mtcase Mttran Mttracs Mtcomp Mtqf Assigned To Number of Cases USD Equivalent Count of Sum of
9 abc ids what rate 90 days pen
10 def invs when rate 30 days aud
Mar 7 '08 #4
Stewart Ross
2,545 Expert Mod 2GB
Hi. You are being somewhat ambitious here, and given the sample rows you have posted a free-text search would be of limited use to you.

Databases are built from tables with discrete fields, usually of differing types, storing data for a specific purpose. Your sample data shows that there are not that many text-only fields, and for those that are the values are columnar in structure (as they should be). I mention this because your free-text search is intended to be across all columns, not just within one or two, yet your data shows no cross-column similarities (which is entirely normal in a relational database application). Looking at your data I would question what return you would get for the effort involved in implementing what you ask.

In SQL, If you wanted to search for a matching string in all the text fields of your table you build a SELECT statement with a WHERE part which includes a "fieldname like *searchtext*" comparison for each and every one of your text fields, all separated by ORs. This is likely to be very slow in operation, given the repeated comparisons for each field on all rows.

Using a comma separated search string would involve some coding in VB to separate the input string into its components. Although this is not difficult, if you had three items to search for the already-slow all-field search would just have to do three times the work and run three times slower still.

All in all, when the range of text values in your tables is so limited I would think it just not worthwhile to take this further. There are alternatives such as filter by form that are built in to Access which require no coding or SQL at all to implement. Perhaps you should investigate these alternatives instead. They are not free-text searches of all fields, but in the context of the sample data you have posted you really do not need an all-column search. You might want it, which is a different matter, but you really don't need it!

-Stewart
Mar 8 '08 #5
JustJim
407 Expert 256MB
It's true!

Really, you have to read the Normalisation post referred to in posst <2> above.

Jim
Mar 9 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Greg | last post by:
I have a page that searches a database by a repairman's name and by a date range. It pulls info by the repairman's name but pulls all info in the database regardless of the date. Below is the code...
2
by: TH | last post by:
I am (still :) working on a recipe database. Now I am trying to figure out how to set it up for an ingredient search. What I want it to be able to do is to search by one ingredient, sometimes by...
6
by: DC | last post by:
Hi, I am programming a search catalogue with 200000 items (and growing). I am currently using the SQL Server 2000 fulltext engine for this task but it does not fit the requirements anymore. ...
1
by: ratnakarp | last post by:
Hi, I have a search text box. The user enters the value in the text box and click on enter button. In code behind on button click i'm writing the code to get the values from the database and...
12
by: Divick | last post by:
Hi all, does any one know of any data structure in which I can search the key and value equally fast? Though I could use hashes, but I can only search in constant time on key but not on value. If...
4
by: | last post by:
Hello... i have a table which contains a column named "ask" and a column named "per"... my think is that i want to search in "ask" and echo the data stored in "per" for this entry... How do i do...
8
by: stunna | last post by:
Hi there, i want to implement a a system to search for a file in a folder using the file name and if its there, i want it to be displayed. I have one through a couple of books on how to implement...
1
Merlin1857
by: Merlin1857 | last post by:
How to search multiple fields using ASP A major issue for me when I first started writing in VB Script was constructing the ability to search a table using multiple field input from a form and...
5
by: kanley | last post by:
I have a main table with a text description field. In this field, its populated with a string of data. I need to identify from this string of data the name of the vendor using some keywords. I...
12
by: iahamed | last post by:
Hi Everyone, I got two parts of my advance search to work, I am running out of Logic to connect the third. My mind is in swing! Pleaseeeeeeeee Help me. I have 3 Fiels to search, the First two...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.