Aggregate calculations on calculated query fields?

I made a query that uses fields from multiple tables to calculate values for
daily metrics. I need to present monthly totals of the data as sums and/or
means. The tables are linked by the Date (mm/dd/yyyy) field. I enter ‘between
03/01/2010 and 03/31/2010’ in the criteria for the date field. For the totals
I enter ‘group by’ for the date and ‘sum’ or ‘avg’ for the calculated fields.

When the query is executed I receive an error that I ‘tried to execute a
query that does not include the specified expression *** as part of an
aggregate function. *** is the calculated field in the query. The help button
reveals this is also known as Error 3122.

What am I doing wrong? I thought it would be straightforward to select this
data by a date range and perform the calculations. Thank you for any help you
can provide.

KST8WCT wrote:

I made a query that uses fields from multiple tables to calculate values for
daily metrics. I need to present monthly totals of the data as sums and/or
means. The tables are linked by the Date (mm/dd/yyyy) field. I enter ‘between
03/01/2010 and 03/31/2010’ in the criteria for the date field. For the totals
I enter ‘group by’ for the date and ‘sum’ or ‘avg’ for the calculated fields.

When the query is executed I receive an error that I ‘tried to execute a
query that does not include the specified expression *** as part of an
aggregate function. *** is the calculated field in the query. The help button
reveals this is also known as Error 3122.

What am I doing wrong? I thought it would be straightforward to select this
data by a date range and perform the calculations. Thank you for any help you
can provide.


Since you seem to want the totals by month, you need to
calculate a field that either does not include the day part
or standardizes on a fixed day of the month (e.g. the
first). I think the simplest is to replace the date field
in the Select and Group By clauses with an expression like:
Format(datefield, "yyyymm")

Not clear about how your tables are linked by the date
field. Do you use the date field in the Join? It might
help if you posted the query's SQL.

--
Marsh
MVP [MS Access]

The query builds calculated fields from tables such as ACTIVITY and
INVENTORY. All of the tables are related by a DATE field. Relational dbase
class was 16 years ago...

Here is the SQL:
SELECT Format([datefield],"yyyymm") AS Expr1, ACTIVITY.[New Holland Build],
ACTIVITY![New Holland Build]*7.4+ACTIVITY![Number of JIS Pilot Carts
Built]+0.5*ACTIVITY![Number of JIS Track Carts Built] AS NHJISBUILD,
ACTIVITY![CP/HL Build]*5.3+ACTIVITY![Number of JIS Track Carts Built] AS
CPJISBUILD, ACTIVITY![New Holland Build]*4 AS NHJITBUILD, ACTIVITY![CP/HL
Build]*4+ACTIVITY![Number of JIT Pilot Carts Built] AS CPJITBUILD,
ACTIVITY![CP Repack Shipped]+ACTIVITY![HL Repack Shipped]+ACTIVITY![New
Holland Repack Shipped]+ACTIVITY![Hardware Repack Shipped]+ACTIVITY![Cab
Repack Shipped]+ACTIVITY![Manual Repack for Kits Shipped] AS REPACKSHIPPED,
ACTIVITY![RCV lines]+ACTIVITY!Putaways+ACTIVITY![MAC Picks]+ACTIVITY![MAC
Deliveries]+ACTIVITY![Number of Returnables Loaded]+ACTIVITY![Number of
Bucket Racks shipped to Paint]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels
Transactions]+[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![Repack
Pans Restocked] AS TOTALTNX, INVENTORY![Cycle Count Counted]/INVENTORY![Cycle
Count On Hand] AS CYCLECOUNTACCURACY, INVENTORY![Repack Locations (SKUS)
Counted]+INVENTORY![Number of Bays Audited]+INVENTORY![Cycle Count Locations
Counted] AS C010LOCATIONSAUDITED, [C010LOCATIONSAUDITED]-INVENTORY![Repack
Incorrect Pan Quantities]-INVENTORY![Repack Incorrect
Locations]-INVENTORY![Bays Incorrect Location]-INVENTORY![Bays With Incorrect
Label] AS C020CORRECTSKUQTY,
[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![MAC
Deliveries] AS DELIVERIES, [QUALITY AND CI]![B020 Number of late
deliveries]/[DELIVERIES] AS PCTLATE, ([QUALITY AND CI]![Number of wrong part
deliveries]+[QUALITY AND CI]![Number of wrong location
deliveries])/[DELIVERIES] AS PCTWRONGLOC, [QUALITY AND CI]![Total minutes of
final assembly downtime due to Exel]/(ACTIVITY![New Holland Assembly
Time]+ACTIVITY![CP/HL Assembly Time]) AS PCTASYDT, INVENTORY![Cycle Count
Counted]/INVENTORY![Cycle Count On Hand] AS CYCLECTACCRCY, INVENTORY![Bays
Incorrect Location]/INVENTORY![Number of Bays Audited] AS BAYLOCACCURACY,
INVENTORY![Bays With Incorrect Label]/INVENTORY![Number of Bays Audited] AS
BAYLBLACCURACY, INVENTORY![Repack Incorrect Pan Quantities]/INVENTORY![Repack
Locations (SKUS) Counted] AS 2BINCORRQTY, INVENTORY![Repack Incorrect
Locations]/INVENTORY![Repack Locations (SKUS) Counted] AS 2BINCORLOC,
(ACTIVITY![RCV lines]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels Transactions])/LABOR![Total
Receiving Hours] AS RCVLINESPERHR, ACTIVITY!Putaways/LABOR![Total Putaway
Hours] AS PUTPERHR, ACTIVITY![MAC Picks]/LABOR![Total Mac Picking Hours] AS
COPICKPERHR, ACTIVITY![MAC Deliveries]/LABOR![Total Mac Delivery Hours] AS
CODLVPERHR, [REPACKSHIPPED]/LABOR![Total 2-Bin Picking Hours] AS
2BINPIKPERHOUR, [REPACKSHIPPED]/LABOR![Total 2-Bin Delivery Hours] AS
2BINDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Delivery Hours] AS
JISDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Sequencing Hours]
AS JISSEQPERHR, ([NHJITBUILD]+[CPJITBUILD])/LABOR![Total JIT Hours] AS
JITPERHR, ACTIVITY![New Holland Build]*371+ACTIVITY![CP/HL Build]*384 AS
JITPICKS, LABOR![Total Assembly Spider Hours]+LABOR![Total Weld Kit Spider
Hours] AS SUMSPIDERHOURS, [CPJISBUILD]+[NHJISBUILD] AS TOTLJISBUILD,
[NHJITBUILD]+[CPJITBUILD] AS TOTLJITBUILD, (ACTIVITY![RCV
lines]+ACTIVITY![Number of SAP Receiving Transactions]+ACTIVITY![Number of
Small Parcels Transactions])/ACTIVITY![B087 Number of Inbound Receipts
(Loads)] AS LINESPERLOAD
FROM (((ACTIVITY INNER JOIN INVENTORY ON ACTIVITY.DATE = INVENTORY.Date)
INNER JOIN [QUALITY AND CI] ON ACTIVITY.DATE = [QUALITY AND CI].DATE) INNER
JOIN LABOR ON (INVENTORY.Date = LABOR.DATE) AND ([QUALITY AND CI].DATE =
LABOR.DATE) AND (ACTIVITY.DATE = LABOR.DATE)) INNER JOIN SAFETY ON ([QUALITY
AND CI].DATE = SAFETY.DATE) AND (ACTIVITY.DATE = SAFETY.DATE);


"Marshall Barton" wrote:

