sum if

[CoffeeGuru]

I am trying to find the sum of items sold in the last two weeks

something along the lines if this:

If week number is greater than the maximum weeknumber then sum the Volumns of the last two weeks.

Ive tried this:

IIF([CM-DATA].[WeekNo] >= Max[CM_DATA].[WeekNo]-2,SUM([CM_DATA].[Volume]))

only that does not work.

Any ideas.

 

[pr2-eugin]

Try DSum instead.

 

[Brianwarnock]

Domain functions are heavy solutions.
The system cannot know the max of a field until it has processed all of the records, thus you need a query to determine maxofweekno then include this in a query with the table and the criteria for week no will be >= maxofweekno-2 and on the Totals row click Where for this and Sum for Volume

Brian

 

[CoffeeGuru]

Hi Guys

Thanks for your input

Brianwarnock - LAST is a feature within Access which appears not to have a performance issue. Its getting the previous week thats a problem and then summing the two to get a value.
I can get LAST(WeekNo) and even
LAST(WeekNo)-2 actually that should probably be LAST(WeekNo)-1 thinking about it.
But getting the value for Volume for the calculated week and summing it to the volume for the actual week is giving me grief.

pr2-eugin - DSUM is new to me, although I have seen it in Excel I have never used it. If it's anything like the Excel Sumproduct, then it may cause performance issues but I also need to give it a try.

But for now I'm stumped

Martin

 

[pr2-eugin]

Give a basic Query (exact field names and table names) that will simply count the sales between two dates. I will try to write a Query, based on that.

 

[CoffeeGuru]

is this what you mean..

SELECT
CM_DATA.Week,
Sum(CM_DATA.Volume) AS SumOfVolume
FROM CM_DATA
GROUP BY CM_DATA.Week
ORDER BY CM_DATA.Week;

excuse my use of reserved words

 

[pr2-eugin]

In theory this should work..

SELECT CM_DATA.[Week], Sum(CM_DATA.Volume) AS SumOfVolume
FROM CM_DATA
WHERE ((CM_DATA.[Week] In (SELECT Max(CM_DATA.[Week])-2 FROM CM_DATA)) OR (CM_DATA.[Week] In (SELECT Max(CM_DATA.[Week]) FROM CM_DATA)))
GROUP BY CM_DATA.[Week];

 

[CoffeeGuru]

Hi Paul

Thanks for that, I will try and make it fit.
I assume [Agents] should = [CM_DATA]

The result I got from your code was

Week SumOfVolume
51 40928
52 28678

Hmmm Due to the WHERE clause (last 2 weeks) the average for the whole of the calendar year is now wrong. The Query only works over the last 2 weeks.
I'm guessing that this query needs to be added to my main query as a sub query which for me is a bit of a nightmare

Martin

 

[pr2-eugin]

I assume [Agents] should = [CM_DATA]

Oops ! Sorry Yes it is [CM_DATA].

Hmmm Due to the WHERE clause (last 2 weeks) the average for the whole of the calendar year is now wrong. The Query only works over the last 2 weeks.
I'm guessing that this query needs to be added to my main query as a sub query

Explain a little bit more !

 

[CoffeeGuru]

My end output needs be
numbers in brackets = test data

AVERAGE [CM_DATA].[VOLUME] (5.786)
ADD
SUM [CM_DATA].[VOLUME] for last two weeks (1+3=4)
MULTIPLY
7
SUBTRACT
[CM_DATA].[STOCK] (2)

so the formula will be like this:
ReOrder: =(5.786 + 4) * 7 - 2
Reorder: = 67 (fixed at 0dp)


AVERAGE [CM_DATA].[VOLUME] sums all the available units sold ([VOLUME]) for the selected calendar year [CM_DATA].[YEAR]
This is set in table [CURRENT_USER].[YEAR]
Table [CURRENT_USER], is a 1 row table containing all the users selection parameters that are used throughout my queries.
My data is stored on SQL Server.

Plus a few other fields from joined tables which if I stick with Access should be simple.

Martin

 

[Brianwarnock]

I did not understand the response to me as I did not mention Last or it's performance. I never use Last as it has no relevance in an active relational database

Excuse my use of reserved words

Queryi
Select max(week) as maxofweekno
From cm_data

Query2
Select sum(volume) as sumofvolume
From cm_data,query1
Where cm_data.week <= query1.maxofweek-1