Counting data from csv

[kobusstrydom]

Hi All

I have a csv file created from e-mail gateway app for incoming and outgoing mails. I am trying to get e-mail stats from this file. One column contains "To" data. In the "to" field I may have more than one recipient per line (one mail cc to many) eg. user1@domain.com, user2@domain.com, user3@domain.com etc. in the next line I might get user2@domain.com. So for this my stats would say I recieved 2 mails (the line counts) but 3 recipients. Here's what I need. Need the data to be sorted to show only the first recipients name and do a count of how many mails that recipient received. In the outgoing it works nice, because I only have one sender in the "From" field there I just run the query wizard and presto. This doesn't work to well in the incoming since the data differs as the "To" recipients are'nt all the same.

Please help

 

[Jon K]

Try this Totals Query, replacing with the correct table name:-

SELECT IIf(InStr([To],","), Left([To], InStr([To],",")-1), [To]) AS [FirstRecipient], Count(*) AS [Count]
FROM [TableName]
GROUP BY IIf(InStr([To],","), Left([To], InStr([To],",")-1), [To])

.

 

[kobusstrydom]

Thanx Jon works like a dream