从多个表中识别不匹配的查询 [英] Discerning unmatched query from multiple tables
问题描述
我目前有一个不匹配的查询运行多个表，但我想查看哪些字段一目了然。
这里的背景是我的尝试创建一个新的数据库，结合来自不同部门和独立公司的数据。如此多的列，当只有一个块中存在拼写错误或冲突的数据时，我会标记整个客户行，从而导致手工搜索差异。
我也在寻找有一个更好的方法来同时转换和等同表的差异。一个表具有完整的客户名称，而其他表将其拆分。有些表有产品代码，有些则有产品名称。
使用Access 2007攻击此项目的最佳方法是什么？此外，如果时间允许，我想创建一个表格前端，以选择是否要使用从其他公司导入的表中的数据覆盖现有客户数据？
最初，看起来好像你手上有一个真正的挑战，但首先是几个问题。
- 每个表都有一个唯一的字段/主键，以及是所有表共有的字段？我正在寻找基于个人'SSAN的EmployeeID之类的东西，存在于所有表中。
- 每个表是否具有相同数量的字段，并且您在比较所有表中的值字段？
- 每个表是否包含相同的记录数？
- 发布一些示例数据以及表/字段名称将非常有帮助。
1。是的我在所有相关的表格中都有共同字段。
2/3。不，没有一个表具有相同数量的列字段或客户行。虽然我目前关注的是我试图合并的2个表，但是还有另一个表需要在单个结果表中标记（或删除）客户。
字段名称来自单独的公司与我公司的表中的字段名称不匹配。另外，我需要比较的一些数据在一个表格中被删除，而不是在需要进行比较的单独表格中。
4。以下是示例数据：
内部表
SSN，FirstName，LastName，MiddleInit，日期，描述，代码
123-456-789 ，John，Doe，M，11/16/2011，Pen，Accounting
外部表
公司代码，格式化SSN， 格式化名称，日期，描述
123456，123-456-789，DOE，JOHN MIDDLE，2011-11-16上午12:00，P
拖欠表
格式化SSN，格式化名称，拖欠日期， ; Reason，Company
" 123-456-789"，DOE，JOHN MIDDLE"，2011-01-19 10:17 AM，Bad Check，" ; 123456
在此示例中，公司代码123456仅等于会计。
当我导入外部和欠款CSV并为其设置数据类型时日期为日期，然后错误输出，结果必须将数据类型设置为一个文本字段。
我用来完成最终数据表/查询的更多表中还有更多字段。
Pen的代码可能有多个P从P到Pn或XYZ
I currently have an unmatched query running for multiple tables, however I would like to view which field(s) are different at a glance.
The background here is that I am trying to create a new database combining data from different departments and a separate company. With so many columns, when there is a typo or conflicting data in only one block I have the entire customer row flagged, resulting in searching for the differences by hand.
I am also looking for a better method to convert and equate the differences from the tables at the same time. One table has the full customer name, while the others split it up. Some tables have product codes while others have product names.
What is the best method to attack this project using Access 2007? Additionally time permitting I would like to create a Form frontend to choose if I want to overwrite existing customer data with data from tables imported from a different company?
Initially, it appears as though you would have a real challenge on your hands, but first a couple of questions.
- Does each Table have a Unique Field/Primary Key, and is the Field common to all Tables? I am looking for something like an EmployeeID based on an individual''s SSAN, that exists in all the Tables.
- Does each Table have the same number of Fields, and are you comparing Values in ALL of the Fields?
- Does each Table contain the same Number of Records?
- Posting some Sample Data, along with Table/Field Names wouold be very helpful.
1. Yes I do have the common field in all tables which are related.
2/3. No, none of the tables have the same number of column fields or rows of customers. While my focus is currently on 2 tables that I am trying to merge there is even another table that will need to flag (or delete) the customer in the single resultant table.
Field names from the seperate company does not match the field names in my company''s tables. Also some of the data that I need to compare is abreviated in one table and not in a seperate table that need to be compared as well.
4. Here is the sample data:
Internal Table
SSN,FirstName,LastName,MiddleInit,Date,Description ,Code
123-456-789,John,Doe,M,11/16/2011,Pen,Accounting
External Table
"Company Code","Formatted SSN","Formatted Name","Date","Description"
"123456","123-456-789","DOE, JOHN MIDDLE",2011-11-16 12:00 AM,"P"
Delinquent Table
"Formatted SSN","Formatted Name","Delinquent Date","Reason","Company"
"123-456-789","DOE, JOHN MIDDLE",2011-01-19 10:17 AM,"Bad Check","123456"
Company Code 123456 is equal to only Accounting in this example.
When I import the External and Delinquent CSVs and set the Data Type for the Date as Date then it errors out and resultantly the Data Type has to be set as a text field.
There are more fields from more tables that I use to complete the final data table/query.
The Code for Pen could have multiple abbreviations from P to Pn or XYZ
这篇关于从多个表中识别不匹配的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
