Sub Totals on Data Report! 2

[BiggerBrother]

This is a duplicate of another post in the VB6 forum. I had no luck there, and so it was syggested that I try here. I hope you can help.

I have a database in access that I send an SQL query to. This returns a dataset in groups. I currently display this dataset on a standard datareprot in vb6. I want to add a sub total for each group:

apples 6
pears 12
Fruit 18

carrots 6
veg 6

Totals 24

I currently have the totals set up as a function in the report footer, but now need to add the sub totals function.

Thanks for any help

BB

 

[GoDawgs]

Can you do the report in Access?

 

[dhookom]

If you can't create subtotals in DataReports (I thought you could) and you can't use Access, then you may have to create a union query. This union query would be like

SELECT Category, Product, Count(*) as NumOf
FROM tblProducts
GROUP BY Category, Product
UNION ALL
SELECT Category, "Z " & Category, Count(*)
FROM tblProducts
GROUP BY Category, "Z " & Category;

Caution "air sql"...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[BiggerBrother]

I might be missing it, but so far I can't find the subtotals function. Am I doing something wrong? Thanks for the code though.

BB

 

[dhookom]

I am not a VB programmer beyond dabbling. I did notice there are groups in DataReports and there seem to be some totally functionality. How it all works is beyong me. From my experience, DataReports are so week they should be used for only the simplest lists. When I did more VB programming, I used ActiveReports or CrystalReports.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Morgance]

If you have which product category it belongs to in a table, for instance:

tblProduct
Product ProductType
Apple Fruit
Pear Fruit
Lettuce Veggie

You can recreate a query to Output it that way by doing the following 2 queries. I created this on the fly, so there may be a faster way.

Query 1 named qrySales:

SELECT tblProduct.ProductType, tblSales.Product, Sum(tblSales.Qty) AS QtySold, 1 AS OrderingLevel
FROM tblSales INNER JOIN tblProduct ON tblSales.Product = tblProduct.Product
GROUP BY tblProduct.ProductType, tblSales.Product, 1

union all

SELECT tblProduct.ProductType, "" as Product, Sum(tblSales.Qty) AS QtySold, 2 AS OrderingLevel
FROM tblSales INNER JOIN tblProduct ON tblSales.Product = tblProduct.Product
GROUP BY tblProduct.ProductType,"", 2


Query 2 named qrySalesOutput

SELECT Switch([orderingLevel]=2,[ProductType],True,[Product]) AS Category, qrySales.QtySold
FROM qrySales
ORDER BY qrySales.ProductType, qrySales.OrderingLevel;

Basically the first query gets the totals for the product sales and the totals for the Product Type. The ordering level is just a number for me to tell which is the apple results and which is the fruit results, also to sort by in the second query. The second query creates a category field and puts apple in it if it's orderinglevel 2, fruit if it's not a 2. Outputs in fuit order, then ordering level (Not displayed in output).

The final output is
Category QtySold
Orange 20
apple 1
Fruit 21
lettuce 3
Veggie 3

You might also try adding conditional formating on the ordering level in this query to highlight the Fruit Veggie Subtotals.

Hope that helps...
Liz

 

[BiggerBrother]

Thnak you for all your help. Problem solved!

BB