Split table based into several tables based on a query

[MKragh77]

Hi I need to split a table consisting of different customers.

I want to execute a query that group the customers identity and then uses this query to create n tables with the customers name as table name and with the right data in it.
Table1
customer;food
Martin; apple
Dan;burger
Tino;banana
Dan; ketchup
Martin; strawberry

So I would want to identify how many customernames i have and group these in a query.
Then create a table with the tablename of the customer including the records belonging to that customer
And it would continue untill all customers has been split into separate tables.

Result!

MartinTbl
Martin;apple
Martin;strawber ry

DanTbl
Dan;ketchup
Dan;burger

....

I have tried to find a solution - and my main objective is to find a solution that creates these tables

[NeoPa]

What you're proposing is possible, but it's a very bad idea indeed. Please see Database Normalisation and Table Structures for why.

[MKragh77]

Hi NeoPa

Yeah i know - every thread that I have been searching nearly almost end up with that the poor guy who asked for a solution gets an answer that it is possible and is asked for an explanation on why he wants to do this. and despite that he never gets the answer. ;o)

I am recieving a large file of information on different customers that I need to alter and adjust, "wash" etc before I can split that table into several tables displaying only that specific customers information.

So I am aware of the normalization issue but yet I need a solution for what I have asked for.

best regards

[Rabbit]

The reason it comes up every time the question is asked is because it's almost always a bad idea to split up the data into multiple tables. Only rarely is it a good idea to break normalization rules and I haven't seen anything in your description that would make it a good idea to denormalize the data. If you want to display certain rows to certain people, all you need to do is use a filter.

In the end, you're free to design your tables however you want. But we would be remiss if we did not bring up normalization because it's such an important concept that saves you tons of trouble down the road.

If you want to continue down that path, you will need to use VBA code. Create a recordset with the distinct customer IDs, loop through it and create a table for each row in that recordset. In pseudocode, it would look something like this:

recordset variable = select distinct customer id from table

for each row in recordset variable
   run SQL create table row.customerName
   run SQL insert into table row.customerName ...
next row

[NeoPa]

MKragh77:
So I am aware of the normalization issue but yet I need a solution for what I have asked for.

Frankly, I very much doubt that is true as so much of what you say makes it clear that you don't even have an inkling - even after reading that so many experts, everywhere you look, warn you against doing what you're asking for help to do.

I would suggest you look into it, at least, before discarding the advice of everyone who knows anything about the matter.

Rabbit has pointed you in a direction that will enable you to produce what you've asked for and at the same time has re-issued a dire warning about taking that path - as any responsible database expert would feel compelled to do. At the end of the day though, it is down to you to choose to ignore the advice or to look into the subject and first understand why it is so very important before deciding which approach to use.

Be aware though, if your task is for an employer, there is a good chance that you'll be in for a bucket-load of criticism if anyone with an inkling of database understanding realises you've taken such an approach.

[MKragh77]

Dear Rabbit and NeoPA

I did not intend to disrespect your efforts in trying to help me and deeply regret if this resulted in NeoPA´s last reply. I am (well) aware of the complications that you adresses. Nevertheless we have some business processes that require me to extract those records into seperate tables and from there they would be exported as txt files to different analytical tools. So I am not trying to corrupt the database but I am using my database to do some manipulation of these records before they are separated and used i those other tools.
I appriciate your help Rabbit, but I don´t have very much experience i VBA so I am litteraly looking for a ready-to-go solution ;o)

best regards

[zmbd]

MKragh77
I Know,I Know,I Know,I Know,
I'm going to ask you to provide a 'sanitized' version of your data set... because you can do this with a select query...

OK
Now take a moment to listen/read.

In your last post:

me to extract those records into seperate tables and from there they would be exported as txt files to different analytical tools

This can be done by query(ies) most likely without making a single table!

I work on and maintain large Laboratory Information Systems (LIMS) with thousands of entries that I split out for various departments and I never once create a table to do so... all via query. Some I use for reports sent to PDF, other's to excel files, and yet other data to text files that a very old legacy database imports

SO What you ask can most likely be done without disregarding what we're telling you about Normalization.

[Rabbit]

You'll probably want to learn the basics of VBA before taking on a project of this size. Here is a link to a tutorial on Access / VBA: http://www.functionx.com/vbaccess/Lesson01.htm

[NeoPa]

MKragh77:
I did not intend to disrespect your efforts in trying to help me and deeply regret if this resulted in NeoPa's last reply

I'm sorry if you felt my last reply was simply a result of ire. From my perspective it was a last effort to try to impress upon you the great importance of the point. Let me reassure you that, even if I felt you were trying to avoid dealing with the point raised, I was not offended by your choice. Even if I had been a little bit then I had no right to be. Your choices are your own and I should understand that as well as anyone.

Nevertheless, had you explained your requirements as well in your earlier posts as you now have in your last, your position would have been clearer and understanding why you felt it was of less importance would have been clearer. This is important for us because it's true that separate tables for the purpose of exporting data is very much less of an issue for a normalised database than tables that are used as sources of data (and generally integral to the db) are.

The new problem with that approach (I'm sorry. It's still not problem-free.) is that once you create any table for such purposes you will then become responsible for maintaining or clearing them. This is a headache I wouldn't recommend you bring upon yourself unless there is no alternative. It gets messy, then after some time even messier. Deleting tables can cause bloating of the database and so can simply clearing the existing data. Bloating is hard to avoid completely in Access but this approach would cause more than necessary.

ZMBD has already given some good advice on exporting data via queries. Knowing only as much as I do of your situation this seems to me to be your most sensible approach. Export formats can work with queries as well as tables in case you were unaware of that.

However you choose to proceed is your choice and I'm sure you won't lack for members here willing to help you through it.

[MKragh77]

Hi NeoPa
Thank you for your kind reply. I should have spent more time on explaining the purpose of this database.
Basically it is a database that is only used to perform different queries and updating the records since the sourcedata is not allways sufficient.
So I use the database to import sourcedata - do some manipulation - and split the table into several tables and from there export these new tables (sometimes 100+ customer tables). Then I keep the sourcedata for documentation - but there are no direct integration with other systems. So it is just a stand-alone-database ;o)

best regards

[NeoPa]

That sound like a perfect scenario for using queries, but as I said earlier, I'm sure whichever approach you choose in the end, now that we can be sure you aren't making it due to our negligence in bringing the issues to your attention, we will be able to help you along with.

Let us know if there is further help you need on the matter.

[twinnyfo]

All,

Just to throw my two cents in, even though I have been doing the database thing for quite a few years, I still learn things on this forum, based on "good database principles" that have caused me to rework my designs.

My most recent update was this week spent changing how I archive old records. Even though I was only changing three tables (plus three tables used for archiving), based on NeoPa's advice, it still took me all week long to track down and fix all the connected pieces. Even though doing things "right" may seem like the wrong answer at the time, the further down the road you go with a poor design, the more painful it is to correct it.

@MKragh77,

Based on everything you've said, it sounds like a query-based approach described above is what you are looking for. Are you really "exporting tables"? Where are you exporting them to?

If you are merely gathering data associated with a particular customer, as mentioned numerous times on this thread and I am sure countless other places on the web, there is no need for a table--unless you are exporting it to someone else with a database who will be using that format.

Even so, as NeoPa said, we are willing to help whatever you decide as your approach.