Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008
    Posts
    3

    Unanswered: How do I get all the records containing the same information in several fields?

    I should know this, but I cannot remember how I did it years ago.

    Here's what I have: A database containing the names and contact information on a couple hundred small businesses in the construction field. We are a small general contractor and want to bring in other small businesses as subcontractors.
    So, I made 5 fields in my original table and called them Service1, Service2, etc. In these fields I have descriptions of what they do - i.e. plumbing, HVAC, gas pipelines.
    Many of these companies have more than one service, so, if I want to ask my database to give me the names of all the companies that do "plumbing" (for example) what query can I write, preferably with a drop-down list of all the services represented (I have standardized them across the board, so there is no record saying "Plumber" instead of "plumbing.") Let's say we have a project, I need a plumber, and I want the list of all the plumbing companies, even if that information is in their 2nd, 3rd or 4th field of services.

    Any help here? If you can give me one process to follow, I can take it from there.
    Thanks in advance!

    ~~ Lyn in Colorado

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    Can you upload a copy of your DB? It may be a little easier if we can take a look at the exact manner in which your tables are organized in order to develop your query.

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The design is not normalized with the repeating fields, but:

    ...WHERE Service1 = 'Plumber' OR Service2 = 'Plumber' OR...
    Paul

  4. #4
    Join Date
    Jul 2008
    Posts
    3

    My contractor db

    Here's the contractor db I'm having trouble with. It's only a few days old, so it's in a pretty rough stage at this point.

    Thanks for the help!

    ~~ Lyn
    Attached Files Attached Files

  5. #5
    Join Date
    Aug 2006
    Posts
    559
    Well, PBaldy said it. I've had to redo this database that I'm working on like 5 times already because it's not normalized. Meaning that the fields, like PBaldy says, are repeating.

    You may want to do some research on 'database normalization' or break those "Service" areas out into other fields. Like have a "Main Service Offered", "Alternative Service Offered", "Other Service Offered".

    However, if you do want to keep it the way you have it, PBaldy is right.... You'd have to do a query like, "Select "Contractor Names" from tblContractor where Service 1 = Plumber OR Service 2 = Plumber OR Service 3 = Plumber.

    And second thought is that you may want to change your "Service" field to just a regular text box rather than a memo since those are mainly designed for using more than 255 characters.

  6. #6
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    While I would certainly endorse the desirability of fully normalising a database, sometimes it is not always possible (dealing with legacy stuff or multiple data sources for instance). In these circumstances, one solution you can try is to create a series of queries, each one selecting the data from a different field and then join them together with the UNION clause. You can use the DISTINCT keyword to filter out duplicates and place an ORDER BY clause at the end.

Posting Permissions

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