Access 2007: Help in summing up two summed columns. 2

[chromarog]

I need some access 2007 help. I have a form I'm working on and I thougth what I wanted was something pretty simple but apparently I'm doing it wrong or going about it the wrong way.

I have a query that hit's a table that does some summing up. This is the results:

PRIORITY RegSum OthrSum
16.5
1 14.5
2 308 7.5
3 60.5

Here is the query:
SELECT DISTINCTROW PTQ_VSMP2_Breakdown.PRIORITY, Sum(PTQ_VSMP2_Breakdown.REGHRS) AS RegSum, Sum(PTQ_VSMP2_Breakdown.OTHRS) AS OthrSum
FROM PTQ_VSMP2_Breakdown
WHERE (((PTQ_VSMP2_Breakdown.COMPLETIONDATE) Between [Forms]![Form 2]![StartDay] And [Forms]![Form 2]![EndDay]))
GROUP BY PTQ_VSMP2_Breakdown.PRIORITY;

I need to find a way to add the RegSum and OthrSum together. Is that something to calculate in the query or somewhere else? I'll ultimately use the results in a bar graph.

I've tried all kinds of other functions but I can't seem to find the right one, or my direction/logic is wrong. If I need to back up some steps and take a different approach, please correct me there as well.

 

[Andrzejek]

If you want something like this:
[tt]
PRIORITY RegSum OthrSum ABCD
16.5 16.5
1 14.5 14.5
2 308 7.5 315.5
3 60.5 60.5[/tt]

Why don't you try:
[tt]
SELECT DISTINCTROW PTQ_VSMP2_Breakdown.PRIORITY, Sum(PTQ_VSMP2_Breakdown.REGHRS) AS RegSum, Sum(PTQ_VSMP2_Breakdown.OTHRS) AS OthrSum, [blue]
Sum(PTQ_VSMP2_Breakdown.REGHRS) + Sum(PTQ_VSMP2_Breakdown.OTHRS) As ABCD[/blue]
FROM PTQ_VSMP2_Breakdown
WHERE (((PTQ_VSMP2_Breakdown.COMPLETIONDATE) Between [Forms]![Form 2]![StartDay] And [Forms]![Form 2]![EndDay]))
GROUP BY PTQ_VSMP2_Breakdown.PRIORITY;
[/tt]


Have fun.

---- Andy

 

[dhookom]

If any of the Sum() values are Null, you will need to use Nz() to calculate the correct totals.

SELECT PRIORITY, Sum(REGHRS) AS RegSum, 
 Sum(OTHRS) AS OthrSum, 
 Sum(Nz(REGHRS,0)) + Sum(Nz(OTHRS,0)) As ABCD
FROM PTQ_VSMP2_Breakdown
WHERE COMPLETIONDATE Between [Forms]![Form 2]![StartDay] And [Forms]![Form 2]![EndDay]
GROUP BY PRIORITY;

Duane
Hook'D on Access
MS Access MVP

 

[chromarog]

I owe you one.
Thanks a million.
Rog...

 

[SkipVought]

I owe you one.

Why not simply click the link to...
[blue]
Thank Tek-Tip Contributor
for this valuable post![/blue].

The [purple]little purple Stars[/purple] accomplish several important things.

First, it gives positive feedback to contributors, that their posts have been helpful.

Second, it identifies threads as containing helpful posts, so that other members can benefit.

And third, it identifies the original poster (that's YOU, BTW), as a grateful member, that not only receives, but is willing to give tokens of thanks.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[chromarog]

Cool, thanks Skip. I didn't realize that.
Thanks for that.
Rog...

 

[chromarog]

Oh and Dhookom, I'll have to look into that, there is another column that indicates that hours were put into the process but they were classified as a normal procedure and not a breakdown. I'll use your information for the next step in the project though.

Again, much appreciated for all the help.
Rog...

 

[chromarog]

I have another question related to this. How do I get the results to a text box? I would like to populate the results in a text box for each "priority" from the resulting Sum field ABCD. Is DLOOKUP what I'm looking for?

I've thought about a single button that fires three seperate queries to capture individual results but that seems a bit inneficient to me, and I'm really new to this and I'd like to do it right.

 

[dhookom]

I would use a continuous subform based on the totals query. If your text boxes need some other format, then you might be able to use Dlookup() in control sources like:

=Dlookup("ABCD","Your Query Name","PRIORITY=1")
=Dlookup("ABCD","Your Query Name","PRIORITY=2")
=Dlookup("ABCD","Your Query Name","PRIORITY=3")

This assumes the Priority field is numeric. I also assume you will never have more than the three PRIORITY values into the future or you will need to update your form design.

Duane
Hook'D on Access
MS Access MVP