Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Fri Aug 21 13:18:35 CDT 2009
Hi Jeff, Here is the answer tuned for your sample data: Select C, Count(C) as Count from ( SELECT Mid([TestField],1,4) AS C FROM zzzTest UNION ALL SELECT Mid([TestField],7,4) AS C FROM zzzTest UNION ALL SELECT Mid([TestField],13,4) AS C FROM zzzTest UNION ALL SELECT Mid([TestField],19,4) AS C FROM zzzTest UNION ALL SELECT Mid([TestField],25,4) AS C FROM zzzTest UNION ALL SELECT Mid([TestField],31,4) AS C FROM zzzTest UNION ALL SELECT Mid([TestField],37,4) AS C FROM zzzTest ) Test where (Test.C <> "") group by C This is MS Access 2007. I assumed that table name is zzzTest and that field name is TestField. Now you can easily generalize the above solution. Thank you. -- Shamil P.S. Answer: C Count 1170 1 1180 1 1537 1 1542 1 1661 1 1922 1 2202 2 2212 2 2214 3 2244 1 2250 1 2296 2 2335 1 2337 1 2349 1 2373 1 2393 1 2397 1 2410 3 2411 1 2415 1 2422 1 2431 6 2477 1 2510 1 2514 3 2516 2 2522 1 ENG 1 -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jeff Barrows Sent: Thursday, August 20, 2009 11:55 PM To: accessd Subject: [AccessD] Counting multiple instances of a specific string I have a field, clocks, that can contain either three or four character employee clock numbers. The field can contain numbers (123 or 1234) OR text (ENG). The field can contain one clock number or multiple clock numbers (seperated by a comma and a space). The users claim that it HAS to be this way or they could be typing the same data multiple times with the only difference being the clock number. Now, here is the problem. They (Management) have decided that they need to be able to get a count of how many times the individual clock number appears in the table. How do I check for multiple entries for a clock number when it could be null, one clock number, or many clock numbers in one field? Here is a sample of what the data looks like in the field: *clocks*** 2214, 2410, 2349, 2373, 2514, 2431 1537, 2296, 2202, 2212 2244 2411 2415, 2214, 2393 2431 2431, 1542, 2514, 1922 2431, 2214, 2516 2431, 2510, 2335 2410, 2202, 1170, 2212, 2431 2477 2410 2514 2516, 2397, 2422, 2296, ENG 2522, 2337, 1661, 2250, 1180 -- Jeff Barrows -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 4353 (20090820) __________ The message was checked by ESET NOD32 Antivirus. http://www.esetnod32.ru __________ Information from ESET NOD32 Antivirus, version of virus signature database 4356 (20090821) __________ The message was checked by ESET NOD32 Antivirus. http://www.esetnod32.ru