split data into individual groups

[kfm2000]

hello,

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.

thanks for your help

[OBP]

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.

ps Is it a CVS or CSV (Comma Seperated Values)?

[kfm2000]

thank you for you reply - I would like to export the data as CSV and a seperate file for each group

[OBP]

OK, do you have a Table name and a File name?
I will use your sample data to set something up for you.

[kfm2000]

table name is Trak3 Master

each filename should be names what ever the group is

thanks again

[OBP]

OK, I will post soemthing tomorrow.

[OBP]

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

DoCmd.TransferText acExportDelim, , _
"Trak3_Master_Query", "C:\Access Databases\yellowgroup.csv"

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?

[kfm2000]

currently we have over 50 groups and may expand more. I can add the group names to a table if that easy for the coding side.

[OBP]

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.

[kfm2000]

can you please provide me with an example of code I can work with - if this is going to complicated than i can do this in excel.

thanks again

[OBP]

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?

Are you watching wimbledon or stuck at work?

[kfm2000]

can use a table if it make thing easy, this will be ongoing.

like i said if this too much work then i'll have to complete the task in excel.

no i'm not watching Wimbledon - i wish i was.

[OBP]

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?

[kfm2000]

thank you - it will very from day to day but it shouldn't be more than 1000 ( i hope )

[OBP]

OK, I have got the query for the group list and will now create the code to work through it, which is stage 1.

[OBP]

Got the code picking each group name for the filename from the query.

One point, are you going to overwrite these files each time, append to them or have them separate and dated?

[kfm2000]

no the csv's will be archived so we will create new ones each time we run the macro

[OBP]

Are there just the 4 fields that you posted going to the csv file?

[kfm2000]

yes

[OBP]

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.

[kfm2000]

looks great - how can I add the heading on each CSV?

[OBP]

What do you mean by "Heading"?
Do you mean feild names?

[kfm2000]

sorry - feild names please

[OBP]

After this line

Open "C:\Access Databases\" & groupname For Output As #1

paste in this line of code

Write #1, "First Name, Last Name, UserName, Group" & vbLf

[kfm2000]

looks great - just a question when I open the CVS in Excel the field names are in cell a1 and the othe data in cell b1 and so on - see screen shot.19692

[OBP]

That is normal for CSV, you have to parse the data using Excel's Data> Text to Columns.
Or you use a Data>import from text to convert it as it cis imported.

[kfm2000]

thank you - have a great weekend