Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unhappy Unanswered: subtotal on access versus sql

    Hi guys I have a client that I was trying to creat a subtotal with on sql with her database. I just imported the data from the tables over and I thought I was going to create a subtotal for all the categories but I was having difficulty. Now at work when I used one of the database I created on sql server I was able to create the subtotals just fine. Now granted her databases is not a relational database. Its kinda just put together (not normalized) is that a huge factor? am asking a silly question? I'm trying to understand because in Access she was able to create her subtotals, I get the data in sql and no go. Can anyone explain, so that my little mind can understand. Please

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Subtotaling is a presentation issue. You should let your report software handle that (Crystal Reports, SQL Server Reporting Services, Excel, Access Data Project...).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by blindman
    Subtotaling is a presentation issue. You should let your report software handle that (Crystal Reports, SQL Server Reporting Services, Excel, Access Data Project...).
    so the design of the database has nothing to do with it? Cause I was creating it on Reporting Services. Also when I was able to create the subtotals easy with my database here at work
    Last edited by desireemm; 06-18-09 at 12:30.

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I assume you were having an issue with the Group By logic with one vs. the other.

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    yeah I think thats it, see she has about 4 or 5 group bars and shes got this report to give her subtotals per category and then the grand total on the footer. Now I try to do that with RS and it doesnt work out that way. I get a bunch of duplicates for starters

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    here is what the design view looks like of the report
    Attached Thumbnails Attached Thumbnails GroupinginAccess.jpg  

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    oops sorry that needed to be rotated
    Attached Thumbnails Attached Thumbnails GroupinginAccess.jpg  

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    quick and dirty:

    select contractdate, ServicesCovered, sum(hours) from tablea
    where workdate = '2009-06-19'
    group by contractdate, ServicesCovered
    compute sum(sum(hours)) -- will be total for all groupings

  9. #9
    Join Date
    Aug 2009
    Posts
    16
    I have actually found that using the GROUPING function in combination with a WITH ROLLUP operator to work really great in creating sub totals and grand totals in a query result. I have a detailed post here "Using GROUPING and the WITH ROLLUP operator to create sub totals" where you can see how it works. The GROUPING function in a CASE statement should give you what you want.
    Last edited by spartiatis; 08-28-09 at 01:08.

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Oh ok thank you, quick question this is the query I use in a matrix on RS and it works as far as the sorting order for the columns but they totals are not come out correclty

    Code:
    SELECT     People_tbls.[Parent ID], People_tbls.[Parent Last Name], People_tbls.[Referral Status], TanfActivity_tbl.[Contact Date], TanfActivity_tbl.[Earned hours], 
                          TanfActivity_tbl.[Catagory for hours], TanfActivity_tbl.[Services Covered], People_tbls.[Parent First Name]
    FROM         People_tbls INNER JOIN
                          TanfActivity_tbl ON People_tbls.[Parent ID] = TanfActivity_tbl.[Parent ID]
    WHERE     (TanfActivity_tbl.[Catagory for hours] IN (N'ITP', N'Parenting', N'Education', N'Cultural', N'Job Skills Training', N'Travel Time')) AND 
                          (TanfActivity_tbl.[Contact Date] BETWEEN @Beginning_ContactDate AND @End_ContactDate) AND (TanfActivity_tbl.[Catagory for hours] = N'ITP' OR
                          TanfActivity_tbl.[Catagory for hours] = N'Parenting' OR
                          TanfActivity_tbl.[Catagory for hours] = N'Job Skills Training' OR
                          TanfActivity_tbl.[Catagory for hours] = N'Cultural' OR
                          TanfActivity_tbl.[Catagory for hours] = N'Education' OR
                          TanfActivity_tbl.[Catagory for hours] = N'Travel Time')
    ORDER BY CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'ITP' THEN 0 ELSE 1 END, 
                          CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Parenting' THEN 0 ELSE 1 END, 
                          CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Job Skills Training' THEN 0 ELSE 1 END, 
                          CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Education' THEN 0 ELSE 1 END, 
                          CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Cultural' THEN 0 ELSE 1 END, 
                          CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Travel Time' THEN 0 ELSE 1 END, TanfActivity_tbl.[Catagory for hours], 
                          TanfActivity_tbl.[Services Covered], People_tbls.[Parent Last Name]

  11. #11
    Join Date
    Aug 2009
    Posts
    16
    Can you show us a screenshot and explain what is wrong with your totals?

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    ok when I ran a query here are the totals I got

    Code:
    Education				
    	40004	Acedemic Assessment Testing	4.	
    			Sum Of Earned hours:	4.
    ITP				
    	40007	Goals and Objectives	2.	
    	40007	Balancing Work and Family Workshop	3.	
    	40014	Balancing Work and Family Workshop	4.	
    	40015	Balancing Work and Family Workshop	4.	
    			Sum Of Earned hours:	13.
    Job Skills Training				
    	40007	Job Skills Training	1.	
    	40010	Food Service Workshop	4.	
    	40011	Job Skills Training	1.	
    	40011	Food Service Workshop	4.	
    	40014	Reworked Resume	2.	
    			Sum Of Earned hours:	12.
    Parenting				
    	39995	Structured Parenting Activity	2.	
    	39996	Structured Parenting Activity	2.	
    	39997	Structured Parenting Activity	2.	
    	39998	Structured Parenting Activity	2.
    	39999	Structured Parenting Activity	2.
    	40000	Structured Parenting Activity	2.
    	40000	Parenting Class with Cristina	4.
    	40001	Structured Parenting Activity	2.
    	40002	Structured Parenting Activity	2.
    	40003	Structured Parenting Activity	2.
    	40004	Structured Parenting Activity	2.
    	40005	Structured Parenting Activity	2.
    	40006	Structured Parenting Activity	2.
    	40007	Structured Parenting Activity	2.
    	40008	Structured Parenting Activity	2.
    	40009	Structured Parenting Activity	2.
    	40010	Structured Parenting Activity	2.
    	40011	Structured Parenting Activity	2.
    	40012	Structured Parenting Activity	2.
    	40013	Structured Parenting Activity	2.	
    	40014	Structured Parenting Activity	2.	
    	40015	Structured Parenting Activity	2.	
    	40016	Structured Parenting Activity	2.	
    	40017	Structured Parenting Activity	2.	
    	40018	Structured Parenting Activity	2.	
    	40019	Structured Parenting Activity	2.	
    	40020	Structured Parenting Activity	2.	
    	40021	Structured Parenting Activity	2.	
    	40022	Structured Parenting Activity	2.	
    	40023	Structured Parenting Activity	2.	
    	40024	Structured Parenting Activity	2.	
    	40025	Structured Parenting Activity	2.	
    			Sum Of Earned hours:	66.
    Travel Time				
    	40000	Travel Time	1.	
    	40004	Travel Time	1.	
    	40007	Travel Time	1.	
    	40010	Travel Time	1.	
    	40011	Travel Time	1.	
    	40014	Travel Time	1.	
    	40015	Travel Time	1.	
    			Sum Of Earned hours:	7.
    			Sum Of Earned hours1:	102.
    				
    Cultural				
    	40007	Sewed on Ribbon Shirts	4.	
    	40008	Sewed on Ribbon Shirts	4.	
    	40009	Sewed on Ribbon Shirts	4.	
    	40010	Sewed on Ribbon Shirts	4.	
    	40011	Sewed on Ribbon Shirts	4.	
    	40012	Sewed on Ribbon Shirts	4.	
    	40013	Sewed on Ribbon Shirts	4.	
    			Sum Of Earned hours:	28.
    Job Skills Training				
    	40000	Job Skills Training	4.	
    	40007	Job Skills Training	4.	
    	40008	Job Skills Training	4.	
    	40009	Job Skills Training	4.	
    	40010	Job Skills Training	4.	
    	40011	Job Skills Training	4.	
    	40012	Job Skills Training	4.	
    	40013	Job Skills Training	4.	
    	40014	Job Skills Training	4.	
    			Sum Of Earned hours:	36.
    Parenting				
    	40001	Balancing Work and Family Workshop	4.	
    			Sum Of Earned hours:	4.
    Travel Time				
    	40000	Travel Time	1.	
    	40001	Travel Time	1.	
    	40014	Travel Time	1.	
    			Sum Of Earned hours:	3.
    			Sum Of Earned hours1:	71.
    			Earned hours Grand Total Sum:	2,304.5


    now i have a query in RS that doesnt get me the same results (its in a matrix)

    [code]


    Code:
    ITP	Parenting		Job Skills Training	Travel Time	Cultural	Education	Total
    20.5	3		3.5	14			41
    8	44		1	3			56
    12	68		5	18		4	107
    11	66		10	10		4	101
    15	77		6.5	15	6	3	122.5
    8	74		19	13	9		123
    5	104		20	16			145
    9	82		24.5	16		4	135.5
    8	8		73	21			110
    8	78		22	14	3		125
    4	62		13	4			83
    15	9		7.5	10			41.5
    12	21		52	7			92
    4.5	82		2.5	6			95
    12	80		21	13			126
    13	66		12	9		4	104
    2.5	34		4	18			58.5
    4	78			1			83
    4	12			3	6		25
    4	12			4	6		26
    4	78			1			83
    4			2	6			12
    	4		36	3	28		71
    	74		2.5	4			80.5
    	78		6.5	8			92.5
    	74		5	4			83
    	62		8	4		4	78
    	31						31
    				2	6		8
    				2	6		8
    				1			1
    187.5	1461		356.5	250	70	23	2348

  13. #13
    Join Date
    Aug 2009
    Posts
    16
    Sorry I can't help with the Matrix. You first query looks fine and it looks like the subtotals are correct, but I do not know anything about how you are populating the matrix.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •