We help IT Professionals succeed at work.

SQL-code for Subtotals in VB DataReport

ako74 asked
Hi, I have a single table in Access from which I want to do subtotals using sql in DataReport (VB6). How do I write the sql-code?
Watch Question

You would normally do the sub-totals in the report itself, not in the sql for the report.  Sub-totals in SQL can be very messy.

The VB6 Report generator makes it very easy to add sub-totals for each major category in your report.



As I understand it, obtaining sub-totals in the report is easy when you have a parent-child structure in the database. But in my case I happen to have a single table, i.e. only one table with no relationship. In such a case I have found no method in VB6's DataReport for sub-totals. If you know of any I would be glad to learn the details.
what field do you want to sub-total on?  What is the table structure.

I don't know anything about DataReport, but you can always simulate a parent-child relationship from a single table. If you have:

    Table1( ID, Category, Value, etc)

Create this query:

    SELECT DISTINCT Category FROM Table1;

The relation between the query and the table is one-to-many linked on the field Category.

Does that help?


Thanks, it helped. - As a mattter of fact it solved my problem.
The parent-child relationship which should have been done in the database is in this way done or simulated in the DataReport.