Where, Where, Group By, Sum (Newbie)

[Olddogg]

I am building a revenue scan for my company.

The query that I am trying to build adds up the revenue for that month.

The first 'where' expression filters out the dates that I want for the additions. (works fine)

The second 'where' I am trying to filter out one of my agents. However, it does not seem to be working. (=Not (agent's name)) It is not filtering out that agent's sales in the sum.

The 'group by' seperates the different sale types. (works fine)

the sum is the addition of the amounts for the seperate sale types.

Is it possible to use two different 'where' expressions?
If so, can somebody give me some pointers on how to comeplete this query.

I appreciate any help that can be given.
Thanks again.

Jon

 

[PHV]

how to comeplete this query
Which ?
Please post the SQL code you have so far.
=Not (agent's name)
<>"agent's name"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Olddogg]

1st 'where': >=#12/1/2004# And <=#12/31/2004#
2nd 'where': Not "fair/wat"
'groupby': "grp/ind" and 13 other sale types down the column
Sum: the amounts
------------------------------------
output:
Sale Type SumOfAmount
cdc-di 265.13
cdc-erp 1200.16
cdc-group 24877.3
cdc-life 1005.11
cdc-si 3270.4
expense recovery 4989.14
fysc-life 3880.05
prizm-erp 37182.32


This is correct other than the fact that the agents fair/wat are still included in the totals.

Thanks PHV

 

[PHV]

2nd 'where': <> "fair/wat"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Olddogg]

Doesn't seem to be working.
I must be doing something wrong.

Any other suggestions I would really appreciate it... Thanks again PHV

 

[lespaul]

can you post the actual SQL statement instead of the Query Design prompts? (switch the view to SQL view)


Leslie

 

[Olddogg]

SELECT [2004 Year End (Sept 30)].[Sale Type], Sum([2004 Year End (Sept 30)].Amount) AS SumOfAmount
FROM [2004 Year End (Sept 30)]
WHERE ((([2004 Year End (Sept 30)].Date)>=#12/1/2004# And ([2004 Year End (Sept 30)].Date)<=#12/31/2004#)) OR ((([2004 Year End (Sept 30)].Date)>=#12/1/2004# And ([2004 Year End (Sept 30)].Date)<=#12/31/2004#) AND (([2004 Year End (Sept 30)].Agent)<>"fair/wat"))
GROUP BY [2004 Year End (Sept 30)].[Sale Type]
HAVING ((([2004 Year End (Sept 30)].[Sale Type])="expense recovery" Or ([2004 Year End (Sept 30)].[Sale Type])="benefit allowance" Or ([2004 Year End (Sept 30)].[Sale Type])="grp/ind" Or ([2004 Year End (Sept 30)].[Sale Type])="cdc-group" Or ([2004 Year End (Sept 30)].[Sale Type])="prizm-erp" Or ([2004 Year End (Sept 30)].[Sale Type])="cdc-erp" Or ([2004 Year End (Sept 30)].[Sale Type])="cdc-life" Or ([2004 Year End (Sept 30)].[Sale Type])="fysc-life" Or ([2004 Year End (Sept 30)].[Sale Type])="fysc-di" Or ([2004 Year End (Sept 30)].[Sale Type])="cdc-di" Or ([2004 Year End (Sept 30)].[Sale Type])="fysc-ci" Or ([2004 Year End (Sept 30)].[Sale Type])="cdc-ci" Or ([2004 Year End (Sept 30)].[Sale Type])="i-h" Or ([2004 Year End (Sept 30)].[Sale Type])="cdc-si"));




There is the SQL of the query...
Thanks again for the help guys.

 

[lespaul]

Ok, so you have a single table:

[2004 Year End (Sept 30)]

with the fields:
Sale Type Amount Date Agent

you want to sum the amounts where the date is between 12/1 and 12/31 where teh agent is NOT "fair/wat", but you want to only include certain sale types?
does this do what you are expecting?:

SELECT [Sale Type], Sum(Amount) AS SumOfAmount

FROM [2004 Year End (Sept 30)]

WHERE A.Date >= #12/1/2004# And A.Date <= #12/31/2004# AND A.Agent<>"fair/wat" AND [Sale Type] IN ("expense recovery", "benefit allowance", "grp/ind", "cdc-group", "prizm-erp", "cdc-erp", "fysc-life", "fysc-di", "cdc-di", "fysc-ci", "cdc-ci", "i-h", "cdc-si")

GROUP BY [Sale Type]

Leslie

 

[PHV]

1st 'where': Between #12/1/2004# And #12/31/2004#
2nd 'where': <> "fair/wat"
3rd 'where': In ('expense recovery', ..., 'cdc-si')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

I left some aliases in the above query, try this one instead:

SELECT [Sale Type], Sum(Amount) AS SumOfAmount

FROM [2004 Year End (Sept 30)]

WHERE Date >= #12/1/2004# And Date <= #12/31/2004# AND Agent<>"fair/wat" AND [Sale Type] IN ("expense recovery", "benefit allowance", "grp/ind", "cdc-group", "prizm-erp", "cdc-erp", "fysc-life", "fysc-di", "cdc-di", "fysc-ci", "cdc-ci", "i-h", "cdc-si")

GROUP BY [Sale Type]

Leslie