Thread: Trying to get sum help

You mean:

Code:

  Select  LocName, sum(sumAmt), sum(sumCount) 
    From  aTable
  Where <your first criteria>
  Group By LocName
  Union
  Select LocName, sum(sumAmt), sum(sumCount)
   From aTable
  Where <your second criteria>
  Group by LocName

I don't have access, so it wouldn't do me any good. Post your query however. What do you mean by 'same results'? Same as what?

okies...

I had to redo I guess I messed with it again after I was getting same results but now I get an error:

Code:

SELECT qryTonyFirstTestRun2.LocName,
Sum(qryTonyFirstTestRun2.SumOfDEPR_CYR_AMT) AS SumOfSumOfDEPR_CYR_AMT, 
Sum(qryTonyFirstTestRun2.SumOfMonthCounter) AS SumOfSumOfMonthCounter, 
qryTonyFirstTestRun2.MonthYR, 
qryTonyFirstTestRun2.Districtcode
FROM qryTonyFirstTestRun2
WHERE (((qryTonyFirstTestRun2.FUNC_LOC_CD) Like "*Repair*"))
GROUPBY qryTonyFirstTestRun2.LocName

UNION

SELECT qryTonyFirstTestRun2.LocName,
Sum(qryTonyFirstTestRun2.SumOfDEPR_CYR_AMT) AS SumOfSumOfDEPR_CYR_AMT, 
Sum(qryTonyFirstTestRun2.SumOfMonthCounter) AS SumOfSumOfMonthCounter, 
qryTonyFirstTestRun2.MonthYR, 
qryTonyFirstTestRun2.Districtcode
FROM qryTonyFirstTestRun2
WHERE (((qryTonyFirstTestRun2.Districtcode) Like "*OK*"))

GROUPBY qryTonyFirstTestRun2.LocName

This is error:

Code:

Syntax error (missing operator) in query expression '(((qryTonyFirstTestRun2.FUNC_LOC_CD) like "*Repair*")) GROUPBY qryTonyFirstTestRun2.LocName'.

This is a new query from the query where I pull all the districtcodes I want to see that are in a repair center. This step I want to get the sum of count and dep amt for each district code that are in an internal repair ctr and then same for external repair ctr. To help determine that is, if the distcode ex: HD is located in the FuncName its internal but if it doesn't contain the district in the FuncName then its external.

Group By is two words

Ok, I think I'm getting very confused...

In posting above my where clause is stating in 1st select anything with "repair" but in my 2nd I have a criteria for the district code.

Are both my where clauses suppose to be querying the same field?

It looks like I'm getting 1 results for each district when I need 2. using this query:

Code:

SELECT qryTonyFirstTestRun2.LocName,
Sum(qryTonyFirstTestRun2.SumOfDEPR_CYR_AMT) AS SumOfSumOfDEPR_CYR_AMT, 
Sum(qryTonyFirstTestRun2.SumOfMonthCounter) AS SumOfSumOfMonthCounter, 
qryTonyFirstTestRun2.Districtcode
FROM qryTonyFirstTestRun2
WHERE (((qryTonyFirstTestRun2.FUNC_LOC_CD) Like "*Repair*"))
GROUP BY qryTonyFirstTestRun2.LocName,qryTonyFirstTestRun2.Districtcode

UNION

SELECT qryTonyFirstTestRun2.LocName,
Sum(qryTonyFirstTestRun2.SumOfDEPR_CYR_AMT) AS SumOfSumOfDEPR_CYR_AMT, 
Sum(qryTonyFirstTestRun2.SumOfMonthCounter) AS SumOfSumOfMonthCounter,  
qryTonyFirstTestRun2.Districtcode
FROM qryTonyFirstTestRun2
WHERE (((qryTonyFirstTestRun2.FUNC_LOC_CD) Not Like "*Repair*"))

GROUP BY qryTonyFirstTestRun2.LocName,qryTonyFirstTestRun2.Districtcode


LocName	SumOfSumOfDEPR_CYR_AMT	SumOfSumOfMonthCounter	Districtcode
Houston	-4021881.22	296	HD
Oklahoma	-3530685.38	240	OK

Since my first query that I'm querying off of now contains every districtcode I'm looking for using (IN "AK", "HD", "OK") with funcName *repair* this gives me all items in a repair ctr in all those districts. but now I want to get 2 results for each district (one that gives me the sum of count and dep amt) for items in a internal rep. ctr. and one for items in an external repair ctr.
Is this possible?

Thanks for your patience.

It appears that 1 of the queries is not returning anything. Add the following to the Select statements after DistrictCode:
'Repair' as Rectype in the first query
'Not Repair' in the second query

Also, run each part of the UNION separate to see which is returning what.