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!
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.
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.