access query to do the same as excel =IFSUMS

  • Thread starter Thread starter Ghurka
  • Start date Start date
G

Ghurka

I need to caluclate the maximum number of tanks by chemical that are on a lot
on any given day over the course of the reporting period. The reporting
period is one year. I can achieve this by using excel and the =IFSUMS
formula. I would like to do this as a query in access 2007 but cannot figure
out how to do it. I have created a cross tab query with days of the report
period in rows, and chemical in column
headers, but I cannot figure out how to do a conditional statement that
tests whether the control date is between the in.date and out.date of the
record.

In excel the formula is:
=SUMIFS(Aggregate!$M$2:$M$833,Aggregate!$P$2:$P$833,"<="&$A2,Aggregate!$N$2:$N$833,">="&$A2,Aggregate!$I$2:$I$833,B$1)

Where columns are:
Aggregate is the sheet with the data
M= control tank count, basically a 1 in each row
P= date when tank is received
A = Control dates of the report - every day 10/1/20008 - 9/30/2009
N = Date when tank leaves lot
I = chemical type

So what I do is, I sum the tank control instance for each time the control
date is between the in and out dates AND the chemical matches (inclusive of
in & out dates)

In the end I then do a =max(column) by chemical to see the max count of tank
by chemical.

I am stumped as with the cross tab I get the chemical type across the top,
and the date down the left side, and can count the max number of tanks, but I
can't conditionalize the query to adjust for max value within the parameters
of in and out date. So the max is higher than what it should be.

My SQL in the cross tab is:

TRANSFORM Count(Aggregate.[Tank Count]) AS [CountOfTank Count]
SELECT Aggregate.[Control Date], Count(Aggregate.[Tank Count]) AS [Total Of
Tank Count]
FROM Aggregate
GROUP BY Aggregate.[Control Date]
PIVOT Aggregate.[Chemical];

Clear as mud?

Any help would be great, thanks!!!
 
Using your input -
M= control tank count, basically a 1 in each row
P= date when tank is received
N = Date when tank leaves lot
I = chemical type

SELECT Sum(IIf(DateDiff("d",IIf([P]<CVDate([Report start]),CVDate([Report
start]),[P]),IIf([N]>CVDate([Report end]),CVDate([Report
end]),[N]))<0,0,(DateDiff("d",IIf([P]<CVDate([Report start]),CVDate([Report
start]),[P]),IIf([N]>CVDate([Report end]),CVDate([Report end]),[N]))+1)*[M]))
AS Expr1, Ghurka.I
FROM Ghurka
GROUP BY Ghurka.I;
 
Thanks, I will give this a try.....

KARL DEWEY said:
Using your input -
M= control tank count, basically a 1 in each row
P= date when tank is received
N = Date when tank leaves lot
I = chemical type

SELECT Sum(IIf(DateDiff("d",IIf([P]<CVDate([Report start]),CVDate([Report
start]),[P]),IIf([N]>CVDate([Report end]),CVDate([Report
end]),[N]))<0,0,(DateDiff("d",IIf([P]<CVDate([Report start]),CVDate([Report
start]),[P]),IIf([N]>CVDate([Report end]),CVDate([Report end]),[N]))+1)*[M]))
AS Expr1, Ghurka.I
FROM Ghurka
GROUP BY Ghurka.I;

--
Build a little, test a little.


Ghurka said:
I need to caluclate the maximum number of tanks by chemical that are on a lot
on any given day over the course of the reporting period. The reporting
period is one year. I can achieve this by using excel and the =IFSUMS
formula. I would like to do this as a query in access 2007 but cannot figure
out how to do it. I have created a cross tab query with days of the report
period in rows, and chemical in column
headers, but I cannot figure out how to do a conditional statement that
tests whether the control date is between the in.date and out.date of the
record.

In excel the formula is:
=SUMIFS(Aggregate!$M$2:$M$833,Aggregate!$P$2:$P$833,"<="&$A2,Aggregate!$N$2:$N$833,">="&$A2,Aggregate!$I$2:$I$833,B$1)

Where columns are:
Aggregate is the sheet with the data
M= control tank count, basically a 1 in each row
P= date when tank is received
A = Control dates of the report - every day 10/1/20008 - 9/30/2009
N = Date when tank leaves lot
I = chemical type

So what I do is, I sum the tank control instance for each time the control
date is between the in and out dates AND the chemical matches (inclusive of
in & out dates)

In the end I then do a =max(column) by chemical to see the max count of tank
by chemical.

I am stumped as with the cross tab I get the chemical type across the top,
and the date down the left side, and can count the max number of tanks, but I
can't conditionalize the query to adjust for max value within the parameters
of in and out date. So the max is higher than what it should be.

My SQL in the cross tab is:

TRANSFORM Count(Aggregate.[Tank Count]) AS [CountOfTank Count]
SELECT Aggregate.[Control Date], Count(Aggregate.[Tank Count]) AS [Total Of
Tank Count]
FROM Aggregate
GROUP BY Aggregate.[Control Date]
PIVOT Aggregate.[Chemical];

Clear as mud?

Any help would be great, thanks!!!
Click to expand...
Click to expand...
 
Back
Top