Search database for namee

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database and would like to make a search function on my form by our
client's last name. The form is based on a join query. The name of the
client doesn't just show up in one column, it could show up in about 25
different columns in the form. I want the users to be able to type in a last
name and have all of the records show up if the last name is found anywhere
in it. I have really tried reading a lot of these posts already and have a
book and just can't seem to figure this out. Could someone help me out and
explain this to me as simply as possible? I really, really appreciate any
time spent on helping me. My boss is becoming inpatient with how long it's
taking me to figure this out and I'm not sure what else to do. Again, thank
you so much for your help in advance.
 
You may want to post a bit about the structure of your database. If your
name could show up in one of 25 columns, then your database structure MAY be
badly flawed.

In answer you your question, you can search for one string in more than one
field using "or".
 
You have a serious normalization problem with your database. The client's
last name should only be in one column in one table. If you have 25 different
places listing the client's last name, you need to rebuild the database from
scratch. Harsh but true.

But to see if we can help with your current perdicament, please post the SQL
of the query of the Form.
 
I want to clarify. I have 8 tables in my database. I have a query bringing
them together and am basing my form on the query. On the query, the client's
name could show up in 25 places. I want to query the query and return full
records. In each record, the client's name could show up in 25 places. Does
this make a difference in your answer, or is my database still not normalized
at all?
 
Why do you have 8 tables? If they all contain the same fields, they should
be one table (with a separate field to indicate customer type or whatever
makes them fall in your current separate tables).

You say it could be in one of 25 fields. Why? A name should always be in
the Name field. If a company has up to 25 people associated with it, You
don't create 25 fields (contact1, contact2, contact3, etc.) This would be a
case where you'd need a separate table to store the people. A company would
have one record in the "company" table and multiple records in the "contact"
table. If a company has ten contacts, they'd have ten entries in that
second table. If they have 50 contacts, they'd have 50 records. Of course,
that second table would need to store the company number or some other key
to relate it to the company table...


tblcompany
CompanyNumber
CompanyName
CompanyAddress1
etc.

tblContacts
CompanyNumber
ContactFirstName
ContactLastName
ContactDirectPhone
ContactCellPhone
Active Y/N
etc.
 
Not that based on your previous response, your tblcompany would also have a
drop-down identifier so you can "virtually" separate them into the eight
tables you have now.

tblcompany
CompanyNumber
CompanyName
CompanyType
CompanyAddress1
etc.
 
There's only one practical way for us to know: Show us the SQL. Open the
query in SQL view and copy and past it here. Information on primary keys and
relationships would be a nice touch too.
 
Back
Top