Sum problem (on a one to many) 1

[JasonPurdueEE]

Ok guys, I am realy stumped on this one. Heres what I have: In my database I have a one-to-many relationship where one WTL_id can have multiple Impacts. I store the TotalRegHours field in the WTL_id table. When I pull the info onto my report, if I have multiple Impacts, it shows the TotalRegHours each time. This causes my calculation to be off dramatically.

I tried to hide duplicates but this didnt work either (if there were 2 records that were unique but had the same number in the TotalRegHours column, it hid the second one). This wont work. Theres also the problem with hiding duplicates when the report spans to the next page (which mine will most of the time).

I need to have it display (or just sum) the TotalRegHours only once per WTL_id. Does anybody know how to do this? If clarification is needed, please let me know. Thanks.

JASON

 

[jrbarnett]

Jason

I would add a group header or footer based on Impact, that way you can put it just below or above the detail line and it will only appear once per group in the report.
Use Tools -> Sorting and Grouping in report design.

John

 

[JasonPurdueEE]

John,
Thanks so much, that worked perfectly! Now I need to sum only the visible. I tried this, but it didnt work. Any suggestions?

=Sum(Iif([TotalRegHours] = Visible, [TotalRegHours], 0))

JASON

 

[jrbarnett]

In the group footer put a textbox with the controlsource
=Sum(Fieldname) from the detail band.

John

 

[JasonPurdueEE]

John,
That sums both visible and invisible, but I just want the visible ones. I really appreciate your help on this.

JASON

 

[jrbarnett]

