Export records in groups when value in field changes 1

[trulyblessed]

I was wondering if there is an easy/automated way to export a group of records out of an Access table based on criteria of a certain field? I have a table that has 5,053,349 records in it and I need to create mini files that can be exported into Excel. The groupings of the mini files need to be by NDC number (which is a field in the table). So, for each NDC number, I need a different file. There are about 300 unique NDC numbers.

Any help would be greatly appreciated.
Thanks,
Kim

 

[substitute]

I can't see any way to do this without using VBA, as you have to loop through the NDCs and change the spreadsheet name each time.

Because I'm bored today I wrote an example to export one spreadsheet per surname from my family history database - you should be able to modify the idea for your use:

First, create a module and put this code in it:

Sub exportgroups()

Dim sName As String

DoCmd.OpenQuery "MakeDistinctNames"
Do While DCount("Surname", "DistinctNames&quot > 0
sName = DLookup("FirstofSurname", "FirstName&quot
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "OneName", "c:\my documents\group" & sName & ".xls", True
DoCmd.OpenQuery "DeleteFirstName"
Loop

End Sub


Then create queries as below (I've quoted the SQL as its the easy way to post a query definition)

MakeDistinctNames:
SELECT DISTINCT Person.Surname INTO DistinctNames
FROM Person;

FirstName:
SELECT First(DistinctNames.Surname) AS FirstOfSurname
FROM DistinctNames;


DeleteFirstName:
DELETE DistinctNames.*, DistinctNames.Surname
FROM DistinctNames
WHERE (((DistinctNames.Surname)=DLookUp("firstofsurname","firstName&quot));


OneName:
SELECT Person.*
FROM Person INNER JOIN FirstName ON Person.Surname = FirstName.FirstOfSurname;


What the queries do is allow a simple set of queries to return all the names sequentially, by first making a table of names, then exporting and deleting the first name from the list until all names have been used.

To make it work for you you nees to change the queries to use NDC not surname and to use your table not my Person table.

Good luck

 

[substitute]

Whoops, posted that instead of previewing it!
It needs a couple of changes:

First, where the code I posted says Sub and End Sub at each end it needs to say Function and End Function. You can then run the code from a macro using the RunCode action.

Second, it won't work if there is a null surname (or NDC) in the table. Change the MakeDistinctNames query to

SELECT DISTINCT Person.Surname INTO DistinctNames
FROM Person
WHERE (((Person.Surname) Is Not Null));

This does mean you can't export the nulls.

 

[trulyblessed]

Thank you for all of this great sample code. This looks like it would really work for what I need it to do. The only other question I have is what will it do or what do I need to do when one of the files it is going to export is larger than Excel can handle. For example, I know that one of the NDCs has 361,000 rows attached to it. So, if I export it as an Excel document, it won't capture all of those records and I'm not sure if it will let me know which NDCs had this issue. Do you think I should export them as text documents and then bring them into Excel later and the ones that are too big, I'll just have to handle manually or is there a better solution to this? I really like the idea of being able to export them to Excel and they are just ready to go. If I do need to export them as text, what is the syntax for that?

Thank you so much for your help!

 

[substitute]

I thought of the rows problem as I was writing it. Excel's limit is 65000 rows. TransferSpreadsheet probably blows up!

There is a way of writing a query so it numbers the rows and you can then put a limit of 65,000 on the number. That doesn't help with the remaining 240,000 rows though. That would require adding something to feed successive limits to the query the way it currently feeds successive names.

You can use DoCmd.TransferText to create a CSV file, which you could chop up with a text editor (takes a long time to scroll 300,000 lines though!). CSV files are opened by Excel as a native format so there is no increase in complexity if you do this. The syntax (keeping everything else the same) changes to:

DoCmd.TransferText acExportDelim, , "OneName", "c:\my documents\group" & sName & ".csv", True

Keep coming back if you need more help.