"grouping" sums by another category

[Guest]

I thought that a PivotTable was going to be my answer, but now, I don't think
so.

I have a table with account numbers and payment amounts. I need sums of
payments by account numbers, but by given groupings of account numbers. Some
account numbers are on their own, other groups are two or three account
numbers together. I thought I might be able to create a query with logic
something like:

sum payments when account number = x or y and call it field a
sum payments when account number = z and call it field b
sum payments when account number = a or b or c and call it field c
and so on...

To me, though, it looks like I'd have to create a separate query for each
field...?

Any ideas on the simpliest way to accomplish my goal? The end result will be
an excel spreadsheet with the account number(s) in one column and total
payments for that account number group in the second column.

Thanks!
Emma

 

[[MVP] S.Clark]

You could add a column to the data and populate it with the group of which
that record belongs, then group by that category as well as the Account
number.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[Michel Walsh]

Hi,

Describe the associations into a table at that effect:

Account CallIt
x a
y a
z b
a c
b c
c c


Next, make a total query based on an INNER JOIN where account *your table)
will be joinned with account (the ad hoc table), and you will group by on
the new field CallIt.



A crosstab query CREATES new field, based on ACTUAL values, and these
created field appear horizontally. Since you wanted a VERTICAL result of
existing fields, a crosstab query was not, indeed, needed.



Hoping it may help,
Vanderghast, Access MVP