pls help!!!"grouping on more then one field" !!! not working out!!!!!!

[elizachacko]

i have ben trying really hard to group on two fields in data reports. but not working out!!!. will someone be kind enough to help???
this is my prob.
i have a table called bookregister with the foll fields
category, author, bookname, stock

what i want is this : in my data report

category
author
bookname

sample:

computer(category)

aaaa(author)
ffffff(bookname)
fgfghgf "
fgfgfggh "

walter(author)
dfgdfgfg(bookname)
dfdgfghg "
economics(category)

siam (author)
gghgh (bookname)
fgfgh "
i hope my prob is understood: ie grouping first on the basis of ategory and then on author
it is so easy in access but it's driving me nuts in vb!!!!

pls help!!!!!!!
elizabeth_c123@rediffmail.com

 

[vb5prgrmr]

Is this what you mean????
[tt]
SELECT Category, Author, BookName FROM WhereEver Group By Category, Author, BookName
[/tt]
or
[tt]
SELECT Category, Author, BookName FROM WhereEver Group By Category, Author
[/tt]

Good Luck

 

[SemperFiDownUnda]

Grouping is usesless unless you have some type of agrigated function otherwise it is just sorting.
Now if you are talking about a HFlexGrid then that is agian something different.

How are you diplaying this data?

By your question I'd say just do a

SELECT Category, Author, BookName
FROM Blah
ORDER BY Category, Author, BookName


GROUP BY is for when you have something like

SELECT Category, Author, BookName, COUNT(*)
FROM Blah
GROUP BY Category, Author, BookName

and you'll get a count at the BookName level.

 

[PraveenMenon]

Wot do u want in the resultset? why are u grouping this??? All the Best
Praveen Menon
pcmin@rediffmail.com

 

[elizachacko]

thanks for the suggestions! but see, if i do it using the group by or order clause, the headings get repeated. i do not want the heading to be repeated.
see, if we do this in access using the report wizard,we add two grouping levels, first,by category ,then by author. doing in this manner , we get a report, i want this same type of report in vb using data environment and data report.
i hope i am clear. pls help if u have understood.


can somebody also tell me how to use an access report in vb?
elizabeth_c123@rediffmail.com

 

[SemperFiDownUnda]

Don't they do this via nested queries in most of the low end report generators?
So you would make a query to get all categories
then make a query to get Category and author then make that a sub query (forgot exact term CR uses) of the first
and so on.

 

[topher0303]

To add a new group in the report, right click the report and click on "Insert Group Header/Footer". Is this what you are looking for?

Chris.

 

[topher0303]

Actually, the way you do this is set the commands in the data environment in a hierarchy format and link them together. After doing that, right click the report designer and select Retreive Structure. It should then give you your group header/footers sections you need for your report.

Here is a link on setting up the commands in a a hierarchy:

http://msdn.microsoft.com/library/d...ndingcontrolplacementondatareportdesigner.asp

Let me know if this works for you or not. I have been able to get it to work in my projects.

Chris.

 

[suoirotciv]

If you want to use an access report using vb its possible . . . just follow the instruction below

=================================================

Print MS Access Report

Preparations
Add 1 Command Button to your form.
Add 1 reference to Microsoft Access X.0 Object Library (From
VB Menu choose Project -> References..., mark the Microsoft Access X.0 Object Library check box and press OK).

Form Code

Private Sub Command1_Click()
Dim ac As Access.Application
Set ac = New Access.Application
' open the database.
' replace the "c:\myDir\myDBFileName.mdb" below with your
' database file name
ac.OpenCurrentDatabase ("c:\myDir\myDBFileName.mdb&quot
' uncomment the line below if you want to see Print Preview
' ac.Visible = True
' replace the acViewNormal below with acViewPreview
' if you want to see Print Preview
ac.DoCmd.OpenReport "Catalog", acViewNormal
' delete the line below if you want to see Print Preview
ac.CloseCurrentDatabase
End Sub
Please pardon the grammar.
Not good in english.

 

[elizachacko]

Thanks for the suggestions.
As for doing it by queries and subqueries, SemperFiDownUnda, I really didn’t get u.
Could u send the query for my prob, if u don’t mind?

 

[elizachacko]

Chris, I read through the msdn topic on “understanding control placement on data report”
But I couldn’t figure out its use in my case.
Could u help me out. I am using only a single table called “BOOKS’ with the following fields
Category, author, bookname.
As I said earlier, I used the grouping tab on the data environment, and grouped on the basis of both category and author. It groups correctly.
But my problem lies in presenting it on the data report.
According to the grouping that I have done, my data report looks as below:

Computer

siam
basics of computing
foxpro6.0

computer

walter

visual basic

economics)

gupta
economics today
advanced economics
economics

Sharma

Importance of economics



This report is bsed on the following data

Category author bookname

Computer walter visual basic
Computer siam basics of computing
Computer siam foxpro6.0
Economics gupta economics today
Economics gupta advanced economics
Economics Sharma Importance of economics



Now comes the real problem:
If u look into the above data report, we find that the headings: computer and economics are being repeated for different authors. I am trying to remove this repetition in my data report .

Pls help me out if u have understood my problem . I would be really grateful.