如何插入连接表 [英] How to insert into joined tables

问题描述

大家好。


我几乎不好意思问这个问题，但事实上，我在Access中的大部分涉猎都是通过GUI进行的。现在我需要做一些更深入的事情，并且不知道从哪里开始寻找。正如大多数人现在都知道的那样，我使用Access 2003来处理一些相当大的存档数据库？每个基本上都是一整年发生的事件记录。一年结束后，那一年的数据永远不会改变。


无论如何，为了节省这些相当大的数据库的空间，我想从某种程度上提取所有的独特值重复的20字节描述字段，并将它们存储在一个单独的表（一个中心的，而不是每个DB一个）中，并带有一个自动编号PK。然后，我将使用索引的长整数字段替换每个大数据库上的description字段，其中的值从新表中加载。将来对这些表的任何访问都将通过一个查询将它们与描述表连接起来。


到目前为止，非常好。我过去曾经一次或两次尝试过，一切正常。如果我没记错的话，性能确实受到影响，但只是轻微的。此时性能不是我关注的问题。


我想知道的是，鉴于我的查询加入了一个存档表和新描述表来填写文本描述，怎么会我要插入新的描述？我的意思是，我可以插入到连接的查询中（我可能很难表达）并且新描述会自动出现在描述表中，或者我需要先添加描述，然后将记录插入到另一个表中匹配的ID？


实际上我只是设置了另一个试用数据库，我正在讨论这种设置。这是返回？重组的连接？日志记录。

展开 | 选择 | Wrap | 行号

解决方案

大家好。


我几乎不好意思问这个问题，但实际上我在Access中的大部分时间都是通过GUI进行的。现在我需要做一些更深入的事情，并且不知道从哪里开始寻找。正如大多数人现在都知道的那样，我使用Access 2003来处理一些相当大的存档数据库？每个基本上都是一整年发生的事件记录。一年结束后，那一年的数据永远不会改变。


无论如何，为了节省这些相当大的数据库的空间，我想从某种程度上提取所有的独特值重复的20字节描述字段，并将它们存储在一个单独的表（一个中心的，而不是每个DB一个）中，并带有一个自动编号PK。然后，我将使用索引的长整数字段替换每个大数据库上的description字段，其中的值从新表中加载。将来对这些表的任何访问都将通过一个查询将它们与描述表连接起来。


到目前为止，非常好。我过去曾经一次或两次尝试过，一切正常。如果我没记错的话，性能确实受到影响，但只是轻微的。此时性能不是我关注的问题。


我想知道的是，鉴于我的查询加入了一个存档表和新描述表来填写文本描述，怎么会我要插入新的描述？我的意思是，我可以插入到连接的查询中（我可能很难表达）并且新描述会自动出现在描述表中，或者我需要先添加描述，然后将记录插入到另一个表中匹配的ID？


实际上我只是设置了另一个试用数据库，我正在讨论这种设置。这是返回？重组的连接？日志记录。

展开 | 选择 | Wrap | 行号

之前我这样做的方式是将两个表视为单独的更新。在新记录中打开日志表。打开描述表并添加描述。获取最后一条记录的ID号，并将其添加到日志表中。

我真的希望避免这种处理。通过简单导入每日文本文件来加载数据，如果可能的话，我想避免对此进行任何更改。


我必须确保整个事情如果需要，可以从头开始重新生成，所以如果我改变数据的加载方式，我将需要返回并重新提取并重新处理从记录历史开始的所有年份的数据（几年前）。并非我没有必要这样做一次或两次，但这是一种痛苦。

我真的希望避免那种处理。通过简单导入每日文本文件来加载数据，如果可能的话，我想避免对此进行任何更改。


我必须确保整个事情如果需要，可以从头开始重新生成，所以如果我改变数据的加载方式，我将需要返回并重新提取并重新处理从记录历史开始的所有年份的数据（几年前）。并非我没有必要这样做一次或两次，但这是一种痛苦。

对不起。不明白。我知道你现在没有加载数据，因为你问的是如何。因此，您不需要以任何方式执行此操作。你的意思是你需要重建文本文件吗？如果是这样，那么这个过程不会影响到那个。只要存储数据，您就可以以任何您喜欢的方式调用它。数据也不需要加载不同。只是以这种特殊的方式记录下来。


