Querying Recordset

  • General Layout of Problem: We get a large file of data (in excess of 150K rows, 35 columns) that needs to be split out into ~40 individual files. This isn't really a problem, as I can set those recordsets and transfer to individual files just fine. But those new files need to include a summary - by month - of the data.


    How would one go about summing the recordset?


    To complicate matters further, I need to sum by two separate criteria (WHERE FIELD=''Yes' - and FIELD!=''Yes') - and I have to do those sums by a varying number of criteria for each recordset. At minimum, I'd have 6 SUM queries for each month, representing up to 72 SUM queries for the end of the year file. At maximum, I'd have 30 SUM queries for each month, representing up to 360 SUM queries for the end of the year file.


    I can set up those queries no problem, I'm just curious if there's a better way than to establish a brand new recordset for each SELECT SUM query.


    I'm already creating the recordset to move the data to a new file. Can I simply sum the recordset I've already created? If so, what's the best way to do that? And yes, I'm open to worksheet formulas or any other solution that one can think of.

    Learning the hard way, with a few good books and the help of an online forum.
    Always interested in learning more, and discovering what Excel can really do.