数据清理 [英] Data Cleaning
问题描述
由于我刚刚开始我的新工作，我继承了MS Access
数据库，该数据库在一个表中有近13000条记录。现在，我的
的任务是清理数据库，也可能将表分成两部分。
任何人都可以给我一些关于如何清理复制的想法
记录没有经过所有记录。
任何帮助或想法将不胜感激？我不是一个技术人员，但我对数据库非常熟悉。我没有处理过具有13000条记录的MS
访问数据库。
有哪些字段？ ？
---
如果（a）此邮件
包含病毒，请立即告知我们（通过电话或回复邮件） br />
（b）您不是预定的收件人
（c）您认为此电子邮件是垃圾邮件。
我们已尽最大努力确保
以上都不适用。谢谢
由AVG反病毒系统检查（ http：// www .grisoft.com）。
版本：6.0.690 /病毒库：451 - 发布日期：22/05/2004
< blockquote>您好，
您可以尝试在SQL查询中使用DISTINCTROW选项。这将是
只从表中选择唯一的行/记录，并且不会返回
重复的行/记录。 （查看Access帮助，如果你不知道如何
进行选择查询）
例如（使用我自己的数据库）
SELECT DISTINCTROW tblLessonDetails.ID，tblLessonDetails.Grade，
tblLessonDetails.Rank，tblLessonDetails.LessonNumber，
tblLessonDetails.LessonName，tblLessonDetails.Activity INTO tblCleaned
。从tblLessonDetails;
这将选择唯一的记录，并将它们复制到使用MAKETABLE新表
'INTO''选项。然后你将得到一个新的
表减去重复的记录。
如果你的表包含一个唯一的ID（可能是这样），它可能是
最好选择表中的所有值，除了ID字段
然后在SQL语句中使用DISTINCT选项而不是
DISTINCTROW选项。这将根据查询设计网格中指定的字段仅返回具有唯一值
的记录。
即
SELECT DISTINCT tblLessonDetails.ID，tblLessonDetails.Grade，
tblLessonDetails.Rank，tblLessonDetails.LessonNumber，
tblLessonDetails.LessonName，tblLessonDetails.Activity INTO tblCleaned
FROM tblLessonDetails;
这是另一种方法，无需编辑SQL（来自Access
帮助）
1.在设计视图中打开查询。
2.通过点击查询设计视图外的任意位置选择查询
设计网格和字段列表。
3.单击工具栏上的属性以显示查询的属性
表。
执行以下操作之一：
4.根据
基础表或查询中的字段，在查询中显示重复记录
*将UniqueRecords属性设置为Yes 。
/>
5.根据
查询设计网格中的字段，在查询中显示重复记录
*将UniqueValues属性设置为是。
记得从主菜单工具栏中选择查询>生成表格查询
希望这会有所帮助，
David Thomas。
sp*****@usc.edu （Santosh）在留言中写道消息：其中图6a ************************** @ posting.google。 com> ...
由于我刚刚开始我的新工作，我继承了一个MS Access
数据库，该数据库在一个表中有近13000条记录。现在，我的任务是清理数据库，并可能将表分成两部分。
任何人都可以给我一些关于如何清理重复的记录而不经过所有记录的想法。
任何帮助或想法将不胜感激？我不是一个技术人员，但我对数据库非常熟悉。我没有处理具有13000条记录的MS
访问数据库。
Santosh写道：
由于我刚刚开始我的新工作，我继承了一个MS Access
数据库，该数据库在一个表中有近13000条记录。现在，我的任务是清理数据库，并可能将表分成两部分。
任何人都可以给我一些关于如何清理重复的记录而不经过所有记录的想法。
任何帮助或想法将不胜感激？我不是一个技术人员，但我对数据库非常熟悉。我没有处理具有13000条记录的MS
访问数据库。
在你搞乱数据库之前，我建议你/>
备份它。也许制作它的副本并使用
副本进行游戏并进行实验。
通常将表格分成1到多个结构。例如，
你可能有一张表，其中包含客户名称，地址，城市，州，邮政和
，然后是其他一些数据。基本上你会为
客户创建一条记录（1面）和许多方面的其他数据。
然后你创建表格和报告两张桌子。
Since I just started my new work, I have inherited a MS Access
database which has nearly 13000 records in a single table. Now, my
mandate is to clean the database and maybe split the table into two.
Can anyone please give me some ideas on how to clean duplicated
records without going through all the records.
Any help or idea will be greatly appreciated? I am not much of a tech
whiz but I am quite familiar with databases. I haven''t dealt with a MS
access database with 13000 records.
what are the fields???
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
Hi there,
you could try using the DISTINCTROW option in an SQL query. This will
only select the unique rows/records from a table and will not return
duplicate rows/records. (look up Access help if you don''t know how to
make a select query)
for example (using my own DB)
SELECT DISTINCTROW tblLessonDetails.ID, tblLessonDetails.Grade,
tblLessonDetails.Rank, tblLessonDetails.LessonNumber,
tblLessonDetails.LessonName, tblLessonDetails.Activity INTO tblCleaned
FROM tblLessonDetails;
This will select the unique records and copy them into a new table
using the MAKETABLE ''INTO'' option. You will then end up with a new
table minus the duplicate records.
If your table contains a unique ID (as it probably does) it may be
better to select all the values in the table except for the ID field
and then use the DISTINCT option in the SQL statement instead of the
DISTINCTROW option. This will return only records with unique values
based on the fields specified in the query design grid.
i.e
SELECT DISTINCT tblLessonDetails.ID, tblLessonDetails.Grade,
tblLessonDetails.Rank, tblLessonDetails.LessonNumber,
tblLessonDetails.LessonName, tblLessonDetails.Activity INTO tblCleaned
FROM tblLessonDetails;
This is another way to do it without editing the SQL (from Access
help)
1.Open a query in Design view.
2.Select the query by clicking anywhere in query Design view outside
the design grid and the field lists.
3.Click Properties on the toolbar to display the query''s property
sheet.
Do one of the following:
4.Prevent showing duplicate records in a query based on fields in the
underlying table or query
* Set the UniqueRecords property to Yes.
5.Prevent showing duplicate records in a query based on fields in the
query design grid
* Set the UniqueValues property to Yes.
remember to choose Query>Make Table Query from the main menu toolbar
hope this helps,
David Thomas.
sp*****@usc.edu (Santosh) wrote in message news:<6a**************************@posting.google. com>...Since I just started my new work, I have inherited a MS Access
database which has nearly 13000 records in a single table. Now, my
mandate is to clean the database and maybe split the table into two.
Can anyone please give me some ideas on how to clean duplicated
records without going through all the records.
Any help or idea will be greatly appreciated? I am not much of a tech
whiz but I am quite familiar with databases. I haven''t dealt with a MS
access database with 13000 records.
Santosh wrote:
Since I just started my new work, I have inherited a MS Access
database which has nearly 13000 records in a single table. Now, my
mandate is to clean the database and maybe split the table into two.
Can anyone please give me some ideas on how to clean duplicated
records without going through all the records.
Any help or idea will be greatly appreciated? I am not much of a tech
whiz but I am quite familiar with databases. I haven''t dealt with a MS
access database with 13000 records.
Before you go messing around with the database, I recommend that you
make a backup of it. Maybe make a copy of it and play around with the
copy and experiment with it.
Most often you split a table into a 1 to many structure. For example,
you may have a table with customer name, address, city, state, zip and
then some other data. Basically you would create a record for the
customer (1 side) and the other data on the many side.
Then you create forms and reports with the two tables.
这篇关于数据清理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