Use =DSum ("TotalRegHours", "WTL_ID", "WTL_ID='" & Me!txtWTL_ID & "'&quot

replace txtWTL_ID with the name of a control with the WTL_ID in (note, you can make the control hidden if you don't want it to appear on your report.

John

 

[JasonPurdueEE]

John,
I tweaked what you have there to get it to work for me. Heres what i ended up with:

=DSum("TotalREGHours","TblWrenchTimeLog","[WTL_ID] = [WTL_ID]&quot

Thanks again!

JASON

 

[JasonPurdueEE]

well it looks like I celebrated a little too early. this works well, as long as you are looking at all the records. but my report is built off the results of a filter by form (which in turn is built off a query). I cant find a way to have this DSUM look at the filtered results. do you have any suggestions?

JASON

 

[jrbarnett]

Jason

Instead of using a filter by form, create a selection form to run the report from. Details thus:

http://www.tek-tips.com/faqs.cfm?spid=702&sfid=3619

It has been discussed many times in the Access Reports and Access Forms forums by people such as myself.

John

 

[JasonPurdueEE]

John,
A couple more quick questions if you dont mind:

1. How should my form and combo boxes be set up to use the method described in your link? Bound? Unbound?

Here is how I have my form set up now. changing the combo boxes alters the record (which I dont want). I just want it to serve as criteria and leave the underlying data alone.

Form Record Source.....Query1

For the combo boxes:
Combobox Control Source.....Crew
Combobox Row Source.....SELECT [Tbl_CrewID].[Crew] FROM
Tbl_CrewID ORDER BY [Tbl_CrewID].[Crew]


Query1 is what the report runs off of. When I set the form's Record Source to nothing, it wont allow me to select anything from my combo boxes.

Thanks for all your help on this John.

JASON

 

[JasonPurdueEE]

nevermind, I'm retarded. I figured it out. I should have had more coffee and thought about this before asking a dumb question. Thanks again for all your help.

JASON

 

[jrbarnett]

Jason

Well done for figuring it out yourself.

John

 

[agsjjs]

I have been reading this thread and I have the same challenge.
I tried a DSUM like you suggested, jrbarnett...and I get an error message in my report.

As a result of my one to many relationship, EmpPay repeats itself for each job. I have 1 EmpPay value for the multiple jobs an employee completes. I want to see each job, but EmpPay only once. So I created a Job footer to get 1 record of EmpPay and that works great. I now want to get EmpPayTotals for the fiscal week. Which is a sum of all the job Emp totals, but it is my understanding that you cannot calculate on a calulated field. So, I have created another footer on FiscalWeek. In that footer, I added a DSUM.

DSum ("EmpPay", "[Employee Wages]!", "FiscalWeek=FiscalWeek&quot
DSum («expr», «domain», «criteria»)
«expr» EmpPay is field that I want to calculate
«domain» [Employee Wages]! is the query that EmpPay is from
«criteria» FiscalWeek=FiscalWeek I want a SUM of EmpPay where the fiscal week in FiscalWeek footer is equal to the FiscalWeek in the query.

However, this does not work! Not sure if my syntax is correct! Any help is appreciated.

 

[jrbarnett]

Jason

You can calculate on a calculated field - just make sure that you use the control name (right click on the textbox and look at the name property, and use that in the expression).

As for your DSum, use the same technique:
DSum ("EmpPay", "[Employee Wages]!", "FiscalWeek=" & Me!txtFiscalWeek)
- replace the txtFiscalWeek with the control name on the report showing your week number.

John

 

[JasonPurdueEE]

Hey agsjjs,
Let me see if I can shed some aditional light on this. I didnt end up using the DSum at all. Heres the method I used, with variable names adjusted to your situation:

after grouping and sorting and all that, add a text box in the pay footer. lets call it "RunningPaySum". Set its Control Source to EmpPay and change it to Running Sum Over All. after you verify that its working correctly you can make the field invisible and set its Can Shrink property to Yes.

now add a text box where ever you want the summary and set its Control Source to =[RunningPaySum]

thats all there is to it. hope this helps, if you have additional questions I would be more than happy to try to help you out.

JASON

 

[chezealot9]

Hey guys, Im looking over all your comments and I think they are really close to the problem I am having but I dont know enough about Access reports to know for sure. Quick background is I am making a report to display employees vacation/sick time usage, at the top is how much time each employee is given and at the bottom is a list of individual dates they have used so far. I am trying to calculate the sum of the time already used for each category that shows up on the top and I tried the line of code I think you were mentioning :

=DSum("Hours","Data_Absence_Usage","[Out_of_Office_Type] = " & Me!Out_of_Office_Type)

where hours is the field I want the total of, data_absence_usage is the table and so on.....

and I keep getting a pop up box asking for the value of Me.
Am I tackling this the best way or is there a easier way? Greatly appreciate any help in advance.

 

[agsjjs]

Hi John! Thanks for your feedback! Here is what I have found from testing.

I have added the DSUM into the expression builder for my calculation field and I changed the txtFiscalWeek to the actual control name. I run the report, and I am prompted for Me. Access thinks Me is a prompt!?

I tried the others scenario, calculating on other report fields. In the FiscalWeek footer, I created a field with the expression =Sum([datEmpPay]). Where datEmpPay is the control name of the field I want to SUM which is in the Employee footer. I am then prompted for datEmpPay. Is my syntax correct?

I am trying to achieve the following subtotals
Job Detail-All Jobs-Works Great!
Employee Totals-EmpPay without duplicates-Works Great!
FiscalWeek Totals-Sum of Employee Totals or Sum EmpPay without duplicates-Does not work :-(

Thanks for all your help!
Angelica

 

[agsjjs]

Hi Jason! Your advice worked perfectly! Thank you so much! I have been working on this off and on for a week.

It is also better solution for me than DSUM Because I have multiple filters in the query my report runs!

Also, yeah Purdue! I graduated in 97 from Krannert'

Thanks Again!
Angelica

 

[JasonPurdueEE]

Hey Angelica,
Glad to see you got it working. And nice to see a fellow Boilermaker on the boards!

Boiler up!
JASON

 

[JasonPurdueEE]

Hey chezealot9,
Did you get your problem solved? You could probably use the same method me and Angelica used. Let me know if you have any moe questions.

JASON