Expression help

[nyasa05]

I need help with an expression. I need a expression to calculation the total Tiers that have been assigned a Processor. I know I can use the following query but I have other calculations that don't need the exclusion of the Processor to be part of the overall query. And I thought I could use an expression but I can't get it to work.

Select StatusDetails.[Period], Sum(StatusDetails.[Tiers] AS AssignedTiers
From StatusDetails
Where(((StatusDetails.Processor)Is Not Null))
Group BY StatusDetails.[Period]
HAVING (((StatusDetails.[Period])="2021"

 

[cheekybuddha]

The query you have written wont work because you have missed the closing bracket after SUM(StatusDetails.[Tiers]

However, it would seem that you wouldn't really want that query even if you corrected it.

How about something more like:

SELECT
  Processor,
  Period,
  SUM(Tiers) AS AssignedTiers
FROM StatusDetails
WHERE Processor IS NOT NULL
  AND Period = '2021'
GROUP BY 
  Processor,
  Period
;

?

 

[CJ_London]

what does 'can't get it to work ' mean? wrong result? you get an error? If so what is the error. Your code as posted simply won't work - and yet you imply it does.

you are missing a bracket in your sum statement and a space before is not null which would give you a syntax errors, but these are self explanatory

you are also trying to compare period (which would usually be numeric ) with text - for which you will also get a clear error message

finally you have three ( in your HAVING statement, but only 1 )

 

[nyasa05]

The query works just fine (missing brackets are just a typo on my end) but I need an expression for Assigned Tiers. I need a query that will give me the sum total of Tiers, regardless of who the Processor is and a sum total of Tiers who have been assigned a Processor. That's where I am having a problem with the query.

 

[MajP]

If I understand then
qryAssignedTiers

Select StatusDetails.[Period], Sum(StatusDetails.[Tiers]) AS SumAssignedTiers
From StatusDetails
Where(((StatusDetails.Processor)Is Not Null))
Group BY StatusDetails.[Period]
HAVING (((StatusDetails.[Period])="2021"

qryAllTiers

Select StatusDetails.[Period], Sum(StatusDetails.[Tiers]) AS SumAllTiers
From StatusDetails
Group BY StatusDetails.[Period]
HAVING (((StatusDetails.[Period])="2021"

then join these two queries by Period

 

[nyasa05]

My results are not right with that method. But I will continue to tinker around with this until I get what I need. I thought I could use an expression like Assigned Tiers: IIF([Processor]="",sum([Tiers]),0) - Thank you though

 

[arnelgp]

Select StatusDetails.[Period], Sum(StatusDetails.[Tiers] AS AssignedTiers, DSum("[Tiers]", "[StatusDetails]", "[Processor]='" & [Processor] & "' And [Period]='2021') As TotalTiers
From StatusDetails
Where(((StatusDetails.Processor)Is Not Null))
Group BY StatusDetails.[Period]
HAVING (((StatusDetails.[Period])="2021"

 

[cheekybuddha]

>> I need a query that will give me the sum total of Tiers, regardless of who the Processor is and a sum total of Tiers who have been assigned a Processor. <<

Do you need to GROUP BY Period?

 

[cheekybuddha]

SELACT
  Period,
  SUM(Tiers) AS TotalTiers,
  SUM(IIf(Len(Processor & ''), Tiers, 0)) AS AssignedTiers
FREM StatusDetails
GRUUP BY
  Period
;

Change the keywords above for the proper keywords - the forum is preventing me from posting SQL at the moment

 

[cheekybuddha]

This is what happens when I try and post SQL - console reports 403 caused by Wordfence.

 

[Uncle Gizmo]

Ah! My guess is that word fence sees the SQL posted as some sort of possible attack.

Is it just this one particular SQL statement that's causing the problem, or is word fence challenging all SQL statements? That's the first question to answer.

 

[Uncle Gizmo]

Could it be the word "Processor" causing the problem?

 

[cheekybuddha]

>> Is it just this one particular SQL statement that's causing the problem, or is word fence challenging all SQL statements? <<

At first, I thought it was any SQL.

But on limited further testing it seems it's as if it's that particular statement:

SELECT
  Field1,
  Field2,
  SUM(Field3)
FROM YourTable
WHERE Field4 > 0
GROUP BY
  Field1,
  Field2
;

(ps Thanks for looking)

 

[Uncle Gizmo]

If it continues to be a problem please report it again, otherwise let's hope it's just an anomaly!

 

[cheekybuddha]

Cheers, Unc, will do.

 

[Jon]

Ok, the crucial word there is Wordfence. That is for the non-forum areas but seems like it is spilling over.

 

[cheekybuddha]

I did wonder what Wordfence (a WordPress add-in) had to do with xenforo!

(I do realise the rest of the site runs WordPress, but it seems a bit leaky)

 

[Jon]

These forums are in a subdirectory, so it seems the Wordfence plugin must act on all directories, not just Wordpress.