Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Unanswered: How to search multiple fields??

    Hi,

    I have approx 3,000 rows (they are all names of suppliers)
    I have approx 100 fields (they are named Field1, Field2, Field3 etc)

    In Field 1, we will 'randomly' enter the name of any product they are approved to supply (eg. table, chair, computer etc).

    In Field2, we will 'randomly' enter the name of any other product they are approved to supply.

    How can I search each of the 100 fields and return the names of all suppliers who are approved to supply 'tables'?

    I have created a Query with a condition which shows =table in Field 1. But if I put 'table' in the other fields as Criteria then it dfoes not work (it will only work if ALL fields have 'table'. I have also tried to use OR, but this does not work either.

    Any help woudl be much appreciated.

    Cheers,

    Paul

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183

    How to search multiple fields??

    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    Hi apr pillai,

    Thanks for a very comprehensive answer - the thread was great. It isjust too complicated for me to understand.

    I did manage to find a microsoft thread which advises I am formatting my database incorrectly. And I should be using more Rows instead of trying to input data in many fields.

    This makes it easier to search for data, apparently.

    Cheers.

  4. #4
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by reddevil1 View Post
    Hi,

    I have approx 3,000 rows (they are all names of suppliers)
    I have approx 100 fields (they are named Field1, Field2, Field3 etc)

    In Field 1, we will 'randomly' enter the name of any product they are approved to supply (eg. table, chair, computer etc).

    In Field2, we will 'randomly' enter the name of any other product they are approved to supply.

    How can I search each of the 100 fields and return the names of all suppliers who are approved to supply 'tables'?

    I have created a Query with a condition which shows =table in Field 1. But if I put 'table' in the other fields as Criteria then it dfoes not work (it will only work if ALL fields have 'table'. I have also tried to use OR, but this does not work either.

    Any help woudl be much appreciated.

    Cheers,

    Paul
    Paul,

    I think you have already learn this, but you do have a design issue.

    You should have each product on a separate row (record) in a related table.

    This will then allow a simple query on one field.

    While apr pillai's example would work for a properly design (normalized) database, it really is not what you should use in this case. I agree with you that you should fix the design issue.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Boyd's advice is right on the money here! Any time you're entering the identical kind of data, such as products, into multiple fields instead of entering it into multiple records in a related table, you assuredly have a non-normalized database, and are making almost any data manipulative tasks ten times harder to do!

    Likewise, most experienced developers will tell you that a table with more than 25-35 fields is also sure to be non-normalized. Take the time now to correct your design flaws and you'll thank yourself later!

    Good luck with your project!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Missinglinq View Post
    Likewise, most experienced developers will tell you that a table with more than 25-35 fields is also sure to be non-normalized.
    sure to be? nonsense!!

    increases the likelihood, but doesn't make it a certainty
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    Thanks for your comments guys, and for the confirmation regarding the design issue.

    I didn;t want to increase the number of rows coz i thought the size of the database would become too large (I will have maybe 8,000 rows eventually?).

    But I need to manipulate the data to make the reports work so re-designing is the only way.

    Thanks.

  8. #8
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by reddevil1 View Post
    Thanks for your comments guys, and for the confirmation regarding the design issue.

    I didn;t want to increase the number of rows coz i thought the size of the database would become too large (I will have maybe 8,000 rows eventually?).

    But I need to manipulate the data to make the reports work so re-designing is the only way.

    Thanks.
    Databases generally are more efficient with more rows than form fields.

    Having "8,000 rows eventually" should be considered a small number of records.

    I would much rather have 8,000 rows with 10 fields than 800 rows with over 50 fields.

    If you will look into how JET and other database engines use buffers/read-cache to gain performance, things will make more sense. Having really large record sizes (lots of fields) can possible have negative performs hits because Microsoft Jet's read-cache is not longer helping reduce disk reads and/or network traffic.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •