Query SQL syntax error

[Lanser]

I want to adapt a query written by another to include a CC to email address, but in the Query designer there are two tables shown that are not in the table listing Contacts_Opened By and Contacts_Assigned To I looked at the sql and tried to copy paste to add an extra one Contacts_CC To but keep getting a syntax error that I just can't spot can anyone point me in the right direction?

Original Query

SELECT Issues.ID, Issues.Title, [Contacts_Assigned to].[First Name] & " " & [Contacts_Assigned To].[Last Name] AS [Assigned To Name], [Contacts_Opened By].[First Name] & " " & [Contacts_Opened By].[Last Name] AS [Opened By Name], Issues.[Opened Date], Issues.Comment, Issues.[Due Date], [Contacts_Opened By].[E-mail Address] AS [Opened By E-Mail], [Contacts_Assigned To].[E-mail Address] AS [Assigned To E-Mail], Issues.Status, Issues.Category, Issues.Priority, Issues.[Assigned To], Issues.[Opened By], Issues.[Action Taken], Issues.Department, Issues.Shift, Issues.PrintedId, Issues.RevisedDueDate, Issues.Review, Issues.[CC To], Issues.IssuePic
FROM Contacts AS [Contacts_Opened By] INNER JOIN (Contacts AS [Contacts_Assigned To] INNER JOIN Issues ON [Contacts_Assigned To].ID = Issues.[Assigned To]) ON [Contacts_Opened By].ID = Issues.[Opened By]
WHERE (((Issues.Status)<"Closed"))
ORDER BY Issues.ID DESC;

 

[namliam]

SELECT Issues.ID, Issues.Title, [Contacts_Assigned to].[First Name] & " " & [Contacts_Assigned To].[Last Name] AS [Assigned To Name], [Contacts_Opened By].[First Name] & " " & [Contacts_Opened By].[Last Name] AS [Opened By Name], Issues.[Opened Date], Issues.Comment, Issues.[Due Date], [Contacts_Opened By].[E-mail Address] AS [Opened By E-Mail], [Contacts_Assigned To].[E-mail Address] AS [Assigned To E-Mail], Issues.Status, Issues.Category, Issues.Priority, Issues.[Assigned To], Issues.[Opened By], Issues.[Action Taken], Issues.Department, Issues.Shift, Issues.PrintedId, Issues.RevisedDueDate, Issues.Review, Issues.[CC To], Issues.IssuePic
FROM        Contacts AS [[B][U]Contacts_Opened By[/U][/B]] 
INNER JOIN (Contacts AS [[B][U]Contacts_Assigned To[/U][/B]] 
INNER JOIN Issues                             ON [Contacts_Assigned To].ID = Issues.[Assigned To]) 
                                              ON [Contacts_Opened By].ID   = Issues.[Opened By]
WHERE (((Issues.Status)<"Closed"))
ORDER BY Issues.ID DESC;

Your 'missing' tables are aliasses for the existing table Contacts.

Cant really see any obvious errors with the query itself... one thing to note though is that <"Closed" may cause some unexpected results

 

[Lanser]

Sorry I just placed the orginal unaltered sql in the original post below is my attempt

SELECT Issues.ID, Issues.Title, [Contacts_Assigned to].[First Name] & " " & [Contacts_Assigned To].[Last Name] AS [Assigned To Name], [Contacts_Opened By].[First Name] & " " & [Contacts_Opened By].[Last Name] AS [Opened By Name], Issues.[Opened Date], Issues.Comment, Issues.[Due Date], [Contacts_Opened By].[E-mail Address] AS [Opened By E-Mail], [Contacts_Assigned To].[E-mail Address] AS [Assigned To E-Mail], Issues.Status, Issues.Category, Issues.Priority, Issues.[Assigned To], Issues.[Opened By], Issues.[Action Taken], Issues.Department, Issues.Shift, Issues.PrintedId, Issues.RevisedDueDate, Issues.Review, Issues.[CC To], Issues.IssuePic

FROM Contacts AS [Contacts_Opened By] 
INNER JOIN (Contacts AS [Contacts_Assigned To] 
INNER JOIN Issues ON [Contacts_Assigned To].ID = Issues.[Assigned To] [B][COLOR=red](Contacts AS [Contacts_CC To] [/COLOR][/B]
[COLOR=red][B]INNER JOIN Issues ON [Contacts_CC To].ID = Issues.[CC To])[/B][/COLOR][COLOR=black])[/COLOR] ON [Contacts_Opened By].ID = Issues.[Opened By]

