sum up multiple fields in query given conditions

[Pete64]

Basically I have a query that I've created to sum up multiple fields from a table, the only problem is I have a number field that is a company number that is repeated within my table for other contacts from the same company, that is then picked up by the query. I dont want this to be repeated within my query instead I want if this number is repeated to calculate the sum of all columns related to that specific numbered field into the first instance of that numbered field.

is this possible?

I have no clue on how to start here please help?
Or if I am being to unspecific I will give more

 

[namliam]

Use a group by on that field

Regards

The Mailman

 

[Pete64]

I am

yeah I was using a group by on that field but I was also using the By group on other fields, I've got it working now though cheers ; )

 

[Pete64]

why does

why does the query builder automatically select Group By as the aggregate function to be performed, and why can't I just select nothing.

 

[namliam]

Nothing... Just not, group by mean your grouping on something meaning you have to do First, max, where, avg whatever to other fields...

Group by is just the default setting....

Regards

 

[Pete64]

I understand

yeah i understand this concept but what if you want to perform no function to a field what do I put into this box I can't put Group By, this will alter my query, I just want to view the field with the rest of the query.

; )

 

[namliam]

I am not completely clear on your problem... try beeing more explicite... or giving an example... or posting a sample db...

Regards

 

[Pete64]

Sorry man am just questioning access I understand what your saying and I think I need to sit down with query builder a bit.

Your answer has solved my problem cheers for all the help

;-)

 

[Pat Hartman]

This isn't an Access issue, it is an SQL issue. In an aggregate query, EVERY field must be aggregated in some way. The default is group by. But you may wish to use first, last, min, or max for various reasons. Numeric fields are normally aggregated with an arithmetic aggregate function such as Sum() or Avg(). Consider the following set of records and hopefully you will understand the issue:

Sam, EnglishI, 85, 1/4/3
Sam, EnglishI, 77, 1/15/3
Sam, AlgebraI, 92, 1/5/3
Sam, AlgebraI, 80, 1/6/3
Pat, EnglishI, 95, 1/4/3
Pat, EnglishI, 92, 1/15/3
Pat, AlgebraI, 100, 1/5/3
Pat, AlgebraI, 99, 1/6/3

You may want the Average grade for each student:
Select Student, Avg(Grade) As StudentAvg
From YourTable Group by Student;

You may want the Average grade for each subject by student:
Select Student, Subject, Avg(Grade) as StudentGradeAvg
From YourTable Group by Student, Subject;

You may want the Average grade for each subject:
Select Subject, Avg(Grade) As SubjectAvg
From YourTable Group by Subject;

Notice that two of these queries omit a field. If they did not, they would not aggregate as intended. Also notice that none of the queries includes the date field. Including the date field would prevent any aggregation from taking place. The output recordset would be identical to the input recordset.

Your problem is that you are including a field in your query which cannot be properly aggregated.

Sometimes you may want to include details with the aggregated values. This requires two queries. One to aggregate and a second to join the aggregation query back to the main table to obtain some other piece of information.