KST8WCT wrote:

I made a query that uses fields from multiple tables to calculate values for
daily metrics. I need to present monthly totals of the data as sums and/or
means. The tables are linked by the Date (mm/dd/yyyy) field. I enter ‘between
03/01/2010 and 03/31/2010’ in the criteria for the date field. For the totals
I enter ‘group by’ for the date and ‘sum’ or ‘avg’ for the calculated fields.

When the query is executed I receive an error that I ‘tried to execute a
query that does not include the specified expression *** as part of an
aggregate function. *** is the calculated field in the query. The help button
reveals this is also known as Error 3122.

What am I doing wrong? I thought it would be straightforward to select this
data by a date range and perform the calculations. Thank you for any help you
can provide.


Since you seem to want the totals by month, you need to
calculate a field that either does not include the day part
or standardizes on a fixed day of the month (e.g. the
first). I think the simplest is to replace the date field
in the Select and Group By clauses with an expression like:
Format(datefield, "yyyymm")

Not clear about how your tables are linked by the date
field. Do you use the date field in the Join? It might
help if you posted the query's SQL.

--
Marsh
MVP [MS Access]
.

You have several problems --

You have a spreadsheet instead of relational database --
ACTIVITY![New Holland Build]
ACTIVITY![Number of JIS Pilot Carts Built]
ACTIVITY![Number of JIS Track Carts Built]
ACTIVITY![CP/HL Build]
ACTIVITY![CP Repack Shipped]

It should be like this --
TblACTIVITY --
ActivityID - Autonumber - primary key
ActivityDate - DateTime
Activity - text - linked to table listing all of the type of activity
performed so the you pick instead of typing - fewer mistakes
Quanity - number
etc.

If you are going to join on a date the first create a list of dates --
qryAllDates --
SELECT [QUALITY AND CI].DATE
FROM [QUALITY AND CI]
GROUP BY [QUALITY AND CI].DATE
UNION SELECT ACTIVITY.DATE
FROM ACTIVITY
GROUP BY ACTIVITY.DATE
UNION SELECT INVENTORY.Date
FROM INVENTORY
GROUP BY INVENTORY.Date
UNION SELECT LABOR.DATE
FROM LABOR
GROUP BY LABOR.DATE
UNION SELECT SAFETY.DATE
FROM SAFETY
GROUP BY SAFETY.DATE;

Then use this --
FROM ((((qryAllDates LEFT JOIN ACTIVITY ON qryAllDates.Date = ACTIVITY.DATE)
LEFT JOIN INVENTORY ON qryAllDates.Date = INVENTORY.Date)
LEFT JOIN [QUALITY AND CI] ON qryAllDates.Date = [QUALITY AND CI].DATE) LEFT
JOIN LABOR ON qryAllDates.Date = LABOR.DATE) LEFT JOIN SAFETY ON
qryAllDates.Date = SAFETY.DATE;

--
Build a little, test a little.


"KST8WCT" wrote:

The query builds calculated fields from tables such as ACTIVITY and
INVENTORY. All of the tables are related by a DATE field. Relational dbase
class was 16 years ago...

Here is the SQL:
SELECT Format([datefield],"yyyymm") AS Expr1, ACTIVITY.[New Holland Build],
ACTIVITY![New Holland Build]*7.4+ACTIVITY![Number of JIS Pilot Carts
Built]+0.5*ACTIVITY![Number of JIS Track Carts Built] AS NHJISBUILD,
ACTIVITY![CP/HL Build]*5.3+ACTIVITY![Number of JIS Track Carts Built] AS
CPJISBUILD, ACTIVITY![New Holland Build]*4 AS NHJITBUILD, ACTIVITY![CP/HL
Build]*4+ACTIVITY![Number of JIT Pilot Carts Built] AS CPJITBUILD,
ACTIVITY![CP Repack Shipped]+ACTIVITY![HL Repack Shipped]+ACTIVITY![New
Holland Repack Shipped]+ACTIVITY![Hardware Repack Shipped]+ACTIVITY![Cab
Repack Shipped]+ACTIVITY![Manual Repack for Kits Shipped] AS REPACKSHIPPED,
ACTIVITY![RCV lines]+ACTIVITY!Putaways+ACTIVITY![MAC Picks]+ACTIVITY![MAC
Deliveries]+ACTIVITY![Number of Returnables Loaded]+ACTIVITY![Number of
Bucket Racks shipped to Paint]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels
Transactions]+[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![Repack
Pans Restocked] AS TOTALTNX, INVENTORY![Cycle Count Counted]/INVENTORY![Cycle
Count On Hand] AS CYCLECOUNTACCURACY, INVENTORY![Repack Locations (SKUS)
Counted]+INVENTORY![Number of Bays Audited]+INVENTORY![Cycle Count Locations
Counted] AS C010LOCATIONSAUDITED, [C010LOCATIONSAUDITED]-INVENTORY![Repack
Incorrect Pan Quantities]-INVENTORY![Repack Incorrect
Locations]-INVENTORY![Bays Incorrect Location]-INVENTORY![Bays With Incorrect
Label] AS C020CORRECTSKUQTY,
[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![MAC
Deliveries] AS DELIVERIES, [QUALITY AND CI]![B020 Number of late
deliveries]/[DELIVERIES] AS PCTLATE, ([QUALITY AND CI]![Number of wrong part
deliveries]+[QUALITY AND CI]![Number of wrong location
deliveries])/[DELIVERIES] AS PCTWRONGLOC, [QUALITY AND CI]![Total minutes of
final assembly downtime due to Exel]/(ACTIVITY![New Holland Assembly
Time]+ACTIVITY![CP/HL Assembly Time]) AS PCTASYDT, INVENTORY![Cycle Count
Counted]/INVENTORY![Cycle Count On Hand] AS CYCLECTACCRCY, INVENTORY![Bays
Incorrect Location]/INVENTORY![Number of Bays Audited] AS BAYLOCACCURACY,
INVENTORY![Bays With Incorrect Label]/INVENTORY![Number of Bays Audited] AS
BAYLBLACCURACY, INVENTORY![Repack Incorrect Pan Quantities]/INVENTORY![Repack
Locations (SKUS) Counted] AS 2BINCORRQTY, INVENTORY![Repack Incorrect
Locations]/INVENTORY![Repack Locations (SKUS) Counted] AS 2BINCORLOC,
(ACTIVITY![RCV lines]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels Transactions])/LABOR![Total
Receiving Hours] AS RCVLINESPERHR, ACTIVITY!Putaways/LABOR![Total Putaway
Hours] AS PUTPERHR, ACTIVITY![MAC Picks]/LABOR![Total Mac Picking Hours] AS
COPICKPERHR, ACTIVITY![MAC Deliveries]/LABOR![Total Mac Delivery Hours] AS
CODLVPERHR, [REPACKSHIPPED]/LABOR![Total 2-Bin Picking Hours] AS
2BINPIKPERHOUR, [REPACKSHIPPED]/LABOR![Total 2-Bin Delivery Hours] AS
2BINDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Delivery Hours] AS
JISDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Sequencing Hours]
AS JISSEQPERHR, ([NHJITBUILD]+[CPJITBUILD])/LABOR![Total JIT Hours] AS
JITPERHR, ACTIVITY![New Holland Build]*371+ACTIVITY![CP/HL Build]*384 AS
JITPICKS, LABOR![Total Assembly Spider Hours]+LABOR![Total Weld Kit Spider
Hours] AS SUMSPIDERHOURS, [CPJISBUILD]+[NHJISBUILD] AS TOTLJISBUILD,
[NHJITBUILD]+[CPJITBUILD] AS TOTLJITBUILD, (ACTIVITY![RCV
lines]+ACTIVITY![Number of SAP Receiving Transactions]+ACTIVITY![Number of
Small Parcels Transactions])/ACTIVITY![B087 Number of Inbound Receipts
(Loads)] AS LINESPERLOAD
FROM (((ACTIVITY INNER JOIN INVENTORY ON ACTIVITY.DATE = INVENTORY.Date)
INNER JOIN [QUALITY AND CI] ON ACTIVITY.DATE = [QUALITY AND CI].DATE) INNER
JOIN LABOR ON (INVENTORY.Date = LABOR.DATE) AND ([QUALITY AND CI].DATE =
LABOR.DATE) AND (ACTIVITY.DATE = LABOR.DATE)) INNER JOIN SAFETY ON ([QUALITY
AND CI].DATE = SAFETY.DATE) AND (ACTIVITY.DATE = SAFETY.DATE);


