Sum by character within value

[sumdumgai]

Is it possible to group sums based on the 3rd character within a value? For example, let's say I have a table T1 with fields F1 and F2 with values as this:


F1, F2
2A1C, 30
2A2C, 15
2A1D, 20
2A1E, 10
2A2D, 5


Can you create a query that sums by the 3rd character in a field so that the resultant datasheet gives:


C, Qty
1, 60
2, 20



Thanks for any help.

 

[pbaldy]

You can use the Mid() function to get the third character and group on it. Probably the different components of the existing field should be stored separately.

 

[Gasman]

Extract that third character with the MID() function as a separate field and group/total on that.
If you cannot do it all in one query, create a query with that field, and then a totals query on the first query.

 

[sumdumgai]

Something like this but I can't get it right?

SELECT T1.[F1], Sum(T1.F2) AS SumOfF2
FROM T1
GROUP BY mid(T1.[F1],3,1);

 

[Gasman]

Try

SELECT MID(F1,3,1) AS C, Sum(F2) AS Qty FROM T1
GROUP BY MID(F1,3,1)

 

[jdraw]

Try

SELECT Mid(T1.[F1],3,1) AS ThirdChar
, Sum(T1.F2) AS Qty
FROM T1
GROUP BY Mid(T1.[F1],3,1);

OOOoooops: I see Gasman has replied while I was testing.

 

[Dreamweaver]

I would look at adding an extra field to store the 3rd value as you will have one problem after another and it could even impact the speed of querys once you get a good sized recordset.

 

[The_Doc_Man]

sumdumgai - I do not want to be an obstructionist, but I see troubles like this continuing in your future. This is yet another example of "winging it" - which will occur when you are trying to fit a ton of "stuff" (you know what KIND of stuff) into a half-ton truck. I remember the problem you are working through.

There are two ways to handle this kind of thing and you picked the second, probably because it occurred "after the fact" of original design.

The CORRECT way to handle this was to have anticipated the need to search by sub-set, in which case you would have made the searchable sub-set markers as individual fields. Then if you needed to see them together, you could do a query that concatenated them - but when you needed them separately, they would be separate. However, you have a size issue that makes that difficult.

You can do nothing about this now and are forced to use the MID() function to pull apart what you want. (That was the SECOND way to do this.) Remember this the next time you start designing things for searching.

As a technical note, I believe this might have been approached a different way with stuff you actually have. Am I right in guessing that this thing you are searching is your product code that is actually a mish-mosh of several factors? Which is why you are trying to focus on a single character?

Because if so, you could STILL do the search directly another way - by a JOIN with your detailed product table where you have a single product key that LOOKS like 2A1C, but you have individual attributes in individual fields corresponding to the 2, A, 1, and C separately. Further, if you use the "IN" construct, you might even make this a tad faster. If I am correct in my supposition, here is what your query MIGHT look like...

SELECT T1.PROD, SUM( T1.QTY ) AS TSUM FROM T1 INNER JOIN PTABLE ON T1.PROD = PTABLE.PROD WHERE PTABLE.F3 = "C" GROUP BY T1.PROD ;

This might also work and MIGHT be faster:

SELECT T1.PROD, SUM( T1.QTY ) AS TSUM FROM T1 WHERE T1.PROD IN 
( SELECT PRODID FROM PTABLE WHERE F3 = "C" ) ;

Obviously, I'm using PTABLE as the name of your product table. Either of these approaches might work if you are still using the database that you described in your earlier sessions. And if I guessed wrong, then I wasted my time but less of yours.