Expression help (1 Viewer)

nyasa05

New member
Local time
Today, 01:06
Joined
Aug 31, 2015
Messages
11
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

AWF VIP
Local time
Today, 06:06
Joined
Jul 21, 2014
Messages
626
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:
Code:
SELECT
  Processor,
  Period,
  SUM(Tiers) AS AssignedTiers
FROM StatusDetails
WHERE Processor IS NOT NULL
  AND Period = '2021'
GROUP BY 
  Processor,
  Period
;
?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:06
Joined
Feb 19, 2013
Messages
12,955
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

New member
Local time
Today, 01:06
Joined
Aug 31, 2015
Messages
11
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

You've got your good things, and you've got mine.
Local time
Today, 01:06
Joined
May 21, 2018
Messages
4,909
If I understand then
qryAssignedTiers
Code:
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
Code:
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

New member
Local time
Today, 01:06
Joined
Aug 31, 2015
Messages
11
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

error reading drive A:
Local time
Today, 13:06
Joined
May 7, 2009
Messages
12,388
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

AWF VIP
Local time
Today, 06:06
Joined
Jul 21, 2014
Messages
626
>> 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

AWF VIP
Local time
Today, 06:06
Joined
Jul 21, 2014
Messages
626
Code:
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 :poop:
 

cheekybuddha

AWF VIP
Local time
Today, 06:06
Joined
Jul 21, 2014
Messages
626
1617362362126.png


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

Attachments

  • 20210402_122520_awf_screenshot.png
    20210402_122520_awf_screenshot.png
    108.2 KB · Views: 15
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 06:06
Joined
Jul 9, 2003
Messages
12,819
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.
 

cheekybuddha

AWF VIP
Local time
Today, 06:06
Joined
Jul 21, 2014
Messages
626
>> 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:
Code:
SELECT
  Field1,
  Field2,
  SUM(Field3)
FROM YourTable
WHERE Field4 > 0
GROUP BY
  Field1,
  Field2
;

(ps Thanks for looking)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 06:06
Joined
Jul 9, 2003
Messages
12,819
If it continues to be a problem please report it again, otherwise let's hope it's just an anomaly!
 

Jon

Access World Site Owner
Staff member
Local time
Today, 06:06
Joined
Sep 28, 1999
Messages
4,826
Ok, the crucial word there is Wordfence. That is for the non-forum areas but seems like it is spilling over.
 

cheekybuddha

AWF VIP
Local time
Today, 06:06
Joined
Jul 21, 2014
Messages
626
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

Access World Site Owner
Staff member
Local time
Today, 06:06
Joined
Sep 28, 1999
Messages
4,826
These forums are in a subdirectory, so it seems the Wordfence plugin must act on all directories, not just Wordpress.
 

Users who are viewing this thread

Top Bottom
日韩精品亚洲专区在线影院