Repost :Crosstab with complex calc field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Not sure if I have broken any rules by reposting but I assumed that I did not
get any replies due to the timing of the first post, Saturday was probably a
bad choice.

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 ?
 
Hi,

I don't have a solution, but my first thought
is that you've gone "excel-wide" too soon
with your crosstab. Access queries likes "thin."

What values can MonthDate be? If they are not
just the first of a month/year, what does your data
look like if you also group on month and year in
a simple "thin" totals query? The purpose being
that you want some way to be able to average
"this month" with "last month."

SELECT
T.LocCode,
T.RevItem,
T.ChWidth,
Sum(T.[#Cheeses]) AS SumTotal,
DateSerial(Year(T.MonthDate),Month(T.MonthDate),1) As MY
FROM TotalsQry As T
WHERE
(((T.MonthDate)>DateSerial(Year(Date()), Month(Date())-8,1)))
GROUP BY
T.LocCode,
T.RevItem,
T.ChWidth,
DateSerial(Year(T.MonthDate),Month(T.MonthDate),1);

I don't know your original table(s), but I wonder if
the above could not just as easily be performed on
the original data (no need for "TotalsQry" possibly?)

So you bring the above query into query designer
*twice*, right-mouse click on them, choose "Properties,"
give one an alias of "TM" (for "ThisMonth") and the other an alias
of "LM" (for "LastMonth").

LeftJoin ThisMonth to LastMonth on
LocCode,
RevItem,
ChWidth

Hold down the Ctl key and select
LocCode,
RevItem,
ChWidth,
SumTotal,
MY
in ThisMonth virtual table,
then drag-and-drop selected fields
down to the grid.

In Criteria row under ThisMonth.MY column, type

DateSerial(Year(LM.MY),Month(LM.MY)+1,1)

In another empty column of grid, type in Field row

RlngAvg: (Nz(TM.SumTotal,0) + Nz(LM.SumTotal,0))/2

Possibly next build a "tblStrength"

Strength MinS MaxS
A 50 9999999
B 30 49
C 15 29
D 10 14
E 0 9

If above query were "qryRlngAvg,"

SELECT
Q.LocCode,
Q.RevItem,
Q.ChWidth,
Q.SumTotal,
Q.MY,
Q.RlngAv,
T.Strength
FROM
qryRlngAvg As Q,
tblStrength As T
WHERE
Q.RlngAvg >= T.MinS
AND
Q.RlngAvg <= T.MaxS;

*If the above is correct,* there might now be
several options......
count strengths over a group...
save into a table to further manipulate...
apply a "Steve Dassin-like" crosstab..

The important thing is that you now
have some "thin" data to continue working
with.

I apologise that I have to get ready for work
now, but hope that might get you started
(especially if no one else responds).

good luck,

gary


SthOzNewbie said:
Not sure if I have broken any rules by reposting but I assumed that I did
not
get any replies due to the timing of the first post, Saturday was probably
a
bad choice.

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 ?
Click to expand...
 
Actually, I believe you want *Null* for conditions
that you don't have 2 months:

RlngAvg: (TM.SumTotal + LM.SumTotal)/2)
 
Gary,

Thanks for taking the time to reply. "thin" is definately the way to go and
ways of doing this will become more apparent to me as I spend time with
Access and forget my evil Excel ways. Hopefully you're monitoring for this
reply.

I got as far as the second query - qryRlgAvg and discovered a missing peice
that may affect the end result. My existing calculation for this algorithm in
Excel will produce 6 averages even if there are not a full 7 sequential
months of sales data. This is so that I can assign 6 'strengths' to complete
the final part of the equation as it has been given to me ie count the A's,
count the B's count the C's etc.

I suspect the first query will need to be modified to show a sumTotal for
each month even if there is no data for that particular month so that I can
show a value for each of the 6 avg's even if it =0.

Any ideas how I could do that or am I still trying to think too Excel ?

Here is the current SQL-

SELECT [TotalsQry].[LocCode], [TotalsQry].[RevItem], [TotalsQry].[ChWidth],
Sum([TotalsQry]![#Cheeses]) AS SumTotal,
DateSerial(Year([TotalsQry]![MonthDate]),Month([TotalsQry]![MonthDate]),1) AS
MY
FROM TotalsQry
WHERE ((([TotalsQry]![MonthDate])>DateSerial(Year(Date()),Month(Date())-8,1)))
GROUP BY [TotalsQry].[LocCode], [TotalsQry].[RevItem],
[TotalsQry].[ChWidth],
DateSerial(Year([TotalsQry]![MonthDate]),Month([TotalsQry]![MonthDate]),1);

I have persisted with the TotalsQry as an intermediary for now as it is a
bit cleaner for me as far as tracing the code goes (still trying to get my
head around SQL syntax)

Thanks,


Gary Walter said:
Hi,

I don't have a solution, but my first thought
is that you've gone "excel-wide" too soon
with your crosstab. Access queries likes "thin."

What values can MonthDate be? If they are not
just the first of a month/year, what does your data
look like if you also group on month and year in
a simple "thin" totals query? The purpose being
that you want some way to be able to average
"this month" with "last month."

SELECT
T.LocCode,
T.RevItem,
T.ChWidth,
Sum(T.[#Cheeses]) AS SumTotal,
DateSerial(Year(T.MonthDate),Month(T.MonthDate),1) As MY
FROM TotalsQry As T
WHERE
(((T.MonthDate)>DateSerial(Year(Date()), Month(Date())-8,1)))
GROUP BY
T.LocCode,
T.RevItem,
T.ChWidth,
DateSerial(Year(T.MonthDate),Month(T.MonthDate),1);

I don't know your original table(s), but I wonder if
the above could not just as easily be performed on
the original data (no need for "TotalsQry" possibly?)

So you bring the above query into query designer
*twice*, right-mouse click on them, choose "Properties,"
give one an alias of "TM" (for "ThisMonth") and the other an alias
of "LM" (for "LastMonth").

LeftJoin ThisMonth to LastMonth on
LocCode,
RevItem,
ChWidth

Hold down the Ctl key and select
LocCode,
RevItem,
ChWidth,
SumTotal,
MY
in ThisMonth virtual table,
then drag-and-drop selected fields
down to the grid.

In Criteria row under ThisMonth.MY column, type

DateSerial(Year(LM.MY),Month(LM.MY)+1,1)

In another empty column of grid, type in Field row

RlngAvg: (Nz(TM.SumTotal,0) + Nz(LM.SumTotal,0))/2

Possibly next build a "tblStrength"

Strength MinS MaxS
A 50 9999999
B 30 49
C 15 29
D 10 14
E 0 9

If above query were "qryRlngAvg,"

SELECT
Q.LocCode,
Q.RevItem,
Q.ChWidth,
Q.SumTotal,
Q.MY,
Q.RlngAv,
T.Strength
FROM
qryRlngAvg As Q,
tblStrength As T
WHERE
Q.RlngAvg >= T.MinS
AND
Q.RlngAvg <= T.MaxS;

*If the above is correct,* there might now be
several options......
count strengths over a group...
save into a table to further manipulate...
apply a "Steve Dassin-like" crosstab..

The important thing is that you now
have some "thin" data to continue working
with.

I apologise that I have to get ready for work
now, but hope that might get you started
(especially if no one else responds).

good luck,

gary


SthOzNewbie said:
Not sure if I have broken any rules by reposting but I assumed that I did
not
get any replies due to the timing of the first post, Saturday was probably
a
bad choice.

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 ?
Click to expand...
Click to expand...
 
:
I got as far as the second query - qryRlgAvg and discovered a missing peice
that may affect the end result. My existing calculation for this algorithm in
Excel will produce 6 averages even if there are not a full 7 sequential
months of sales data. This is so that I can assign 6 'strengths' to complete
the final part of the equation as it has been given to me ie count the A's,
count the B's count the C's etc.

I suspect the first query will need to be modified to show a sumTotal for
each month even if there is no data for that particular month so that I can
show a value for each of the 6 avg's even if it =0.

Here is the current SQL-

SELECT [TotalsQry].[LocCode], [TotalsQry].[RevItem], [TotalsQry].[ChWidth],
Sum([TotalsQry]![#Cheeses]) AS SumTotal,
DateSerial(Year([TotalsQry]![MonthDate]),Month([TotalsQry]![MonthDate]),1) AS
MY
FROM TotalsQry
WHERE ((([TotalsQry]![MonthDate])>DateSerial(Year(Date()),Month(Date())-8,1)))
GROUP BY [TotalsQry].[LocCode], [TotalsQry].[RevItem],
[TotalsQry].[ChWidth],
DateSerial(Year([TotalsQry]![MonthDate]),Month([TotalsQry]![MonthDate]),1);
Click to expand...
Hi,

There may be some more-technical term for this situation,
but I call it "creating something from nothing."

It almost always involves an outer join of "everything possible"
with the real data. Where the real data does not match up,
you NZ a result.

IMHO, this should be done in your TotalsQry.

I believe you want to create a "virtual table" of
every distinct group (possibly LocCode, RevItem, ChWidth?)
and an "associated date" for the time span of 7 months.

Then LEFT JOIN this "virtual table" to your original
data matching on "dates" and Nz'ing 0 values for #Cheeses
where there is no match.

Please provide the SQL for your TotalsQry (plus pertinent table(s),
fields, data types, and example data if possible) and I will try to get
back to help you.

(BTW, I don't believe your use of "bangs," i.e. "!" are
properly applied. I might suggest using "." and restrict the use
of bangs only for objects you have created ...like Forms!frmX!txt1)

*As a guess*, you might have a table "Production" with fields:

LocCode
RevItem
ChWidth
ProdDate
NumCheeses <--BTW, it always end up a bad idea (for me)
when I start out design using any punctuation
(or spaces) in a field name -- like "#Cheeses."

We want a qry that enumerates the last 7 months for each group
(this will be the "virtual table" that we LEFT JOIN to real data).

Let's start with the date part.

Create a table "Iotas" with one Long field "Iota"
and 7 records whose values go from 0 to 6.

Iota
0
1
2
3
4
5
6

So..to enumerate "dates" from the current datemonth
back to the datemonth 6 months ago ,

qryEnumDates

SELECT
DateSerial(Year(Date()),Month(Date())-Iota,1) AS BeginMonth,
DateSerial(Year(Date()),Month(Date())-[Iota]+1,0) AS EndMonth
FROM Iotas;


producing:

qryEnumDates BeginMonth EndMonth
7/1/2005 7/31/2005
6/1/2005 6/30/2005
5/1/2005 5/31/2005
4/1/2005 4/30/2005
3/1/2005 3/31/2005
2/1/2005 2/28/2005
1/1/2005 1/31/2005


lets assume some simple data for data table

Production ProdID LocCode RevItem ChWidth ProdDate NumCheeses
1 A 1 5 1/6/2005 20
2 B 1 5 1/6/2005 25
3 A 1 5 2/9/2005 16
4 B 2 9 2/9/2005 14
5 A 1 5 3/12/2005 31
6 B 1 5 3/12/2005 49
7 A 3 7 4/7/2005 37
8 B 1 5 4/7/2005 42
9 A 1 5 5/15/2005 25
10 B 4 3 5/15/2005 16
11 A 1 5 6/20/2005 37
12 B 1 5 6/20/2005 41
13 A 1 5 7/3/2005 18


what are our distinct "groups?"

SELECT DISTINCT
Production.LocCode,
Production.RevItem,
Production.ChWidth
FROM Production;

qryDistinctGroups LocCode RevItem ChWidth
A 1 5
A 3 7
B 1 5
B 2 9
B 4 3


Our example data gives us 5 distinct groups.
We want our query to produce a "virtual table"
to give us 7 "date" records for each group...
we do this with a Cartesian Join:

SELECT
qryDistinctGroups.LocCode,
qryDistinctGroups.RevItem,
qryDistinctGroups.ChWidth,
qryEnumDates.BeginMonth,
qryEnumDates.EndMonth
FROM qryDistinctGroups, qryEnumDates;


qryVT LocCode RevItem ChWidth BeginMonth EndMonth
A 1 5 7/1/2005 7/31/2005
A 1 5 6/1/2005 6/30/2005
A 1 5 5/1/2005 5/31/2005
A 1 5 4/1/2005 4/30/2005
A 1 5 3/1/2005 3/31/2005
A 1 5 2/1/2005 2/28/2005
A 1 5 1/1/2005 1/31/2005
A 3 7 7/1/2005 7/31/2005
A 3 7 6/1/2005 6/30/2005
A 3 7 5/1/2005 5/31/2005
A 3 7 4/1/2005 4/30/2005
A 3 7 3/1/2005 3/31/2005
A 3 7 2/1/2005 2/28/2005
A 3 7 1/1/2005 1/31/2005
B 1 5 7/1/2005 7/31/2005
B 1 5 6/1/2005 6/30/2005
B 1 5 5/1/2005 5/31/2005
B 1 5 4/1/2005 4/30/2005
B 1 5 3/1/2005 3/31/2005
B 1 5 2/1/2005 2/28/2005
B 1 5 1/1/2005 1/31/2005
B 2 9 7/1/2005 7/31/2005
B 2 9 6/1/2005 6/30/2005
B 2 9 5/1/2005 5/31/2005
B 2 9 4/1/2005 4/30/2005
B 2 9 3/1/2005 3/31/2005
B 2 9 2/1/2005 2/28/2005
B 2 9 1/1/2005 1/31/2005
B 4 3 7/1/2005 7/31/2005
B 4 3 6/1/2005 6/30/2005
B 4 3 5/1/2005 5/31/2005
B 4 3 4/1/2005 4/30/2005
B 4 3 3/1/2005 3/31/2005
B 4 3 2/1/2005 2/28/2005
B 4 3 1/1/2005 1/31/2005


the above could have been accomplished in one query,
but maybe easier to understand this way.

So now we have our "virtual table,"
all that is left is to LEFT JOIN this to "Production"
and sum NumCheeses.

SELECT
Q.LocCode,
Q.RevItem,
Q.ChWidth,
Q.BeginMonth AS MY,
Sum(Nz([NumCheeses],0)) AS SumTotal
FROM qryVT AS Q LEFT JOIN Production AS P
ON
(Q.ChWidth = P.ChWidth)
AND
(Q.RevItem = P.RevItem)
AND
(Q.LocCode = P.LocCode)
AND
(((P.ProdDate)>=[Q].[BeginMonth] And (P.ProdDate)<[Q].[EndMonth]+1))
GROUP BY Q.LocCode, Q.RevItem, Q.ChWidth, Q.BeginMonth;

producing "something from nothing":

qryTotals LocCode RevItem ChWidth MY SumTotal
A 1 5 1/1/2005 20
A 1 5 2/1/2005 16
A 1 5 3/1/2005 31
A 1 5 4/1/2005 0
A 1 5 5/1/2005 25
A 1 5 6/1/2005 37
A 1 5 7/1/2005 18
A 3 7 1/1/2005 0
A 3 7 2/1/2005 0
A 3 7 3/1/2005 0
A 3 7 4/1/2005 37
A 3 7 5/1/2005 0
A 3 7 6/1/2005 0
A 3 7 7/1/2005 0
B 1 5 1/1/2005 25
B 1 5 2/1/2005 0
B 1 5 3/1/2005 49
B 1 5 4/1/2005 42
B 1 5 5/1/2005 0
B 1 5 6/1/2005 41
B 1 5 7/1/2005 0
B 2 9 1/1/2005 0
B 2 9 2/1/2005 14
B 2 9 3/1/2005 0
B 2 9 4/1/2005 0
B 2 9 5/1/2005 0
B 2 9 6/1/2005 0
B 2 9 7/1/2005 0
B 4 3 1/1/2005 0
B 4 3 2/1/2005 0
B 4 3 3/1/2005 0
B 4 3 4/1/2005 0
B 4 3 5/1/2005 16
B 4 3 6/1/2005 0
B 4 3 7/1/2005 0


Again....I have to get ready for work so hope
this will be enough for now...

(I did not verify numbers so I may have made
some dopey mistake)

good luck,

gary
 
Gary,

Thanks again for taking the time to spell out the detail, it has been very
helpful. I should point out that the original data comes from a download file
from our ERP system but is not raw data but rather has already been partially
summarised by running through a report and therefore is "de-normalised" to an
extent. For instance, I don't get a transaction date, rather the data is
summarised by fiscal week and year. Hence I have tried to "normalise" it by
setting up a table that cross references the fiscal week and year with a
single date which is why I refer to the source data below as coming from a
query.

I've followed the steps you outlined and here are the results -

Source Data - qryNSalesData

ID LocCode ChWidth WeekDate RevItem QtyCheesesNormLength
2205 02 250 13-Dec-04 01533 6 1000
11947 02 250 07-Mar-05 01533 24 1000

Virtual Table - results as expected

LocCode RevItem ChWidth NormLength BeginMonth EndMonth
02 01533 250 1000 01-Oct-04 31-Oct-04
02 01533 250 1000 01-Nov-04 30-Nov-04
02 01533 250 1000 01-Dec-04 31-Dec-04
02 01533 250 1000 01-Jan-05 31-Jan-05
02 01533 250 1000 01-Feb-05 28-Feb-05
02 01533 250 1000 01-Mar-05 31-Mar-05
02 01533 250 1000 01-Apr-05 30-Apr-05
02 01533 250 1000 01-May-05 31-May-05
02 01533 250 1000 01-Jun-05 30-Jun-05

SQL-
SELECT [qryDistGrps].[LocCode], [qryDistGrps].[RevItem],
[qryDistGrps].[ChWidth], [qryDistGrps].[NormLength],
[qryEnumDates].[BeginMonth], [qryEnumDates].[EndMonth]
FROM qryDistGrps, qryEnumDates
ORDER BY [qryDistGrps].[LocCode], [qryDistGrps].[RevItem],
[qryEnumDates].[BeginMonth], [qryEnumDates].[EndMonth];

Totals Query - still excludes 0 values

LocCode RevItem ChWidth NormLength MY SumTotal
02 01533 250 1000 01-Dec-04 6
02 01533 250 1000 01-Mar-05 24

SQL-
SELECT [qryVirtTbl].[LocCode], [qryVirtTbl].[RevItem],
[qryVirtTbl].[ChWidth], [qryVirtTbl].[NormLength], [qryVirtTbl].[BeginMonth]
AS MY, Sum(Nz([qryNSalesData].[QtyCheeses],0)) AS SumTotal
FROM qryVirtTbl LEFT JOIN qryNSalesData ON
([qryVirtTbl].[NormLength]=[qryNSalesData].[NormLength]) AND
([qryVirtTbl].[RevItem]=[qryNSalesData].[RevItem]) AND
([qryVirtTbl].[ChWidth]=[qryNSalesData].[ChWidth]) AND
([qryVirtTbl].[LocCode]=[qryNSalesData].[LocCode]) AND
((([qryNSalesData].[WeekDate])>=[qryVirtTbl].[BeginMonth] And
([qryNSalesData].[WeekDate])<[qryVirtTbl].[EndMonth]+1))
GROUP BY [qryVirtTbl].[LocCode], [qryVirtTbl].[ChWidth],
[qryVirtTbl].[RevItem], [qryVirtTbl].[NormLength], [qryVirtTbl].[BeginMonth]
ORDER BY [qryVirtTbl].[LocCode], [qryVirtTbl].[RevItem];

I'm pretty sure I followed your inst to the letter. If I take out the date
comparison clause I get the below -

LocCode RevItem ChWidth NormLengthMY SumTotal
02 01533 250 1000 01-Oct-04 30
02 01533 250 1000 01-Nov-04 30
02 01533 250 1000 01-Dec-04 30
02 01533 250 1000 01-Jan-05 30
02 01533 250 1000 01-Feb-05 30
02 01533 250 1000 01-Mar-05 30
02 01533 250 1000 01-Apr-05 30
02 01533 250 1000 01-May-05 30
02 01533 250 1000 01-Jun-05 30

Have I missed something ?

Thanks

Gary Walter said:
:
I got as far as the second query - qryRlgAvg and discovered a missing peice
that may affect the end result. My existing calculation for this algorithm in
Excel will produce 6 averages even if there are not a full 7 sequential
months of sales data. This is so that I can assign 6 'strengths' to complete
the final part of the equation as it has been given to me ie count the A's,
count the B's count the C's etc.

I suspect the first query will need to be modified to show a sumTotal for
each month even if there is no data for that particular month so that I can
show a value for each of the 6 avg's even if it =0.

Here is the current SQL-

SELECT [TotalsQry].[LocCode], [TotalsQry].[RevItem], [TotalsQry].[ChWidth],
Sum([TotalsQry]![#Cheeses]) AS SumTotal,
DateSerial(Year([TotalsQry]![MonthDate]),Month([TotalsQry]![MonthDate]),1) AS
MY
FROM TotalsQry
WHERE ((([TotalsQry]![MonthDate])>DateSerial(Year(Date()),Month(Date())-8,1)))
GROUP BY [TotalsQry].[LocCode], [TotalsQry].[RevItem],
[TotalsQry].[ChWidth],
DateSerial(Year([TotalsQry]![MonthDate]),Month([TotalsQry]![MonthDate]),1);
Click to expand...
Hi,

There may be some more-technical term for this situation,
but I call it "creating something from nothing."

It almost always involves an outer join of "everything possible"
with the real data. Where the real data does not match up,
you NZ a result.

IMHO, this should be done in your TotalsQry.

I believe you want to create a "virtual table" of
every distinct group (possibly LocCode, RevItem, ChWidth?)
and an "associated date" for the time span of 7 months.

Then LEFT JOIN this "virtual table" to your original
data matching on "dates" and Nz'ing 0 values for #Cheeses
where there is no match.

Please provide the SQL for your TotalsQry (plus pertinent table(s),
fields, data types, and example data if possible) and I will try to get
back to help you.

(BTW, I don't believe your use of "bangs," i.e. "!" are
properly applied. I might suggest using "." and restrict the use
of bangs only for objects you have created ...like Forms!frmX!txt1)

*As a guess*, you might have a table "Production" with fields:

LocCode
RevItem
ChWidth
ProdDate
NumCheeses <--BTW, it always end up a bad idea (for me)
when I start out design using any punctuation
(or spaces) in a field name -- like "#Cheeses."

We want a qry that enumerates the last 7 months for each group
(this will be the "virtual table" that we LEFT JOIN to real data).

Let's start with the date part.

Create a table "Iotas" with one Long field "Iota"
and 7 records whose values go from 0 to 6.

Iota
0
1
2
3
4
5
6

So..to enumerate "dates" from the current datemonth
back to the datemonth 6 months ago ,

qryEnumDates

SELECT
DateSerial(Year(Date()),Month(Date())-Iota,1) AS BeginMonth,
DateSerial(Year(Date()),Month(Date())-[Iota]+1,0) AS EndMonth
FROM Iotas;


producing:

qryEnumDates BeginMonth EndMonth
7/1/2005 7/31/2005
6/1/2005 6/30/2005
5/1/2005 5/31/2005
4/1/2005 4/30/2005
3/1/2005 3/31/2005
2/1/2005 2/28/2005
1/1/2005 1/31/2005


lets assume some simple data for data table

Production ProdID LocCode RevItem ChWidth ProdDate NumCheeses
1 A 1 5 1/6/2005 20
2 B 1 5 1/6/2005 25
3 A 1 5 2/9/2005 16
4 B 2 9 2/9/2005 14
5 A 1 5 3/12/2005 31
6 B 1 5 3/12/2005 49
7 A 3 7 4/7/2005 37
8 B 1 5 4/7/2005 42
9 A 1 5 5/15/2005 25
10 B 4 3 5/15/2005 16
11 A 1 5 6/20/2005 37
12 B 1 5 6/20/2005 41
13 A 1 5 7/3/2005 18


what are our distinct "groups?"

SELECT DISTINCT
Production.LocCode,
Production.RevItem,
Production.ChWidth
FROM Production;

qryDistinctGroups LocCode RevItem ChWidth
A 1 5
A 3 7
B 1 5
B 2 9
B 4 3


Our example data gives us 5 distinct groups.
We want our query to produce a "virtual table"
to give us 7 "date" records for each group...
we do this with a Cartesian Join:

SELECT
qryDistinctGroups.LocCode,
qryDistinctGroups.RevItem,
qryDistinctGroups.ChWidth,
qryEnumDates.BeginMonth,
qryEnumDates.EndMonth
FROM qryDistinctGroups, qryEnumDates;


qryVT LocCode RevItem ChWidth BeginMonth EndMonth
A 1 5 7/1/2005 7/31/2005
A 1 5 6/1/2005 6/30/2005
A 1 5 5/1/2005 5/31/2005
A 1 5 4/1/2005 4/30/2005
A 1 5 3/1/2005 3/31/2005
A 1 5 2/1/2005 2/28/2005
A 1 5 1/1/2005 1/31/2005
A 3 7 7/1/2005 7/31/2005
A 3 7 6/1/2005 6/30/2005
A 3 7 5/1/2005 5/31/2005
A 3 7 4/1/2005 4/30/2005
A 3 7 3/1/2005 3/31/2005
A 3 7 2/1/2005 2/28/2005
A 3 7 1/1/2005 1/31/2005
B 1 5 7/1/2005 7/31/2005
B 1 5 6/1/2005 6/30/2005
B 1 5 5/1/2005 5/31/2005
B 1 5 4/1/2005 4/30/2005
B 1 5 3/1/2005 3/31/2005
B 1 5 2/1/2005 2/28/2005
B 1 5 1/1/2005 1/31/2005
B 2 9 7/1/2005 7/31/2005
B 2 9 6/1/2005 6/30/2005
B 2 9 5/1/2005 5/31/2005
B 2 9 4/1/2005 4/30/2005
B 2 9 3/1/2005 3/31/2005
B 2 9 2/1/2005 2/28/2005
B 2 9 1/1/2005 1/31/2005
B 4 3 7/1/2005 7/31/2005
B 4 3 6/1/2005 6/30/2005
B 4 3 5/1/2005 5/31/2005
B 4 3 4/1/2005 4/30/2005
B 4 3 3/1/2005 3/31/2005
B 4 3 2/1/2005 2/28/2005
B 4 3 1/1/2005 1/31/2005


the above could have been accomplished in one query,
but maybe easier to understand this way.

So now we have our "virtual table,"
all that is left is to LEFT JOIN this to "Production"
and sum NumCheeses.

SELECT
Q.LocCode,
Q.RevItem,
Q.ChWidth,
Q.BeginMonth AS MY,
Sum(Nz([NumCheeses],0)) AS SumTotal
FROM qryVT AS Q LEFT JOIN Production AS P
ON
(Q.ChWidth = P.ChWidth)
AND
(Q.RevItem = P.RevItem)
AND
(Q.LocCode = P.LocCode)
AND
(((P.ProdDate)>=[Q].[BeginMonth] And (P.ProdDate)<[Q].[EndMonth]+1))
GROUP BY Q.LocCode, Q.RevItem, Q.ChWidth, Q.BeginMonth;

producing "something from nothing":

qryTotals LocCode RevItem ChWidth MY SumTotal
A 1 5 1/1/2005 20
A 1 5 2/1/2005 16
A 1 5 3/1/2005 31
A 1 5 4/1/2005 0
A 1 5 5/1/2005 25
A 1 5 6/1/2005 37
A 1 5 7/1/2005 18
A 3 7 1/1/2005 0
A 3 7 2/1/2005 0
A 3 7 3/1/2005 0
A 3 7 4/1/2005 37
A 3 7 5/1/2005 0
A 3 7 6/1/2005 0
A 3 7 7/1/2005 0
B 1 5 1/1/2005 25
B 1 5 2/1/2005 0
B 1 5 3/1/2005 49
B 1 5 4/1/2005 42
B 1 5 5/1/2005 0
B 1 5 6/1/2005 41
B 1 5 7/1/2005 0
B 2 9 1/1/2005 0
B 2 9 2/1/2005 14
B 2 9 3/1/2005 0
B 2 9 4/1/2005 0
B 2 9 5/1/2005 0
B 2 9 6/1/2005 0
B 2 9 7/1/2005 0
B 4 3 1/1/2005 0
B 4 3 2/1/2005 0
B 4 3 3/1/2005 0
B 4 3 4/1/2005 0
B 4 3 5/1/2005 16
B 4 3 6/1/2005 0
B 4 3 7/1/2005 0


Again....I have to get ready for work so hope
this will be enough for now...

(I did not verify numbers so I may have made
some dopey mistake)

good luck,

gary
Click to expand...
 
Hi,

Can you include computed field for BeginMonth
in original query "qryNSalesData"?

Or just try ("qryNSalesDataWithBeginMonth")

SELECT
ID,
LocCode,
ChWidth,
WeekDate,
DateSerial(Year(WeekDate),Month(WeekDate),1) AS BeginMonth,
RevItem,
QtyCheesesNormLength
FROM qryNSalesData;

{or just add one more field to your cross-reference table
called "BeginMonth" and run update query on it to set
"BeginMonth" using DateSerial on WeekDate field.
Then bring this extra field in qryNSalesData}

Either way, you would then LEFT JOIN your "virtual table"
to the query above and you can design in Query Designer
because you no longer have an non-equi join.

Do you know what I mean?

Start a new query and add "virtual table" query
and query above.

Give "virtual table" an alias of "VT"
and the sales query an alias of "S"

(I'll assume below that you have adjusted "qryNSalesData"
to include a date field "BeginMonth")

Drag-and-drop fields from VT over to matching S fields
(NormLength, RevItem, ChWidth, LocCode, BeginMonth).

Right-mouse click on each join, select "Properties", and choose
option to "include all of VT and only matching S" (probably
Option 2).

Your SQL should now look like:

SELECT
VT.LocCode,
VT.RevItem,
VT.ChWidth,
VT.NormLength,
VT.BeginMonth AS MY,
Sum(Nz(S.QtyCheeses,0)) AS SumTotal
FROM
qryVirtTbl AS VT
LEFT JOIN
qryNSalesData AS S
ON
(VT.NormLength = S.NormLength)
AND
(VT.RevItem = S.RevItem)
AND
(VT.ChWidth = S.ChWidth)
AND
(VT.LocCode = S.LocCode)
AND
(VT.BeginMonth = S.BeginMonth)
GROUP BY
VT.LocCode,
VT.ChWidth,
VT.RevItem,
VT.NormLength,
VT.BeginMonth
ORDER BY
VT.LocCode,
VT.RevItem;

Does this produce expected results?

SthOzNewbie said:
Thanks again for taking the time to spell out the detail, it has been very
helpful. I should point out that the original data comes from a download
file
from our ERP system but is not raw data but rather has already been
partially
summarised by running through a report and therefore is "de-normalised" to
an
extent. For instance, I don't get a transaction date, rather the data is
summarised by fiscal week and year. Hence I have tried to "normalise" it
by
setting up a table that cross references the fiscal week and year with a
single date which is why I refer to the source data below as coming from a
query.

I've followed the steps you outlined and here are the results -

Source Data - qryNSalesData

ID LocCode ChWidth WeekDate RevItem QtyCheesesNormLength
2205 02 250 13-Dec-04 01533 6 1000
11947 02 250 07-Mar-05 01533 24 1000

Virtual Table - results as expected

LocCode RevItem ChWidth NormLength BeginMonth EndMonth
02 01533 250 1000 01-Oct-04 31-Oct-04
02 01533 250 1000 01-Nov-04 30-Nov-04
02 01533 250 1000 01-Dec-04 31-Dec-04
02 01533 250 1000 01-Jan-05 31-Jan-05
02 01533 250 1000 01-Feb-05 28-Feb-05
02 01533 250 1000 01-Mar-05 31-Mar-05
02 01533 250 1000 01-Apr-05 30-Apr-05
02 01533 250 1000 01-May-05 31-May-05
02 01533 250 1000 01-Jun-05 30-Jun-05

SQL-
SELECT [qryDistGrps].[LocCode], [qryDistGrps].[RevItem],
[qryDistGrps].[ChWidth], [qryDistGrps].[NormLength],
[qryEnumDates].[BeginMonth], [qryEnumDates].[EndMonth]
FROM qryDistGrps, qryEnumDates
ORDER BY [qryDistGrps].[LocCode], [qryDistGrps].[RevItem],
[qryEnumDates].[BeginMonth], [qryEnumDates].[EndMonth];

Totals Query - still excludes 0 values

LocCode RevItem ChWidth NormLength MY SumTotal
02 01533 250 1000 01-Dec-04 6
02 01533 250 1000 01-Mar-05 24

SQL-
SELECT [qryVirtTbl].[LocCode], [qryVirtTbl].[RevItem],
[qryVirtTbl].[ChWidth], [qryVirtTbl].[NormLength],
[qryVirtTbl].[BeginMonth]
AS MY, Sum(Nz([qryNSalesData].[QtyCheeses],0)) AS SumTotal
FROM qryVirtTbl LEFT JOIN qryNSalesData ON
([qryVirtTbl].[NormLength]=[qryNSalesData].[NormLength]) AND
([qryVirtTbl].[RevItem]=[qryNSalesData].[RevItem]) AND
([qryVirtTbl].[ChWidth]=[qryNSalesData].[ChWidth]) AND
([qryVirtTbl].[LocCode]=[qryNSalesData].[LocCode]) AND
((([qryNSalesData].[WeekDate])>=[qryVirtTbl].[BeginMonth] And
([qryNSalesData].[WeekDate])<[qryVirtTbl].[EndMonth]+1))
GROUP BY [qryVirtTbl].[LocCode], [qryVirtTbl].[ChWidth],
[qryVirtTbl].[RevItem], [qryVirtTbl].[NormLength],
[qryVirtTbl].[BeginMonth]
ORDER BY [qryVirtTbl].[LocCode], [qryVirtTbl].[RevItem];

I'm pretty sure I followed your inst to the letter. If I take out the date
comparison clause I get the below -

LocCode RevItem ChWidth NormLengthMY SumTotal
02 01533 250 1000 01-Oct-04 30
02 01533 250 1000 01-Nov-04 30
02 01533 250 1000 01-Dec-04 30
02 01533 250 1000 01-Jan-05 30
02 01533 250 1000 01-Feb-05 30
02 01533 250 1000 01-Mar-05 30
02 01533 250 1000 01-Apr-05 30
02 01533 250 1000 01-May-05 30
02 01533 250 1000 01-Jun-05 30

Have I missed something ?

Thanks

Gary Walter said:
:
I got as far as the second query - qryRlgAvg and discovered a missing
peice
that may affect the end result. My existing calculation for this
algorithm in
Excel will produce 6 averages even if there are not a full 7 sequential
months of sales data. This is so that I can assign 6 'strengths' to
complete
the final part of the equation as it has been given to me ie count the
A's,
count the B's count the C's etc.

I suspect the first query will need to be modified to show a sumTotal
for
each month even if there is no data for that particular month so that I
can
show a value for each of the 6 avg's even if it =0.

Here is the current SQL-

SELECT [TotalsQry].[LocCode], [TotalsQry].[RevItem],
[TotalsQry].[ChWidth],
Sum([TotalsQry]![#Cheeses]) AS SumTotal,
DateSerial(Year([TotalsQry]![MonthDate]),Month([TotalsQry]![MonthDate]),1)
AS
MY
FROM TotalsQry
WHERE
((([TotalsQry]![MonthDate])>DateSerial(Year(Date()),Month(Date())-8,1)))
GROUP BY [TotalsQry].[LocCode], [TotalsQry].[RevItem],
[TotalsQry].[ChWidth],
DateSerial(Year([TotalsQry]![MonthDate]),Month([TotalsQry]![MonthDate]),1);
Click to expand...
Hi,

There may be some more-technical term for this situation,
but I call it "creating something from nothing."

It almost always involves an outer join of "everything possible"
with the real data. Where the real data does not match up,
you NZ a result.

IMHO, this should be done in your TotalsQry.

I believe you want to create a "virtual table" of
every distinct group (possibly LocCode, RevItem, ChWidth?)
and an "associated date" for the time span of 7 months.

Then LEFT JOIN this "virtual table" to your original
data matching on "dates" and Nz'ing 0 values for #Cheeses
where there is no match.

Please provide the SQL for your TotalsQry (plus pertinent table(s),
fields, data types, and example data if possible) and I will try to get
back to help you.

(BTW, I don't believe your use of "bangs," i.e. "!" are
properly applied. I might suggest using "." and restrict the use
of bangs only for objects you have created ...like Forms!frmX!txt1)

*As a guess*, you might have a table "Production" with fields:

LocCode
RevItem
ChWidth
ProdDate
NumCheeses <--BTW, it always end up a bad idea (for me)
when I start out design using any punctuation
(or spaces) in a field name -- like "#Cheeses."

We want a qry that enumerates the last 7 months for each group
(this will be the "virtual table" that we LEFT JOIN to real data).

Let's start with the date part.

Create a table "Iotas" with one Long field "Iota"
and 7 records whose values go from 0 to 6.

Iota
0
1
2
3
4
5
6

So..to enumerate "dates" from the current datemonth
back to the datemonth 6 months ago ,

qryEnumDates

SELECT
DateSerial(Year(Date()),Month(Date())-Iota,1) AS BeginMonth,
DateSerial(Year(Date()),Month(Date())-[Iota]+1,0) AS EndMonth
FROM Iotas;


producing:

qryEnumDates BeginMonth EndMonth
7/1/2005 7/31/2005
6/1/2005 6/30/2005
5/1/2005 5/31/2005
4/1/2005 4/30/2005
3/1/2005 3/31/2005
2/1/2005 2/28/2005
1/1/2005 1/31/2005


lets assume some simple data for data table

Production ProdID LocCode RevItem ChWidth ProdDate NumCheeses
1 A 1 5 1/6/2005 20
2 B 1 5 1/6/2005 25
3 A 1 5 2/9/2005 16
4 B 2 9 2/9/2005 14
5 A 1 5 3/12/2005 31
6 B 1 5 3/12/2005 49
7 A 3 7 4/7/2005 37
8 B 1 5 4/7/2005 42
9 A 1 5 5/15/2005 25
10 B 4 3 5/15/2005 16
11 A 1 5 6/20/2005 37
12 B 1 5 6/20/2005 41
13 A 1 5 7/3/2005 18


what are our distinct "groups?"

SELECT DISTINCT
Production.LocCode,
Production.RevItem,
Production.ChWidth
FROM Production;

qryDistinctGroups LocCode RevItem ChWidth
A 1 5
A 3 7
B 1 5
B 2 9
B 4 3


Our example data gives us 5 distinct groups.
We want our query to produce a "virtual table"
to give us 7 "date" records for each group...
we do this with a Cartesian Join:

SELECT
qryDistinctGroups.LocCode,
qryDistinctGroups.RevItem,
qryDistinctGroups.ChWidth,
qryEnumDates.BeginMonth,
qryEnumDates.EndMonth
FROM qryDistinctGroups, qryEnumDates;


qryVT LocCode RevItem ChWidth BeginMonth EndMonth
A 1 5 7/1/2005 7/31/2005
A 1 5 6/1/2005 6/30/2005
A 1 5 5/1/2005 5/31/2005
A 1 5 4/1/2005 4/30/2005
A 1 5 3/1/2005 3/31/2005
A 1 5 2/1/2005 2/28/2005
A 1 5 1/1/2005 1/31/2005
A 3 7 7/1/2005 7/31/2005
A 3 7 6/1/2005 6/30/2005
A 3 7 5/1/2005 5/31/2005
A 3 7 4/1/2005 4/30/2005
A 3 7 3/1/2005 3/31/2005
A 3 7 2/1/2005 2/28/2005
A 3 7 1/1/2005 1/31/2005
B 1 5 7/1/2005 7/31/2005
B 1 5 6/1/2005 6/30/2005
B 1 5 5/1/2005 5/31/2005
B 1 5 4/1/2005 4/30/2005
B 1 5 3/1/2005 3/31/2005
B 1 5 2/1/2005 2/28/2005
B 1 5 1/1/2005 1/31/2005
B 2 9 7/1/2005 7/31/2005
B 2 9 6/1/2005 6/30/2005
B 2 9 5/1/2005 5/31/2005
B 2 9 4/1/2005 4/30/2005
B 2 9 3/1/2005 3/31/2005
B 2 9 2/1/2005 2/28/2005
B 2 9 1/1/2005 1/31/2005
B 4 3 7/1/2005 7/31/2005
B 4 3 6/1/2005 6/30/2005
B 4 3 5/1/2005 5/31/2005
B 4 3 4/1/2005 4/30/2005
B 4 3 3/1/2005 3/31/2005
B 4 3 2/1/2005 2/28/2005
B 4 3 1/1/2005 1/31/2005


the above could have been accomplished in one query,
but maybe easier to understand this way.

So now we have our "virtual table,"
all that is left is to LEFT JOIN this to "Production"
and sum NumCheeses.

SELECT
Q.LocCode,
Q.RevItem,
Q.ChWidth,
Q.BeginMonth AS MY,
Sum(Nz([NumCheeses],0)) AS SumTotal
FROM qryVT AS Q LEFT JOIN Production AS P
ON
(Q.ChWidth = P.ChWidth)
AND
(Q.RevItem = P.RevItem)
AND
(Q.LocCode = P.LocCode)
AND
(((P.ProdDate)>=[Q].[BeginMonth] And (P.ProdDate)<[Q].[EndMonth]+1))
GROUP BY Q.LocCode, Q.RevItem, Q.ChWidth, Q.BeginMonth;

producing "something from nothing":

qryTotals LocCode RevItem ChWidth MY SumTotal
A 1 5 1/1/2005 20
A 1 5 2/1/2005 16
A 1 5 3/1/2005 31
A 1 5 4/1/2005 0
A 1 5 5/1/2005 25
A 1 5 6/1/2005 37
A 1 5 7/1/2005 18
A 3 7 1/1/2005 0
A 3 7 2/1/2005 0
A 3 7 3/1/2005 0
A 3 7 4/1/2005 37
A 3 7 5/1/2005 0
A 3 7 6/1/2005 0
A 3 7 7/1/2005 0
B 1 5 1/1/2005 25
B 1 5 2/1/2005 0
B 1 5 3/1/2005 49
B 1 5 4/1/2005 42
B 1 5 5/1/2005 0
B 1 5 6/1/2005 41
B 1 5 7/1/2005 0
B 2 9 1/1/2005 0
B 2 9 2/1/2005 14
B 2 9 3/1/2005 0
B 2 9 4/1/2005 0
B 2 9 5/1/2005 0
B 2 9 6/1/2005 0
B 2 9 7/1/2005 0
B 4 3 1/1/2005 0
B 4 3 2/1/2005 0
B 4 3 3/1/2005 0
B 4 3 4/1/2005 0
B 4 3 5/1/2005 16
B 4 3 6/1/2005 0
B 4 3 7/1/2005 0


Again....I have to get ready for work so hope
this will be enough for now...

(I did not verify numbers so I may have made
some dopey mistake)

good luck,

gary
Click to expand...
Click to expand...
 
Sorry...left out grouping process..

Drag-and drop the 5 fields from VT down
to a field row in grid. (You can select all
of the ones you need by holding down
CTRL key while you click on them, then
just drag down the selection to a field row
in grid.)

In VT.BeginMonth column, type in

MY:

at start of field row.

In top menu, click on Sigma icon to change
to a groupby query.

In a new column, in field row type

SumTotal: Nz(S.QtyCheeses,0)

and change its GroupBy to Sum.

Change sort row to Ascending under
columns in grid for LocCode and RevItem.

*NOW*
Your SQL should now look like:

SELECT
VT.LocCode,
VT.RevItem,
VT.ChWidth,
VT.NormLength,
VT.BeginMonth AS MY,
Sum(Nz(S.QtyCheeses,0)) AS SumTotal
FROM
qryVirtTbl AS VT
LEFT JOIN
qryNSalesData AS S
ON
(VT.NormLength = S.NormLength)
AND
(VT.RevItem = S.RevItem)
AND
(VT.ChWidth = S.ChWidth)
AND
(VT.LocCode = S.LocCode)
AND
(VT.BeginMonth = S.BeginMonth)
GROUP BY
VT.LocCode,
VT.ChWidth,
VT.RevItem,
VT.NormLength,
VT.BeginMonth
ORDER BY
VT.LocCode,
VT.RevItem;

Does this produce expected results?
Click to expand...

good luck,

gary
 
Gary,

Thanks, worked a treat. I already had a BeginMonth type cross reference in
the table I used to normalise the week/year data.

I'll give the rolling avg qry a try when I get to the office tomorrow.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab with complex calc field 1
Missing Row Field in Crosstab Query 1
Access Transposing data in Access? 8
Adding Calcs to a Crosstab? Or Form? 4
Count Instances 1
Excel Help with dates 2
Scrabble Value calculation for Welsh words 0
Crosstab Query 2

Back
Top