Tweaking a query

[Jeff]

This is my current query. I am getting back all matches when the two
fields are equal (or one contains the other). (e.g. Swim gives me City
Swim, Swim Party, etc.) I would now like to just get the first
occurrence where this happens. (so Swim would only give me City
Swim.)

SELECT [Participation ACE Convert Query].[Member ID], [Participation
ACE Convert Query].[First Name], [Participation ACE Convert Query].
[Last Name], [Participation ACE Convert Query].Program, [Last Name],
[Participation ACE Convert Query].OutingName, [Participation ACE
Convert Query].Volhours, [Participation ACE Convert Query].Baseline,
[Participation ACE Convert Query].NumberChecked, [Participation ACE
Convert Query].LifeSkill, [Participation ACE Convert
Query].StaffComment, Outings.EventID
FROM Outings INNER JOIN [Participation ACE Convert Query] ON
([Participation ACE Convert Query].OutingName Like "*" &
Outings.EventName & "*") AND (Outings.ProgramID Like "*" &
[Participation ACE Convert Query].Program & "*");

What would I need to change about this query to do that?

Thanks!

Jeff

 

[John W. Vinson]

This is my current query. I am getting back all matches when the two
fields are equal (or one contains the other). (e.g. Swim gives me City
Swim, Swim Party, etc.) I would now like to just get the first
occurrence where this happens. (so Swim would only give me City
Swim.)

"First" by what sort order? Access Tables have no order, so you must specify
some field. Or do you want the first outing for each member?

You are using a VERY inefficient and unreliable technique joining your two
tables by wildcarded substrings. This will defeat any indexes and will require
very careful control of the values entered into the joining fields. I'm not
sure what to suggest to improve it other than to have a meaningless EventID as
a link, and use a WHERE clause to do the substringing.