By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,282 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Search all from one table

P: 3
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
Share this Question
Share on Google+
5 Replies


JustJim
Expert 100+
P: 407
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

P: 3
Sorry I meant to click reply!
Mar 7 '08 #3

P: 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

Expert Mod 2.5K+
P: 2,545
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
Expert 100+
P: 407
It's true!

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

Jim
Mar 9 '08 #6

Post your reply

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