Report totals (1 Viewer)

Teri Bridges

Member
Local time
Yesterday, 18:32
Joined
Feb 21, 2022
Messages
53
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.
Outline query.png


Outline report.png
 

plog

Banishment Pending
Local time
Yesterday, 18:32
Joined
May 11, 2011
Messages
10,916
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

Member
Local time
Yesterday, 18:32
Joined
Feb 21, 2022
Messages
53
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.
LessonDuration.png
 

plog

Banishment Pending
Local time
Yesterday, 18:32
Joined
May 11, 2011
Messages
10,916
You need to put it where you want it to appear.
 

Teri Bridges

Member
Local time
Yesterday, 18:32
Joined
Feb 21, 2022
Messages
53
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.
 

Attachments

  • TeriV12.zip
    1.3 MB · Views: 56

plog

Banishment Pending
Local time
Yesterday, 18:32
Joined
May 11, 2011
Messages
10,916
It does matter where you put it. I put it where you said you wanted it and it ran fine for me.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:32
Joined
Feb 19, 2002
Messages
36,284
You need to use a report with a subreport. That way the amount only occurs ONCE and the sum will be correct.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:32
Joined
May 7, 2009
Messages
16,393
see your report and the code on the Load event.
 

Attachments

  • TeriV12.zip
    1.4 MB · Views: 43

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:32
Joined
Feb 19, 2002
Messages
36,284
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.
 

Users who are viewing this thread

Top Bottom