sum in grouping not working as I need it to

[JJSRich]

I have a report, that comes from a SQL query, one of those monsters that is about 30 fields wide. I have 5 levels of grouping,

Company Rep
Company
Subcontractor
Site
House
Designer

The house has a number of hours that it should take, and how long it actually did take. Typically there is only one designer, but there are exceptions. The report works fine until two people work on the same house< then we get double counting.. i.e.


Plot 1 | 5 hours assigned
[tab]Bob | 3 hours worked
Plot 2 | 3 hours assigned
[tab]Harold | 4 hours worked
Plot 3 | 2 hours assigned
[tab]Bob | 1 hour worked
[tab]Harold | 1 hour worked

totals
[tab]12 hours assigned
[tab]9 hours worked

In reality, only 10 hours were assigned, but because of how the grouping is working, Access appears to be summing up each row to get to the assigned hours. This is correct for the worked hours, but not for assigned. How can I count each item only once?

 

[dhookom]

You typically use a running sum text box in the House Group Section for Hours Assigned. Then just reference the name of the running sum text box in the Site Footer to get the correct sum.

Duane
Hook'D on Access
MS Access MVP

 

[JJSRich]

That's what I already have in place, and it doesn't work, as an assigned hours is generated for each house on a site. I tried breaking it out, and putting the sum in the footer, and the double counting just happens earlier on the form.

 

[dhookom]

What exactly did you try? Give use some text box names and control sources and other significant properties as well as report sections.

double counting just happens earlier on the form

Is this a report or form?

You can also calculate these values in the record source of the report by join in a totals query with the exact numbers you need.

Duane
Hook'D on Access
MS Access MVP

 

[JJSRich]

Report, sorry. Just getting a bit flustered with the limitations of Access as a frontend. At least it's not where I keep the data.

I am scrapping the idea of summing up anything and just running a subquery to get the data for each site, as that is what we are most concerned with. Basically your second suggestion.

 

[dhookom]

You could try create a totals query grouped by Site and have it total the Hours Assigned. If you join this query to your record source then you won't need to use Sum() in the Site footer.

Duane
Hook'D on Access
MS Access MVP

 

[JJSRich]

That sounds like a good idea, but I didn't think I could join in a query like that, given I am using a pass-through query. I'm exploring other avenues as well.

 

[dhookom]

I typically use DAO to change the SQL property of P-T queries as needed.

Duane
Hook'D on Access
MS Access MVP

 

[JJSRich]

Umm. sorry, really rusty, it's been over a year since I touched this application. I basically do this

Set q = DBEngine(0)(0).QueryDefs("WeeklyReport")

sqlstr = "SELECT"
sqlstr = sqlstr & " q.quote_id"
...
30 rows of data, joins, limits, etc
...
q.sql = sqlstr
q.Close

    stDocName = "WeeklyReport"
    DoCmd.OpenReport stDocName, acPreview

I think I am getting closer, my subqueries aren't quite right yet, too broad.

 

[dhookom]

Is there still a question here?


Duane
Hook'D on Access
MS Access MVP

 

[JJSRich]

Not any more I don't think. It's still not working correctly, but the main question has been taken care of.