顺便说一句，如果你重做整个事物并按照你希望检索它们的顺序存储它们，你只能检索存档的描述。

Hi all.

I?m almost embarrassed to ask this one, but in fact most of my dabbling in Access has been at a fairly simple level, via the GUI. Now I need to do something slightly deeper, and don?t know where to start looking. As most of you would be aware by now, I?m using Access 2003 to work with some fairly large archive databases ? each is basically a log of events which happened over an entire year. Once the year has finished, that year?s data should never be changed.

Anyway, To save space in these rather large databases, I want to extract all the unique values from a somewhat repetitive 20-byte description field and store them in a separate table (one central one, not one per DB) with an autonumber PK. I?ll then replace the description field on each of the big databases with an indexed long integer field with the values here loaded from the new table. Any access to these tables in future will be through a query which joins them with the description table.

So far, so good. I have done this on a trial basis once or twice in the past, and everything worked fine. If I remember correctly the performance did suffer, but only slightly. Performance is not my concern at this point.

What I want to know is, given my query which joins an archive table and the new descriptions table to fill in the textual description, how would I go about inserting new descriptions? I mean, can I insert into the joined query (I?m probably expressing this poorly) and have the new descriptions automagically appear in the descriptions table, or will I need to add the description first, then insert the record into the other table with the matching ID?

In fact I?ve just set up another trial database with the kind of setup I?m talking about. Here is the join which returns the ?reconstituted? log record.

Expand|Select|Wrap|Line Numbers

解决方案

Hi all.

I?m almost embarrassed to ask this one, but in fact most of my dabbling in Access has been at a fairly simple level, via the GUI. Now I need to do something slightly deeper, and don?t know where to start looking. As most of you would be aware by now, I?m using Access 2003 to work with some fairly large archive databases ? each is basically a log of events which happened over an entire year. Once the year has finished, that year?s data should never be changed.

Anyway, To save space in these rather large databases, I want to extract all the unique values from a somewhat repetitive 20-byte description field and store them in a separate table (one central one, not one per DB) with an autonumber PK. I?ll then replace the description field on each of the big databases with an indexed long integer field with the values here loaded from the new table. Any access to these tables in future will be through a query which joins them with the description table.

So far, so good. I have done this on a trial basis once or twice in the past, and everything worked fine. If I remember correctly the performance did suffer, but only slightly. Performance is not my concern at this point.

What I want to know is, given my query which joins an archive table and the new descriptions table to fill in the textual description, how would I go about inserting new descriptions? I mean, can I insert into the joined query (I?m probably expressing this poorly) and have the new descriptions automagically appear in the descriptions table, or will I need to add the description first, then insert the record into the other table with the matching ID?

In fact I?ve just set up another trial database with the kind of setup I?m talking about. Here is the join which returns the ?reconstituted? log record.

Expand|Select|Wrap|Line Numbers

The way that I have done this before is to consider the two tables as separate for updating. Have the log table open at the new record. Open the description table and add the description. Get the id number of the last record and add this to the log table.

I''m really hoping to avoid that sort of processing. The data is loaded via a simple import of a daily text file, and I''d like to be avoid any change to this if possible.

I have to ensure that the entire thing can be re-generated from scratch if necessary, so if I change the way the data is loaded, I will be required to go back and re-extract and re-process all of the years worth of data from the beginning of recorded history (a few years ago). Not that I haven''t had to do this once or twice, but it is rather a pain.

I''m really hoping to avoid that sort of processing. The data is loaded via a simple import of a daily text file, and I''d like to be avoid any change to this if possible.

I have to ensure that the entire thing can be re-generated from scratch if necessary, so if I change the way the data is loaded, I will be required to go back and re-extract and re-process all of the years worth of data from the beginning of recorded history (a few years ago). Not that I haven''t had to do this once or twice, but it is rather a pain.

Sorry. Don''t understand. I know you are not loading the data now as you are asking how. So you do not need to do this in any way you did it before. Do you mean that you need to reconstitute the text files? If that is so then this process will not affect that. As long as the data is stored you can recall it in any way you like. The data does not need to be loaded differently either. Just recorded in this particular way.

BTW you can only retrieve the archived descriptions if you redo the entire thing and store them in the order in which you wish to retrieve them.

这篇关于如何插入连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！