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.

0
Utf
3/24/2010 1:30:01 PM
access.queries 6343 articles. 1 followers. Follow

8 Replies
1161 Views

Similar Articles

[PageSpeed] 0

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]
0
Marshall
3/24/2010 2:02:29 PM
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]
> .
> 
0
Utf
3/24/2010 6:07:01 PM
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]
> > .
> > 
0
Utf
3/24/2010 11:39:01 PM
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]
> > > .
> > > 
0
Utf
3/25/2010 1:56:09 PM
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]
> > > > .
> > > > 
0
Utf
3/25/2010 2:25:01 PM
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.
0
Marshall
3/25/2010 2:41:22 PM
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.
> .
> 
0
Utf
3/25/2010 3:25:01 PM
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]
> > > > > .
> > > > > 
0
Utf
3/25/2010 3:26:01 PM
Reply:

Similar Artilces:

Aggregate returning 4 "non-aggregate" records.
A simple aggregate Query is Grouping on 4 Fields (one Field uses a Lookup..but I see NO "duplicates" in that). Then I run "sum"s on several Long Integer Fields. Result = a few (4) "duplicate aggregate values". Any known Access Bug for this? TIA - Bob As far as I understand, that is perfectly ok. Name Score Mary 1 Mary 3 Ann 4 then SELECT name, SUM(score) FROM myTable GROUP BY name will lead to a duplicated sum of scores: Mary 4 Ann 4 The only bug I am aware is when you have an indexed field not allowing ...

Query on zip codes
I have a query that searches a database for matching zip codes. The user enters the desired 5 digit zip code and the query displays the necessary information. The database has zip codes stored as 3 digit (first 3 digits of zip) and 5 digit. How can I write the query to find both the 3 and 5 digit matches? -- Dirk "Dirk_Bob" <DirkBob@discussions.microsoft.com> wrote in message news:347CDA14-9807-445A-8E94-4C729A6119F6@microsoft.com... > I have a query that searches a database for matching zip codes. The user > enters the desired 5 digit zip code and the query disp...

Loan Calculation
Can I get some help with some loan calculation formula? I need to calculate payments based on 360/365/366/365.25 days in a year. That's one part. The other is, I also need to figure accurate payments if the days to first payment from date of contract is not the standard 30, say it is 1, 3, 10, 45, 60, 90, 100 etc, any number that the user can opt. How do I do this? Also I see some about last payment being different from the regular payments. WHat does that mean? Thanks Frank Hi, There are a load of financial functions built into excel. Have a look at the help pages for financial func...

Percentage calculation
I have a spreadsheet with several columns and cells with values which I need to increase by 20%. How do I do that all at the same time. thanks Daniel Daniel, Put 1.2 in a cell, copy it. Select the cells to be increased. Edit - Paste Special - Multiply. Trash the 1.2 cell. -- Earl Kiosterud ------------------------------------------- "Daniel Louwrens" <flint_dlouwrens@nospambigpond.com> wrote in message news:u9UqXpHiDHA.2504@TK2MSFTNGP09.phx.gbl... > I have a spreadsheet with several columns and cells with values which I need > to increase by 20%. > How do ...

Column Number as parameter in Query to select data
Hi is it possible that we can give Column Number as a parameter to select Records at Runtime in access table . eg, Table having structure like this, Where Qtr1..Qtr4 Stores Amt Paid by customer, If runtime i provide Column name to quaery it should return Cust with specified quarter like "Select Cust,<Column No> from tbl" , Where Column No is the parameter Cust,Qtr1,Qtr2,Qtr3,Qtr4 X-10000-20000-30000-4000 I know if the data table is normalized like this Cust,Qtr-Sales X-Q1-1000 X-Q2-2000 X-Q3-3000 X-Q4-4000 it is quite easy to select , but my proble is , presently i am...

How is Return for Period calculated?
I am using Money 2003 and I did a Performance by Investment Account report for 2004. For most of stocks the Return for Period column is the Realized Gain/Loss + the Gain/Loss of your current holdings. However for some stocks the numbers don't seem to add up. For instance the realized gain is $400, gain of what I currently have invested is $500, and the return is reported as about $100. Could someone explain to me how this is calculated or if it's a bug. Thanks! Jon ...

