Report totals (1 Viewer)

[Teri Bridges]

Guys I am going to give it my best shot explaining what I am doing and what my issue is.

My database has a form for Lessons, and that form has a sub form for events.

The lesson has a duration say .25 (15 minutes)

I created a query to show me my lessons and associated events so I could make a report.
So what is happening is, in the query if I have three events in the lesson, say lesson 1 the duration is showing 3 times so it thinks the lesson duration is .75 .

When I run the report each lesson has the correct lesson duration but when I create the sum field for the duration it is pulling from the query, posting .75 for the lesson not .25.
I am sure there is a solution for this but I have no Idea.

 

[plog]

In the report, what is the code behind the Lesson Duration calculation? Are you in fact using =SUM([Lesson Duration])?

Side note, don't use spaces in field names, makes coding and querying a little more difficult.

 

[Teri Bridges]

I am using =Sum([LessonDuration])
From what I can tell it is totaling from the query, which is probably right. I just don't know how to get what I want.

 

[plog]

You need to put it where you want it to appear.

 

[Teri Bridges]

You need to put it where you want it to appear.

Does not matter where I put it. Here is my DB if you want to check it out. You can use course FIN-AA300 to run the report, it has a parameter set.

 

[plog]

It does matter where you put it. I put it where you said you wanted it and it ran fine for me.

 

[Pat Hartman]

You need to use a report with a subreport. That way the amount only occurs ONCE and the sum will be correct.

 

[arnelgp]

see your report and the code on the Load event.

 

[Pat Hartman]

You can frequently use code to get past design flaws but if you use a subreport for the details, you don't need code because the data on the main report isn't duplicated to support each detail row.

It is sometimes more trouble to fix a problem correctly after the fact than to patch it but in the long run, a foundation without cracks is a whole lot easier to maintain and expand. Separating the query, you would take tblLessonEvents and the tables to the right and put them into the subreport's query and remove them from the main report's query.

Building the report correctly from the beginning would probably have added 5-10 minutes to the development time.