473,320 Members | 1,820 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Split table based into several tables based on a query

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;strawberry

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
Sep 2 '14 #1
11 1367
NeoPa
32,556 Expert Mod 16PB
What you're proposing is possible, but it's a very bad idea indeed. Please see Database Normalisation and Table Structures for why.
Sep 2 '14 #2
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
Sep 3 '14 #3
Rabbit
12,516 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. recordset variable = select distinct customer id from table
  2.  
  3. for each row in recordset variable
  4.    run SQL create table row.customerName
  5.    run SQL insert into table row.customerName ...
  6. next row
Sep 3 '14 #4
NeoPa
32,556 Expert Mod 16PB
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.
Sep 3 '14 #5
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
Sep 4 '14 #6
zmbd
5,501 Expert Mod 4TB
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.
Sep 4 '14 #7
Rabbit
12,516 Expert Mod 8TB
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
Sep 4 '14 #8
NeoPa
32,556 Expert Mod 16PB
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.
Sep 4 '14 #9
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
Sep 5 '14 #10
NeoPa
32,556 Expert Mod 16PB
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.
Sep 5 '14 #11
twinnyfo
3,653 Expert Mod 2GB
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.
Sep 5 '14 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: jbroome | last post by:
I have several tables that contain the same information as Identifying records but then with additional info that differs from table to table. e.g. 1 database of school students with their...
3
by: Joanna Epstein | last post by:
I have several tables formatted as follows: ITEM #, ITEM DESCRIPTION, PL, JANUARY ITEM #, ITEM DESCRIPTION, PL, FEBRUARY And so on… I need to create a final report that merges all of the...
1
by: elfyn | last post by:
I'm currently building a DB that you will keep track of vehicles, services to the vehicles and parts used for the servicing of the vehicles. I've created a table for Customers, Vehicles and...
1
by: atahim | last post by:
I have a master table with over 300,000 records that i need to split into 500+ smaller tables. I have a branch field in the master that i can use as an identifier. I even created a branch table...
11
by: DraguVaso | last post by:
Hi, I want to make a small application in VB.NET that relinks all the query's and tables in an Access database that are linked via ODBC to an SQL Server. It must be able to relink all the tables...
7
by: alexander324 | last post by:
I have an access table that has around 4000 records showing various carrier names, origin and destination city, state and zip along distance and cost etc. Is there a way to automate a process that...
5
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data from 2005/01/01 ~ 2007/05/xx in single big...
2
by: farouqdin | last post by:
Hi all i have code which loops through table and deletes the duplicate records. This code does it for one table. How do i change it so it goes through several tables? On Error Resume Next Dim...
4
by: Cyprus106 | last post by:
Apparently, Im incapable of properly executing this query! I've been at this for far too long and gotten nowhere. Forgive me if this is a umb mistake; I'm not great at SQL. I've got three tables,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.