WHERE (((Issues.Status)<"Closed"))
ORDER BY Issues.ID DESC;

 

[namliam]

Your original query can be re-writen slightly, this can cause problems if your using outer joins, but since you arent here.... it is not a problem.

SELECT Issues.ID, Issues.Title, [Contacts_Assigned to].[First Name] & " " & [Contacts_Assigned To].[Last Name] AS [Assigned To Name], [Contacts_Opened By].[First Name] & " " & [Contacts_Opened By].[Last Name] AS [Opened By Name], Issues.[Opened Date], Issues.Comment, Issues.[Due Date], [Contacts_Opened By].[E-mail Address] AS [Opened By E-Mail], [Contacts_Assigned To].[E-mail Address] AS [Assigned To E-Mail], Issues.Status, Issues.Category, Issues.Priority, Issues.[Assigned To], Issues.[Opened By], Issues.[Action Taken], Issues.Department, Issues.Shift, Issues.PrintedId, Issues.RevisedDueDate, Issues.Review, Issues.[CC To], Issues.IssuePic
FROM       Contacts AS [Contacts_Opened By] 
INNER JOIN Contacts AS [Contacts_Assigned To] ON [Contacts_Opened By].ID   = Issues.[Opened By]
INNER JOIN Issues                             ON [Contacts_Assigned To].ID = Issues.[Assigned To]
WHERE (((Issues.Status)<"Closed"))
ORDER BY Issues.ID DESC;

See if that helps you

 

[Lanser]

Thanks for the reply but that just gives me the following error

Syntax error (missing operator) in query expression ‘[Contacts_Opened By].ID =Issues.[Opened By] INNER JOIN Issues ON[Contacts_Assigned to].ID=Issues.[Assigned To]’.

I have 2 tables Issues and Contacts, Issues has 2 fields Assigned To and CC To both populated from Contacts, I currently use this query to send an email to the Assigned To email address but now I want to include the CC To email in the query so I can send a cc copy to another person

 

[namliam]

this then?

SELECT Issues.ID, Issues.Title, [Contacts_Assigned to].[First Name] & " " & [Contacts_Assigned To].[Last Name] AS [Assigned To Name], [Contacts_Opened By].[First Name] & " " & [Contacts_Opened By].[Last Name] AS [Opened By Name], Issues.[Opened Date], Issues.Comment, Issues.[Due Date], [Contacts_Opened By].[E-mail Address] AS [Opened By E-Mail], [Contacts_Assigned To].[E-mail Address] AS [Assigned To E-Mail], Issues.Status, Issues.Category, Issues.Priority, Issues.[Assigned To], Issues.[Opened By], Issues.[Action Taken], Issues.Department, Issues.Shift, Issues.PrintedId, Issues.RevisedDueDate, Issues.Review, Issues.[CC To], Issues.IssuePic
FROM       Issues
INNER JOIN Contacts AS [Contacts_Assigned To] ON Issues.[Opened By]   = [Contacts_Opened By].ID   
INNER JOIN Contacts AS [Contacts_Opened By]   ON Issues.[Assigned To] = [Contacts_Assigned To].ID
WHERE (((Issues.Status)<"Closed"))
ORDER BY Issues.ID DESC;

Note I am trying to help you but trying to teach you how to do it...

 

[Lanser]

thank you for your time and effort, that still gives a syntax error and I can't see what changes you've made that would include the CC To field.
I have kinda solved the issue by creating a second duplicate contacts table to hold the CC .

 

[namliam]

Yeah, there isnt any change particular to adding the CC field, and your solution that your discribing is the correct solution. You have to add another contacts table.

2 things I want to add...
1) have you accounted for having NO CCs?? I.e. a null value in CC?
2) what if there are multiple people on CC ?? Or BCC?

 

[Lanser]

hehe funny you should mention that, I use Waynes FnSendMailSafe to send the email and that does fall over when no CC to is specified. Getting around it atm by populating the CC to field with a default dummy until I can figure out how to do it properly.

there will only ever be 1 CC so that shouldnt be a problem