I'm new to access vba coding, what i'm looking for help is I have an access database table which I need to export to CVS - the problem I have is the data needs to be split data into individual groups. (group is in column D)
The attached excel spreadsheet is an example of the table which I need to export to CVS
no idea where to start. any help would be much appreciated.
This should be no problem, would the data be exported one group at a time to seperate files or sent as groups at one time to one file?
For the first type, one group at a time an SQL query can be run to filter the data for each group.
For the second type a single "Group By" query can be used.
Once you can tell us which way to process the data we can provide some VBA to perform the operation.
I have a question about the csv file, does it need field names?
Also how many groups are there likely to be and will they change?
The simplest method to do this is to have a Parameter Query for each group with a single line of VBA code to send it to a csv file like this
However if the groups are likely to change or be added to it will take more sophisticated VBA code.
Are the group names held in a Table for selection as they should be?
The groups should be in a table both for updating purposes and it should have a combo drop down for Users to select the correct group, it is good design pracxtice to only have repetitive data entered once and then selected by users.
This prevents miss spellings etc.
With that many groups it will not be very practical to have over 50 parameter queries, so it looks like it will require a much more sophisticated set of VBA code.
The code is complicated, but doable, if you are going to use a group table and I have a name for it.
I can also use a query to obtain the Group names if you are not.
Will you do this manually in Excel and is it just a 1 one off?
It would be a lot of work to it regularly in Excel, so I will complete the VBA to make it automatic without the table first, but you should have the table for your users, if there are any.
It will probably take me a while so it will probably be tomorrow before I can post it.
But don't worry about my time as I am retired and this is just one of my hobbies.
ps how many records does your main table currently have?
OK, here is the database, it has a form called save files with 2 command buttons on it, save group files and read file.
This line of code is where it puts the files on my disc drive, you will need to change it to where you want them to go.
Open "C:\Access Databases\" & groupname For Output As #1
There are message boxes in the code telling you which group is being actioned and what data is being sent to the file, you obviously won't need them in your version.
The read file button only reads one file to check that they have data and you won't need it in your database.
split data into individual groups
Posting Permissions
Reply With Quote