I don't understand this error. 2

[SmokinWrek]

I don't work much in Access, but I have written SQL statements before for my VB programs. I am currently trying to write a query in Access, but am coming up with an error I don't quite understand, and of course, help is of no help at all.

Here's my query:

SELECT i.ClientID, SUM(d.Peices) AS Qty, d.Region
FROM JobDetail AS d INNER JOIN [SELECT ClientID, JobID 
FROM JobInfo AS j 
WHERE ((j.JobDate)>=#5/1/2007#) And ((j.JobDate)<=#5/30/2007#)]. AS i ON d.JobID=i.JobID
ORDER BY i.ClientID, d.Region;

The error I get is:

"You tried to execute a query that does not include the specified expression 'ClientID' as part of an aggregate."

Can anyone help me? I don't understand why ClientID would have to be part of an aggregate!?

Kevin

 

[r937]

in your SELECT statement, you have an aggregate expression -- SUM()

therefore, all the non-aggregate expressions in the SELECT must be in the GROUP BY clause

SELECT i.ClientID
     , SUM(d.Peices) AS Qty
     , d.Region
  FROM JobDetail AS d 
INNER 
  JOIN [
       SELECT ClientID, JobID 
         FROM JobInfo AS j 
        WHERE j.JobDate >= #5/1/2007#    
          And j.JobDate)<= #5/30/2007#)
       ]. AS i 
    ON i.JobID = d.JobID
[b]GROUP
    BY[/b] i.ClientID
     , d.Region;

r937.com | rudy.ca

 

[PHV]

Why not simply this ?
SELECT i.ClientID, SUM(d.Peices) AS Qty, d.Region
FROM JobDetail AS d INNER JOIN JobInfo AS i ON d.JobID = i.JobID
WHERE j.JobDate BETWEEN #2007-05-01# And #2007-05-30#
GROUP BY i.ClientID, d.Region
ORDER BY i.ClientID, d.Region;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SmokinWrek]

Thanks, r937. I was not aware of that restriction when using an aggregate expression.

PHV, is the BETWEEN operator inclusive (i.e. - If j.JobDate was 05/01/2007 or 05/30/2007 would it be included?)? Also, if I do the INNER JOIN on the whole JobInfo table, wouldn't that use more memory (depending on the number and size of the additional fields in the JobInfo table)?

Here's my final code (I added an additional field and am using the ClientName field now instead of ClientID).

SELECT c.ClientName, d.InRange, d.Region, SUM(Pieces) AS Qty
FROM Clients AS c, JobDetail AS d INNER JOIN [SELECT ClientID, JobID 
FROM JobInfo AS j 
WHERE ((j.JobDate)>=#5/1/2007#) And ((j.JobDate)<=#5/30/2007#)]. AS i ON d.JobID=i.JobID
WHERE i.ClientID = c.ClientID
GROUP BY d.InRange, d.Region, c.ClientName
ORDER BY c.ClientName, d.Region, d.InRange;

Kevin

 

[PHV]

Again, why not simply this ?

SELECT c.ClientName, d.Region, d.InRange, SUM(Pieces) AS Qty
FROM (JobInfo AS i
INNER JOIN JobDetail AS d ON i.JobID = d.JobID)
INNER JOIN Clients AS c ON i.ClientID = c.ClientID
WHERE i.JobDate BETWEEN #2007-05-01# And #2007-05-30#
GROUP BY c.ClientName, d.Region, d.InRange
ORDER BY c.ClientName, d.Region, d.InRange

BTW, yes, Between is inclusive and I think that a join is faster than a correlative subquery.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SmokinWrek]

PHV,

I believe I originally had it written without the subquery, but when I ran into problems, I experimented writing it different ways. I believe the reason I had it as a subquery was simply because I started with the simplest part of the query and began building upon it (i.e. - I started with just the subquery).

Thanks to both of you for your help!

Kevin