IIF issue

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I'm trying to write a query (to help with a database conversion I'm
working on). There will be a new table that ties Events and Members
together. I'm trying to tie data together as much as I can
(understanding that this will not give me 100% of the results, which
will later have to entered manually.)

Currently the two tables are not in any kind of relationship. There is
an EventName on the existing Events table. I want to pull the EventID
from the Events table, if the OutingName in my query from my Members
table contains the Event Name from the Events table. Unfortunately,
the OutingName also contains other information besides just the Event
Name.

This is the query I have, but it doesn't seem to be working. What am I
doing wrong?

Thanks in advance

Jeff

SELECT [Participation Convert Query].[Member ID], [Participation
Convert Query].[First Name], [Participation Convert Query].[Last
Name], [Participation Convert Query].OutingName, [Participation
Convert Query].Volhours, [Participation Convert Query].Baseline,
[Participation Convert Query].LifeSkill, [Participation Convert
Query].StaffComment, IIf([OutingName] Like "*" & [EventName] & "*",
[Outings].[EventID]) AS EventID
FROM Outings, [Participation Convert Query];
 
Different scenarios come to mind.

Do you want to return records even when the OutingName does not contain the
EventName value? If yes, this query will do that, but it will return
duplicate records because of the cartesian join being used between the
tables:

SELECT [Participation Convert Query].[Member ID], [Participation
Convert Query].[First Name], [Participation Convert Query].[Last
Name], [Participation Convert Query].OutingName, [Participation
Convert Query].Volhours, [Participation Convert Query].Baseline,
[Participation Convert Query].LifeSkill, [Participation Convert
Query].StaffComment,
IIf(Outings.[OutingName] Like "*" &
[Participation Convert Query].[EventName] & "*",
[Outings].[EventID], Outings.[OutingName]) AS EventID
FROM Outings, [Participation Convert Query];


Do you want to return records only when the OutingName does contain the
EventName value? If yes, this query will do that, and will not return any
duplicates:

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


If EventName contains just single values, where one value might be the
OutingName value, or might be some other value, then this query is even
simpler:

SELECT [Participation Convert Query].[Member ID], [Participation
Convert Query].[First Name], [Participation Convert Query].[Last
Name], [Participation Convert Query].OutingName, [Participation
Convert Query].Volhours, [Participation Convert Query].Baseline,
[Participation Convert Query].LifeSkill, [Participation Convert
Query].StaffComment, [Outings].[EventID]
FROM Outings INNER JOIN [Participation Convert Query]
ON Outings.[OutingName] =
[Participation Convert Query].[EventName];

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Jeff said:
I'm trying to write a query (to help with a database conversion I'm
working on). There will be a new table that ties Events and Members
together. I'm trying to tie data together as much as I can
(understanding that this will not give me 100% of the results, which
will later have to entered manually.)

Currently the two tables are not in any kind of relationship. There is
an EventName on the existing Events table. I want to pull the EventID
from the Events table, if the OutingName in my query from my Members
table contains the Event Name from the Events table. Unfortunately,
the OutingName also contains other information besides just the Event
Name.

This is the query I have, but it doesn't seem to be working. What am I
doing wrong?

Thanks in advance

Jeff

SELECT [Participation Convert Query].[Member ID], [Participation
Convert Query].[First Name], [Participation Convert Query].[Last
Name], [Participation Convert Query].OutingName, [Participation
Convert Query].Volhours, [Participation Convert Query].Baseline,
[Participation Convert Query].LifeSkill, [Participation Convert
Query].StaffComment, IIf([OutingName] Like "*" & [EventName] & "*",
[Outings].[EventID]) AS EventID
FROM Outings, [Participation Convert Query];
Click to expand...
 
Back
Top