AVG Function in a Query

[Guest]

A table lists all the shipments by item, by order line, by date from the
year 2003 thru the present. One of the fields is a week number, YrWk, where
200420 denotes the 20th week of the year 2004.

The first query is a filtering query which limits the data to a specific
team code and time frame. In this instance the team is AD1 and the time
frame is >#12/13/2003#.

Next is a crosstab query based on the above query. This lists the data by
item number and sum of YrWl

The next query is based on the crosstab query above. This displays the item
number, the total for the timeframe, and the latest 15 weeks of shipments.

I would like to add a calculated filed in this query which will give the
AVERAGE weekly shipments of the last 15 weeks of shipments. Into a blank
filed I enter the following:

Ship15: Avg([200450]+[200449]+[200448]) and so on .

When I run the query I get the following message:

“You tried to execute a query that doesn’t include the specified expression
‘MtlNmbr’ as part of the aggregate function.â€

Is it possible to do what I want, or are there other steps which must be
taken?

The SQLs follow:

Query 1
SELECT tblShpngLines.MtlNmbr, tblShpngLines.PostingDate,
tblShpngLines.Descrip, tblShpngLines.Qty, tblItemData.MRPCntr,
tblShpngLines.YrWk
FROM tblShpngLines INNER JOIN tblItemData ON tblShpngLines.MtlNmbr =
tblItemData.MaterialNbr
WHERE (((tblShpngLines.PostingDate)>#12/13/2003#) AND
((tblItemData.MRPCntr)="AD1"))
ORDER BY tblShpngLines.YrWk DESC;

Crosstab query

TRANSFORM Sum(qryShpAndTeamAD1Only.Qty) AS [The Value]
SELECT qryShpAndTeamAD1Only.MtlNmbr, Sum(qryShpAndTeamAD1Only.Qty) AS [Total
Of Qty]
FROM qryShpAndTeamAD1Only
GROUP BY qryShpAndTeamAD1Only.MtlNmbr
ORDER BY qryShpAndTeamAD1Only.YrWk DESC
PIVOT qryShpAndTeamAD1Only.YrWk;

Last query
SELECT qryShpAndTeamAD1Only_Crosstab.MtlNmbr,
qryShpAndTeamAD1Only_Crosstab.[Total Of Qty],
qryShpAndTeamAD1Only_Crosstab.[200450],
qryShpAndTeamAD1Only_Crosstab.[200449],
qryShpAndTeamAD1Only_Crosstab.[200448],
qryShpAndTeamAD1Only_Crosstab.[200447],
qryShpAndTeamAD1Only_Crosstab.[200446],
qryShpAndTeamAD1Only_Crosstab.[200445],
qryShpAndTeamAD1Only_Crosstab.[200444],
qryShpAndTeamAD1Only_Crosstab.[200443],
qryShpAndTeamAD1Only_Crosstab.[200442],
qryShpAndTeamAD1Only_Crosstab.[200441],
qryShpAndTeamAD1Only_Crosstab.[200440],
qryShpAndTeamAD1Only_Crosstab.[200439],
qryShpAndTeamAD1Only_Crosstab.[200438],
qryShpAndTeamAD1Only_Crosstab.[200437], qryShpAndTeamAD1Only_Crosstab.[200436]
FROM qryShpAndTeamAD1Only_Crosstab;

 

[Duane Hookom]

It would help to see just your current structure with sample records and
what you eventually wanted to get out of it. You mention the crosstab "This
lists the data by item number and sum of YrWl" yet I don't see any "item
number" or "sum of YrWl". You also mention "Query 1" but provide the sql
possibly for "qryShpAndTeamAD1Only". You should also provide the data type
of at least YrWk. I don't think you need the first query since you can limit
the records directly in the crosstab.


--
Duane Hookom
MS Access MVP

A table lists all the shipments by item, by order line, by date from the
year 2003 thru the present. One of the fields is a week number, YrWk, where
200420 denotes the 20th week of the year 2004.

The first query is a filtering query which limits the data to a specific
team code and time frame. In this instance the team is AD1 and the time
frame is >#12/13/2003#.

Next is a crosstab query based on the above query. This lists the data by
item number and sum of YrWl

The next query is based on the crosstab query above. This displays the item
number, the total for the timeframe, and the latest 15 weeks of shipments.

I would like to add a calculated filed in this query which will give the
AVERAGE weekly shipments of the last 15 weeks of shipments. Into a blank
filed I enter the following:

Ship15: Avg([200450]+[200449]+[200448]) and so on .

When I run the query I get the following message:

"You tried to execute a query that doesn't include the specified expression
'MtlNmbr' as part of the aggregate function."

Is it possible to do what I want, or are there other steps which must be
taken?

The SQLs follow:

Query 1
SELECT tblShpngLines.MtlNmbr, tblShpngLines.PostingDate,
tblShpngLines.Descrip, tblShpngLines.Qty, tblItemData.MRPCntr,
tblShpngLines.YrWk
FROM tblShpngLines INNER JOIN tblItemData ON tblShpngLines.MtlNmbr =
tblItemData.MaterialNbr
WHERE (((tblShpngLines.PostingDate)>#12/13/2003#) AND
((tblItemData.MRPCntr)="AD1"))
ORDER BY tblShpngLines.YrWk DESC;

Crosstab query

TRANSFORM Sum(qryShpAndTeamAD1Only.Qty) AS [The Value]
SELECT qryShpAndTeamAD1Only.MtlNmbr, Sum(qryShpAndTeamAD1Only.Qty) AS [Total
Of Qty]
FROM qryShpAndTeamAD1Only
GROUP BY qryShpAndTeamAD1Only.MtlNmbr
ORDER BY qryShpAndTeamAD1Only.YrWk DESC
PIVOT qryShpAndTeamAD1Only.YrWk;

Last query
SELECT qryShpAndTeamAD1Only_Crosstab.MtlNmbr,
qryShpAndTeamAD1Only_Crosstab.[Total Of Qty],
qryShpAndTeamAD1Only_Crosstab.[200450],
qryShpAndTeamAD1Only_Crosstab.[200449],
qryShpAndTeamAD1Only_Crosstab.[200448],
qryShpAndTeamAD1Only_Crosstab.[200447],
qryShpAndTeamAD1Only_Crosstab.[200446],
qryShpAndTeamAD1Only_Crosstab.[200445],
qryShpAndTeamAD1Only_Crosstab.[200444],
qryShpAndTeamAD1Only_Crosstab.[200443],
qryShpAndTeamAD1Only_Crosstab.[200442],
qryShpAndTeamAD1Only_Crosstab.[200441],
qryShpAndTeamAD1Only_Crosstab.[200440],
qryShpAndTeamAD1Only_Crosstab.[200439],
qryShpAndTeamAD1Only_Crosstab.[200438],
qryShpAndTeamAD1Only_Crosstab.[200437], qryShpAndTeamAD1Only_Crosstab.[200436]
FROM qryShpAndTeamAD1Only_Crosstab;

 

[Guest]

Sorry for the delay in answering. I've tried to rethink the problem and put
it in clearer terms.
Seq MtlNmbr Descrip MvmntType PostingDate Qty UOM LocCurr UserName Year WkNum Day YrWk
22797 7207876 FAST-FIX ASSEMBLY 601 9/2/03 2 EA
79.72 PARENTE 2003 36 Tue 200336
The above is a single record from the table named tblShpngLines. There are
currently 415,695 records. This table does NOT contain individual item data,
except for description..

Seq Plant MaterialNbr MaterialDescr UOM Mtyp DChNbr Site MRPCntr Rlev ABCCode MRP Type MFGTechCode
4108 5000 7207876 FAST-FIX ASSEMBLY EA FERT 26984 M AD1 P A ZV 235
The above is a partial single record from the table named tblItemData.
There are currently 18,585 records.

MtlNmbr MaterialDescr MRPCntr Mtyp MvmntType PostingDate Qty UOM LocCurr Year WkNum Day YrWk
7207876 FAST-FIX ASSEMBLY AD1 FERT 601 11/12/04 2 EA
81.9 2004 46 Fri 200446
The above is a single record from a query, qryADOnlyShip, which uses the two
(2) tables. The link is MtlNmbr to MaterialNbr. The second, third and
fourth fields are from the latter table, tblItemData and all other fields
from the former table, TblShpngLines. The criteria used (the filter) is MRP
=â€AD1â€; Mtyp = “FERT†and PostingDate >#12/13/2003#. There are currently
220,096 lines or hits. It is this population I want to analyze.

I create a crosstab query using the Crosstab Query Wizard. I base the
crosstab query on qryADOnlyShip. I select the field MtlNmbr for the row
heading. I select YrWk field for the column headings. I select the Qty
field as the one to calculate, using the Sum function. I check Yes, include
row sums. After the query is created, I go into the design view and set the
Sort to Descending for the YrWk. This is so that the most recent weeks
appear beginning in column three, after the MtlNmbr in column one and the
Total of Qty in column two. This last piece of data is the total of all the
YrWk’s whose PostingDate is >#12/13/2003#.

A partial row of this crosstab looks like this:

MtlNmbr Total Of Qty 200450 200449 200448 200447 200446 200445 200444 200443
7207876 14040 416 271 263 275 297 290 344 274


My next query uses the crosstab query from above and a query selecting item
data for a specific team, AD1.
In the design view, I only use 15 weeks of the ship data as well as the
total qty. A partial row looks like this:

MtlNmbr MFGTechCode MFGTeamCode MaterialDescr Total Of
Qty 200450 200449 200448 200447 200446 200445 200444 200443
7207876 235 AD FAST-FIX ASSEMBLY 14040 416 271 263 275 297 290 344 274

I want to perform some calculations on the 15 weeks of ship data, such as
average weekly shipment (Avg), and standard deviation of the data (both StDev
and StDevP). If I place the following in a blank field then I do get the
average.

WklyAvg:
([200450]+[200449]+[200448]+[200447]+[200446]+[200445]+[200444]+[200443]+[200442]+[200441]+[200440]+[200439]+[200438]+[200437]+[200436])/15

However, this is really just the total of the 15 weeks divided by 15.

I thought I could use the Avg function such as WklyAvg:
Avg([200450]+[200449]+[200448]+[200447]+[200446]+[200445]+[200444]+[200443]+[200442]+[200441]+[200440]+[200439]+[200438]+[200437]+[200436])

This is when I get the message, “You tried to execute a query that doesn’t
include the specified expression ‘MtlNmbr’ as part of the aggregate function.â€

I don’t understand what the message is telling me.

SQL for the query in which I’m attempting to do calculations
SELECT qryADOnlyShip_Crosstab.MtlNmbr, qryADOnlyItemData.MFGTechCode,
qryADOnlyItemData.MFGTeamCode, qryADOnlyItemData.MaterialDescr,
qryADOnlyShip_Crosstab.[Total Of Qty], qryADOnlyShip_Crosstab.[200450],
qryADOnlyShip_Crosstab.[200449], qryADOnlyShip_Crosstab.[200448],
qryADOnlyShip_Crosstab.[200447], qryADOnlyShip_Crosstab.[200446],
qryADOnlyShip_Crosstab.[200445], qryADOnlyShip_Crosstab.[200444],
qryADOnlyShip_Crosstab.[200443], qryADOnlyShip_Crosstab.[200442],
qryADOnlyShip_Crosstab.[200441], qryADOnlyShip_Crosstab.[200440],
qryADOnlyShip_Crosstab.[200439], qryADOnlyShip_Crosstab.[200438],
qryADOnlyShip_Crosstab.[200437], qryADOnlyShip_Crosstab.[200436],
qryADOnlyItemData.MRPCntr, qryADOnlyItemData.ABCCode,
qryADOnlyItemData.MFGTechCodeDesc, qryADOnlyItemData.MFGTeamCodeDesc,
qryADOnlyItemData.LotSize, qryADOnlyItemData.RythTime,
qryADOnlyItemData.SterType, qryADOnlyItemData.LTWks,
qryADOnlyItemData.LTDays, qryADOnlyItemData.ZFactor, IIf([Total Of
Qty]>0,[Total Of Qty]/248,0) AS DlyAvg,
([200450]+[200449]+[200448]+[200447]+[200446]+[200445]+[200444]+[200443]+[200442]+[200441]+[200440]+[200439]+[200438]+[200437]+[200436])/15 AS WklyAvg
FROM qryADOnlyShip_Crosstab INNER JOIN qryADOnlyItemData ON
qryADOnlyShip_Crosstab.MtlNmbr = qryADOnlyItemData.MaterialNbr
WHERE (((qryADOnlyItemData.MFGTechCode) In ("235")))
`ORDER BY qryADOnlyShip_Crosstab.MtlNmbr, qryADOnlyItemData.MFGTechCode,
qryADOnlyItemData.MFGTeamCode;



SQL for the crosstab
TRANSFORM Sum(qryADOnlyShip.Qty) AS [The Value]
SELECT qryADOnlyShip.MtlNmbr, Sum(qryADOnlyShip.Qty) AS [Total Of Qty]
FROM qryADOnlyShip
GROUP BY qryADOnlyShip.MtlNmbr
ORDER BY qryADOnlyShip.YrWk DESC
PIVOT qryADOnlyShip.YrWk;

Not sure where to go next.

It would help to see just your current structure with sample records and
what you eventually wanted to get out of it. You mention the crosstab "This
lists the data by item number and sum of YrWl" yet I don't see any "item
number" or "sum of YrWl". You also mention "Query 1" but provide the sql
possibly for "qryShpAndTeamAD1Only". You should also provide the data type
of at least YrWk. I don't think you need the first query since you can limit
the records directly in the crosstab.


--
Duane Hookom
MS Access MVP

A table lists all the shipments by item, by order line, by date from the
year 2003 thru the present. One of the fields is a week number, YrWk, where
200420 denotes the 20th week of the year 2004.

The first query is a filtering query which limits the data to a specific
team code and time frame. In this instance the team is AD1 and the time
frame is >#12/13/2003#.

Next is a crosstab query based on the above query. This lists the data by
item number and sum of YrWl

The next query is based on the crosstab query above. This displays the item
number, the total for the timeframe, and the latest 15 weeks of shipments.

I would like to add a calculated filed in this query which will give the
AVERAGE weekly shipments of the last 15 weeks of shipments. Into a blank
filed I enter the following:

Ship15: Avg([200450]+[200449]+[200448]) and so on .

When I run the query I get the following message:

"You tried to execute a query that doesn't include the specified expression
'MtlNmbr' as part of the aggregate function."

Is it possible to do what I want, or are there other steps which must be
taken?

The SQLs follow:

Query 1
SELECT tblShpngLines.MtlNmbr, tblShpngLines.PostingDate,
tblShpngLines.Descrip, tblShpngLines.Qty, tblItemData.MRPCntr,
tblShpngLines.YrWk
FROM tblShpngLines INNER JOIN tblItemData ON tblShpngLines.MtlNmbr =
tblItemData.MaterialNbr
WHERE (((tblShpngLines.PostingDate)>#12/13/2003#) AND
((tblItemData.MRPCntr)="AD1"))
ORDER BY tblShpngLines.YrWk DESC;

Crosstab query

TRANSFORM Sum(qryShpAndTeamAD1Only.Qty) AS [The Value]
SELECT qryShpAndTeamAD1Only.MtlNmbr, Sum(qryShpAndTeamAD1Only.Qty) AS [Total
Of Qty]
FROM qryShpAndTeamAD1Only
GROUP BY qryShpAndTeamAD1Only.MtlNmbr
ORDER BY qryShpAndTeamAD1Only.YrWk DESC
PIVOT qryShpAndTeamAD1Only.YrWk;

Last query
SELECT qryShpAndTeamAD1Only_Crosstab.MtlNmbr,
qryShpAndTeamAD1Only_Crosstab.[Total Of Qty],
qryShpAndTeamAD1Only_Crosstab.[200450],
qryShpAndTeamAD1Only_Crosstab.[200449],
qryShpAndTeamAD1Only_Crosstab.[200448],
qryShpAndTeamAD1Only_Crosstab.[200447],
qryShpAndTeamAD1Only_Crosstab.[200446],
qryShpAndTeamAD1Only_Crosstab.[200445],
qryShpAndTeamAD1Only_Crosstab.[200444],
qryShpAndTeamAD1Only_Crosstab.[200443],
qryShpAndTeamAD1Only_Crosstab.[200442],
qryShpAndTeamAD1Only_Crosstab.[200441],
qryShpAndTeamAD1Only_Crosstab.[200440],
qryShpAndTeamAD1Only_Crosstab.[200439],
qryShpAndTeamAD1Only_Crosstab.[200438],
qryShpAndTeamAD1Only_Crosstab.[200437], qryShpAndTeamAD1Only_Crosstab.[200436]
FROM qryShpAndTeamAD1Only_Crosstab;

 

[Duane Hookom]

I'm really sorry but I just can't figure out what you have and where you
want to go. It is really confusing attempting to see your records due to
unequal spacing and wrapping. Do we really need to see all those fields? Are
they all important to the solution?

I asked the datat type of YrWk but you didn't provide it. Are Year, WkNum,
Day, and YrWk all calculated from the PostingDate field? If so, do you have
a good reason for storing calculated values?

--
Duane Hookom
MS Access MVP

Sorry for the delay in answering. I've tried to rethink the problem and put
it in clearer terms.
Seq MtlNmbr Descrip MvmntType PostingDate Qty UOM LocCurr UserName Year WkNum Day YrWk
22797 7207876 FAST-FIX ASSEMBLY 601 9/2/03 2 EA
79.72 PARENTE 2003 36 Tue 200336
The above is a single record from the table named tblShpngLines. There are
currently 415,695 records. This table does NOT contain individual item data,
except for description..

Seq Plant MaterialNbr MaterialDescr UOM Mtyp DChNbr Site MRPCntr Rlev ABCCode MRP Type MFGTechCode
4108 5000 7207876 FAST-FIX ASSEMBLY EA FERT 26984 M AD1 P A ZV 235
The above is a partial single record from the table named tblItemData.
There are currently 18,585 records.

MtlNmbr MaterialDescr MRPCntr Mtyp MvmntType PostingDate Qty UOM LocCurr Year WkNum Day YrWk
7207876 FAST-FIX ASSEMBLY AD1 FERT 601 11/12/04 2 EA
81.9 2004 46 Fri 200446
The above is a single record from a query, qryADOnlyShip, which uses the two
(2) tables. The link is MtlNmbr to MaterialNbr. The second, third and
fourth fields are from the latter table, tblItemData and all other fields
from the former table, TblShpngLines. The criteria used (the filter) is MRP
="AD1"; Mtyp = "FERT" and PostingDate >#12/13/2003#. There are currently
220,096 lines or hits. It is this population I want to analyze.

I create a crosstab query using the Crosstab Query Wizard. I base the
crosstab query on qryADOnlyShip. I select the field MtlNmbr for the row
heading. I select YrWk field for the column headings. I select the Qty
field as the one to calculate, using the Sum function. I check Yes, include
row sums. After the query is created, I go into the design view and set the
Sort to Descending for the YrWk. This is so that the most recent weeks
appear beginning in column three, after the MtlNmbr in column one and the
Total of Qty in column two. This last piece of data is the total of all the
YrWk's whose PostingDate is >#12/13/2003#.

A partial row of this crosstab looks like this:

MtlNmbr Total Of Qty 200450 200449 200448 200447 200446 200445 200444 200443
7207876 14040 416 271 263 275 297 290 344 274


My next query uses the crosstab query from above and a query selecting item
data for a specific team, AD1.
In the design view, I only use 15 weeks of the ship data as well as the
total qty. A partial row looks like this:

MtlNmbr MFGTechCode MFGTeamCode MaterialDescr Total Of
Qty 200450 200449 200448 200447 200446 200445 200444 200443
7207876 235 AD FAST-FIX ASSEMBLY 14040 416 271 263 275 297 290 344 274

I want to perform some calculations on the 15 weeks of ship data, such as
average weekly shipment (Avg), and standard deviation of the data (both StDev
and StDevP). If I place the following in a blank field then I do get the
average.

WklyAvg:
([200450]+[200449]+[200448]+[200447]+[200446]+[200445]+[200444]+[200443]+[20
0442]+[200441]+[200440]+[200439]+[200438]+[200437]+[200436])/15

However, this is really just the total of the 15 weeks divided by 15.

I thought I could use the Avg function such as WklyAvg:
Avg([200450]+[200449]+[200448]+[200447]+[200446]+[200445]+[200444]+[200443]+
[200442]+[200441]+[200440]+[200439]+[200438]+[200437]+[200436])

This is when I get the message, "You tried to execute a query that doesn't
include the specified expression 'MtlNmbr' as part of the aggregate function."

I don't understand what the message is telling me.

SQL for the query in which I'm attempting to do calculations
SELECT qryADOnlyShip_Crosstab.MtlNmbr, qryADOnlyItemData.MFGTechCode,
qryADOnlyItemData.MFGTeamCode, qryADOnlyItemData.MaterialDescr,
qryADOnlyShip_Crosstab.[Total Of Qty], qryADOnlyShip_Crosstab.[200450],
qryADOnlyShip_Crosstab.[200449], qryADOnlyShip_Crosstab.[200448],
qryADOnlyShip_Crosstab.[200447], qryADOnlyShip_Crosstab.[200446],
qryADOnlyShip_Crosstab.[200445], qryADOnlyShip_Crosstab.[200444],
qryADOnlyShip_Crosstab.[200443], qryADOnlyShip_Crosstab.[200442],
qryADOnlyShip_Crosstab.[200441], qryADOnlyShip_Crosstab.[200440],
qryADOnlyShip_Crosstab.[200439], qryADOnlyShip_Crosstab.[200438],
qryADOnlyShip_Crosstab.[200437], qryADOnlyShip_Crosstab.[200436],
qryADOnlyItemData.MRPCntr, qryADOnlyItemData.ABCCode,
qryADOnlyItemData.MFGTechCodeDesc, qryADOnlyItemData.MFGTeamCodeDesc,
qryADOnlyItemData.LotSize, qryADOnlyItemData.RythTime,
qryADOnlyItemData.SterType, qryADOnlyItemData.LTWks,
qryADOnlyItemData.LTDays, qryADOnlyItemData.ZFactor, IIf([Total Of
Qty]>0,[Total Of Qty]/248,0) AS DlyAvg,

([200450]+[200449]+[200448]+[200447]+[200446]+[200445]+[200444]+[200443]+[20
0442]+[200441]+[200440]+[200439]+[200438]+[200437]+[200436])/15 AS WklyAvg

FROM qryADOnlyShip_Crosstab INNER JOIN qryADOnlyItemData ON
qryADOnlyShip_Crosstab.MtlNmbr = qryADOnlyItemData.MaterialNbr
WHERE (((qryADOnlyItemData.MFGTechCode) In ("235")))
`ORDER BY qryADOnlyShip_Crosstab.MtlNmbr, qryADOnlyItemData.MFGTechCode,
qryADOnlyItemData.MFGTeamCode;



SQL for the crosstab
TRANSFORM Sum(qryADOnlyShip.Qty) AS [The Value]
SELECT qryADOnlyShip.MtlNmbr, Sum(qryADOnlyShip.Qty) AS [Total Of Qty]
FROM qryADOnlyShip
GROUP BY qryADOnlyShip.MtlNmbr
ORDER BY qryADOnlyShip.YrWk DESC
PIVOT qryADOnlyShip.YrWk;

Not sure where to go next.

It would help to see just your current structure with sample records and
what you eventually wanted to get out of it. You mention the crosstab "This
lists the data by item number and sum of YrWl" yet I don't see any "item
number" or "sum of YrWl". You also mention "Query 1" but provide the sql
possibly for "qryShpAndTeamAD1Only". You should also provide the data type
of at least YrWk. I don't think you need the first query since you can limit
the records directly in the crosstab.


--
Duane Hookom
MS Access MVP

A table lists all the shipments by item, by order line, by date from the
year 2003 thru the present. One of the fields is a week number, YrWk, where
200420 denotes the 20th week of the year 2004.

The first query is a filtering query which limits the data to a specific
team code and time frame. In this instance the team is AD1 and the time
frame is >#12/13/2003#.

Next is a crosstab query based on the above query. This lists the data by
item number and sum of YrWl

The next query is based on the crosstab query above. This displays

the
item

number, the total for the timeframe, and the latest 15 weeks of shipments.

I would like to add a calculated filed in this query which will give the
AVERAGE weekly shipments of the last 15 weeks of shipments. Into a blank
filed I enter the following:

Ship15: Avg([200450]+[200449]+[200448]) and so on .

When I run the query I get the following message:

"You tried to execute a query that doesn't include the specified expression
'MtlNmbr' as part of the aggregate function."

Is it possible to do what I want, or are there other steps which must be
taken?

The SQLs follow:

Query 1
SELECT tblShpngLines.MtlNmbr, tblShpngLines.PostingDate,
tblShpngLines.Descrip, tblShpngLines.Qty, tblItemData.MRPCntr,
tblShpngLines.YrWk
FROM tblShpngLines INNER JOIN tblItemData ON tblShpngLines.MtlNmbr =
tblItemData.MaterialNbr
WHERE (((tblShpngLines.PostingDate)>#12/13/2003#) AND
((tblItemData.MRPCntr)="AD1"))
ORDER BY tblShpngLines.YrWk DESC;

Crosstab query

TRANSFORM Sum(qryShpAndTeamAD1Only.Qty) AS [The Value]
SELECT qryShpAndTeamAD1Only.MtlNmbr, Sum(qryShpAndTeamAD1Only.Qty) AS [Total
Of Qty]
FROM qryShpAndTeamAD1Only
GROUP BY qryShpAndTeamAD1Only.MtlNmbr
ORDER BY qryShpAndTeamAD1Only.YrWk DESC
PIVOT qryShpAndTeamAD1Only.YrWk;

Last query
SELECT qryShpAndTeamAD1Only_Crosstab.MtlNmbr,
qryShpAndTeamAD1Only_Crosstab.[Total Of Qty],
qryShpAndTeamAD1Only_Crosstab.[200450],
qryShpAndTeamAD1Only_Crosstab.[200449],
qryShpAndTeamAD1Only_Crosstab.[200448],
qryShpAndTeamAD1Only_Crosstab.[200447],
qryShpAndTeamAD1Only_Crosstab.[200446],
qryShpAndTeamAD1Only_Crosstab.[200445],
qryShpAndTeamAD1Only_Crosstab.[200444],
qryShpAndTeamAD1Only_Crosstab.[200443],
qryShpAndTeamAD1Only_Crosstab.[200442],
qryShpAndTeamAD1Only_Crosstab.[200441],
qryShpAndTeamAD1Only_Crosstab.[200440],
qryShpAndTeamAD1Only_Crosstab.[200439],
qryShpAndTeamAD1Only_Crosstab.[200438],
qryShpAndTeamAD1Only_Crosstab.[200437], qryShpAndTeamAD1Only_Crosstab.[200436]
FROM qryShpAndTeamAD1Only_Crosstab;

 

[Guest]

The main table is:

FIELD NAME DATA TYPE
MtlNmbr Text
PostingDate Date/Time
Qty Number
Yr Text
WkNum Text
Day Text
YrWk Text

I am using a select query to limit the population of data I want to be used
in the crosstab query.

I am trying to calculate the average weekly shipments of the items for a
specific time period, 15 weeks.

I also want to calculate the StDev and the StDevP of the shipping data by
item for the same time period.

I thought I could do use built-in functions in a query to do these
calculations and then create a report based on said query.

I'm really sorry but I just can't figure out what you have and where you
want to go. It is really confusing attempting to see your records due to
unequal spacing and wrapping. Do we really need to see all those fields? Are
they all important to the solution?

I asked the datat type of YrWk but you didn't provide it. Are Year, WkNum,
Day, and YrWk all calculated from the PostingDate field? If so, do you have
a good reason for storing calculated values?

--
Duane Hookom
MS Access MVP

Sorry for the delay in answering. I've tried to rethink the problem and put
it in clearer terms.
Seq MtlNmbr Descrip MvmntType PostingDate Qty UOM LocCurr UserName Year WkNum Day YrWk
22797 7207876 FAST-FIX ASSEMBLY 601 9/2/03 2 EA
79.72 PARENTE 2003 36 Tue 200336
The above is a single record from the table named tblShpngLines. There are
currently 415,695 records. This table does NOT contain individual item data,
except for description..

Seq Plant MaterialNbr MaterialDescr UOM Mtyp DChNbr Site MRPCntr Rlev ABCCode MRP Type MFGTechCode
4108 5000 7207876 FAST-FIX ASSEMBLY EA FERT 26984 M AD1 P A ZV 235
The above is a partial single record from the table named tblItemData.
There are currently 18,585 records.

MtlNmbr MaterialDescr MRPCntr Mtyp MvmntType PostingDate Qty UOM LocCurr Year WkNum Day YrWk
7207876 FAST-FIX ASSEMBLY AD1 FERT 601 11/12/04 2 EA
81.9 2004 46 Fri 200446
The above is a single record from a query, qryADOnlyShip, which uses the two
(2) tables. The link is MtlNmbr to MaterialNbr. The second, third and
fourth fields are from the latter table, tblItemData and all other fields
from the former table, TblShpngLines. The criteria used (the filter) is MRP
="AD1"; Mtyp = "FERT" and PostingDate >#12/13/2003#. There are currently
220,096 lines or hits. It is this population I want to analyze.

I create a crosstab query using the Crosstab Query Wizard. I base the
crosstab query on qryADOnlyShip. I select the field MtlNmbr for the row
heading. I select YrWk field for the column headings. I select the Qty
field as the one to calculate, using the Sum function. I check Yes, include
row sums. After the query is created, I go into the design view and set the
Sort to Descending for the YrWk. This is so that the most recent weeks
appear beginning in column three, after the MtlNmbr in column one and the
Total of Qty in column two. This last piece of data is the total of all the
YrWk's whose PostingDate is >#12/13/2003#.

A partial row of this crosstab looks like this:

MtlNmbr Total Of Qty 200450 200449 200448 200447 200446 200445 200444 200443
7207876 14040 416 271 263 275 297 290 344 274


My next query uses the crosstab query from above and a query selecting item
data for a specific team, AD1.
In the design view, I only use 15 weeks of the ship data as well as the
total qty. A partial row looks like this:

MtlNmbr MFGTechCode MFGTeamCode MaterialDescr Total Of
Qty 200450 200449 200448 200447 200446 200445 200444 200443
7207876 235 AD FAST-FIX ASSEMBLY 14040 416 271 263 275 297 290 344 274

I want to perform some calculations on the 15 weeks of ship data, such as
average weekly shipment (Avg), and standard deviation of the data (both StDev
and StDevP). If I place the following in a blank field then I do get the
average.

WklyAvg:
([200450]+[200449]+[200448]+[200447]+[200446]+[200445]+[200444]+[200443]+[20
0442]+[200441]+[200440]+[200439]+[200438]+[200437]+[200436])/15

However, this is really just the total of the 15 weeks divided by 15.

I thought I could use the Avg function such as WklyAvg:
Avg([200450]+[200449]+[200448]+[200447]+[200446]+[200445]+[200444]+[200443]+
[200442]+[200441]+[200440]+[200439]+[200438]+[200437]+[200436])

This is when I get the message, "You tried to execute a query that doesn't
include the specified expression 'MtlNmbr' as part of the aggregate function."

I don't understand what the message is telling me.

SQL for the query in which I'm attempting to do calculations
SELECT qryADOnlyShip_Crosstab.MtlNmbr, qryADOnlyItemData.MFGTechCode,
qryADOnlyItemData.MFGTeamCode, qryADOnlyItemData.MaterialDescr,
qryADOnlyShip_Crosstab.[Total Of Qty], qryADOnlyShip_Crosstab.[200450],
qryADOnlyShip_Crosstab.[200449], qryADOnlyShip_Crosstab.[200448],
qryADOnlyShip_Crosstab.[200447], qryADOnlyShip_Crosstab.[200446],
qryADOnlyShip_Crosstab.[200445], qryADOnlyShip_Crosstab.[200444],
qryADOnlyShip_Crosstab.[200443], qryADOnlyShip_Crosstab.[200442],
qryADOnlyShip_Crosstab.[200441], qryADOnlyShip_Crosstab.[200440],
qryADOnlyShip_Crosstab.[200439], qryADOnlyShip_Crosstab.[200438],
qryADOnlyShip_Crosstab.[200437], qryADOnlyShip_Crosstab.[200436],
qryADOnlyItemData.MRPCntr, qryADOnlyItemData.ABCCode,
qryADOnlyItemData.MFGTechCodeDesc, qryADOnlyItemData.MFGTeamCodeDesc,
qryADOnlyItemData.LotSize, qryADOnlyItemData.RythTime,
qryADOnlyItemData.SterType, qryADOnlyItemData.LTWks,
qryADOnlyItemData.LTDays, qryADOnlyItemData.ZFactor, IIf([Total Of
Qty]>0,[Total Of Qty]/248,0) AS DlyAvg,

([200450]+[200449]+[200448]+[200447]+[200446]+[200445]+[200444]+[200443]+[20
0442]+[200441]+[200440]+[200439]+[200438]+[200437]+[200436])/15 AS WklyAvg

FROM qryADOnlyShip_Crosstab INNER JOIN qryADOnlyItemData ON
qryADOnlyShip_Crosstab.MtlNmbr = qryADOnlyItemData.MaterialNbr
WHERE (((qryADOnlyItemData.MFGTechCode) In ("235")))
`ORDER BY qryADOnlyShip_Crosstab.MtlNmbr, qryADOnlyItemData.MFGTechCode,
qryADOnlyItemData.MFGTeamCode;



SQL for the crosstab
TRANSFORM Sum(qryADOnlyShip.Qty) AS [The Value]
SELECT qryADOnlyShip.MtlNmbr, Sum(qryADOnlyShip.Qty) AS [Total Of Qty]
FROM qryADOnlyShip
GROUP BY qryADOnlyShip.MtlNmbr
ORDER BY qryADOnlyShip.YrWk DESC
PIVOT qryADOnlyShip.YrWk;

Not sure where to go next.

It would help to see just your current structure with sample records and
what you eventually wanted to get out of it. You mention the crosstab "This
lists the data by item number and sum of YrWl" yet I don't see any "item
number" or "sum of YrWl". You also mention "Query 1" but provide the sql
possibly for "qryShpAndTeamAD1Only". You should also provide the data type
of at least YrWk. I don't think you need the first query since you can limit
the records directly in the crosstab.


--
Duane Hookom
MS Access MVP



A table lists all the shipments by item, by order line, by date from the
year 2003 thru the present. One of the fields is a week number, YrWk,
where
200420 denotes the 20th week of the year 2004.

The first query is a filtering query which limits the data to a specific
team code and time frame. In this instance the team is AD1 and the time
frame is >#12/13/2003#.

Next is a crosstab query based on the above query. This lists the data by
item number and sum of YrWl

The next query is based on the crosstab query above. This displays the
item
number, the total for the timeframe, and the latest 15 weeks of shipments.

I would like to add a calculated filed in this query which will give the
AVERAGE weekly shipments of the last 15 weeks of shipments. Into a blank
filed I enter the following:

Ship15: Avg([200450]+[200449]+[200448]) and so on .

When I run the query I get the following message:

"You tried to execute a query that doesn't include the specified
expression
'MtlNmbr' as part of the aggregate function."

Is it possible to do what I want, or are there other steps which must be
taken?

The SQLs follow:

Query 1
SELECT tblShpngLines.MtlNmbr, tblShpngLines.PostingDate,
tblShpngLines.Descrip, tblShpngLines.Qty, tblItemData.MRPCntr,
tblShpngLines.YrWk
FROM tblShpngLines INNER JOIN tblItemData ON tblShpngLines.MtlNmbr =
tblItemData.MaterialNbr
WHERE (((tblShpngLines.PostingDate)>#12/13/2003#) AND
((tblItemData.MRPCntr)="AD1"))
ORDER BY tblShpngLines.YrWk DESC;

Crosstab query

TRANSFORM Sum(qryShpAndTeamAD1Only.Qty) AS [The Value]
SELECT qryShpAndTeamAD1Only.MtlNmbr, Sum(qryShpAndTeamAD1Only.Qty) AS
[Total
Of Qty]
FROM qryShpAndTeamAD1Only
GROUP BY qryShpAndTeamAD1Only.MtlNmbr
ORDER BY qryShpAndTeamAD1Only.YrWk DESC
PIVOT qryShpAndTeamAD1Only.YrWk;

Last query
SELECT qryShpAndTeamAD1Only_Crosstab.MtlNmbr,
qryShpAndTeamAD1Only_Crosstab.[Total Of Qty],
qryShpAndTeamAD1Only_Crosstab.[200450],
qryShpAndTeamAD1Only_Crosstab.[200449],
qryShpAndTeamAD1Only_Crosstab.[200448],
qryShpAndTeamAD1Only_Crosstab.[200447],
qryShpAndTeamAD1Only_Crosstab.[200446],
qryShpAndTeamAD1Only_Crosstab.[200445],
qryShpAndTeamAD1Only_Crosstab.[200444],
qryShpAndTeamAD1Only_Crosstab.[200443],
qryShpAndTeamAD1Only_Crosstab.[200442],
qryShpAndTeamAD1Only_Crosstab.[200441],
qryShpAndTeamAD1Only_Crosstab.[200440],
qryShpAndTeamAD1Only_Crosstab.[200439],
qryShpAndTeamAD1Only_Crosstab.[200438],
qryShpAndTeamAD1Only_Crosstab.[200437],
qryShpAndTeamAD1Only_Crosstab.[200436]
FROM qryShpAndTeamAD1Only_Crosstab;

 

[Duane Hookom]

Again, "Are Year, WkNum, Day, and YrWk all calculated from the PostingDate
field? If so, do you have a good reason for storing calculated values?"

I would create the 15 weeks based on the difference in weeks between a
specific value and PostingDate. This assumes an answer to the above
question.
--
Duane Hookom
MS Access MVP

The main table is:

FIELD NAME DATA TYPE
MtlNmbr Text
PostingDate Date/Time
Qty Number
Yr Text
WkNum Text
Day Text
YrWk Text

I am using a select query to limit the population of data I want to be
used
in the crosstab query.

I am trying to calculate the average weekly shipments of the items for a
specific time period, 15 weeks.

I also want to calculate the StDev and the StDevP of the shipping data by
item for the same time period.

I thought I could do use built-in functions in a query to do these
calculations and then create a report based on said query.

I'm really sorry but I just can't figure out what you have and where you
want to go. It is really confusing attempting to see your records due to
unequal spacing and wrapping. Do we really need to see all those fields?
Are
they all important to the solution?

I asked the datat type of YrWk but you didn't provide it. Are Year,
WkNum,
Day, and YrWk all calculated from the PostingDate field? If so, do you
have
a good reason for storing calculated values?

--
Duane Hookom
MS Access MVP

Sorry for the delay in answering. I've tried to rethink the problem
and put
it in clearer terms.
Seq MtlNmbr Descrip MvmntType PostingDate Qty UOM LocCurr UserName Year WkNum Day YrWk
22797 7207876 FAST-FIX ASSEMBLY 601 9/2/03 2 EA
79.72 PARENTE 2003 36 Tue 200336
The above is a single record from the table named tblShpngLines. There are
currently 415,695 records. This table does NOT contain individual item data,
except for description..

Seq Plant MaterialNbr MaterialDescr UOM Mtyp DChNbr Site MRPCntr Rlev ABCCode MRP Type MFGTechCode
4108 5000 7207876 FAST-FIX ASSEMBLY EA FERT 26984 M AD1 P A ZV 235
The above is a partial single record from the table named tblItemData.
There are currently 18,585 records.

MtlNmbr MaterialDescr MRPCntr Mtyp MvmntType PostingDate Qty UOM
LocCurr Year WkNum Day YrWk
7207876 FAST-FIX ASSEMBLY AD1 FERT 601 11/12/04 2 EA
81.9 2004 46 Fri 200446
The above is a single record from a query, qryADOnlyShip, which uses
the two
(2) tables. The link is MtlNmbr to MaterialNbr. The second, third and
fourth fields are from the latter table, tblItemData and all other
fields
from the former table, TblShpngLines. The criteria used (the filter)
is MRP
="AD1"; Mtyp = "FERT" and PostingDate >#12/13/2003#. There are
currently
220,096 lines or hits. It is this population I want to analyze.

I create a crosstab query using the Crosstab Query Wizard. I base the
crosstab query on qryADOnlyShip. I select the field MtlNmbr for the
row
heading. I select YrWk field for the column headings. I select the
Qty
field as the one to calculate, using the Sum function. I check Yes, include
row sums. After the query is created, I go into the design view and
set the
Sort to Descending for the YrWk. This is so that the most recent weeks
appear beginning in column three, after the MtlNmbr in column one and
the
Total of Qty in column two. This last piece of data is the total of
all the
YrWk's whose PostingDate is >#12/13/2003#.

A partial row of this crosstab looks like this:

MtlNmbr Total Of Qty 200450 200449 200448 200447 200446 200445 200444 200443
7207876 14040 416 271 263 275 297 290 344 274


My next query uses the crosstab query from above and a query selecting item
data for a specific team, AD1.
In the design view, I only use 15 weeks of the ship data as well as the
total qty. A partial row looks like this:

MtlNmbr MFGTechCode MFGTeamCode MaterialDescr Total Of
Qty 200450 200449 200448 200447 200446 200445 200444 200443
7207876 235 AD FAST-FIX ASSEMBLY 14040 416 271 263 275 297 290 344 274

I want to perform some calculations on the 15 weeks of ship data, such
as
average weekly shipment (Avg), and standard deviation of the data (both StDev
and StDevP). If I place the following in a blank field then I do get
the
average.

WklyAvg:
([200450]+[200449]+[200448]+[200447]+[200446]+[200445]+[200444]+[200443]+[20
0442]+[200441]+[200440]+[200439]+[200438]+[200437]+[200436])/15

However, this is really just the total of the 15 weeks divided by 15.

I thought I could use the Avg function such as WklyAvg:
Avg([200450]+[200449]+[200448]+[200447]+[200446]+[200445]+[200444]+[200443]+
[200442]+[200441]+[200440]+[200439]+[200438]+[200437]+[200436])

This is when I get the message, "You tried to execute a query that
doesn't
include the specified expression 'MtlNmbr' as part of the aggregate function."

I don't understand what the message is telling me.

SQL for the query in which I'm attempting to do calculations
SELECT qryADOnlyShip_Crosstab.MtlNmbr, qryADOnlyItemData.MFGTechCode,
qryADOnlyItemData.MFGTeamCode, qryADOnlyItemData.MaterialDescr,
qryADOnlyShip_Crosstab.[Total Of Qty], qryADOnlyShip_Crosstab.[200450],
qryADOnlyShip_Crosstab.[200449], qryADOnlyShip_Crosstab.[200448],
qryADOnlyShip_Crosstab.[200447], qryADOnlyShip_Crosstab.[200446],
qryADOnlyShip_Crosstab.[200445], qryADOnlyShip_Crosstab.[200444],
qryADOnlyShip_Crosstab.[200443], qryADOnlyShip_Crosstab.[200442],
qryADOnlyShip_Crosstab.[200441], qryADOnlyShip_Crosstab.[200440],
qryADOnlyShip_Crosstab.[200439], qryADOnlyShip_Crosstab.[200438],
qryADOnlyShip_Crosstab.[200437], qryADOnlyShip_Crosstab.[200436],
qryADOnlyItemData.MRPCntr, qryADOnlyItemData.ABCCode,
qryADOnlyItemData.MFGTechCodeDesc, qryADOnlyItemData.MFGTeamCodeDesc,
qryADOnlyItemData.LotSize, qryADOnlyItemData.RythTime,
qryADOnlyItemData.SterType, qryADOnlyItemData.LTWks,
qryADOnlyItemData.LTDays, qryADOnlyItemData.ZFactor, IIf([Total Of
Qty]>0,[Total Of Qty]/248,0) AS DlyAvg,

([200450]+[200449]+[200448]+[200447]+[200446]+[200445]+[200444]+[200443]+[20
0442]+[200441]+[200440]+[200439]+[200438]+[200437]+[200436])/15 AS
WklyAvg

FROM qryADOnlyShip_Crosstab INNER JOIN qryADOnlyItemData ON
qryADOnlyShip_Crosstab.MtlNmbr = qryADOnlyItemData.MaterialNbr
WHERE (((qryADOnlyItemData.MFGTechCode) In ("235")))
`ORDER BY qryADOnlyShip_Crosstab.MtlNmbr,
qryADOnlyItemData.MFGTechCode,
qryADOnlyItemData.MFGTeamCode;



SQL for the crosstab
TRANSFORM Sum(qryADOnlyShip.Qty) AS [The Value]
SELECT qryADOnlyShip.MtlNmbr, Sum(qryADOnlyShip.Qty) AS [Total Of Qty]
FROM qryADOnlyShip
GROUP BY qryADOnlyShip.MtlNmbr
ORDER BY qryADOnlyShip.YrWk DESC
PIVOT qryADOnlyShip.YrWk;

Not sure where to go next.



:

It would help to see just your current structure with sample records
and
what you eventually wanted to get out of it. You mention the crosstab "This
lists the data by item number and sum of YrWl" yet I don't see any
"item
number" or "sum of YrWl". You also mention "Query 1" but provide the
sql
possibly for "qryShpAndTeamAD1Only". You should also provide the data type
of at least YrWk. I don't think you need the first query since you
can limit
the records directly in the crosstab.


--
Duane Hookom
MS Access MVP



A table lists all the shipments by item, by order line, by date
from the
year 2003 thru the present. One of the fields is a week number,
YrWk,
where
200420 denotes the 20th week of the year 2004.

The first query is a filtering query which limits the data to a specific
team code and time frame. In this instance the team is AD1 and the time
frame is >#12/13/2003#.

Next is a crosstab query based on the above query. This lists the data by
item number and sum of YrWl

The next query is based on the crosstab query above. This displays the
item
number, the total for the timeframe, and the latest 15 weeks of shipments.

I would like to add a calculated filed in this query which will
give the
AVERAGE weekly shipments of the last 15 weeks of shipments. Into a blank
filed I enter the following:

Ship15: Avg([200450]+[200449]+[200448]) and so on .

When I run the query I get the following message:

"You tried to execute a query that doesn't include the specified
expression
'MtlNmbr' as part of the aggregate function."

Is it possible to do what I want, or are there other steps which
must be
taken?

The SQLs follow:

Query 1
SELECT tblShpngLines.MtlNmbr, tblShpngLines.PostingDate,
tblShpngLines.Descrip, tblShpngLines.Qty, tblItemData.MRPCntr,
tblShpngLines.YrWk
FROM tblShpngLines INNER JOIN tblItemData ON tblShpngLines.MtlNmbr
=
tblItemData.MaterialNbr
WHERE (((tblShpngLines.PostingDate)>#12/13/2003#) AND
((tblItemData.MRPCntr)="AD1"))
ORDER BY tblShpngLines.YrWk DESC;

Crosstab query

TRANSFORM Sum(qryShpAndTeamAD1Only.Qty) AS [The Value]
SELECT qryShpAndTeamAD1Only.MtlNmbr, Sum(qryShpAndTeamAD1Only.Qty)
AS
[Total
Of Qty]
FROM qryShpAndTeamAD1Only
GROUP BY qryShpAndTeamAD1Only.MtlNmbr
ORDER BY qryShpAndTeamAD1Only.YrWk DESC
PIVOT qryShpAndTeamAD1Only.YrWk;

Last query
SELECT qryShpAndTeamAD1Only_Crosstab.MtlNmbr,
qryShpAndTeamAD1Only_Crosstab.[Total Of Qty],
qryShpAndTeamAD1Only_Crosstab.[200450],
qryShpAndTeamAD1Only_Crosstab.[200449],
qryShpAndTeamAD1Only_Crosstab.[200448],
qryShpAndTeamAD1Only_Crosstab.[200447],
qryShpAndTeamAD1Only_Crosstab.[200446],
qryShpAndTeamAD1Only_Crosstab.[200445],
qryShpAndTeamAD1Only_Crosstab.[200444],
qryShpAndTeamAD1Only_Crosstab.[200443],
qryShpAndTeamAD1Only_Crosstab.[200442],
qryShpAndTeamAD1Only_Crosstab.[200441],
qryShpAndTeamAD1Only_Crosstab.[200440],
qryShpAndTeamAD1Only_Crosstab.[200439],
qryShpAndTeamAD1Only_Crosstab.[200438],
qryShpAndTeamAD1Only_Crosstab.[200437],
qryShpAndTeamAD1Only_Crosstab.[200436]
FROM qryShpAndTeamAD1Only_Crosstab;