deleting records

[adrian]

probably a really simple answer, but here's my basic problem:

i have a list of items which have been issued to various people. in this
table the name is in the form of a full name (first name and surname). in
another table i have users full name with their first names and surnames as
separate columns.

in my query i need to search by surname by itself (ignoring first name) but
when i add the users table to my query, it won't let me delete any records
any more because its a one-to-many relationship. therefore is it possible to
get round this anyway, either by searching for just part of the full name or
by somehow solving the relationship problem. i would prefer not to have to
modify any of the tables by adding new columns

if you could answer fairly simply please, because i'm nowhere near being an
expert in access
thanks, adrian

 

[Allen]

Try this as a criteria:

FullName LIKE "*[Surname]"

assuming the Surname comes last, but if it can come first then use

FullName LIKE "*[Surname]*"

The * is your wildcard.

For more examples, http://www.aislebyaisle.com/access/tools2.htm has a
demo with a form called devExportObjects that uses LIKE with * in VBA
code.

 

[adrian]

Sorry, I've tried it with a wildcard but it doesn't ask me which surname I
want to search for. It simply comes up with everyone. What is strange is that
it only comes up with 222 items (with Like "*[Surname]") when there are
actually 308 items in total (at the moment). There seems to be no pattern,
but this is just a side issue and will probably get sorted out by the correct
search term

Thanks,
Adrian

 

[adrian]

I've solved my own problem

The required text should be:
Like "*" & [Surname]

Adrian