MS Access help please!

[Anne in Carlisle]

I have been using the Access databse reasonably successfully for several projects and have been able to manage everything so far ...... Now I've got a question I can't find the answer to!

To cut a long story short I have a project with about 18 fields (columns). Peoples names appear in 12 of these fields. For each record (row) a particular name (say Fred Bloggs) could appear in any ONE field or none of them.

I want Access to list all the records in which Fred Bloggs appears whichever field it might be in.

So far I have tried "Find" but that only finds one at a time and you have to click on to get the next one. I can get it to show all the records where Fred Bloggs appears in (say) field 6, then I can make it show all those in field 7 etc etc.

Is it possible to get a list of all of them at once?????? I'm a real numpty when it comes to queries and such like :(

Anne

[Grimsqueaker]

you would need a query along the following lines:

Assuming there are 3 fields where the name could exist...

SELECT * FROM [I]table[/I] WHERE ([I]field1[/I]='[I]name[/I]') OR ([I]field2[/I]='[I]name[/I]') OR ([I]field3[/I]='[I]name[/I]');

just change table to the name of the table in your database, and likewise with the field names and the name parameter

[Anne in Carlisle]

Thanks for helping, Tim. I still can't get it to work but I have found parts of the database and help files I didn't know existed though!!

I keep getting 'syntax error(missing operator) in query expression'. Unfortunately I can't find out what's missing - such helpful help files - NOT!!!!

Never mind its only a little personal database I thought might help me get to grips with the process but I think I'll admit defeat! Really appreciate your suggestion - it did get me a bit further.

Anne

[col48]

Anne

Chances are it is a missing space or a misplaced quote.
An alternative is to use a UNION to join together the results of separate queries

SELECT * FROM table WHERE field1 = 'name'
UNION
SELECT * FROM table WHERE field2 = 'name'
UNION
SELECT * FROM table WHERE field3 = 'name';

and you might consider using a parameter so that you don't have to change the query every time

SELECT * FROM table WHERE field1 = [Surname] OR field2 = [Surname] ....;

It should prompt you to enter the surname just once when you run the query - to avoid human confusion, Surname should not be the name of one of the fields.

In my database, I have a separate table with the names in and assign a unique identifier to each person; then when I want to refer to that person elsewhere I use that identifier. This means I can separate people who happen to have the same name, can refer to people whose names are as yet unknown and have only one place to change if I find I have misspelt the name or am now able to add it. It makes the queries a bit more complicated but the admin more straightforward.

SELECT * FROM table, people WHERE people.namefield = 'name' and (table.field1 = people.identfield OR table.field2 = people.identfield.....);

Any of the SQL in this post can be built in Design View (although Access will often build more complicated-looking SQL to do the same job).