"Marshall Barton" wrote:

KST8WCT wrote:

I made a query that uses fields from multiple tables to calculate values for
daily metrics. I need to present monthly totals of the data as sums and/or
means. The tables are linked by the Date (mm/dd/yyyy) field. I enter ‘between
03/01/2010 and 03/31/2010’ in the criteria for the date field. For the totals
I enter ‘group by’ for the date and ‘sum’ or ‘avg’ for the calculated fields.

When the query is executed I receive an error that I ‘tried to execute a
query that does not include the specified expression *** as part of an
aggregate function. *** is the calculated field in the query. The help button
reveals this is also known as Error 3122.

What am I doing wrong? I thought it would be straightforward to select this
data by a date range and perform the calculations. Thank you for any help you
can provide.


Since you seem to want the totals by month, you need to
calculate a field that either does not include the day part
or standardizes on a fixed day of the month (e.g. the
first). I think the simplest is to replace the date field
in the Select and Group By clauses with an expression like:
Format(datefield, "yyyymm")

Not clear about how your tables are linked by the date
field. Do you use the date field in the Join? It might
help if you posted the query's SQL.

--
Marsh
MVP [MS Access]
.

Thanks for the input, Karl. Under your scenario the person entering the data
would have a form where they enter the date, select the activity (say, from a
combo box) and then enter the quantity, correct? If so, I am probably stuck
because the clerks won't go for taking the time to do all of that.

I currently have it setup so that the data entry person opens a form and
enters the date followed by all of the results for the day. Then I made the
query shown before to do all the calculations. All of that goes to a report
which shows the daily results. Is there a way to run aggregate functions like
sum and avg under this scenario?

Shawn


"KARL DEWEY" wrote:

You have several problems --

You have a spreadsheet instead of relational database --
ACTIVITY![New Holland Build]
ACTIVITY![Number of JIS Pilot Carts Built]
ACTIVITY![Number of JIS Track Carts Built]
ACTIVITY![CP/HL Build]
ACTIVITY![CP Repack Shipped]

It should be like this --
TblACTIVITY --
ActivityID - Autonumber - primary key
ActivityDate - DateTime
Activity - text - linked to table listing all of the type of activity
performed so the you pick instead of typing - fewer mistakes
Quanity - number
etc.

If you are going to join on a date the first create a list of dates --
qryAllDates --
SELECT [QUALITY AND CI].DATE
FROM [QUALITY AND CI]
GROUP BY [QUALITY AND CI].DATE
UNION SELECT ACTIVITY.DATE
FROM ACTIVITY
GROUP BY ACTIVITY.DATE
UNION SELECT INVENTORY.Date
FROM INVENTORY
GROUP BY INVENTORY.Date
UNION SELECT LABOR.DATE
FROM LABOR
GROUP BY LABOR.DATE
UNION SELECT SAFETY.DATE
FROM SAFETY
GROUP BY SAFETY.DATE;

Then use this --
FROM ((((qryAllDates LEFT JOIN ACTIVITY ON qryAllDates.Date = ACTIVITY.DATE)
LEFT JOIN INVENTORY ON qryAllDates.Date = INVENTORY.Date)
LEFT JOIN [QUALITY AND CI] ON qryAllDates.Date = [QUALITY AND CI].DATE) LEFT
JOIN LABOR ON qryAllDates.Date = LABOR.DATE) LEFT JOIN SAFETY ON
qryAllDates.Date = SAFETY.DATE;

--
Build a little, test a little.


"KST8WCT" wrote:

The query builds calculated fields from tables such as ACTIVITY and
INVENTORY. All of the tables are related by a DATE field. Relational dbase
class was 16 years ago...

Here is the SQL:
SELECT Format([datefield],"yyyymm") AS Expr1, ACTIVITY.[New Holland Build],
ACTIVITY![New Holland Build]*7.4+ACTIVITY![Number of JIS Pilot Carts
Built]+0.5*ACTIVITY![Number of JIS Track Carts Built] AS NHJISBUILD,
ACTIVITY![CP/HL Build]*5.3+ACTIVITY![Number of JIS Track Carts Built] AS
CPJISBUILD, ACTIVITY![New Holland Build]*4 AS NHJITBUILD, ACTIVITY![CP/HL
Build]*4+ACTIVITY![Number of JIT Pilot Carts Built] AS CPJITBUILD,
ACTIVITY![CP Repack Shipped]+ACTIVITY![HL Repack Shipped]+ACTIVITY![New
Holland Repack Shipped]+ACTIVITY![Hardware Repack Shipped]+ACTIVITY![Cab
Repack Shipped]+ACTIVITY![Manual Repack for Kits Shipped] AS REPACKSHIPPED,
ACTIVITY![RCV lines]+ACTIVITY!Putaways+ACTIVITY![MAC Picks]+ACTIVITY![MAC
Deliveries]+ACTIVITY![Number of Returnables Loaded]+ACTIVITY![Number of
Bucket Racks shipped to Paint]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels
Transactions]+[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![Repack
Pans Restocked] AS TOTALTNX, INVENTORY![Cycle Count Counted]/INVENTORY![Cycle
Count On Hand] AS CYCLECOUNTACCURACY, INVENTORY![Repack Locations (SKUS)
Counted]+INVENTORY![Number of Bays Audited]+INVENTORY![Cycle Count Locations
Counted] AS C010LOCATIONSAUDITED, [C010LOCATIONSAUDITED]-INVENTORY![Repack
Incorrect Pan Quantities]-INVENTORY![Repack Incorrect
Locations]-INVENTORY![Bays Incorrect Location]-INVENTORY![Bays With Incorrect
Label] AS C020CORRECTSKUQTY,
[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![MAC
Deliveries] AS DELIVERIES, [QUALITY AND CI]![B020 Number of late
deliveries]/[DELIVERIES] AS PCTLATE, ([QUALITY AND CI]![Number of wrong part
deliveries]+[QUALITY AND CI]![Number of wrong location
deliveries])/[DELIVERIES] AS PCTWRONGLOC, [QUALITY AND CI]![Total minutes of
final assembly downtime due to Exel]/(ACTIVITY![New Holland Assembly
Time]+ACTIVITY![CP/HL Assembly Time]) AS PCTASYDT, INVENTORY![Cycle Count
Counted]/INVENTORY![Cycle Count On Hand] AS CYCLECTACCRCY, INVENTORY![Bays
Incorrect Location]/INVENTORY![Number of Bays Audited] AS BAYLOCACCURACY,
INVENTORY![Bays With Incorrect Label]/INVENTORY![Number of Bays Audited] AS
BAYLBLACCURACY, INVENTORY![Repack Incorrect Pan Quantities]/INVENTORY![Repack
Locations (SKUS) Counted] AS 2BINCORRQTY, INVENTORY![Repack Incorrect
Locations]/INVENTORY![Repack Locations (SKUS) Counted] AS 2BINCORLOC,
(ACTIVITY![RCV lines]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels Transactions])/LABOR![Total
Receiving Hours] AS RCVLINESPERHR, ACTIVITY!Putaways/LABOR![Total Putaway
Hours] AS PUTPERHR, ACTIVITY![MAC Picks]/LABOR![Total Mac Picking Hours] AS
COPICKPERHR, ACTIVITY![MAC Deliveries]/LABOR![Total Mac Delivery Hours] AS
CODLVPERHR, [REPACKSHIPPED]/LABOR![Total 2-Bin Picking Hours] AS
2BINPIKPERHOUR, [REPACKSHIPPED]/LABOR![Total 2-Bin Delivery Hours] AS
2BINDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Delivery Hours] AS
JISDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Sequencing Hours]
AS JISSEQPERHR, ([NHJITBUILD]+[CPJITBUILD])/LABOR![Total JIT Hours] AS
JITPERHR, ACTIVITY![New Holland Build]*371+ACTIVITY![CP/HL Build]*384 AS
JITPICKS, LABOR![Total Assembly Spider Hours]+LABOR![Total Weld Kit Spider
Hours] AS SUMSPIDERHOURS, [CPJISBUILD]+[NHJISBUILD] AS TOTLJISBUILD,
[NHJITBUILD]+[CPJITBUILD] AS TOTLJITBUILD, (ACTIVITY![RCV
lines]+ACTIVITY![Number of SAP Receiving Transactions]+ACTIVITY![Number of
Small Parcels Transactions])/ACTIVITY![B087 Number of Inbound Receipts
(Loads)] AS LINESPERLOAD
FROM (((ACTIVITY INNER JOIN INVENTORY ON ACTIVITY.DATE = INVENTORY.Date)
INNER JOIN [QUALITY AND CI] ON ACTIVITY.DATE = [QUALITY AND CI].DATE) INNER
JOIN LABOR ON (INVENTORY.Date = LABOR.DATE) AND ([QUALITY AND CI].DATE =
LABOR.DATE) AND (ACTIVITY.DATE = LABOR.DATE)) INNER JOIN SAFETY ON ([QUALITY
AND CI].DATE = SAFETY.DATE) AND (ACTIVITY.DATE = SAFETY.DATE);


