Grouping and Summing Mltpl Records to One Record

[rexwrx]

Hi everyone,

I have one table that I want to query. It's a basic table that contains:

MYKEY, AMOUNT
1234430, 100
1234435, 200
1234435, 100

1264435, 300
1264430, 200

1154435, 100
1154435, 219


1294430, 983
2010, 845
........ so on and so forth


Now, I simply want to GROUP MyKEY ENDING with 4430 and 4435 into one line and SUM the AMOUNT, thus showing like this:

MYKEY, AMOUNT
1234430and1234435, 100+200+100 --> (123 being the first common group)

1264430and1264435, 300+200

1154435and1154435, 100+219

etc etc.

i tried using simple sum and group by but does NOT work:

SELECT MYKEY, SUM(AMOUNT)
FROM MYTABLE
WHERE MYKEY LIKE ('*4430') OR ('*4435')
GROUP BY MYKEY

It does NOT ROLL-UP the 4430 and 4435 together.

QS: do i need to use VBA for this?

QS: do i need to have subqueries?

QS: please help?

Thanks!

[Pat Hartman]

Your design violates first normal form which is why you are having this problem. No field should contain multiple attributes. It seems that the last four digits of the MyKey field have a meaning and so should be stored separately. Access supports multi-field primary keys so if you can split the field, you will have fewer problems in the future.

Try this query:
SELECT Right(MYKEY,4), SUM(AMOUNT)
FROM MYTABLE
WHERE Right(MYKEY,4) = '4430' OR Right(MYKEY,4) = '4435'
GROUP BY Right(MYKEY,4);

[rexwrx]

Originally Posted by Pat Hartman

Your design violates first normal form which is why you are having this problem. No field should contain multiple attributes. It seems that the last four digits of the MyKey field have a meaning and so should be stored separately. Access supports multi-field primary keys so if you can split the field, you will have fewer problems in the future.

Try this query:
SELECT Right(MYKEY,4), SUM(AMOUNT)
FROM MYTABLE
WHERE Right(MYKEY,4) = '4430' OR Right(MYKEY,4) = '4435'
GROUP BY Right(MYKEY,4);

thanks. i'll try this out.

[rexwrx]

ok, basically i want to get these results:

EXAMPLE:

MyKey Amount
1234430 100
1234435 120
================
4430_4435 220

2214430 200
3214435 80
=================
4430_4435 280

5004430 600
1004435 950
===================
4430_4435 1550

......... and so on
or basically just have another field that has the SUM values where the first 4 characters match.

[Pat Hartman]

What causes the three groups? In the first example, the first three characters of the code are the same but that isn't the case with the other sets.