By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,373 Members | 1,792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,373 IT Pros & Developers. It's quick & easy.

SQL Summary problem

P: n/a
I have an MS Access query that I was hoping to summarize on the Tenure
field but, instead, the rows are created as if the Tenure field were an
Employee ID. So I have duplicate Tenure fields when I thought there
would be just one per different Tenure field with the results summed
within each Tenure. In fact, if I swap out the Tenure field output with
the Employee ID, I get the same results with the exception of the
Employee ID field. I have been trying to really understand the
mechanics of SQL to solve these problems not just to find a quick fix.
This one appears that the "hiredate" field is my problem even though I
am using the DateDiff function to return a result. This is what I think
makes the row think it's an individual record (like a single employee
id) instead of grouping and summing on the Tenure result (such as
">12M"). I just can't figure out how to make it group and sum by
Tenure. Please help! Here is my query:

SELECT
iif(DateDiff("m",e.HireDate,Now()) is null,"Unknown"
,iif(DateDiff("m",e.HireDate,Now()) <= 3,"0-3M"
,iif(DateDiff("m",e.HireDate,Now()) > 3 and
DateDiff("m",e.HireDate,Now()) <= 6,"4-6M"
,iif(DateDiff("m",e.HireDate,Now()) > 6 and
DateDiff("m",e.HireDate,Now()) <= 9,"7-9M"
,iif(DateDiff("m",e.HireDate,Now()) > 9 and
DateDiff("m",e.HireDate,Now()) <= 12,"10-12M"
,iif(DateDiff("m",e.HireDate,Now()) > 12,">12M","")))))) as [Tenure]
, b.division as [Division]
, b.region as [Region]
, b.district as [District]
, b.rbr as [Cost Center]
, b.name as [Branch]
, sum(iif(a.loanfunddate >= #1/1/2005# and a.loanfunddate <=
#1/31/2005#,1,0)) as [Jan #]
, sum(iif(a.loanfunddate >= #1/1/2005# and a.loanfunddate <=
#1/31/2005#,a.loanamount,0)) as [Jan $]
, sum(iif(a.loanfunddate >= #2/1/2005# and a.loanfunddate <=
#2/28/2005#,1,0)) as [Feb #]
, sum(iif(a.loanfunddate >= #2/1/2005# and a.loanfunddate <=
#2/28/2005#,a.loanamount,0)) as [Feb $]
, sum(iif(a.loanfunddate >= #3/1/2005# and a.loanfunddate <=
#3/31/2005#,1,0)) as [Mar #]
, sum(iif(a.loanfunddate >= #3/1/2005# and a.loanfunddate <=
#3/31/2005#,a.loanamount,0)) as [Mar $]
, sum(iif(a.loanfunddate >= #4/1/2005# and a.loanfunddate <=
#4/30/2005#,1,0)) as [Apr #]
, sum(iif(a.loanfunddate >= #4/1/2005# and a.loanfunddate <=
#4/30/2005#,a.loanamount,0)) as [Apr $]
, sum(iif(a.loanfunddate >= #5/1/2005# and a.loanfunddate <=
#5/31/2005#,1,0)) as [May #]
, sum(iif(a.loanfunddate >= #5/1/2005# and a.loanfunddate <=
#5/31/2005#,a.loanamount,0)) as [May $]
, sum(iif(a.loanfunddate >= #6/1/2005# and a.loanfunddate <=
#6/30/2005#,1,0)) as [Jun #]
, sum(iif(a.loanfunddate >= #6/1/2005# and a.loanfunddate <=
#6/30/2005#,a.loanamount,0)) as [Jun $]
, sum(iif(a.loanfunddate >= #1/1/2005# and a.loanfunddate <=
#6/30/2005#,1,0)) as [YTD #]
, sum(iif(a.loanfunddate >= #1/1/2005# and a.loanfunddate <=
#6/30/2005#,a.loanamount,0)) as [YTD $]
FROM (((fundings AS a LEFT JOIN branch AS b ON b.branch = a.branchno)
LEFT JOIN loanofficers AS lo ON lo.loanproducercode =
a.loanproducercode)
LEFT JOIN eeqry AS e ON e.empno = lo.empno)
WHERE
a.loanfunddate >= #1/1/2005#
and a.loanfunddate <= #6/30/2005#
and e.terminationdate is null
group by DateDiff("m",e.HireDate,Now()), b.division, b.region,
b.district, b.rbr, b.name
order by b.district, b.rbr, DateDiff("m",e.HireDate,Now());

A sample output is the following:

Tenure Division Region District Cost Center Branch Jan # Jan $ Feb
# Feb $ Mar # Mar $ Apr # Apr $ May # May $ Jun # Jun $ YTD # YTD $
4-6M C C12 C1212 123 OK 1 $220,000 0 $0 1 $162,000 0 $0 0 $0 0 $0 2 $382,000
7-9M C C12 C1212 123 OK 1 $348,000 0 $0 1 $50,000 0 $0 0 $0 0 $0 2 $398,000
10-12M C C12 C1212 123 OK 0 $0 1 $216,000 0 $0 0 $0 0 $0 0 $0 1 $216,000
12M C C12 C1212 123 OK 0 $0 2 $411,860 4 $598,300 0 $0 0 $0 0 $0 6 $1,120,160
12M C C12 C1212 123 OK 0 $0 4 $593,697 3 $475,385 0 $0 0 $0 0 $0 7 $1,069,082
12M C C12 C1212 123 OK 3 $459,000 17 $3,085,304 5 $662,805 0 $0 0 $0 0 $0 25 $4,207,109
12M C C12 C1212 123 OK 13 $1,392,620 18 $2,450,210 11 $2,272,032 0 $0 0 $0 0 $0 42 $6,114,862


Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I'd say your problem is that you've got the expression DateDiff
("m",e.HireDate,Now()) in your GROUP BY and ORDER BY clause as well as in
your SELECT clause. Try using [Tenure] in your GROUP BY and ORDER BY fields
and see what that does. If it doesn't help, please post the structure of
the tables and some sample rows so we can debug it more effectively.

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.