Inputting names from a table

[djchapple]

The members of this forum (99%) and I (1%) have created a query to look through a table selecting the number of times a particular volunteer has manned the Visitor Centre in a predefined period.

Because of the way in which names are entered into the database I have had to search for (say) a first name of "David" plus a family name of "Chapple". This is because names are often entered as "David and Marilyn Chapple" so then name David Chapple may not actually appear in the relevant field.

Further information can be found in the thread "Wrong syntax - missing operator".

Now we have around 70 - 80 volunteers so to repeat the query, as it stands, for every single volunteer would take forever.

The first and last names of the volunteers are stored separately in another table so I am hoping that I can strip the names from the other table, one at a time, automatically. That wa I will get all the data I need in one fell swoop.

The query as it stands is shown below with the search names in red. The sessions volunteered are storesd in Expr7 and Expr8 as a -1.

SELECT [Table Rota].ID,
[Table Rota].RealDate,
[Table Rota].Morning,
[Table Rota].Afternoon,
IIf([Morning] Like "*David*",True) AS Expr1,
IIf([Morning] Like "*Chapple*",True) AS Expr2,
IIf([Afternoon] Like "*David*",True) AS Expr3,
IIf([Afternoon] Like "*Chapple*",True) AS Expr4,
[Expr1]+[Expr2] AS Expr5,
[Expr3]+[Expr4] AS Expr6,
IIf([Expr5]=-2,True) AS Expr7,
IIf([Expr6]=-2,True) AS Expr8
FROM [Table Rota]
WHERE ((([Table Rota].RealDate)>#8/31/2010# And ([Table Rota].RealDate)<#9/1/2011#));

 

[JANR]

Does this give you what you want?

SELECT [Table Rota].ID,
[Table Rota].RealDate,
[Table Rota].Morning,
[Table Rota].Afternoon
FROM [Table Rota]
WHERE [Table Rota].RealDate>#8/31/2010# And [Table Rota].RealDate)<#9/1/2011#
AND ([Morning] & "|" & [Afternoon] Like "*David*" OR [Morning] & "|" & [Afternoon] Like "*Chapple*");

JR