A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Weighted Calculations



 
 
Thread Tools Display Modes
  #1  
Old September 23rd, 2009, 04:53 PM posted to microsoft.public.access.reports
TYSITC
external usenet poster
 
Posts: 4
Default Weighted Calculations

I'm trying to total an aggregate function in a report. Here's the basics.

UnitIn- UnitOut - Yield - WtYield
10100 - 10000 - 99.01% - 90.01%
2000 - 1000 - 50% - 4.55%
Totals
12100 - 11000 - N/A - 94.56%

WtYield =UnitOut/sum([UnitOut])

=sum([WtYield]) does not work. These calculations are taking place in a
report. I tried to make it happen in a query, but due to the group by
requirement in the query in order to show all the proper fields (not shown
here in interest of brevity) for the report it doesn't sum up the LBSProd
properly.

Is there a way to remove the Group By in the query, and still have the data
show in a report perhaps?

Any ideas?
  #2  
Old September 23rd, 2009, 05:52 PM posted to microsoft.public.access.reports
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Weighted Calculations

Post the SQL of your query as you have it now. Open query in design view,
click on VIEW - SQL View, highlight all, copy, and paste in a post.
--
Build a little, test a little.


"TYSITC" wrote:

I'm trying to total an aggregate function in a report. Here's the basics.

UnitIn- UnitOut - Yield - WtYield
10100 - 10000 - 99.01% - 90.01%
2000 - 1000 - 50% - 4.55%
Totals
12100 - 11000 - N/A - 94.56%

WtYield =UnitOut/sum([UnitOut])

=sum([WtYield]) does not work. These calculations are taking place in a
report. I tried to make it happen in a query, but due to the group by
requirement in the query in order to show all the proper fields (not shown
here in interest of brevity) for the report it doesn't sum up the LBSProd
properly.

Is there a way to remove the Group By in the query, and still have the data
show in a report perhaps?

Any ideas?

  #3  
Old September 23rd, 2009, 05:55 PM posted to microsoft.public.access.reports
TYSITC
external usenet poster
 
Posts: 4
Default Weighted Calculations