"Marshall Barton" wrote:

KST8WCT wrote:

I made a query that uses fields from multiple tables to calculate values for
daily metrics. I need to present monthly totals of the data as sums and/or
means. The tables are linked by the Date (mm/dd/yyyy) field. I enter ‘between
03/01/2010 and 03/31/2010’ in the criteria for the date field. For the totals
I enter ‘group by’ for the date and ‘sum’ or ‘avg’ for the calculated fields.

When the query is executed I receive an error that I ‘tried to execute a
query that does not include the specified expression *** as part of an
aggregate function. *** is the calculated field in the query. The help button
reveals this is also known as Error 3122.

What am I doing wrong? I thought it would be straightforward to select this
data by a date range and perform the calculations. Thank you for any help you
can provide.


Since you seem to want the totals by month, you need to
calculate a field that either does not include the day part
or standardizes on a fixed day of the month (e.g. the
first). I think the simplest is to replace the date field
in the Select and Group By clauses with an expression like:
Format(datefield, "yyyymm")

Not clear about how your tables are linked by the date
field. Do you use the date field in the Join? It might
help if you posted the query's SQL.

--
Marsh
MVP [MS Access]
.

There is a way to have your cake and it too.
Create a macro that generates a record for each possibly activity for the
data entry date, display using datasheet form, and then a command button to
finalize by deleting all records with zero or null.

--
Build a little, test a little.


"KST8WCT" wrote:

Thanks for the input, Karl. Under your scenario the person entering the data
would have a form where they enter the date, select the activity (say, from a
combo box) and then enter the quantity, correct? If so, I am probably stuck
because the clerks won't go for taking the time to do all of that.

I currently have it setup so that the data entry person opens a form and
enters the date followed by all of the results for the day. Then I made the
query shown before to do all the calculations. All of that goes to a report
which shows the daily results. Is there a way to run aggregate functions like
sum and avg under this scenario?

Shawn


"KARL DEWEY" wrote:

You have several problems --

You have a spreadsheet instead of relational database --
ACTIVITY![New Holland Build]
ACTIVITY![Number of JIS Pilot Carts Built]
ACTIVITY![Number of JIS Track Carts Built]
ACTIVITY![CP/HL Build]
ACTIVITY![CP Repack Shipped]

It should be like this --
TblACTIVITY --
ActivityID - Autonumber - primary key
ActivityDate - DateTime
Activity - text - linked to table listing all of the type of activity
performed so the you pick instead of typing - fewer mistakes
Quanity - number
etc.

If you are going to join on a date the first create a list of dates --
qryAllDates --
SELECT [QUALITY AND CI].DATE
FROM [QUALITY AND CI]
GROUP BY [QUALITY AND CI].DATE
UNION SELECT ACTIVITY.DATE
FROM ACTIVITY
GROUP BY ACTIVITY.DATE
UNION SELECT INVENTORY.Date
FROM INVENTORY
GROUP BY INVENTORY.Date
UNION SELECT LABOR.DATE
FROM LABOR
GROUP BY LABOR.DATE
UNION SELECT SAFETY.DATE
FROM SAFETY
GROUP BY SAFETY.DATE;

Then use this --
FROM ((((qryAllDates LEFT JOIN ACTIVITY ON qryAllDates.Date = ACTIVITY.DATE)
LEFT JOIN INVENTORY ON qryAllDates.Date = INVENTORY.Date)
LEFT JOIN [QUALITY AND CI] ON qryAllDates.Date = [QUALITY AND CI].DATE) LEFT
JOIN LABOR ON qryAllDates.Date = LABOR.DATE) LEFT JOIN SAFETY ON
qryAllDates.Date = SAFETY.DATE;

--
Build a little, test a little.


"KST8WCT" wrote:

The query builds calculated fields from tables such as ACTIVITY and
INVENTORY. All of the tables are related by a DATE field. Relational dbase
class was 16 years ago...

