Combining table data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table like such (with additional columns to those shown):

Name Type Amount
Mike A 20
Mike B 40
Scott A 20
Scott A 30
Paul B 40

What I want, is a table that shows each name only once, with the amount(s)
for that name summed and (here's the kicker) the type changed to show either
A, B or A & B. The output I'd hope would look like:

Name Type Amt
Mike A&B 60
Scott A 50
Paul B 40

I've been told this isn't going to happen. Can anyone prove my colleagues
wrong and help me out with this? Thanks in advance!
 
Emma,

The way I would approach this depends on this... Are there only 2
possible Types, A and B, as indicated in your example?
 
Yes, Steve, just two types. Which means every person is actually one of three
types in the end: either just A(s), just B(s) or A(s)&B(s).

Any help you could offer would be so greatly appreciated!
 
Emma,

The SQL view of a query to get the desired results is as follows...

SELECT [Person], Min([Type]) & IIf(Min([Type])<>Max([Type]),"&" &
Max([Type]),"") AS CombinedType, Sum(Amount) AS TotalAmount
FROM YourTable
GROUP BY [Person]

Notice I have changed the name of your first field to Person. "Name" is
a Reserved Word (i.e. has a special meaning) in Access, and as such
should not be used as the name of a field or control or database object.
 
Back
Top