SQL Join Query

[gmazza76]

Good afternoon

I have around 5 tables I want to create one overall query from, but I am coming up against an issue when creating my first join
I use the code below to pull agent names etc... from a source table which is ok, but I will need to add the 2nd query below

I will need to do around 5 more queries like "Query2" but no matter what I try I cannot work out which join is best as I want the data to be to the right of the "initial Query" and how I would work out the multiple WHERE clauses and table naming. I have had a look and I have tried several ways of doing this but I usually get an error either with the JOIN or syntax issue

Initial Query
select [AgentID], [Name], [SDate], [Lead] AS [Department Lead], [Team Leader]
from Table1
Where CISCOID is not null and [CX_Lead] = 'Nxxxme' and [Title] = 'Standard'

code works on The following to calculate a field on its own

Query2

SELECT [Cdate] as [Call Date], count([AgentID]) as [IB]
FROM Table2
WHERE (((Table2.[Lead])='Name') AND ((Table2.[Flag])='Handled'))
GROUP BY Table2.[CDate], Table2.[agent], Table2.[Manager];

thanks in advance
Gavin

 

[gmazza76]

I have currently tried to do a left join as follows

SELECT [AgentID], [Name], [SDate], [Lead] AS [Department Lead], [Team Leader], count(c.[Agent Peripheral Number])
FROM Table1
LEFT JOIN Table2 c ON AgentID = c.[AGENTID2]
WHERE (c.[Lead])='Name') AND ((c.[Flag])='Handled')

But I get an error JOIN expression not supported

thanks

 

[welshgasman]

(c.[Lead])='Name') AND ((c.[Flag])='Handled')

Are those Fieldnames?

 

[gmazza76]

The ones in [] are and the ' ' criteria for the fields

 

[welshgasman]

So Lead would contain 'Name' ?, not an actual name?

 

[gmazza76]

Sorry, Yes it would

 

[welshgasman]

TBH, I do most of my sql starting from the QBE design window, then modify that, if that does not aupport a particular aspect of what I am trying to do.

Thinking about it, it is just criteria, whether any exists is a different matter.