Searching in Access MDB

O

Oliver

I have a Data base with about 5000 customer names and other fields. When I
imported the info into my Access Form the first and last names of the
customers are in one field, "Name". The spacing between the first and last
names is not the same in all records so my problem is when I do a Find search
for "John Doe" if I don't have the exact number of spaces between first and
last name the name won't come up and I have to add or delete the spacing to
find the record.. Is there a way I can search the field for "John Doe" so it
finds all John Doe's no matter what the spacing is between the first and last
name.
 
D

DL

If these customer names are individuals, the db should have been setup with
seperate fields for both FirstName and SecondName. You could then use a
query to combine FirstName and SecondName, with a name spacer, that
calculated field being shown on your Form - Though you would still need both
Name fields if using the Form to add records.
I would think you need to modify your db design to prevent future problems
with data entry.
 
J

Jeff Boyce

Oliver

I'm with DL -- take the time now to separate FName and LName into two
fields. You'll be glad you did.
 
O

Oliver

Thanks for your reply however I actually have about 200,000 records in this
database and it would be quite tedious if not impossible to have a first name
and last name field. (It certainly should be that way). So I guess what
you're saying that when I do a "Find" search I need to have the correct
spacing between the first and last names oe is there a "wild card" I can use?
Thanks
 
D

DL

200k records does this mean 200k individual customers or have you fallen
into the trap of duplicating data? - sounds suspiciously like it! -
If the fundimmental design is flawed then failing to correct it asap will
likely lead to further problems, and even more difficulty in correcting at a
later time.
 
N

Nadia

Re the fact that your name data is combined is a fundamental problem. This
will haunt you if not resolved.

Is your name data derived from another database or source (and hence
subservient to it)? Is it "standalone"?

If standalone, then I suggest you undertake a program of separating out the
data as min two fields. This can be done without affecting existing data -
new fields etc. - once completed queries and reports can be changed to
reference this new data.

Re your immediate problem, it should be possible to query the data for
records with the first name string anywhere within the name field (query1),
query the data for records with the second name string ... (query2), and then
query again to search for records which instance in both query1 and query2
(these being input to query3).

Good Luck

Nadia.
 
Top