Problem with aggregate function.

[indraT]

How can i write aggregate function in report in the adp file.

I have a query with expression.

like
a+b and give the alias name as service

a+b+c and give the alias name as mechanical

I want to use this service and mechanical in my report as

=Sum([service]*2 + [mechanical]02.5)

when i run the report error come "Aggregate function are only allowed on the output fields of the record source.

Then i try to put a new expression in query as
[service]*2 = new, which i canot perform, error come saying the service not present in the query.

Is there any way i can solve this problem. please let me know how can we calculate the expression out of expression in sql server.

Thanks.
Indra.

 

[cmmrfrds]

select
someid,
(a+b) as service,
(a+b+c) as mechanical
From yourtable

Is this what you are asking?

 

[indraT]

Here is the detail explaination:

SELECT dbo.Jobs.JobID, dbo.Jobs.JobStation, dbo.Jobs.JobStatus, dbo.Jobs.InvoiceDate, dbo.Jobs.JobDate, dbo.Jobs.JobUnit, dbo.Jobs.Customer,
dbo.Jobs.Field, dbo.Jobs.WellName, dbo.Jobs.WellID, dbo.Jobs.WellQuarter, dbo.Jobs.WellSubsection, dbo.Jobs.WellTownship, dbo.Jobs.WellRange,
dbo.Jobs.WellMeridian, dbo.Jobs.JobNumber, dbo.JobPersonnel.EmployeeCategory, dbo.JobPersonnel.EmployeeID, dbo.Items.ItemNumber,
dbo.Items.ItemProduct, dbo.Items.ItemService, dbo.Items.BonusCategory, dbo.JobPrices.DiscountedPrice, dbo.JobPersonnel.ElectriclineBonus,
dbo.JobPersonnel.SlicklineBonus, dbo.JobPersonnel.ConsumableBonus, dbo.JobPersonnel.BonusOverride, dbo.JobPersonnel.DriveHours,
dbo.JobPersonnel.SpecialBonus, dbo.Employees.EmployeeGuarantee, dbo.Employees.EmployeeVacationPercent,
dbo.Employees.EmployeeFirstName, dbo.Employees.EmployeeLastName, dbo.Employees.EmployeeLocation, dbo.Jobs.JobProvince,
CASE WHEN ([JobPersonnel].[DriveHours] > 4) THEN ((DriveHours - 4) * 10) ELSE 0 END AS DriveBonus,
CASE WHEN ([Items].[BonusCategory] = 'Service') THEN DiscountedPrice ELSE 0 END AS Service,
CASE WHEN ([Items].[BonusCategory] = 'Mechanical') THEN DiscountedPrice ELSE 0 END AS Mechanical,
CASE WHEN ([Items].[BonusCategory] = 'Expendable') THEN DiscountedPrice ELSE 0 END AS Expendable, dbo.JobPrices.ItemNumber AS Expr5,
dbo.JobPrices.Quantity
FROM dbo.Jobs INNER JOIN
dbo.JobPersonnel ON dbo.Jobs.JobNumber = dbo.JobPersonnel.JobNumber INNER JOIN
dbo.Employees ON dbo.JobPersonnel.EmployeeID = dbo.Employees.EmployeeID INNER JOIN
dbo.JobPrices ON dbo.Jobs.JobNumber = dbo.JobPrices.JobNumber INNER JOIN
dbo.Items ON dbo.JobPrices.ItemNumber = dbo.Items.ItemNumber
WHERE (dbo.Jobs.InvoiceDate >= @Invoice_Date_From__MM_DD_YY__) AND (dbo.Jobs.InvoiceDate <= @Invoice_Date_To__MM_DD_YY__) AND
(dbo.Jobs.JobStatus = 7)

above is the query I crated using table Jobs, where, case statement are there and those column are expression base, not present in table. I want to use those column in my report say =sum ([service]*2 + [mechanical]02.5])

but when i run the querry its give the error message. I wanted to put another expression in querry using "sum ([service]*2 + [mechanical]02.5])" it also give me the error message. I have a lot of function like this in my front end and really worried now where i can make this working or not, or is there any way i can solve this problem, so that my migration from access to sql can be used soon.

Thanks for help.

Indra.

 

[cmmrfrds]

There should be no reason that the assigned names cannot be used in the report. Check the column names in your report. You cannot use the assigned names in the same select statement, but need to refer to the underlying data. That does not look possible in your case. There is another way and that is a derived query, but I don't have time now to show an example.

Is this in a group footer or report total? If it is per line then the sum is not used.
=sum ([service]*2 + [mechanical]02.5])
this part does not look correct.
[mechanical]02.5] ???

 

[indraT]

Some of the total are group total as a report header and some are report total in footer.

Actually my sum function in report is as below:

=Sum(([Service]*[ElectriclineBonus]/100)+([Mechanical]*[SlicklineBonus]/100)+([Expendable]*[ConsumableBonus]/100))

If you can give me bit brife information on this i will be really greatful on your help. My whole project is getting delay only because of this problem.

Can i ask you one more this. Does macro work on adp or not?.

You can reply me tomorrow if you donot have time today.

Thanks for help.

 

[cmmrfrds]

On a textbox on the detail line in a Form there is the control name under the data tab, but it is the name under the other tab that is used when referencing this field on the Form. So, if the control source is service but the name is text12 then text12 is what you need to reference in the footer control. That is why I like to make sure both names are the same. I think this may be the problem.

 

[cmmrfrds]

You can add the aliases from assigned names if you put then in a special subquery that is in the from clause of the outer query - called a derived query in Sql Server. This same technique is called an inline view in Oracle. You do this in Access by making a separate query and then joining to this query. Illustration.

SELECT dbo.Jobs.JobNumber
DQ.DriveBonus,
DQ.Service,
(DQ.Service + DQ.DriveBonus) as something,
etc......
FROM dbo.Jobs INNER JOIN
(SELECT dbo.Jobs.JobID, dbo.Jobs.JobStation,
CASE WHEN ([JobPersonnel].[DriveHours] > 4) THEN ((DriveHours - 4) * 10) ELSE 0 END AS DriveBonus,
CASE WHEN ([Items].[BonusCategory] = 'Service') THEN DiscountedPrice ELSE 0 END AS Service,
CASE WHEN ([Items].[BonusCategory] = 'Mechanical') THEN DiscountedPrice ELSE 0 END AS Mechanical,
CASE WHEN ([Items].[BonusCategory] = 'Expendable') THEN DiscountedPrice ELSE 0 END AS Expendable
FROM dbo.Jobs
Group by JobNumber) AS DQ
ON dbo.Jobs.JobNumber = DQ.JobNumber

Read up on this type of correlated subquery.

 

[indraT]

Thanks, I just in and will check this and let you know how its goes. Thanks very much for help.

Indra.

 

[indraT]

I check the property in each field in report under other tab and name refer are same as they are in used. but still giving same error massage saying " aggregate function are only allowed on output fields of gthe record source".

Thanks.