Report cannot handle option group by in query based on two tables

[rudolfelizabeth]

Ms access 2003

I built a query based on two tables
separately the query works fine

Then I built a report based on the same query
the report has to use info from these two tables.
when I ran the report it showed the message cannot
handle multi level group by clause in query

Is their any solution to this

 

[genomon]

Please post back the SQL from your query.

Cogito eggo sum – I think, therefore I am a waffle.

 

[dhookom]

What's the SQL view of the query? I expect you are using a subquery and maybe attempting to sort by the results.

Duane
Hook'D on Access
MS Access MVP

 

[rudolfelizabeth]

Problem solved

 

[rudolfelizabeth]

I have another problem with the group by

I enter in the open event of the report

Me.Groudby ="Transdate + ID"

the report must use the info of the query

when I open the report it keeps asking

for the transdate and Id.

any Idea.

 

[genomon]

The best way to do this is to click on the sorting & grouping
button on the menu bar and choose a field, then asc or desc. To my knowledge you cannot concatenate fields for this; if you can, you cannot concatenate different datatypes such as date and numeric.

Cogito eggo sum – I think, therefore I am a waffle.

 

[dhookom]

I'm not sure how genomon understands what you are asking. I can't find either a Groudby or Groupby property of a report.

You can use expressions in the sorting and grouping dialog. These are basically control sources so if you want to sort/group by lastname and firstname, you can set the sorting and grouping to:

  Field/Expression
  =[LastName] & [FirstName]

Duane
Hook'D on Access
MS Access MVP

 

[genomon]

I'm clairvoyant

There are report properties that can be set after you
declare sorting in the dialog. Here is an abbreviated example of code we use on certain reports:

    ' rst is a recordset that returns selected sort info from a table

        Me.GroupLevel(0).ControlSource = rst![fieldname]
        Me.GroupLevel(0).SortOrder = rst![fieldname]
        Me.GroupLevel(0).GroupOn = 0
        Me.GroupLevel(0).GroupInterval = 1
        Me.GroupLevel(0).KeepTogether = 2

Cogito eggo sum – I think, therefore I am a waffle.

 

[rudolfelizabeth]

Hello Dhookom

What genomon is referring to is the orderby and orderby on in the properties fields of the report data.


I have tried this it works perfectly but when you close the porogram and open it again it disappears you have to enter
it again so I went to microsoft page they offer the example
that I used but it's not working

why does the information you supply to these fields disappears

 

[genomon]

Have you tried clicking on "save" before you close the program? Anything you enter in the sorting/grouping dialog box (or the property sheet, but that is not what I was talking about earlier) should not disappear if you save the changes. The VBA snippet I offered above belongs in the module behind the report.

Cogito eggo sum – I think, therefore I am a waffle.

 

[dhookom]

I don't ever use the OrderBy report property. The sorting and grouping levels will always over-ride the OrderBy property.

When I want to change the sorting in a report at run time, I use code similar to Genomon's reply on 20 Aug 09 15:36 to update the ControlSource of a GroupLevel. It's reliable.

Duane
Hook'D on Access
MS Access MVP

 

[rudolfelizabeth]

Here follows another tipical problem with access
I had this code in a database it worked correct now
I have to use the same code it doesnot


SumofDeposits: (SELECT SUM(Quantity*Price) FROM TRANsactions WHERE Transactions!ID = Transactions!ID and Transactions!number=Clientnumber AND Yourbeginningdate <= Yourenddate and Transactions!price >0 )

this code must produce the sum between the beginningdate and enddate for the positive amounts
it doesnot,instead it gives you the total client transactions amount

It ignores the beginningdate and enddate .
the beginningdate is inut by the user
also the enddate

 

[dhookom]

The subquery has Transactions!ID = Transactions!ID which does nothing other than take up space. Since YourBeginningDate and YourEndDate are both entered by the user and most likely will be in date order, Yourbeginningdate <= Yourenddate will also do nothing but take up space.

Don't you want to compare the begin and end dates to a transaction date?

Also, are these parameter prompts (inut by the user)? If so, consider abandoning them in favor of user interaction with controls on forms.

Maybe if you provided significant information regarding your specifications, someone could provide more assistance.

Duane
Hook'D on Access
MS Access MVP