External Data Query
I have a worksheet that was originally set up with the Get External Data-->New Database Query menu option, I used ODBC data sources and entered my user id and password at the time. Since then, I use the Refresh Data menu option monthly to get new data. The password is embedded in the worksheet so I don't need to enter it a second time. However, I just changed my password on the database server and now Refresh Data gives me an ODBC error (Attempt to connect to the server failed.) I can't find a way to update the worksheet with the new password. Help please! >-----Origi...

excel autofilter arrow custom query
hello and thanks for any future help you can give me. i am making a spreadsheet to record data for statistical purposes. m problem is; that i have one column of numbers, that i want to hav saved on this spreadsheet when i open it, and then another column o numbers, that i will put on this spreadsheet after i have opened it some of the numbers in the columns may well be duplications, copies and so i would like to set-up a third column, which shows th duplicated numbers. i have put on an autofilter, and using the arrow selected custom thinking that i could put in a formula for this thir column t...

Merging from a query
I have a database I am working on that has a bunch of addresses in it. I need to be able to select a chunk of dates to be sent to word for a mail merge. I need a way to pass the date criteria data (from a message window) to a query to get the right records, and send the data the query returns to word in by activiating one click event. What is the best way to do this? ...

Referencing forms and fields
I would like to reuse macros across many forms but i do not know the syntax that would allow me to do so. For example, I have a macro that uses setvalue to change the value of a field if another field's value changes. The form field reference is [Forms]![f_events_and_shows_ds]![f_bookings_all].[Form]![entered_date_time]. I would like to invoke the same event in two forms but at present, i have to create (and maintain) two versions of the macro. Can you tell me what the syntax is that would allow me to replace the form name with the current form name (say) and is there som...

how do you calculate a test statistic from excel??
What is it that you want to calculate? Give an example of your data. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "ann" <anonymous@discussions.microsoft.com> wrote in message news:DB3966EC-6560-497D-B21F-1B6B81F11D1F@microsoft.com... > ...

How do I hide calculations/formulas?
I have created a worksheet for the purpose of date tracking. The problem that I have is that 1/14/1900 is displayed after I create a formula. Example; the formula is a2=a1+14. When there is no date listed in a1 1/14/1900 is displayed. I do not want to see this. When I tried to hide this, I was unable to see anything. Is it possible to hide this particular problem? Thanks in advance for any help with this matter. =if(isblank(a1),"",a1+14) ************ Anne Troy www.OfficeArticles.com "Xzilla" <Xzilla@discussions.microsoft.com> wrote in message news:...

Newbee question (fields)
I have created a simple replication of the Appointment form. Using Outlook 2003. 1. How can I recreate the 'note' field as in the Appointment form? I can't find it in the field chooser. 2. What is the viewing order of the Day/Week/Month banner. It looks like: a. Alarm Icon b. Subject c. Location d. Notes (this is the one I am looking for) e. ?????? Is anything else displayed after this? Where can I find more information about these kind of questions. FYI, there is a newsgroup specifically for general Outlook programming issues "...

calculate memory utlisation
How to calculate the memory utilised for allocation of objects in C#?. Can u help me with the code Submitted via EggHeadCafe - Software Developer Portal of Choice Silverlight 2 Beta 2: Doing Data Part III http://www.eggheadcafe.com/tutorials/aspnet/d566463d-83bd-486a-8633-53aa54f405bf/silverlight-2-beta-2-doi.aspx anjali krishna wrote: > How to calculate the memory utilised for allocation of objects in C#?. Can u help me with the code You can use a memory profiler. I hear the Red Gate one is good. There's not really a practical way I know of to track memory used per-...

Calculating(Processors(2)): %
I am running the code below in a .xlsm '07 spreadsheet. The problem I'm having is the code runs painfully slow and I can't figure out why. As soon as it starts my CPU usage spikes up to 52%, 51% of which is EXCEL.EXE. I also get "Calculating(Processors(2)): %" in my status bar constantly calculating "something" and showing me percentages. I tested this code using 1000 rows of data and it took ~8 mins, 2000 rows took ~15 mins, and 4000 rows took ~28 mins. Each row of data has information in columns A-Q. What is causing this latency? All the ...

Help with query 04-20-10
Hello, I need some help with a query that I can't quite figure out the logic for. I need to select the primary key field from table1 where there are no records in which field1 are null and in which the number of records in which field1 =1 is greater than zero. I've tried several permutations to get this query to work and have not had success. Can someone give me a hand please? Thanks S I am not sure I understand the requirements, but see if the following example will help: CREATE TABLE table1 ( keycol INT NOT NULL PRIMARY KEY, datacol INT); INSERT INTO...

