Query: Dealing with getting sums in group, problem with expressions column

Question

Hello sirs!

I have a problem dealing with QUERIES, when grouping and getting the sum of columns.

I have created several "expression" type columns then,  i get an error message: "Subqueries cannot be used in the expression ..."

I want to group the items by [Personnel] and get the sum for OTTotal_2, RN, ROT, RT, SHN, SHOT, SHT, TH, and TD

            Example: 10 Joseph H. Cesar should only show in one row with a total TD = 1.8 + 0.9 = 2.7

I also want to filter the items by a date range which would filter [DateToday]

Looking forward for your kind help!

Download Link to my database file: https://www.dropbox.com/s/5y56p7bt43ipuzn/CPACC\_56.accdb?dl=0

****please hold "Shift key" when opening the file to unlock the file

Open Query: Personnel_DTR_Admin_Summary_1

Expressions used: 

RN: iif([Doublepay] = False, Nz(IIf([DoublePay]=True,2,1)*(24*(IIf(([T2]-[T1])>=([T2_out]-[T1_in]),[T2_out]-[T1_in],IIf(([T2_out]-[T1_in])-([T2]-[T1])<(0.25/24),[T2_out]-[T1_in],[T2]-[T1])))),0) + Nz(IIf([DoublePay]=True,2,1)*(24*(IIf(([T4]-[T3])>=([T4_out]-[T3_in]),[T4_out]-[T3_in],IIf(([T4_out]-[T3_in])-([T4]-[T3])<(0.25/24),[T4_out]-[T3_in],[T4]-[T3])))),0)

• Nz([OTTotal_2],0), 0 )

ROT: iif([Doublepay] = False, [OTTotal_2],0)

RT: [RN]+[ROT]

SHN: iif([Doublepay] = True, Nz(IIf([DoublePay]=True,2,1)*(24*(IIf(([T2]-[T1])>=([T2_out]-[T1_in]),[T2_out]-[T1_in],IIf(([T2_out]-[T1_in])-([T2]-[T1])<(0.25/24),[T2_out]-[T1_in],[T2]-[T1])))),0) + Nz(IIf([DoublePay]=True,2,1)*(24*(IIf(([T4]-[T3])>=([T4_out]-[T3_in]),[T4_out]-[T3_in],IIf(([T4_out]-[T3_in])-([T4]-[T3])<(0.25/24),[T4_out]-[T3_in],[T4]-[T3])))),0)

• Nz([OTTotal_2],0), 0 )

SHOT : iif([Doublepay] = True, [OTTotal_2],0)

SHT : [SHN] +[SHOT]

Looking ahead for your kindest suggestions and help!

more power!

Answer 1

My first thought would be to make a copy (version 2) of the query, and instead of running several columns, only run the TD column, and maybe add criteria that only Joseph H. Cesar.  And maybe without any 'total' columns to just show Joseph H Cesar.  And then have it run each of his individual stats - the 1.8 on one line, and then the 0.9 on the next line/record.

And then try the SUM.

I'm not experienced enough to know why/what is causing the error.  I'm just using the De-bug theory that I would use to hopefully identify where the problem is.  The other day I thought I was doing things great, but having no success.  When simplifying the situation, I realized that I had excluded the 'G' from the Game_ID code, and then another day I found was using 'V_G' when it needed to be 'V-G'.  Very frustrating until I realized my dumb/careless mistake.  And then very glad my theory was right, but had to admonish my secretary for her typing skills.  Actually that was me.

Answer 2

I want to group the items by [Personnel] and get the sum for OTTotal_2, RN, ROT, RT, SHN, SHOT, SHT, TH, and TD

            Example: 10 Joseph H. Cesar should only show in one row with a total TD = 1.8 + 0.9 = 2.7

EDITs --

In your first query I generally find that Access processes a query from bottom-up or from right to left in design view.  Therefore you are creating 'TH' by adding 'RT' to 'SHT' but RT is to the left of that and would not have been processed before you are using it.

Another example is --

SHOT : iif([Doublepay] = True, [OTTotal_2],0)

SHT : [SHN] +[SHOT]

  --  where SHOT is being used before it is produced.

10 Joseph H. Cesar must be in TWO ROWS because you have TWO different DateToday and also one marked as DoublePay and 2nd not.

All your joins in the 3rd query are backwards - they should be arrow away from primary keys.