Adding a totals field to a query based on other records is this possible?

[Jim W]

I have a query that returns down hrs for different modules. I would like to add a field to this query and have the value be the down hrs if the value = a certain value. Very hard to explain please see the table below.

System - Module - HrsDown
1234 - Ch 1 - 1
1234 - Ch 2 - 1.5
1234 - Ch 3 - 0
1234 - Ch 4 - 3

So now I want to add a field Tothrs and I want it to add only if the module = Ch 3 and Ch 4

System - Module - HrsDown - Tothrs
1234 - Ch 1 - 1 3
1234 - Ch 2 - 1.5
1234 - Ch 3 - 0
1234 - Ch 4 - 3

 

[Jon K]

I want it to add only if the module = Ch 3 and Ch 4


Not sure what you mean by "the module = Ch 3 and Ch 4".

It seems you have added 3 in Tothrs in the first record because its module is Ch 1.

 

[Jim W]

Module is the name of the column so the TotHrs would be the total HrsDown if the module = CH 3 or Ch4 so I want it to look like the table below.


System - Module - HrsDown - Tothrs
- 1234 - ,Ch 1 - , 1 , 3
- 1234 - ,Ch 2 - , 1.5 , 3
- 1234 - ,Ch 3 - , 0 , 3
- 1234 - ,Ch 4 - , 3 , 3

Does this make more sense?

Jim

 

[Jon K]

You can use the DSum() function to get the total HrsDown of Ch3 and Ch4

 

[Jim W]

Jon -

How would I write that

TotHrs: DSum([HrsDown] If Module = Ch3 or Ch4
???

Jim

 

[Jon K]

Tothrs: Val(DSum("HrsDown","tableName","[Module] in ('Ch 3', 'Ch 4')"))

 

[Jim W]

Jon - I can not get this to work. The data is not coming from a table its coming from a Query. I tried replacing tableName with the Query name but it does not like it.

Tothrs: Val(DSum("HrsDown","tableName","[Module] in ('Ch 3', 'Ch 4')"))

Jim

 

[Jon K]

The easiest way is to build a new query from your original query. See the example in the attached database, which contains your sample data.


The database was saved from Access 2000.

 

[Jim W]

Jon -
Got it working cool thankyou for the help. One more question can I do the tothrs based on more then 1 value? Meaning
Module and System Number


Tothrs: Val(DSum("HrsDown","Qurey","[SystemNumber] & [Module] in ('Ch 3', 'Ch 4')"))

Jim

 

[Jon K]

You can add [System] in the criteria of DSum().

Copy this query to the SQL View of a new query in my database:-

SELECT System, Module, HrsDown,
Val(DSum("HrsDown","TableName","[System]=" & [System] & " and [Module] in ('Ch 3', 'Ch 4')")) AS TotHrs
FROM TableName;

This should also return the TotHrs for System 2222.


If [System] is a text field, its value must be surrounded by single quotes:-

Val(DSum("HrsDown","TableName","[System]='" & [System] & "' and [Module] in ('Ch 3', 'Ch 4')")) AS TotHrs

 

[Jim W]

Jon- I have it working very well thanks to you. Thankyou so much for your time today. I learned a great deal.

Thanks again

Jim