COM pressing多个领域的一列在MS-访问 [英] Compressing multiple fields to a single column in MS-Access

问题描述

我试图恢复正常使用多个栏目，为同一件事的数据库。例如，街，Street2，...，Street12 。我目前的计划是创建一个单独的街道表，包含列街道和 mainID ，（加上此表的新ID字段，），其中 mainID 是主键的主数据库。 （例）每个 mainID 可能与需要多个街道的记录，允许在主表中去除街列。

I am attempting to normalize a database that uses multiple columns for the same thing. For instance, Street, Street2, ..., Street12. My current plan is to create a separate Street table, containing columns for Street and mainID, (plus a new ID field for this table,) where mainID is the primary key for the main database. (Example) Each mainID could be linked to multiple Street records as needed, allowing for the removal of the Street columns in the main table.

我打算通过子查询引用这个新表（按此页），以简化我们的搜索形式。 （你可以想象，它有点难以维持现在。）这种结构的示例查询可能是 SELECT名字从mainDB WHERE fID的IN（SELECT fID的FROM idStreets WHERE街道= [inputItem]）; 。

I'm planning to reference this new table through subqueries (as per this page) to streamline our search form. (As you can imagine, it's somewhat difficult to maintain right now.) A sample query for this structure might be SELECT name FROM mainDB WHERE fID IN (SELECT fID FROM idStreets WHERE street=[inputItem]);.

的问题是，该数据库已经有一些17000条目;太多用手合理正确的。我应该如何去自动转换？

The problem is that the database already has some 17000 entries; far too many to reasonably correct by hand. How should I go about automating the conversion?

推荐答案

首先，创建街道表上运行联合查询，然后将其输出到一个表：

First, to create the Streets table run a union query then output it to a table:

SELECT mainID, Street1
FROM maintable;
UNION 
SELECT mainID, Street2
FROM maintable;
...
UNION
SELECT mainID, Street12
FROM maintable;

又在哪里事后添加的自动编号ID的生成表查询。

Then the make-table query where afterwards you add an autonumber id.

SELECT * INTO Street FROM unionqry;

最后，你的子查询可以很好地将一个简单的连接：

Finally, your subquery can very well be a simple join:

SELECT name FROM mainDB 
WHERE fID IN 
   (SELECT fID FROM idStreets WHERE street=[inputItem]);

变成了：

SELECT DISTINCT name FROM mainDB 
INNER JOIN Streets ON mainDB.fID = Streets.mainID
WHERE street=[inputItem]);

这篇关于COM pressing多个领域的一列在MS-访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！