I have designed a query, but the results are not coming out proper...
I have 4 tables, Familes, People, LinksEvent, and Events. I cannot change these because it is a database someone else has done that is used for lots of things.
From events, I determine whether it was an MOS event, and then use the code to see which families attended, from LinksEvent. I then use the family codes it draws out to get their names from People. I then also use that same family code for the address and email from Families.
Here is what I have done:
SELECT DISTINCT Events.Name, People.Last_name, People.First_name, Families.Address, Families.Town, Families.PostalCode, IIf(InStr(1,,"@")>0,[Email],"") AS EmailAddress
FROM (People INNER JOIN (Events INNER JOIN LinksEvent ON Events.Code = LinksEvent.Event) ON People.Fam_code = LinksEvent.Family) INNER JOIN Families ON People.Fam_code = Families.Fam_code
WHERE (((Families.Address) Is Not Null) AND ((Events.User1)="MOS") AND ((LinksEvent.Attend)=Yes)) OR (((Events.User1)="MOS") AND ((LinksEvent.Attend)=Yes) AND ((Families.Email) Is Not Null))
ORDER BY Events.Name, People.Last_name, People.First_name;
But the data returned is not the right data. Any clue where this goes wrong? Any help would be appreciated.
Also, here is a screenshot of the design view if it helps: http://i138.photobucket.com/albums/q251/pbecker13/roar.jpg
I have 4 tables, Familes, People, LinksEvent, and Events. I cannot change these because it is a database someone else has done that is used for lots of things.
From events, I determine whether it was an MOS event, and then use the code to see which families attended, from LinksEvent. I then use the family codes it draws out to get their names from People. I then also use that same family code for the address and email from Families.
Here is what I have done:
SELECT DISTINCT Events.Name, People.Last_name, People.First_name, Families.Address, Families.Town, Families.PostalCode, IIf(InStr(1,,"@")>0,[Email],"") AS EmailAddress
FROM (People INNER JOIN (Events INNER JOIN LinksEvent ON Events.Code = LinksEvent.Event) ON People.Fam_code = LinksEvent.Family) INNER JOIN Families ON People.Fam_code = Families.Fam_code
WHERE (((Families.Address) Is Not Null) AND ((Events.User1)="MOS") AND ((LinksEvent.Attend)=Yes)) OR (((Events.User1)="MOS") AND ((LinksEvent.Attend)=Yes) AND ((Families.Email) Is Not Null))
ORDER BY Events.Name, People.Last_name, People.First_name;
But the data returned is not the right data. Any clue where this goes wrong? Any help would be appreciated.
Also, here is a screenshot of the design view if it helps: http://i138.photobucket.com/albums/q251/pbecker13/roar.jpg
![,"@")>0,[Email],"") AS EmailAddress FROM (People INNER JOIN (Events INNER JOIN LinksEvent ON Events.Code = LinksEvent.Event) ON People.Fam_code = LinksEvent.Family) INNER JOIN Families ON People.Fam_code = Families.Fam_code WHERE (((Families.Address) Is Not Null) AND ((Events.User1)="MOS") AND ((LinksEvent.Attend)=Yes)) OR (((Events.User1)="MOS") AND ((LinksEvent.Attend)=Yes) AND ((Families.Email) Is Not Null)) ORDER BY Events.Name, People.Last_name, People.First_name; But the data returned is not the right data. Any clue where this goes wrong? Any help would be appreciated. Also, here is a screenshot of the design view if it helps: http://i138.photobucket.com/albums/q251/pbecker13/roar.jpg](mailto:,"@")>0,[Email],"") AS EmailAddress
FROM (People INNER JOIN (Events INNER JOIN LinksEvent ON Events.Code = LinksEvent.Event) ON People.Fam_code = LinksEvent.Family) INNER JOIN Families ON People.Fam_code = Families.Fam_code
WHERE (((Families.Address) Is Not Null) AND ((Events.User1)="MOS") AND ((LinksEvent.Attend)=Yes)) OR (((Events.User1)="MOS") AND ((LinksEvent.Attend)=Yes) AND ((Families.Email) Is Not Null))
ORDER BY Events.Name, People.Last_name, People.First_name;
But the data returned is not the right data. Any clue where this goes wrong? Any help would be appreciated.
Also, here is a screenshot of the design view if it helps: http://i138.photobucket.com/albums/q251/pbecker13/roar.jpg){kind=link}