Here is the SQL:
SELECT Format([datefield],"yyyymm") AS Expr1, ACTIVITY.[New Holland Build],
ACTIVITY![New Holland Build]*7.4+ACTIVITY![Number of JIS Pilot Carts
Built]+0.5*ACTIVITY![Number of JIS Track Carts Built] AS NHJISBUILD,
ACTIVITY![CP/HL Build]*5.3+ACTIVITY![Number of JIS Track Carts Built] AS
CPJISBUILD, ACTIVITY![New Holland Build]*4 AS NHJITBUILD, ACTIVITY![CP/HL
Build]*4+ACTIVITY![Number of JIT Pilot Carts Built] AS CPJITBUILD,
ACTIVITY![CP Repack Shipped]+ACTIVITY![HL Repack Shipped]+ACTIVITY![New
Holland Repack Shipped]+ACTIVITY![Hardware Repack Shipped]+ACTIVITY![Cab
Repack Shipped]+ACTIVITY![Manual Repack for Kits Shipped] AS REPACKSHIPPED,
ACTIVITY![RCV lines]+ACTIVITY!Putaways+ACTIVITY![MAC Picks]+ACTIVITY![MAC
Deliveries]+ACTIVITY![Number of Returnables Loaded]+ACTIVITY![Number of
Bucket Racks shipped to Paint]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels
Transactions]+[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![Repack
Pans Restocked] AS TOTALTNX, INVENTORY![Cycle Count Counted]/INVENTORY![Cycle
Count On Hand] AS CYCLECOUNTACCURACY, INVENTORY![Repack Locations (SKUS)
Counted]+INVENTORY![Number of Bays Audited]+INVENTORY![Cycle Count Locations
Counted] AS C010LOCATIONSAUDITED, [C010LOCATIONSAUDITED]-INVENTORY![Repack
Incorrect Pan Quantities]-INVENTORY![Repack Incorrect
Locations]-INVENTORY![Bays Incorrect Location]-INVENTORY![Bays With Incorrect
Label] AS C020CORRECTSKUQTY,
[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![MAC
Deliveries] AS DELIVERIES, [QUALITY AND CI]![B020 Number of late
deliveries]/[DELIVERIES] AS PCTLATE, ([QUALITY AND CI]![Number of wrong part
deliveries]+[QUALITY AND CI]![Number of wrong location
deliveries])/[DELIVERIES] AS PCTWRONGLOC, [QUALITY AND CI]![Total minutes of
final assembly downtime due to Exel]/(ACTIVITY![New Holland Assembly
Time]+ACTIVITY![CP/HL Assembly Time]) AS PCTASYDT, INVENTORY![Cycle Count
Counted]/INVENTORY![Cycle Count On Hand] AS CYCLECTACCRCY, INVENTORY![Bays
Incorrect Location]/INVENTORY![Number of Bays Audited] AS BAYLOCACCURACY,
INVENTORY![Bays With Incorrect Label]/INVENTORY![Number of Bays Audited] AS
BAYLBLACCURACY, INVENTORY![Repack Incorrect Pan Quantities]/INVENTORY![Repack
Locations (SKUS) Counted] AS 2BINCORRQTY, INVENTORY![Repack Incorrect
Locations]/INVENTORY![Repack Locations (SKUS) Counted] AS 2BINCORLOC,
(ACTIVITY![RCV lines]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels Transactions])/LABOR![Total
Receiving Hours] AS RCVLINESPERHR, ACTIVITY!Putaways/LABOR![Total Putaway
Hours] AS PUTPERHR, ACTIVITY![MAC Picks]/LABOR![Total Mac Picking Hours] AS
COPICKPERHR, ACTIVITY![MAC Deliveries]/LABOR![Total Mac Delivery Hours] AS
CODLVPERHR, [REPACKSHIPPED]/LABOR![Total 2-Bin Picking Hours] AS
2BINPIKPERHOUR, [REPACKSHIPPED]/LABOR![Total 2-Bin Delivery Hours] AS
2BINDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Delivery Hours] AS
JISDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Sequencing Hours]
AS JISSEQPERHR, ([NHJITBUILD]+[CPJITBUILD])/LABOR![Total JIT Hours] AS
JITPERHR, ACTIVITY![New Holland Build]*371+ACTIVITY![CP/HL Build]*384 AS
JITPICKS, LABOR![Total Assembly Spider Hours]+LABOR![Total Weld Kit Spider
Hours] AS SUMSPIDERHOURS, [CPJISBUILD]+[NHJISBUILD] AS TOTLJISBUILD,
[NHJITBUILD]+[CPJITBUILD] AS TOTLJITBUILD, (ACTIVITY![RCV
lines]+ACTIVITY![Number of SAP Receiving Transactions]+ACTIVITY![Number of
Small Parcels Transactions])/ACTIVITY![B087 Number of Inbound Receipts
(Loads)] AS LINESPERLOAD
FROM (((ACTIVITY INNER JOIN INVENTORY ON ACTIVITY.DATE = INVENTORY.Date)
INNER JOIN [QUALITY AND CI] ON ACTIVITY.DATE = [QUALITY AND CI].DATE) INNER
JOIN LABOR ON (INVENTORY.Date = LABOR.DATE) AND ([QUALITY AND CI].DATE =
LABOR.DATE) AND (ACTIVITY.DATE = LABOR.DATE)) INNER JOIN SAFETY ON ([QUALITY
AND CI].DATE = SAFETY.DATE) AND (ACTIVITY.DATE = SAFETY.DATE);


"Marshall Barton" wrote:

KST8WCT wrote:

I made a query that uses fields from multiple tables to calculate values for
daily metrics. I need to present monthly totals of the data as sums and/or
means. The tables are linked by the Date (mm/dd/yyyy) field. I enter ‘between
03/01/2010 and 03/31/2010’ in the criteria for the date field. For the totals
I enter ‘group by’ for the date and ‘sum’ or ‘avg’ for the calculated fields.

When the query is executed I receive an error that I ‘tried to execute a
query that does not include the specified expression *** as part of an
aggregate function. *** is the calculated field in the query. The help button
reveals this is also known as Error 3122.

What am I doing wrong? I thought it would be straightforward to select this
data by a date range and perform the calculations. Thank you for any help you
can provide.


Since you seem to want the totals by month, you need to
calculate a field that either does not include the day part
or standardizes on a fixed day of the month (e.g. the
first). I think the simplest is to replace the date field
in the Select and Group By clauses with an expression like:
Format(datefield, "yyyymm")

Not clear about how your tables are linked by the date
field. Do you use the date field in the Join? It might
help if you posted the query's SQL.

--
Marsh
MVP [MS Access]
.

OMG, what kind of table is that?

Well, I don't see how aggregations come into it. Maybe you
want to use another query to do it? If so, I guess it might
look something like this kind of thing:

SELECT Expr1,
Sum(NHJISBUILD) as TotalNHJISBUILD,
Avg(NHJISBUILD) as AverageNHJISBUILD,
...
FROM yourmonsterquery
GROUP BY Expr1
--
Marsh
MVP [MS Access]


KST8WCT wrote:
The query builds calculated fields from tables such as ACTIVITY and
INVENTORY. All of the tables are related by a DATE field. Relational dbase
class was 16 years ago...

