Hi Jean,
It's a little tricky to answer this question without
knowing what the possible range of values are for Type.
If it is a fairly limited range, you could do the
following:
First create a query based on the table that uses iif()
funtions to calculate Type1, Type2, Type3 column values
for each individual record. For example, the expression
for Type1 would be - Type1: iif([Type]="A", "A", "").
Then, create your summary query based on the first
query. Group by ID and Name, Sum ComAmount and
RedAmount, but choose Max for Type1, Type2, etc.
Note that this will be very inefficient if you have much
more than the three types that you use in your example,
but it is feasible so I am passing it along.
Another way of doing what you want to do would be to use
DLookup() functions for Type1, Type2, etc. If you used
that method, you could do everything in one summary
query, but it would be a little more difficult. I'm not
sure which would be faster.
A third method would be to create a custom function.
Using a custom function you would have a lot more
flexibility. For example, you didn't mention what you
would want to do if the next group of records had type D,
E, F. In the examples above these would be Type 4, 5,
6. If you would want them as Type 1, 2, 3; or if you
would prefer to just generate a list in a single field
reading "D, E, F" you could do it in a custom function.
Hopefully this will help give you some ideas. If these
are a little off target, or you want further help with
them, post back with more details of what you are trying
to do.
-Ted Allen