PM Field
Hello, I am doing some work with PM30200, the Payables Paid Transaction History table. The table contains a field called CNTRLTYP (Control Type) that contains an integer. It appears to be used when say an invoice and an unrelated payment have the same voucher number. I believe that this happens when the voucher number and payment number are identical even though the transactions are not related, as when both numbering sequences have the same starting point, ie.. 000000001. It appears that a similar field, by the same name, is used in numerous tables throughout GP. Can anyone confirm my assu...

I want to calculate the grade I need to get an A in my class
-- Neal's Quest A 95% average should get you close. But I'd talk to the instruction to see what the grading scale is. Neal wrote: > > -- > Neal's Quest -- Dave Peterson ...

Calculate Data Field To Data Field in Pivot Table?
Is it possible to create a formula that calculates one data field to another inside a pivot table? For example if I create a SUM in the data field, can I multple that SUM times a percentage in another data field? Is this too simple? You can create a calculated field in a pivot table, and it will use the Sum of underlying values for each field in the calculation. So you could create a formula that includes a percentage, e.g.: =Field1 * Field2 *.05 Ronster wrote: > Is it possible to create a formula that calculates one data field to > another inside a pivot table? For example i...

Formula to calculate an age from a date of birth
I'm looking for the formula to calculate the age from a date of birth. One way: =DATEDIF(A1,TODAY(),"y") where A1 contains the DOB. See http://cpearson.com/excel/datedif.htm for documentation on DATEDIF(). In article <FBF2C1DE-CFEF-442D-BD61-2C0CFCEFC682@microsoft.com>, Lisa C <anonymous@discussions.microsoft.com> wrote: > I'm looking for the formula to calculate the age from a date of birth. for more than just years, just expand. from xlfdic01.xls, if date is in D34... ="Age is "&DATEDIF(D34,TODAY(),"y")&" Years &...

Calculations on a parent form based on info in Child
Bear with me, I am very new to access... I have a contributor table and a donations table. I also have a form for each. On the contributors form I have displayed a child form generated from the donation table which shows the donations that contributor has made. I would like to have a few fields on the parent which totals info listed in the child. For example: Count of donations made, Total value of those donations, and Based on the amount the individual has donated I would like Access to assign a category. Friend, Supporter, patron... I was told to use the following formulas Count of ...

Aggregating data
Hello All: I've already found a great deal of help reading the archives, but I'm now stuck with a structural issue. I am building a spreadsheet to track recurring training requirements for each of the pilots in my corporate flight operation. This is my first attempt at vba and I�m stuck in a few spots. I have one sheet for each pilot with a list of training requirements on each. The last training date is entered for each requirement and the sheet calculates the next due date for each of 15 training requirements based on different variables. My largest problem is creating a rep...

AUTONUMBER Field Increments after "UNDO / CLEAR FORM"
I have an autonumber field in a form. When I use an "UNDO / CLEAR FORM" command button (imbedded onto this form) to undo the contents in the form, the AUTONUMBER value of the next record inputted increments by one. Example: I am adding / inputting record number 15 (I have the AUTONUMBERed "RECNUMBER" field visible on my screen form to enable viewing of a specific record), I discover an error and UNDO / CLEAR the entire form using the command ("UNDO") button I've created. When I wish to re-input the data, I see the record number value of the new r...

Query/Expression Help Required!
Hello, i would appreciate any help in regards to the problem i am having. I will try and explain it to the best of my ability (im a newbie to Access 2000) Here is a snipt of the information i have extracted via a query. I have been able to work out Duration via a calculation. "Agent Pin" "Start Time" "AUX Name" "End Time" "Duration" "7015" "13:45:21" "After Call Work" "13:54:18" "537" "7015" "14:30:25" "After Call Work" "14:31:03" &...

24 Hour Calculations
I was wondering if anyone has a solution for calculating (in 24hr Format): Total Shift Hours per employee when start of shift is: 6/13/05 20:00 ending 6/14/05 04:00. AND Once shift totals are calculated in a column, subtotal both employee weekly hours and company man - hours per shift. If you have entered both the date and time, as you show, in a single cell, then you can simply subtract the two and format as time to get the hours worked. As far as the subtotals are concerned, SUMIF will work for the totals by employee. If the starting times for each shift are identical for all employee...