Here is the SQL:
SELECT Format([datefield],"yyyymm") AS Expr1, ACTIVITY.[New Holland Build],
ACTIVITY![New Holland Build]*7.4+ACTIVITY![Number of JIS Pilot Carts
Built]+0.5*ACTIVITY![Number of JIS Track Carts Built] AS NHJISBUILD,
ACTIVITY![CP/HL Build]*5.3+ACTIVITY![Number of JIS Track Carts Built] AS
CPJISBUILD, ACTIVITY![New Holland Build]*4 AS NHJITBUILD, ACTIVITY![CP/HL
Build]*4+ACTIVITY![Number of JIT Pilot Carts Built] AS CPJITBUILD,
ACTIVITY![CP Repack Shipped]+ACTIVITY![HL Repack Shipped]+ACTIVITY![New
Holland Repack Shipped]+ACTIVITY![Hardware Repack Shipped]+ACTIVITY![Cab
Repack Shipped]+ACTIVITY![Manual Repack for Kits Shipped] AS REPACKSHIPPED,
ACTIVITY![RCV lines]+ACTIVITY!Putaways+ACTIVITY![MAC Picks]+ACTIVITY![MAC
Deliveries]+ACTIVITY![Number of Returnables Loaded]+ACTIVITY![Number of
Bucket Racks shipped to Paint]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels
Transactions]+[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![Repack
Pans Restocked] AS TOTALTNX, INVENTORY![Cycle Count Counted]/INVENTORY![Cycle
Count On Hand] AS CYCLECOUNTACCURACY, INVENTORY![Repack Locations (SKUS)
Counted]+INVENTORY![Number of Bays Audited]+INVENTORY![Cycle Count Locations
Counted] AS C010LOCATIONSAUDITED, [C010LOCATIONSAUDITED]-INVENTORY![Repack
Incorrect Pan Quantities]-INVENTORY![Repack Incorrect
Locations]-INVENTORY![Bays Incorrect Location]-INVENTORY![Bays With Incorrect
Label] AS C020CORRECTSKUQTY,
[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![MAC
Deliveries] AS DELIVERIES, [QUALITY AND CI]![B020 Number of late
deliveries]/[DELIVERIES] AS PCTLATE, ([QUALITY AND CI]![Number of wrong part
deliveries]+[QUALITY AND CI]![Number of wrong location
deliveries])/[DELIVERIES] AS PCTWRONGLOC, [QUALITY AND CI]![Total minutes of
final assembly downtime due to Exel]/(ACTIVITY![New Holland Assembly
Time]+ACTIVITY![CP/HL Assembly Time]) AS PCTASYDT, INVENTORY![Cycle Count
Counted]/INVENTORY![Cycle Count On Hand] AS CYCLECTACCRCY, INVENTORY![Bays
Incorrect Location]/INVENTORY![Number of Bays Audited] AS BAYLOCACCURACY,
INVENTORY![Bays With Incorrect Label]/INVENTORY![Number of Bays Audited] AS
BAYLBLACCURACY, INVENTORY![Repack Incorrect Pan Quantities]/INVENTORY![Repack
Locations (SKUS) Counted] AS 2BINCORRQTY, INVENTORY![Repack Incorrect
Locations]/INVENTORY![Repack Locations (SKUS) Counted] AS 2BINCORLOC,
(ACTIVITY![RCV lines]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels Transactions])/LABOR![Total
Receiving Hours] AS RCVLINESPERHR, ACTIVITY!Putaways/LABOR![Total Putaway
Hours] AS PUTPERHR, ACTIVITY![MAC Picks]/LABOR![Total Mac Picking Hours] AS
COPICKPERHR, ACTIVITY![MAC Deliveries]/LABOR![Total Mac Delivery Hours] AS
CODLVPERHR, [REPACKSHIPPED]/LABOR![Total 2-Bin Picking Hours] AS
2BINPIKPERHOUR, [REPACKSHIPPED]/LABOR![Total 2-Bin Delivery Hours] AS
2BINDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Delivery Hours] AS
JISDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Sequencing Hours]
AS JISSEQPERHR, ([NHJITBUILD]+[CPJITBUILD])/LABOR![Total JIT Hours] AS
JITPERHR, ACTIVITY![New Holland Build]*371+ACTIVITY![CP/HL Build]*384 AS
JITPICKS, LABOR![Total Assembly Spider Hours]+LABOR![Total Weld Kit Spider
Hours] AS SUMSPIDERHOURS, [CPJISBUILD]+[NHJISBUILD] AS TOTLJISBUILD,
[NHJITBUILD]+[CPJITBUILD] AS TOTLJITBUILD, (ACTIVITY![RCV
lines]+ACTIVITY![Number of SAP Receiving Transactions]+ACTIVITY![Number of
Small Parcels Transactions])/ACTIVITY![B087 Number of Inbound Receipts
(Loads)] AS LINESPERLOAD
FROM (((ACTIVITY INNER JOIN INVENTORY ON ACTIVITY.DATE = INVENTORY.Date)
INNER JOIN [QUALITY AND CI] ON ACTIVITY.DATE = [QUALITY AND CI].DATE) INNER
JOIN LABOR ON (INVENTORY.Date = LABOR.DATE) AND ([QUALITY AND CI].DATE =
LABOR.DATE) AND (ACTIVITY.DATE = LABOR.DATE)) INNER JOIN SAFETY ON ([QUALITY
AND CI].DATE = SAFETY.DATE) AND (ACTIVITY.DATE = SAFETY.DATE);


"Marshall Barton" wrote:

KST8WCT wrote:

I made a query that uses fields from multiple tables to calculate values for
daily metrics. I need to present monthly totals of the data as sums and/or
means. The tables are linked by the Date (mm/dd/yyyy) field. I enter ‘between
03/01/2010 and 03/31/2010’ in the criteria for the date field. For the totals
I enter ‘group by’ for the date and ‘sum’ or ‘avg’ for the calculated fields.

When the query is executed I receive an error that I ‘tried to execute a
query that does not include the specified expression *** as part of an
aggregate function. *** is the calculated field in the query. The help button
reveals this is also known as Error 3122.

What am I doing wrong? I thought it would be straightforward to select this
data by a date range and perform the calculations. Thank you for any help you
can provide.


Since you seem to want the totals by month, you need to
calculate a field that either does not include the day part
or standardizes on a fixed day of the month (e.g. the
first). I think the simplest is to replace the date field
in the Select and Group By clauses with an expression like:
Format(datefield, "yyyymm")

Not clear about how your tables are linked by the date
field. Do you use the date field in the Join? It might
help if you posted the query's SQL.

It makes the calculated fields from the data tables. Exactly - I want to do
sums, averages, etc. for a range of dates for a report. I have never messed
around with SQL but I will give it a shot! Thanks.

"Marshall Barton" wrote:

OMG, what kind of table is that?

Well, I don't see how aggregations come into it. Maybe you
want to use another query to do it? If so, I guess it might
look something like this kind of thing:

SELECT Expr1,
Sum(NHJISBUILD) as TotalNHJISBUILD,
Avg(NHJISBUILD) as AverageNHJISBUILD,
...
FROM yourmonsterquery
GROUP BY Expr1
--
Marsh
MVP [MS Access]


KST8WCT wrote:
The query builds calculated fields from tables such as ACTIVITY and
INVENTORY. All of the tables are related by a DATE field. Relational dbase
class was 16 years ago...

