CODEWITHSUNDEEP
c#winformsms-access
Durga
Durga

Reputation: 1303

Adding a SUM() expression causes my Access query to stop working

I am getting following error for a Select Query after adding SUM(b.[Amount_withDiscount]) and inner join on tables to a query that was previously working. Now I want the query to also return the sum of that column in the related table. Where have I gone wrong?

You tried to execute a query that does not include the specified expression 'p.[PFirstName] & ' ' & p.[PLastName]' as part of an aggregate function.

working Query 

string sql = "SELECT p.[PFirstName]&' '&p.[PLastName] as [Patient Name],p.[Age],p.[BirthDate],p.[MobileNo]&' / '&p.[LandlineNo] as [Contact Number] FROM Patient_Registration p WHERE datepart('m',p.[RegDate])=@month AND datepart('yyyy',p.[RegDate])=@year AND p.DoctorID=" + drId;

Query after adding sum and inner join 

string sql = "SELECT p.[PFirstName]&' '&p.[PLastName] as [Patient Name],p.[Age],p.[BirthDate],p.[MobileNo]&' / '&p.[LandlineNo] as [Contact Number], SUM(b.[Amount_withDiscount]) as [Payable Amount] FROM Patient_Registration p INNER JOIN Bill_Master b on p.[PatientID]=b.[Patient_ID] WHERE datepart('m',p.[RegDate])=@month AND datepart('yyyy',p.[RegDate])=@year AND p.DoctorID=" + drId;

Upvotes: 1

Views: 951

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123409

When you added the SUM() expression you changed the query from a regular Select Query into an Aggregation Query that expects each output field to be either

  • part of an aggregate function (SUM(), MAX(), etc.), or
  • included in the GROUP BY clause (which is not present in your query).

In this case, for just one sum, you'd probably be better off using DSum() instead of SUM(), as in

string sql = "SELECT p.[PFirstName]&' '&p.[PLastName] as [Patient Name],p.[Age],p.[BirthDate],p.[MobileNo]&' / '&p.[LandlineNo] as [Contact Number], DSum(\"Amount_withDiscount\",\"Bill_Master\",\"Patient_ID=\" & p.[PatientID]) as [Payable Amount] FROM Patient_Registration p WHERE datepart('m',p.[RegDate])=@month AND datepart('yyyy',p.[RegDate])=@year AND p.DoctorID=" + drId;

Upvotes: 1

Related Questions