Multiply Queries (1 Viewer)

[seany]

I am try to do all in one queries which has two queries imbedded into.

"SELECT Filter_ID, Channel, SpeedBands, Filter_Channel, Filter_type
FROM 
(SELECT [Tbl_Lookup_Channels].[ID] AS Channel, [Tbl_Lookup_SpeedBand].[ID] AS SpeedBand
FROM Tbl_Lookup_Channels, Tbl_Lookup_SpeedBand)
LEFT JOIN 
(SELECT [Site_ID] AS Filter_ID,  [Channel] AS Filter_Channel, [Type] AS Filter_SpeedBand
FROM Tbl_SiteResults
WHERE Site_ID=" & ID &")
ON ((SpeedBand = Filter_Speedband) AND (Channel = Filter_Channel))
GROUP BY Filter_ID, Channel, SpeedBands, Filter_Channel, Filter_type
HAVING Filter_Channel Is Null AND Filter_type Is Null
ORDER BY Channel, SpeedBands;"

If is not work at all it is just asking that it is invalid join to the main query

Thanks

Sean

 

[neileg]

Filter_Channel is one of your join fields, but your HAVING clause wants this field to be null. You can't join on null values.

 

[seany]

Fix it, Square Brakets

SELECT TempQuery1.Filter_ID, TempQuery2.Channel, TempQuery2.SpeedBands, TempQuery1.Filter_Channel, TempQuery1.Filter_type 
FROM 
[SELECT Tbl_Lookup_Channels.ID AS Channel, Tbl_Lookup_SpeedBand.ID AS SpeedBands 
FROM Tbl_Lookup_Channels, Tbl_Lookup_SpeedBand]. AS TempQuery2 
LEFT JOIN 
[SELECT Site_ID AS Filter_ID, Channel AS Filter_Channel, Type AS Filter_type From Tbl_SiteResults 
WHERE Site_ID=" & ID & "]. AS TempQuery1 
ON (TempQuery2.SpeedBands = TempQuery1.Filter_type) AND (TempQuery2.Channel = TempQuery1.Filter_Channel) 
GROUP BY TempQuery1.Filter_ID, TempQuery2.Channel, TempQuery2.SpeedBands, TempQuery1.Filter_Channel, TempQuery1.Filter_type 
HAVING (((TempQuery1.Filter_Channel) Is Null) And ((TempQuery1.Filter_type) Is Null)) 
ORDER BY TempQuery2.Channel, TempQuery2.SpeedBands;