Here is the SQL:
SELECT Format([datefield],"yyyymm") AS Expr1, ACTIVITY.[New Holland Build],
ACTIVITY![New Holland Build]*7.4+ACTIVITY![Number of JIS Pilot Carts
Built]+0.5*ACTIVITY![Number of JIS Track Carts Built] AS NHJISBUILD,
ACTIVITY![CP/HL Build]*5.3+ACTIVITY![Number of JIS Track Carts Built] AS
CPJISBUILD, ACTIVITY![New Holland Build]*4 AS NHJITBUILD, ACTIVITY![CP/HL
Build]*4+ACTIVITY![Number of JIT Pilot Carts Built] AS CPJITBUILD,
ACTIVITY![CP Repack Shipped]+ACTIVITY![HL Repack Shipped]+ACTIVITY![New
Holland Repack Shipped]+ACTIVITY![Hardware Repack Shipped]+ACTIVITY![Cab
Repack Shipped]+ACTIVITY![Manual Repack for Kits Shipped] AS REPACKSHIPPED,
ACTIVITY![RCV lines]+ACTIVITY!Putaways+ACTIVITY![MAC Picks]+ACTIVITY![MAC
Deliveries]+ACTIVITY![Number of Returnables Loaded]+ACTIVITY![Number of
Bucket Racks shipped to Paint]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels
Transactions]+[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![Repack
Pans Restocked] AS TOTALTNX, INVENTORY![Cycle Count Counted]/INVENTORY![Cycle
Count On Hand] AS CYCLECOUNTACCURACY, INVENTORY![Repack Locations (SKUS)
Counted]+INVENTORY![Number of Bays Audited]+INVENTORY![Cycle Count Locations
Counted] AS C010LOCATIONSAUDITED, [C010LOCATIONSAUDITED]-INVENTORY![Repack
Incorrect Pan Quantities]-INVENTORY![Repack Incorrect
Locations]-INVENTORY![Bays Incorrect Location]-INVENTORY![Bays With Incorrect
Label] AS C020CORRECTSKUQTY,
[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![MAC
Deliveries] AS DELIVERIES, [QUALITY AND CI]![B020 Number of late
deliveries]/[DELIVERIES] AS PCTLATE, ([QUALITY AND CI]![Number of wrong part
deliveries]+[QUALITY AND CI]![Number of wrong location
deliveries])/[DELIVERIES] AS PCTWRONGLOC, [QUALITY AND CI]![Total minutes of
final assembly downtime due to Exel]/(ACTIVITY![New Holland Assembly
Time]+ACTIVITY![CP/HL Assembly Time]) AS PCTASYDT, INVENTORY![Cycle Count
Counted]/INVENTORY![Cycle Count On Hand] AS CYCLECTACCRCY, INVENTORY![Bays
Incorrect Location]/INVENTORY![Number of Bays Audited] AS BAYLOCACCURACY,
INVENTORY![Bays With Incorrect Label]/INVENTORY![Number of Bays Audited] AS
BAYLBLACCURACY, INVENTORY![Repack Incorrect Pan Quantities]/INVENTORY![Repack
Locations (SKUS) Counted] AS 2BINCORRQTY, INVENTORY![Repack Incorrect
Locations]/INVENTORY![Repack Locations (SKUS) Counted] AS 2BINCORLOC,
(ACTIVITY![RCV lines]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels Transactions])/LABOR![Total
Receiving Hours] AS RCVLINESPERHR, ACTIVITY!Putaways/LABOR![Total Putaway
Hours] AS PUTPERHR, ACTIVITY![MAC Picks]/LABOR![Total Mac Picking Hours] AS
COPICKPERHR, ACTIVITY![MAC Deliveries]/LABOR![Total Mac Delivery Hours] AS
CODLVPERHR, [REPACKSHIPPED]/LABOR![Total 2-Bin Picking Hours] AS
2BINPIKPERHOUR, [REPACKSHIPPED]/LABOR![Total 2-Bin Delivery Hours] AS
2BINDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Delivery Hours] AS
JISDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Sequencing Hours]
AS JISSEQPERHR, ([NHJITBUILD]+[CPJITBUILD])/LABOR![Total JIT Hours] AS
JITPERHR, ACTIVITY![New Holland Build]*371+ACTIVITY![CP/HL Build]*384 AS
JITPICKS, LABOR![Total Assembly Spider Hours]+LABOR![Total Weld Kit Spider
Hours] AS SUMSPIDERHOURS, [CPJISBUILD]+[NHJISBUILD] AS TOTLJISBUILD,
[NHJITBUILD]+[CPJITBUILD] AS TOTLJITBUILD, (ACTIVITY![RCV
lines]+ACTIVITY![Number of SAP Receiving Transactions]+ACTIVITY![Number of
Small Parcels Transactions])/ACTIVITY![B087 Number of Inbound Receipts
(Loads)] AS LINESPERLOAD
FROM (((ACTIVITY INNER JOIN INVENTORY ON ACTIVITY.DATE = INVENTORY.Date)
INNER JOIN [QUALITY AND CI] ON ACTIVITY.DATE = [QUALITY AND CI].DATE) INNER
JOIN LABOR ON (INVENTORY.Date = LABOR.DATE) AND ([QUALITY AND CI].DATE =
LABOR.DATE) AND (ACTIVITY.DATE = LABOR.DATE)) INNER JOIN SAFETY ON ([QUALITY
AND CI].DATE = SAFETY.DATE) AND (ACTIVITY.DATE = SAFETY.DATE);


"Marshall Barton" wrote:

KST8WCT wrote:

I made a query that uses fields from multiple tables to calculate values for
daily metrics. I need to present monthly totals of the data as sums and/or
means. The tables are linked by the Date (mm/dd/yyyy) field. I enter ‘between
03/01/2010 and 03/31/2010’ in the criteria for the date field. For the totals
I enter ‘group by’ for the date and ‘sum’ or ‘avg’ for the calculated fields.

When the query is executed I receive an error that I ‘tried to execute a
query that does not include the specified expression *** as part of an
aggregate function. *** is the calculated field in the query. The help button
reveals this is also known as Error 3122.

What am I doing wrong? I thought it would be straightforward to select this
data by a date range and perform the calculations. Thank you for any help you
can provide.


Since you seem to want the totals by month, you need to
calculate a field that either does not include the day part
or standardizes on a fixed day of the month (e.g. the
first). I think the simplest is to replace the date field
in the Select and Group By clauses with an expression like:
Format(datefield, "yyyymm")

Not clear about how your tables are linked by the date
field. Do you use the date field in the Join? It might
help if you posted the query's SQL.
.

I will give this a shot as well. I'm very thankful that these forums exist
and are populated by folks like you who share your knowledge.

"KARL DEWEY" wrote:

There is a way to have your cake and it too.
Create a macro that generates a record for each possibly activity for the
data entry date, display using datasheet form, and then a command button to
finalize by deleting all records with zero or null.

--
Build a little, test a little.


"KST8WCT" wrote:

Thanks for the input, Karl. Under your scenario the person entering the data
would have a form where they enter the date, select the activity (say, from a
combo box) and then enter the quantity, correct? If so, I am probably stuck
because the clerks won't go for taking the time to do all of that.

I currently have it setup so that the data entry person opens a form and
enters the date followed by all of the results for the day. Then I made the
query shown before to do all the calculations. All of that goes to a report
which shows the daily results. Is there a way to run aggregate functions like
sum and avg under this scenario?

Shawn


"KARL DEWEY" wrote:

You have several problems --

You have a spreadsheet instead of relational database --
ACTIVITY![New Holland Build]
ACTIVITY![Number of JIS Pilot Carts Built]
ACTIVITY![Number of JIS Track Carts Built]
ACTIVITY![CP/HL Build]
ACTIVITY![CP Repack Shipped]

It should be like this --
TblACTIVITY --
ActivityID - Autonumber - primary key
ActivityDate - DateTime
Activity - text - linked to table listing all of the type of activity
performed so the you pick instead of typing - fewer mistakes
Quanity - number
etc.

If you are going to join on a date the first create a list of dates --
qryAllDates --
SELECT [QUALITY AND CI].DATE
FROM [QUALITY AND CI]
GROUP BY [QUALITY AND CI].DATE
UNION SELECT ACTIVITY.DATE
FROM ACTIVITY
GROUP BY ACTIVITY.DATE
UNION SELECT INVENTORY.Date
FROM INVENTORY
GROUP BY INVENTORY.Date
UNION SELECT LABOR.DATE
FROM LABOR
GROUP BY LABOR.DATE
UNION SELECT SAFETY.DATE
FROM SAFETY
GROUP BY SAFETY.DATE;

