One dataset, multiple subsets

[Ivan Howard]

Hi All,

I would really appreciate any help or suggestions that anyone can give.

I have one dataset (returned by a query) consisting of 6 columns of data. One of the columns is a sales code. There can be multiple sales codes within the main dataset and what I need to do is return one dataset for each of sales codes. I could just write queries to do this, but the sales codes fluctuate significantly so I cannot predetermine a list of codes until I have the returned dataset.

Is there a way to get Access (2003) to split out a dataset based on a particular column? Even if this is by using VBA?

Thanks again for your help.

 

[vbaInet]

Could you explain what you mean by "split the dataset"?

 

[Ivan Howard]

OK. What I mean by "split the dataset" is, lets say I have a table containing 1000 rows of sales data. One field within this table contains a sales code. For this example, lets say there are 15 sales codes (but this will definitely change). I need to be able to see/export 15 subsets, based on each sales code.

Another solution I have is to export the full 1000 rows to Excel and filter, copy and paste manually in Excel. However, I would really like to keep it all within Access.

 

[vbaInet]

Group based on the Sales Code field in a query.

 

[Ivan Howard]

Thanks vbaInet.

I thought about grouping it like you said, but this won't give me 15 separate datasets. I was thinking that using vba, I could get the database to actually split the data into the 15 separate datasets.

Another thought or question I have is that using vba, could I get the database to loop through the dataset and export each dataset into an Excel file/workbook?

 

[jdraw]

It sounds like you're most familiar with Excel and, although you want to keep it in Access, you're indicating a preference to move things to Excel.

You could run a series of queries that select the data you want; you could then put these queries inside a loop and export the selected data to excel, or text or a "working table" in Access.

How often do you need to do this? What is the purpose of separating out the data?

 

[Ivan Howard]

Hi Jdraw,

Thanks for your help. Yes, I am proficient in Excel and I could do this in Excel a lot simpler. However, I will not be running the process and therefore wanted to keep it as simple as possible, hence the thought of keeping it all in Access.

Some time ago, I built a tool within Excel that takes a single dataset of up to 50k rows and separated it into a) separate sheets based on a column, i.e. country name, sales code, etc... and b) the same split, but into completely separate files. This has helped hugely when needing to email/use specific datasets. For this project I will extract the main Access dataset into Excel and split it out into sheets using my tool. With an ODBC connection, this could work quite well.

To answer your question, the dataset refresh and split will need to be run once or maybe twice a month. It is for a feed into an analytics system.

Thanks again to both you and vbaInet for all your help. Best regards, Ivan

 

[vbaInet]

If you want to do things the Access way you need to drop your Excel ways What you just described, i.e. splitting into separate datasets, isn't how Access works.

The table is your dataset, you logically split it (i.e. group the dataset) into categories (in your case Sales Code) and if you want to export the groups into different Excel spreadsheets or different tabs within the spreadsheet you do it in code based on the query.

Have a look at this:

http://www.btabdevelopment.com/ts/default.aspx?PageId=10

 

[Ivan Howard]

Hi vbaInet,

Thanks for the advice... I will take it on board. I just wasn't sure if what I needed could actually be done in Access using some way out code or something. I will check out the link you posted. Thanks.

I have now built and tested the solution of using a SQL statement in Excel VBA against the database and then manipulating it in Excel into exactly what is needed. Sometimes the best solutions are the simpler ones. Thanks again for your help.

 

[vbaInet]

Happy to help Ivan!