Crosstab with complex calc field

[Guest]

Apologies in advance for the long-windedness of this post, I don't know how
else to phrase it.

I am currently calculating this algorithm in Excel but am having to do it
item by item and figured I might be able to do all items at once in Access.

It involves a number of steps -
1. Get last 7 month os sales data in monthly buckets

2. Calculate 2 month rolling average ie avg(month-1,month-2),
avg(month-2,month-3), avg(month-3,month-4).... etc to end up with 6 figures

3. Assign a 'strength' to each of the figures based on (A>=50; B>=30; C>=15;
D>=10; E>=5) example - 6 averages 9, 26, 24, 21, 31, 13 would equal E, C, C,
C, B, D.

4.Count the no. of instances of each of the possible strengths. So in the
above example the number of instances of 'A' are 0, those of 'B' are 1, 'C'
are 3, 'D' are 1, 'E' are 1.
If the number of instances for a given strength and the strengths higher
than it is equal to or more than the parameter (default 5) then associate
that strength with the id. So in the above instance, strength <> A since
instances of A=1 <5; strength <> B since instances of A & B=2 <5. However
instances of A,B & C = 1+1+3 = 5 so the strength for this ID is 'C'

I have set up the following crosstab which will give me the last 7 months of
cheese data -

TRANSFORM Sum(TotalsQry.[#Cheeses]) AS [SumOf#Cheeses]
SELECT TotalsQry.LocCode, TotalsQry.RevItem, TotalsQry.ChWidth,
Sum(TotalsQry.[#Cheeses]) AS [Total Of #Cheeses]
FROM TotalsQry
WHERE (((TotalsQry.MonthDate)>DateSerial(Year(Date()), Month(Date())-8,1)))
GROUP BY TotalsQry.LocCode, TotalsQry.RevItem, TotalsQry.ChWidth
PIVOT TotalsQry.MonthDate;

Is it possible to incorporate the above mentioned algorithm calculation in
the crosstab or should I be pursuing a different design ?

 

[[MVP] S.Clark]

You are proabably on the right track, in that Access is the better home for
the data. I don't think that you can solve all of your algorithm needs in
one query, but through a series of queries, maybe even some action
queries(Make, Append, Update, Delete), you can most likely achieve your
final goal.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting