Grouping and Summing Many 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!

[GolferGuy]

Group by a field like this:
Expr1: right([MyKey], 4)
Where
="4430" or "4435"

Then include another field with this definition:
44304435: "44304435"
GroupBy

Both of these fields will be in the query grid, I assume you will be able to figure out which rows each line will go into.

Also include the Amount column to summarize.

This way you will end up with two columns being output: "44304435" and SumOfAmount. And you should only get one row.

HTH,
Vic

[rexwrx]

Does it matter if there are SEVERAL MYKey with different prefixes (i.e. before the 4430 and 4435)??

e.g.

1234430
1234435

3334430
3334435

4114430
4114435

7894430
7894435

...... and more pairs.

will these work with your query set?

thanks again1

[GolferGuy]

Try it! You will like it. Notice the Right([MyKey], 4). That takes the last four (4) characters from MyKey. That means anything else is ignored.
Actually, if you try it, you would find out it works. And, you will also have the experience that the trial would have given you, so you could ask any questions the running of the test would cause you to want to ask.

[rexwrx]

Try it! You will like it. Notice the Right([MyKey], 4). That takes the last four (4) characters from MyKey. That means anything else is ignored.
Actually, if you try it, you would find out it works. And, you will also have the experience that the trial would have given you, so you could ask any questions the running of the test would cause you to want to ask.

Thanks for that! it works beautifully! Couldn't try it initially as there was some issue with access installation.

Anyway, what is the difference with this query to this one:
selecy '4430and4435' as newKey, amount
from table
WHERE [MyKey] like ('*4430') or [MyKey] like ('*4435')

questioning just at the WHERE LEVEL, as the RIGHT clause if obviously looking more specifically to the RIGHT 4 characters of the field.


another question:

does this query you showed me then produces this result??

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

thanks!

[GolferGuy]

As I have looked back over your initial request, and your follow-up replies, I think I now see that you want the 4430 and 4435 records summarized at the 123 and 221, and 500 level. That is, whenever the first the numbers are the same, summarize the 4430 and 4435 records by the first three number prefix.

1234430 100
1234435 300
-------------
123 400 Total record in query

3244430 350
3244435 150
------------
324 500 Total record in query

5004430 225
5004435 135
-------------
500 360 Total record in query

Is that what you are looking for? If so, then the query needs to be:

The group by field:
Prefix: Left(MyKey, 3)
GroupBy

The where field can be as you put it,
WHERE [MyKey] like ('*4430') OR [MyKey] like ('*4435')

This GroupBy field will cause a record to be generated for every group of records with the first three numbers matching.

If what you want is only ONE record overall, then the previous query suggestion is what you want. I am quite sure that either the WHERE clause I originally suggested, or the one you suggested back to me will work just fine.

Vic

[rexwrx]

YEP! Perfect! thanks for that!! exactly that!

[rexwrx]

now on to the next step:-->

then I want to JOIN this 'results' to another table (TABLE 2)which has

MYKEY AMOUNT
1234430 400
3244430 500
....4430 xxxx.

the ROLLED UP RIGHT(mykey,4) being the same on both table
and the LEFT(mykey,3) being the JOINED FACTOR for both tables.

[GolferGuy]

I really need the questions from my last post answered before trying to help with your last question. What does the actual result of the first answer look like?

[rexwrx]

Result of first answer is basically this:

mykey amount
123 200
223 300
456 120
121 500

being from this:

mykey amount
1234430 100
1234435 100

2234430 150
2234435 150

4564430 20
4564435 100

1214430 200
1214435 300

[GolferGuy]

Have you thought of spliting this field MyKey into it's two parts? I was under the assumption that this was just a one time type of thing, but it looks like there will be ongoing spliting of this field. By putting the first three in one field, and the next 4 into a separate field, it will make your life much easier.

Are you doing all this in Access? Or is the data in a different DB like SQL Server, or ???

I don't know if you could split this field with a view if it is a different backend DB, but you could do that with a query within Access.

Doing a Left(MyKey, 3) and then using that for a join within one query is not something that a good system would be built on.

Can you make a change like this (spliting the MyKey into two fields)?

[rexwrx]

Yes it's all in Access, very minimal number of records also..

Yep I actually use a left(mykey,3) for the join. so that's actually splitting e fields and join based on that??

trouble is i think i only want to join on those where the RIGHT(mykey,4) ends with 4430 or 4435 only.
i.e. IGNORE those records with no ending of 4430 or 4435 on both tables.

[GolferGuy]

Query named "Query6":
SELECT Left([MyKey],3) AS my_key, Sum(tblMyKeyTest01.Amount) AS SumOfAmount
FROM tblMyKeyTest01
WHERE (((Mid([MyKey],4))=4430 Or (Mid([MyKey],4))=4435))
GROUP BY Left([MyKey],3);

Query named "Query7":
SELECT Left([MyKey],3) AS my_key, tblMyKeyTest02.Amount
FROM tblMyKeyTest02
WHERE (((Mid([MyKey],4))=4430 Or (Mid([MyKey],4))=4435));

Query named "Query8"
SELECT Query6.my_key, Query6.SumOfAmount, Query7.Amount
FROM Query6 INNER JOIN Query7 ON Query6.my_key = Query7.my_key;


Is that what you are looking for on the join? That would be the join in Query8.

[rexwrx]

you are a champ!

that's what i was after.

I was actually going to do sub-queries but didn't think Access is able to do it. So eventually, i used the query 6 and query 7 to get to query 8 for the Joins to work like how you've described.

though i used RIGHT(mykey,4) as opposed to MID(xx,xx)

Thanks champ!