SELECT [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM No],
[Prod Report Master Data].Brand, [Prod Report Master Data].Description, [Prod
Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED], [Prod
Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to Line],
[Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
FROM [Prod Report Master Data]
GROUP BY [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM
No], [Prod Report Master Data].Brand, [Prod Report Master Data].Description,
[Prod Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED],
[Prod Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to
Line], [Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
HAVING ((([Prod Report Master Data].Date)=[Production Date]));

"KARL DEWEY" wrote:

Post the SQL of your query as you have it now. Open query in design view,
click on VIEW - SQL View, highlight all, copy, and paste in a post.
--
Build a little, test a little.


"TYSITC" wrote:

I'm trying to total an aggregate function in a report. Here's the basics.

UnitIn- UnitOut - Yield - WtYield
10100 - 10000 - 99.01% - 90.01%
2000 - 1000 - 50% - 4.55%
Totals
12100 - 11000 - N/A - 94.56%

WtYield =UnitOut/sum([UnitOut])

=sum([WtYield]) does not work. These calculations are taking place in a
report. I tried to make it happen in a query, but due to the group by
requirement in the query in order to show all the proper fields (not shown
here in interest of brevity) for the report it doesn't sum up the LBSProd
properly.

Is there a way to remove the Group By in the query, and still have the data
show in a report perhaps?

Any ideas?

  #4  
Old September 23rd, 2009, 06:31 PM posted to microsoft.public.access.reports
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Weighted Calculations

Are you sure you posted the correct SQL as I can not find LBSProd, UnitIn,
UnitOut, Yield, or WtYield within the query.

--
Build a little, test a little.


"TYSITC" wrote:

SELECT [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM No],
[Prod Report Master Data].Brand, [Prod Report Master Data].Description, [Prod
Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED], [Prod
Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to Line],
[Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
FROM [Prod Report Master Data]
GROUP BY [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM
No], [Prod Report Master Data].Brand, [Prod Report Master Data].Description,
[Prod Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED],
[Prod Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to
Line], [Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
HAVING ((([Prod Report Master Data].Date)=[Production Date]));

"KARL DEWEY" wrote:

Post the SQL of your query as you have it now. Open query in design view,
click on VIEW - SQL View, highlight all, copy, and paste in a post.
--
Build a little, test a little.


"TYSITC" wrote:

I'm trying to total an aggregate function in a report. Here's the basics.

UnitIn- UnitOut - Yield - WtYield
10100 - 10000 - 99.01% - 90.01%
2000 - 1000 - 50% - 4.55%
Totals
12100 - 11000 - N/A - 94.56%

WtYield =UnitOut/sum([UnitOut])

=sum([WtYield]) does not work. These calculations are taking place in a
report. I tried to make it happen in a query, but due to the group by
requirement in the query in order to show all the proper fields (not shown
here in interest of brevity) for the report it doesn't sum up the LBSProd
properly.

Is there a way to remove the Group By in the query, and still have the data
show in a report perhaps?

Any ideas?

  #5  
Old September 23rd, 2009, 06:39 PM posted to microsoft.public.access.reports
TYSITC
external usenet poster
 
Posts: 4
Default Weighted Calculations

I simplified the field names for the purpose of the question. Here's my
attempts to do the calculation, but it sums on the individual records, and I
need it to sum [Production Weight] per [Line#] value.

SELECT [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM No],
[Prod Report Master Data].Brand, [Prod Report Master Data].Description, [Prod
Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED], [Prod
Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to Line],
[Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield], Sum([Prod Report Master Data].[Production Weight]) AS
ProdWtSub
FROM [Prod Report Master Data]
GROUP BY [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM
No], [Prod Report Master Data].Brand, [Prod Report Master Data].Description,
[Prod Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED],
[Prod Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to
Line], [Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
HAVING ((([Prod Report Master Data].Date)=[Production Date]));


"KARL DEWEY" wrote:

Are you sure you posted the correct SQL as I can not find LBSProd, UnitIn,
UnitOut, Yield, or WtYield within the query.

--
Build a little, test a little.


"TYSITC" wrote:

SELECT [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM No],
[Prod Report Master Data].Brand, [Prod Report Master Data].Description, [Prod
Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED], [Prod
Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to Line],
[Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
FROM [Prod Report Master Data]
GROUP BY [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM
No], [Prod Report Master Data].Brand, [Prod Report Master Data].Description,
[Prod Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED],
[Prod Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to
Line], [Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
HAVING ((([Prod Report Master Data].Date)=[Production Date]));

"KARL DEWEY" wrote:

Post the SQL of your query as you have it now. Open query in design view,
click on VIEW - SQL View, highlight all, copy, and paste in a post.
--
Build a little, test a little.


"TYSITC" wrote:

I'm trying to total an aggregate function in a report. Here's the basics.

UnitIn- UnitOut - Yield - WtYield
10100 - 10000 - 99.01% - 90.01%
2000 - 1000 - 50% - 4.55%
Totals
12100 - 11000 - N/A - 94.56%

WtYield =UnitOut/sum([UnitOut])

=sum([WtYield]) does not work. These calculations are taking place in a
report. I tried to make it happen in a query, but due to the group by
requirement in the query in order to show all the proper fields (not shown
here in interest of brevity) for the report it doesn't sum up the LBSProd
properly.

Is there a way to remove the Group By in the query, and still have the data
show in a report perhaps?

Any ideas?

  #6  
Old September 23rd, 2009, 07:05 PM posted to microsoft.public.access.reports
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Weighted Calculations

You can not total and have individual weights at the same time.

I am still learning subqueries and did not test this --
SELECT [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM No],
[Prod Report Master Data].Brand, [Prod Report Master Data].Description, [Prod
Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED], [Prod
Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to Line],
[Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield], (SELECT Sum([Prod Report Master Data].[Production Weight]
FROM [Prod Report Master Data] AS [AA] WHERE [AA].[Line#] = [Prod Report
Master Data].[Line#]) AS ProdWtSub
FROM [Prod Report Master Data]
HAVING ((([Prod Report Master Data].Date)=[Production Date]));

You can build a separate query for the totals and join it on the Line#.

--
Build a little, test a little.


"TYSITC" wrote:

I simplified the field names for the purpose of the question. Here's my
attempts to do the calculation, but it sums on the individual records, and I
need it to sum [Production Weight] per [Line#] value.

SELECT [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM No],
[Prod Report Master Data].Brand, [Prod Report Master Data].Description, [Prod
Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED], [Prod
Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to Line],
[Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield], Sum([Prod Report Master Data].[Production Weight]) AS
ProdWtSub
FROM [Prod Report Master Data]
GROUP BY [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM
No], [Prod Report Master Data].Brand, [Prod Report Master Data].Description,
[Prod Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED],
[Prod Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to
Line], [Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
HAVING ((([Prod Report Master Data].Date)=[Production Date]));


"KARL DEWEY" wrote:

Are you sure you posted the correct SQL as I can not find LBSProd, UnitIn,
UnitOut, Yield, or WtYield within the query.

--
Build a little, test a little.


"TYSITC" wrote:

SELECT [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM No],
[Prod Report Master Data].Brand, [Prod Report Master Data].Description, [Prod
Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED], [Prod
Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to Line],
[Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
FROM [Prod Report Master Data]
GROUP BY [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM
No], [Prod Report Master Data].Brand, [Prod Report Master Data].Description,
[Prod Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED],
[Prod Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to
Line], [Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
HAVING ((([Prod Report Master Data].Date)=[Production Date]));

"KARL DEWEY" wrote:

Post the SQL of your query as you have it now. Open query in design view,
click on VIEW - SQL View, highlight all, copy, and paste in a post.
--
Build a little, test a little.


"TYSITC" wrote:

I'm trying to total an aggregate function in a report. Here's the basics.

UnitIn- UnitOut - Yield - WtYield
10100 - 10000 - 99.01% - 90.01%
2000 - 1000 - 50% - 4.55%
Totals
12100 - 11000 - N/A - 94.56%

WtYield =UnitOut/sum([UnitOut])

=sum([WtYield]) does not work. These calculations are taking place in a
report. I tried to make it happen in a query, but due to the group by
requirement in the query in order to show all the proper fields (not shown
here in interest of brevity) for the report it doesn't sum up the LBSProd
properly.

Is there a way to remove the Group By in the query, and still have the data
show in a report perhaps?

Any ideas?

  #7  
Old September 23rd, 2009, 07:30 PM posted to microsoft.public.access.reports
TYSITC
external usenet poster
 
Posts: 4
Default Weighted Calculations

There is a syntax error in this. A missing ), or ] that I can't seem to find.

"KARL DEWEY" wrote:

You can not total and have individual weights at the same time.

I am still learning subqueries and did not test this --
SELECT [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM No],
[Prod Report Master Data].Brand, [Prod Report Master Data].Description, [Prod
Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED], [Prod
Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to Line],
[Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield], (SELECT Sum([Prod Report Master Data].[Production Weight]
FROM [Prod Report Master Data] AS [AA] WHERE [AA].[Line#] = [Prod Report
Master Data].[Line#]) AS ProdWtSub
FROM [Prod Report Master Data]
HAVING ((([Prod Report Master Data].Date)=[Production Date]));

You can build a separate query for the totals and join it on the Line#.

--
Build a little, test a little.


"TYSITC" wrote:

I simplified the field names for the purpose of the question. Here's my
attempts to do the calculation, but it sums on the individual records, and I
need it to sum [Production Weight] per [Line#] value.

SELECT [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM No],
[Prod Report Master Data].Brand, [Prod Report Master Data].Description, [Prod
Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED], [Prod
Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to Line],
[Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield], Sum([Prod Report Master Data].[Production Weight]) AS
ProdWtSub
FROM [Prod Report Master Data]
GROUP BY [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM
No], [Prod Report Master Data].Brand, [Prod Report Master Data].Description,
[Prod Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED],
[Prod Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to
Line], [Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
HAVING ((([Prod Report Master Data].Date)=[Production Date]));


"KARL DEWEY" wrote:

Are you sure you posted the correct SQL as I can not find LBSProd, UnitIn,
UnitOut, Yield, or WtYield within the query.

--
Build a little, test a little.


"TYSITC" wrote:

SELECT [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM No],
[Prod Report Master Data].Brand, [Prod Report Master Data].Description, [Prod
Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED], [Prod
Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to Line],
[Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
FROM [Prod Report Master Data]
GROUP BY [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM
No], [Prod Report Master Data].Brand, [Prod Report Master Data].Description,
[Prod Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED],
[Prod Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to
Line], [Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
HAVING ((([Prod Report Master Data].Date)=[Production Date]));

"KARL DEWEY" wrote:

Post the SQL of your query as you have it now. Open query in design view,
click on VIEW - SQL View, highlight all, copy, and paste in a post.
--
Build a little, test a little.


"TYSITC" wrote:

I'm trying to total an aggregate function in a report. Here's the basics.

UnitIn- UnitOut - Yield - WtYield
10100 - 10000 - 99.01% - 90.01%
2000 - 1000 - 50% - 4.55%
Totals
12100 - 11000 - N/A - 94.56%

WtYield =UnitOut/sum([UnitOut])

=sum([WtYield]) does not work. These calculations are taking place in a
report. I tried to make it happen in a query, but due to the group by
requirement in the query in order to show all the proper fields (not shown
here in interest of brevity) for the report it doesn't sum up the LBSProd
properly.

Is there a way to remove the Group By in the query, and still have the data
show in a report perhaps?

Any ideas?

  #8  
Old September 23rd, 2009, 09:21 PM posted to microsoft.public.access.reports
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Weighted Calculations

It need to be just before the FROM in the subquery like this --
(SELECT Sum([Prod Report Master Data].[Production Weight]) FROM [Prod
Report Master Data] AS [AA] WHERE [AA].[Line#] = [Prod Report Master
Data].[Line#]) AS ProdWtSub

--
Build a little, test a little.


"TYSITC" wrote:

There is a syntax error in this. A missing ), or ] that I can't seem to find.

"KARL DEWEY" wrote:

You can not total and have individual weights at the same time.

I am still learning subqueries and did not test this --
SELECT [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM No],
[Prod Report Master Data].Brand, [Prod Report Master Data].Description, [Prod
Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED], [Prod
Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to Line],
[Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield], (SELECT Sum([Prod Report Master Data].[Production Weight]
FROM [Prod Report Master Data] AS [AA] WHERE [AA].[Line#] = [Prod Report
Master Data].[Line#]) AS ProdWtSub
FROM [Prod Report Master Data]
HAVING ((([Prod Report Master Data].Date)=[Production Date]));

You can build a separate query for the totals and join it on the Line#.

--
Build a little, test a little.


"TYSITC" wrote:

I simplified the field names for the purpose of the question. Here's my
attempts to do the calculation, but it sums on the individual records, and I
need it to sum [Production Weight] per [Line#] value.

SELECT [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM No],
[Prod Report Master Data].Brand, [Prod Report Master Data].Description, [Prod
Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED], [Prod
Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to Line],
[Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield], Sum([Prod Report Master Data].[Production Weight]) AS
ProdWtSub
FROM [Prod Report Master Data]
GROUP BY [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM
No], [Prod Report Master Data].Brand, [Prod Report Master Data].Description,
[Prod Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED],
[Prod Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to
Line], [Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
HAVING ((([Prod Report Master Data].Date)=[Production Date]));


"KARL DEWEY" wrote:

Are you sure you posted the correct SQL as I can not find LBSProd, UnitIn,
UnitOut, Yield, or WtYield within the query.

--
Build a little, test a little.


"TYSITC" wrote:

SELECT [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM No],
[Prod Report Master Data].Brand, [Prod Report Master Data].Description, [Prod
Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED], [Prod
Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to Line],
[Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
FROM [Prod Report Master Data]
GROUP BY [Prod Report Master Data].Date, [Prod Report Master Data].[ITEM
No], [Prod Report Master Data].Brand, [Prod Report Master Data].Description,
[Prod Report Master Data].[Line#], [Prod Report Master Data].[CS PRODUCED],
[Prod Report Master Data].[CS WT], [Prod Report Master Data].[Pounds to
Line], [Prod Report Master Data].[Production Weight], [Prod Report Master
Data].[Start Time], [Prod Report Master Data].[End Time], [Prod Report Master
Data].TotalTime, [Prod Report Master Data].Yield, [Prod Report Master
Data].[Opt Yield]
HAVING ((([Prod Report Master Data].Date)=[Production Date]));

"KARL DEWEY" wrote:

Post the SQL of your query as you have it now. Open query in design view,
click on VIEW - SQL View, highlight all, copy, and paste in a post.
--
Build a little, test a little.


"TYSITC" wrote:

I'm trying to total an aggregate function in a report. Here's the basics.

UnitIn- UnitOut - Yield - WtYield
10100 - 10000 - 99.01% - 90.01%
2000 - 1000 - 50% - 4.55%
Totals
12100 - 11000 - N/A - 94.56%

WtYield =UnitOut/sum([UnitOut])

=sum([WtYield]) does not work. These calculations are taking place in a
report. I tried to make it happen in a query, but due to the group by
requirement in the query in order to show all the proper fields (not shown
here in interest of brevity) for the report it doesn't sum up the LBSProd
properly.

Is there a way to remove the Group By in the query, and still have the data
show in a report perhaps?

Any ideas?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:06 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.