2

I have a Table and in that table a field named Results with values of 3 digit numbers. I have used the count function to report the number of times a value is repeated in said field with this query. 

SELECT [My Table].Results, Count([My Table].Results) AS [Total Results]
FROM [My Table]
GROUP BY [My Table].Results;

How do I use the count function to also return all combinations of the 3 digit number also repeated in field?

Example

Results    Count

123        1
132        1
213        1
789        1
798        1
879        1
897        1

After Query I would like the count to show for all values in the field.

Results  Count

123      3
132      3
213      3
789      4
798      4
879      4
897      4

etc.

Erik A
  • 31,639
  • 12
  • 42
  • 67
Nate Born
  • 21
  • 1

1 Answers1

0

You can join the table on itself, and check if the letters exist in the joined record

SELECT [parent].Results, Count([parent].Results) AS [Total Results]
FROM (SELECT DISTINCT Results FROM [My Table]) parent, [My Table] child
WHERE 
          child.Results = Left(parent.Results, 1) & Mid(parent.Results, 2, 1) & Right(parent.Results, 1) OR
          child.Results = Left(parent.Results, 1) & Right(parent.Results, 1) & Mid(parent.Results, 2, 1) OR
          child.Results = Mid(parent.Results, 2, 1) & Left(parent.Results, 1) &  Right(parent.Results, 1) OR
          child.Results = Mid(parent.Results, 2, 1) & Right(parent.Results, 1) & Left(parent.Results, 1) OR
          child.Results = Right(parent.Results, 1)  & Left(parent.Results, 1) & Mid(parent.Results, 2, 1) OR
          child.Results = Right(parent.Results, 1) & Mid(parent.Results, 2, 1) & Left(parent.Results, 1)
GROUP BY parent.Results

This will replicate each parent row for the number of child rows that contain each symbol of the parent row, and then count the number of rows.

Alternatively, you could use a subquery, but performance of this solution will likely be faster.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thanks for the quick response. When I run the above query I get "You tried to execute a query that does not include the specified expression 'Results' as part of an aggregate function." Sorry I'm a beginner at this. – Nate Born Dec 02 '19 at 11:41
  • @NateBorn See the edit, that one was on me, the last line was not included – Erik A Dec 02 '19 at 11:44
  • Thanks but when I ran the query the results seem to be counting every single digit in the strings and not the combinations. These are my first 10 totals. 000=138312 001=130242 002=143832 003=140530 004=155844 005=108918 006=138207 007=183330 008=164430 009=127365 – Nate Born Dec 02 '19 at 14:50
  • Ah, if you have duplicate digits you need to manually iterate the combinations. Since it's only 3 symbols so 3! combinations that's very doable. If you want more characters, the amount of combinations will rapidly increase, so then you'd probably want to use a user-defined function. – Erik A Dec 02 '19 at 15:23
  • Thanks again I'm getting closer to my goal. My Table consists of a more than 20,000 rows with values ranging from 000 through 999 in one field. Lets say I had a value of 649 in my table I would like the count to only count values of 649, 694, 469, 496, 946 all the combinations of that 3 digit number. So say that in my field I had only the above six values in my table the total would end up 6 for each occurrence. But I need this done for every 3 digit number 000-999. – Nate Born Dec 02 '19 at 16:01
  • Eh, the current query should just do that. If it doesn't work, please provide further information – Erik A Dec 02 '19 at 16:05
  • Okay I shorted my table to 79 rows for testing purposes the current 3 digit numbers in table now range from 987-999. When I ran the query the results for 999 where 81 that would be impossible due to there are only 79 records to match with since I'm using a test table now I can count the actual number of times 999 appear and its only 9 times. I used this number for an example because it has the same number for each digit. My guess is some how the 999 values in the table where multiplied by 9. "9*9=81" Also 987 only appears 8 times but query brings back a result of 64. "8*8=64" – Nate Born Dec 02 '19 at 20:38
  • @NateBorn Try the current edit. I hadn't accounted for exactly the same row existing multiple times since that didn't occur in your sample data. – Erik A Dec 02 '19 at 20:49
  • Thanks I've been trying to figure out how to do this all weekend. Did a whole lot of searching around but couldn't find anyone else with a similar question. – Nate Born Dec 03 '19 at 02:15
  • If this fully answered your question, consider making this answer as accepted. That way, other users can see it has been answered, and you and I get a rep bonus too – Erik A Dec 03 '19 at 07:13
  • Sorry about that my first time asking a question. – Nate Born Dec 03 '19 at 18:25
  • Would it be possible for you to explain the function so I could use this for 4 digit numbers. – Nate Born Dec 03 '19 at 19:08
  • Well, it's just all the combinations iterated. `Left(parent.Results, 1) & Mid(parent.Results, 2, 1) & Right(parent.Results, 1)` is the first, second and third number in sequence. `Left(parent.Results, 1) & Right(parent.Results, 1) & Mid(parent.Results, 2, 1)` is first, third, second, etc., so this doesn't scale well at all. If you add a fourth letter, you will have 4! = 24 conditions – Erik A Dec 03 '19 at 19:11
  • For scaleable solutions, I can write up a VBA one or T-SQL (SQL server) one. Access SQL is limited, unfortunately. – Erik A Dec 03 '19 at 19:16
  • Thanks again I'm a beginner never used VBA. – Nate Born Dec 03 '19 at 20:44
  • Would this function represent 1234 child.Results = Left(parent.Results, 1) & Mid(parent.Results, 2, 1) & Mid(parent.Results, 3, 1) & Right(parent.Results, 1) – Nate Born Dec 04 '19 at 00:34
  • Yeah, that's correct. Now you've gotta do all the combinations – Erik A Dec 04 '19 at 06:27