[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

Need help troubleshooting Access Query SQL

I managed to get this working with just the one join but can't seem to add the second one in.

Here is the code;
SELECT A.UserCount, C.NotifyCount
FROM 
(
SELECT 1 AS Num, Count(B.tblCurrentUsers.UserID) AS UserCount 
   FROM (
   SELECT tblCurrentUsers.UserID, tblCurrentUsers.AppType, Max(tblCurrentUsers.DateTime) AS MaxOfDateTime, Last(tblCurrentUsers.LogType) AS LastOfLogType FROM tblCurrentUsers GROUP BY 1,          
   tblCurrentUsers.UserID, tblCurrentUsers.AppType HAVING (((Last(tblCurrentUsers.LogType))="LogOn"))
   )  AS B
)  AS A 

LEFT JOIN 

(
SELECT 1 AS Num, Count(D.tblNotifications.Read) AS NotifyCount 
   FROM 
   (
   SELECT tblNotifications.Read 
   FROM tblNotifications WHERE (((tblNotifications.Read)=False))
   )  AS D
)  AS C 

ON A.Num = C.Num

LEFT JOIN

(
SELECT 1 AS Num, Count(E.tblEnhance.ID) AS EnhanceCount
FROM
   (
   SELECT tblEnhance.ID
   FROM tblEnhance
   WHERE (((tblEnhance.Completed)=False))
   ) AS E
) AS F

ON A.Num = F.Num

Open in new window


Here is the error;
error
Thanks in advance for the help!
0
SeyerIT
Asked:
SeyerIT
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I'll bite.  

I don't see an ON clause anywhere that relates B to A, D to C, and F to E.
0
 
HainKurtSr. System AnalystCommented:
if you remove line 13-24 (and first line make it "select *"), does it run?
0
 
SeyerITAuthor Commented:
Fixed it! It had to do with the parenthesis.. Needed to add parenthesis where I have them highlighted below:
SELECT A.UserCount, C.NotifyCount, F.EnhanceCount
FROM
((
SELECT 1 AS Num, Count(B.tblCurrentUsers.UserID) AS UserCount
   FROM (
   SELECT tblCurrentUsers.UserID, tblCurrentUsers.AppType, Max(tblCurrentUsers.DateTime) AS MaxOfDateTime, Last(tblCurrentUsers.LogType) AS LastOfLogType FROM tblCurrentUsers GROUP BY 1,          
   tblCurrentUsers.UserID, tblCurrentUsers.AppType HAVING (((Last(tblCurrentUsers.LogType))="LogOn"))
   )  AS B
)  AS A

LEFT JOIN

(
SELECT 1 AS Num, Count(D.tblNotifications.Read) AS NotifyCount
   FROM
   (
   SELECT tblNotifications.Read
   FROM tblNotifications WHERE (((tblNotifications.Read)=False))
   )  AS D
)  AS C

ON A.Num = C.Num)

LEFT JOIN

(
SELECT 1 AS Num, Count(E.tblEnhance.ID) AS EnhanceCount
FROM
   (
   SELECT tblEnhance.ID
   FROM tblEnhance
   WHERE (((tblEnhance.Completed)=False))
   ) AS E
) AS F

ON A.Num = F.Num


I don't fully understand the concept behind the parenthesis in access queries but that did the trick.. Basically the first Join in its entirety needed to be enclsed in parenthesis.
0
 
SeyerITAuthor Commented:
Figured it out.

Thanks for your input though fellows!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now