unique items sum

nitr0

Registered User.
Local time
Today, 13:38
Joined
Oct 15, 2012
Messages
11
hi,

i would like to ask if is possible to make query that make this..

from table pick name, sum1, description, sum2, idofpayment
and return only 1 unique name but make sum of all..

example

my query now return:
Oliver - 297 - for services - 100 - 1982
Marc - 324 - for services - 132 - 1982
Maria - 213 - for services - 213 - 1293
Oliver - 231 - for services - 123 - 1982
....

expected return:
Oliver - sumofsum1 = [297+231], for services - sumofsum2 =[100+123], 1982
Marc - sumofsum1[324], for services - sumofsum2[132], 1982
Maria ...

= sum all same name where id = same,
and i need return all fields because in reports it is group by idofpayment

i am able to do that as sums but not able to sum by id and return id, so i am not able to later group by..

SQL:
SELECT Data.[Name], Data.[sum1], Data.[sum2], Data.[idofpayment], Data.[Description]
FROM Data
WHERE (((Data.[Description])="for services"));
 
Try this, remember to change the table name and field name to fit yours. (It is a bad idea to call a field "Name" it is a reserved word in MS-Access)
SELECT TheName, Sum(Sum1) AS TotalSum1, Data.Description, Sum(Sum2) AS TotalSum2, Idofpayment
FROM Data
GROUP BY TheName, Description, Idofpayment
HAVING Description="for services";
 
i have tried it like this before, but it does not do that..after i delete column Data.[idofpayment], it start to work properly, but i need to have this info
 
Is the below picture not showing what you are looking for?
expected return:
Oliver - sumofsum1 = [297+231], for services - sumofsum2 =[100+123], 1982
Marc - sumofsum1[324], for services - sumofsum2[132], 1982
Maria ...

= sum all same name where id = same,
and i need return all fields because in reports it is group by idofpayment
attachment.php
 

Attachments

  • Oliver.jpg
    Oliver.jpg
    38.3 KB · Views: 152
yes this is exactly what i want, but i cannot reach that..maybe i am blind,

SELECT Data_G.TheName, Sum(Data_G.[Calculation Basis]) AS [SumOfCalculation Basis], Sum(Data_G.Amount) AS SumOfAmount, Sum(Data_G.Rate) AS SumOfRate, Data_G.Description, Data_G.Idofpayment
FROM Data_G
GROUP BY Data_G.TheName, Data_G.Description, Data_G.Idofpayment
HAVING (((Data_G.Description)="for services"));
 

Attachments

  • query_design.png
    query_design.png
    7.3 KB · Views: 90
  • query_view.png
    query_view.png
    61.4 KB · Views: 90
The result from the query you show do all have a different "Idofpayment" number, so maybe did you reach it, (it is diffecult to say then I can't se your "raw" data).
Sorry, but why do you say you can't reach it?
 
now i just looking and you are right, it is solved i even dont noticed..but when i tried if before it was result wrong and i thought i do it same way you told me..i suppose not, so i automaticaly saw same result :D

thanks again..
 

Users who are viewing this thread

Back
Top Bottom