Then use this --
FROM ((((qryAllDates LEFT JOIN ACTIVITY ON qryAllDates.Date = ACTIVITY.DATE)
LEFT JOIN INVENTORY ON qryAllDates.Date = INVENTORY.Date)
LEFT JOIN [QUALITY AND CI] ON qryAllDates.Date = [QUALITY AND CI].DATE) LEFT
JOIN LABOR ON qryAllDates.Date = LABOR.DATE) LEFT JOIN SAFETY ON
qryAllDates.Date = SAFETY.DATE;

--
Build a little, test a little.


"KST8WCT" wrote:

The query builds calculated fields from tables such as ACTIVITY and
INVENTORY. All of the tables are related by a DATE field. Relational dbase
class was 16 years ago...

Here is the SQL:
SELECT Format([datefield],"yyyymm") AS Expr1, ACTIVITY.[New Holland Build],
ACTIVITY![New Holland Build]*7.4+ACTIVITY![Number of JIS Pilot Carts
Built]+0.5*ACTIVITY![Number of JIS Track Carts Built] AS NHJISBUILD,
ACTIVITY![CP/HL Build]*5.3+ACTIVITY![Number of JIS Track Carts Built] AS
CPJISBUILD, ACTIVITY![New Holland Build]*4 AS NHJITBUILD, ACTIVITY![CP/HL
Build]*4+ACTIVITY![Number of JIT Pilot Carts Built] AS CPJITBUILD,
ACTIVITY![CP Repack Shipped]+ACTIVITY![HL Repack Shipped]+ACTIVITY![New
Holland Repack Shipped]+ACTIVITY![Hardware Repack Shipped]+ACTIVITY![Cab
Repack Shipped]+ACTIVITY![Manual Repack for Kits Shipped] AS REPACKSHIPPED,
ACTIVITY![RCV lines]+ACTIVITY!Putaways+ACTIVITY![MAC Picks]+ACTIVITY![MAC
Deliveries]+ACTIVITY![Number of Returnables Loaded]+ACTIVITY![Number of
Bucket Racks shipped to Paint]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels
Transactions]+[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![Repack
Pans Restocked] AS TOTALTNX, INVENTORY![Cycle Count Counted]/INVENTORY![Cycle
Count On Hand] AS CYCLECOUNTACCURACY, INVENTORY![Repack Locations (SKUS)
Counted]+INVENTORY![Number of Bays Audited]+INVENTORY![Cycle Count Locations
Counted] AS C010LOCATIONSAUDITED, [C010LOCATIONSAUDITED]-INVENTORY![Repack
Incorrect Pan Quantities]-INVENTORY![Repack Incorrect
Locations]-INVENTORY![Bays Incorrect Location]-INVENTORY![Bays With Incorrect
Label] AS C020CORRECTSKUQTY,
[NHJISBUILD]+[CPJISBUILD]+[NHJITBUILD]+[CPJITBUILD]+[REPACKSHIPPED]+ACTIVITY![MAC
Deliveries] AS DELIVERIES, [QUALITY AND CI]![B020 Number of late
deliveries]/[DELIVERIES] AS PCTLATE, ([QUALITY AND CI]![Number of wrong part
deliveries]+[QUALITY AND CI]![Number of wrong location
deliveries])/[DELIVERIES] AS PCTWRONGLOC, [QUALITY AND CI]![Total minutes of
final assembly downtime due to Exel]/(ACTIVITY![New Holland Assembly
Time]+ACTIVITY![CP/HL Assembly Time]) AS PCTASYDT, INVENTORY![Cycle Count
Counted]/INVENTORY![Cycle Count On Hand] AS CYCLECTACCRCY, INVENTORY![Bays
Incorrect Location]/INVENTORY![Number of Bays Audited] AS BAYLOCACCURACY,
INVENTORY![Bays With Incorrect Label]/INVENTORY![Number of Bays Audited] AS
BAYLBLACCURACY, INVENTORY![Repack Incorrect Pan Quantities]/INVENTORY![Repack
Locations (SKUS) Counted] AS 2BINCORRQTY, INVENTORY![Repack Incorrect
Locations]/INVENTORY![Repack Locations (SKUS) Counted] AS 2BINCORLOC,
(ACTIVITY![RCV lines]+ACTIVITY![Number of SAP Receiving
Transactions]+ACTIVITY![Number of Small Parcels Transactions])/LABOR![Total
Receiving Hours] AS RCVLINESPERHR, ACTIVITY!Putaways/LABOR![Total Putaway
Hours] AS PUTPERHR, ACTIVITY![MAC Picks]/LABOR![Total Mac Picking Hours] AS
COPICKPERHR, ACTIVITY![MAC Deliveries]/LABOR![Total Mac Delivery Hours] AS
CODLVPERHR, [REPACKSHIPPED]/LABOR![Total 2-Bin Picking Hours] AS
2BINPIKPERHOUR, [REPACKSHIPPED]/LABOR![Total 2-Bin Delivery Hours] AS
2BINDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Delivery Hours] AS
JISDLVPERHR, ([NHJISBUILD]+[CPJISBUILD])/LABOR![Total JIS Sequencing Hours]
AS JISSEQPERHR, ([NHJITBUILD]+[CPJITBUILD])/LABOR![Total JIT Hours] AS
JITPERHR, ACTIVITY![New Holland Build]*371+ACTIVITY![CP/HL Build]*384 AS
JITPICKS, LABOR![Total Assembly Spider Hours]+LABOR![Total Weld Kit Spider
Hours] AS SUMSPIDERHOURS, [CPJISBUILD]+[NHJISBUILD] AS TOTLJISBUILD,
[NHJITBUILD]+[CPJITBUILD] AS TOTLJITBUILD, (ACTIVITY![RCV
lines]+ACTIVITY![Number of SAP Receiving Transactions]+ACTIVITY![Number of
Small Parcels Transactions])/ACTIVITY![B087 Number of Inbound Receipts
(Loads)] AS LINESPERLOAD
FROM (((ACTIVITY INNER JOIN INVENTORY ON ACTIVITY.DATE = INVENTORY.Date)
INNER JOIN [QUALITY AND CI] ON ACTIVITY.DATE = [QUALITY AND CI].DATE) INNER
JOIN LABOR ON (INVENTORY.Date = LABOR.DATE) AND ([QUALITY AND CI].DATE =
LABOR.DATE) AND (ACTIVITY.DATE = LABOR.DATE)) INNER JOIN SAFETY ON ([QUALITY
AND CI].DATE = SAFETY.DATE) AND (ACTIVITY.DATE = SAFETY.DATE);


"Marshall Barton" wrote:

KST8WCT wrote:

I made a query that uses fields from multiple tables to calculate values for
daily metrics. I need to present monthly totals of the data as sums and/or
means. The tables are linked by the Date (mm/dd/yyyy) field. I enter ‘between
03/01/2010 and 03/31/2010’ in the criteria for the date field. For the totals
I enter ‘group by’ for the date and ‘sum’ or ‘avg’ for the calculated fields.

When the query is executed I receive an error that I ‘tried to execute a
query that does not include the specified expression *** as part of an
aggregate function. *** is the calculated field in the query. The help button
reveals this is also known as Error 3122.

What am I doing wrong? I thought it would be straightforward to select this
data by a date range and perform the calculations. Thank you for any help you
can provide.


Since you seem to want the totals by month, you need to
calculate a field that either does not include the day part
or standardizes on a fixed day of the month (e.g. the
first). I think the simplest is to replace the date field
in the Select and Group By clauses with an expression like:
Format(datefield, "yyyymm")

Not clear about how your tables are linked by the date
field. Do you use the date field in the Join? It might
help if you posted the query's SQL.

--
Marsh
MVP [MS Access]
.