Summing up too much data

[JJSRich]

I've got a complex query (54 lines of pass-through to MySQL, Access died on this query) that among a bunch of other information returns:

contract #, employee, job #, hours assigned to job#, hours worked on job #

note: a contract consists of multiple job #'s

It works fine most of the time, each employee logs their hours and those get summed up in the SQL. Where it falls down is when more than one employee works on a single job. The number of hours assigned to the job are getting summed my the report, so if 2 people worked on it, the assigned hours would be 2x what they should be. I have tried to use

=sum([Field name on form])

but it asks me for the value for the field. What would be a better solution?

 

[dhookom]

I assume the hours assigned are in the Job table/level. If this is true, you should be able to display the hours assigned in the Job header or footer section with no Sum().

If you want to total these by Contract, you can use a running sum of a text box from the Job Header or Job Footer section:[tt][blue]
Name: txtJobHrsRS
Control Source: [JobAssignedHrsFld]
Running Sum: Over Group
Visible: No[/blue][/tt]

Then add a text box to the Contract footer section:[tt][blue]
Name: txtJobHrsTotal
Control Source: =txtJobHrsRS
Running Sum: None
Visible: Yes[/blue][/tt]


Duane
Hook'D on Access
MS Access MVP

 

[lameid]

So the hours are related to the job and not the people?

I would have two queries. One for Jobs and one for people on jobs.

Then I would have a sub report for the people on the job and sum in the hours in the main report.

The another possiblity would be to conditionally sum the hours. You would have to do something in the SQL to identify which person's hours to sum. Perhaps outer join to a select that returns the minum person... then if that value is not null, sum it.

The third possiblity would be to normalize the data into Access tables and use those instead.

I suspect the first option I mentioned may kill you performance wise. The second has that potential, I simply don't know enough about MYSQL. The third is to bring the data local which probably viloates the point of having a backend.

 

[lameid]

Having cross-posted, I like Duane's running sum better. For some reason I've got a mental block saying that it won't work.... If Duane recommended it though I expect it will.

 

[JJSRich]

Email replies haven't been getting through, thanks for your replies so far. It's proving to be a real pain in the backside, and it looks like I may need to completely re-write the report, and the SQL driving it.

I'm thinking the easiest way may actually be to add fields in the SQL that do my tallying for me, and then just refer to those. I have another section where things have stopped working as well, too much complex business logic in too many places I think. Problem is, it relates directly to getting money from other people, so it's quite important.

 

[JJSRich]

Umm, erm.. ok. Strike the second bug. PEBKAK, user didn't actually log their hours in to the system and somehow expected them to magically appear.