基于查询将表拆分为多个表 [英] Split table based into several tables based on a query
问题描述
您好我需要拆分一个由不同客户组成的表。
我想执行一个对客户身份进行分组的查询，然后使用此查询与客户创建n个表将名称命名为表名并使用正确的数据。
表1
客户;食品
Martin;苹果
丹;汉堡
蒂诺;香蕉
丹;番茄酱
马丁;草莓
所以我想确定我有多少客户名，并在查询中对这些客户组进行分组。
然后创建一个包含客户表名的表，包括属于该客户的记录
它会继续，直到所有客户被分成不同的表格。
结果！
MartinTbl
马丁;苹果
马丁;草莓
DanTbl
Dan;番茄酱
丹;汉堡
....
我试图找到一个解决方案 - 而我的主要目标是找到一个创建这些表的解决方案
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
推荐答案
你提出的建议是可能的，但确实是一个非常糟糕的主意。请参阅数据库规范化和表结构了解原因。
What you''re proposing is possible, but it''s a very bad idea indeed. Please see Database Normalisation and Table Structures for why.
你好NeoPa
是的我知道 - 我一直在搜索的每一个帖子几乎都是因为那个要求解决方案的穷人获得了答案是可能的，并被要求解释他为什么要这样做。尽管他从来没有得到答案。 ; o）
我收到了一份关于不同客户的大量信息，我需要更改和调整，清洗。之前我可以将该表拆分成几个只显示特定客户信息的表格。
所以我知道规范化问题，但我需要一个解决方案来满足我的要求。
最好的问候
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
每次提出问题时它出现的原因是因为分开几乎总是一个坏主意将数据分成多个表。打破规范化规则很少是一个好主意，我在你的描述中没有看到任何能使数据非规范化的好主意。如果你想向某些人显示某些行，你需要做的只是使用一个过滤器。
最后，你可以自由地设计你想要的表。但如果我们没有提起正常化，我们就会失职，因为它是一个如此重要的概念，可以为你节省大量的麻烦。
如果你想继续下去路径，您将需要使用VBA代码。使用不同的客户ID创建记录集，循环访问它并为该记录集中的每一行创建一个表。在伪代码中，它看起来像这样：
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:
这篇关于基于查询将表拆分为多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
