在同一个表上运行多个附加查询？ [英] Running Multiple Append Queries on the same table?

问题描述

我的团队在工作中使用Cognos运行多个查询来提取数据。然后他们将这些数据导入到Access中，然后通常运行一个追加查询来运行RND函数，为他们提取一些样本来审核帐户。我正在向我的团队提出一个建议，也就是说，如果我们只运行一个Cognos查询并拉入所有列，每个人都需要为每个测试提取样本，这样可以节省时间。然后我们只有一个人会运行这个查询并将其拉入Access，而不是我们可以运行我们的Append（附加到另一个特定于测试的表）和/或选择该表的查询以获得我们的结果。在Append和/ Select查询的例子中，我不知道是否会发生多个人同时运行不同的查询，但是会出现多个用户运行不同查询的问题。同一张桌子？

My team at work uses Cognos to run multiple queries to pull in data. Then they take that data and import into Access and then usually run an Append Query to run a RND function to pull out a few samples for them to review the accounts. I was making a suggestion to my team that maybe if we only ran one Cognos query and pulled in all of the columns that everyone needs to pull samples for each of there tests that this would be better on the time savings aspect. Then only one of us would run this query and pull it into Access and than we can run our Append (to append into another table specific to there test) and/or Select queries off that table to get our results. In the instance of the Append and/Select queries, I don''t know if this would take place that multiple people would be running different queries at the same time, but would there ever be an issue of multiple users running different queries off the same table?

推荐答案

大多数解释对我来说都没什么意义，所以我不知道你要做什么。但是，你的最后一个问题似乎很简单，所以我会为你回答这个问题。

Most of the explanation made frankly little sense to me, so i don''t have a good idea of what you''re trying to do. However, your last questioon seems straightforward enough so I''ll have a stab at answering that for you.

...

In Append和/ Select查询的实例，我不知道是否会发生多个人同时运行不同的查询，但是会出现多个用户运行不同查询的问题表？

...
In the instance of the Append and/Select queries, I don''t know if this would take place that multiple people would be running different queries at the same time, but would there ever be an issue of multiple users running different queries off the same table?

这可能是一个问题，但通过简单的方法使QueryType成为快照来简单地读取（提取）数据通常是可以避免的。


通常使用默认类型Dynaset创建查询。这可以保持数据锁定，但几乎不需要仅仅读取数据。

This can be an issue, but is generally avoidable when simply reading (extracting) data by the simple expedient of making the QueryType a Snapshot.

Queries generally are created with the default type of Dynaset. This can keep data locked, but is almost never necessary for merely reading data.

大多数解释对我来说毫无意义，所以我不知道你想做什么。然而，你的最后一个问题似乎很简单，所以我会为你回答这个问题。


这可能是一个问题，但通常只需阅读（提取）就可以避免数据通过简单的方法使QueryType成为快照。


查询通常使用默认类型的动态集来创建。这可以保持数据锁定，但几乎不需要仅仅读取数据。

Most of the explanation made frankly little sense to me, so i don''t have a good idea of what you''re trying to do. However, your last questioon seems straightforward enough so I''ll have a stab at answering that for you.

This can be an issue, but is generally avoidable when simply reading (extracting) data by the simple expedient of making the QueryType a Snapshot.

Queries generally are created with the default type of Dynaset. This can keep data locked, but is almost never necessary for merely reading data.

我希望这对我想做的事情有一点了解。我团队中的每个人都有自己的前端副本。每个月每个人都运行一次Cognos查询，为每个测试提供数据（只有他们允许我们用来提取信息的系统）。因为他们现在这样做的方式是他们在那里运行单独的Cognos查询并将它们导入Access并从该表中运行和Sample populaton。我正在考虑在创建一个主Cognos查询和将一个表导入Access数据库的意义上简化这一点，并让一些人在那里运行与我相同的表。它将运行一个Append查询，并从该主表中抽取10-20个样本到该特定测试的另一个表集。除了运行这些追加查询以提取样本之外，还可能存在一个或两个也运行的Select查询。我考虑转换到此主表而不是运行特定于此测试的单个Cognos查询的两个Select查询将需要大约30-40分钟才能运行。具有一个主表的数据大小约为200k行。单个查询较小但我觉得运行多个Cognos查询并导入多个文件会更耗时，我只需要一个文件来使用Select或Append查询来提取样本。正如我所说，我不知道会发生这种情况，但我无法想象在某些时候它不会发生，也就是说，如果我正在运行我的一个测试，从主表中取出15个样本决定我的团队也运行一个选择或附加查询，利用相同的主表会有准确性的问题，或者只是给那个人一个错误信息，说这个表正在使用？

I hope this makes a little better sense on what I am trying to do. Everyone on my team has there own copy of the front end. Every month each person runs a Cognos query to pull in there Data for each of there tests (only system they allow us to use to pull in the information). Because the way they do it now is they all run there individual Cognos queries and import them into Access and run and Sample populaton out of that table. I was thinking about simplifying this in the sense of creating one Main Cognos query and importing one table into the Access Database and having a few people run there samples off of the same table as me. It would run an Append query and pull out maybe 10-20 samples from this main table into another table set for that particular test. In addition to running these Append queries to pull out samples there might be a Select query or two that also runs. The two Select queries I was thinking about converting over to this main table instead of running the individual Cognos query specific to this test would take about 30-40 minutes to run. The Data size with one main table is about 200k rows. The individual queries are smaller but I feel would more time consuming to run multiple Cognos queries and importing multiple files that I would just have one file to pull samples from using either a Select or Append query. And as I stated I don''t know that this would happen, but I couldn''t imagine at some point that it not happening, that is if I was running my one test pulling out 15 samples out of this main table and someone decided on my team to also run a Select or Append queries which utilizes this same Main table would there be an issue with accuracy or would it just give that person an error message saying that this table is in use?

如果确保所有查询都设置为快照类型而不是动态集，那么任何潜在的问题都将被最小化（根据我之前的帖子）。


I假设（仍然不是很清楚，我很害怕）主要 table存储在某个地方的后端访问数据库中，尽管在任何地方都没有说明。


您可能还需要查看为Access选择的锁定策略（工具/选项/高级选项卡）。

If you make sure that all the queries are set up as snapshot type rather than Dynaset, then any potential problems will be minimised (as per my earlier post).

I assume (still not very clear I''m afraid) that the "Main" table is stored in a Back-end Access database somewhere, although this is never stated anywhere.

You may need also, to look at the locking policies chosen for Access (Tools / Options / Advanced tab).

这篇关于在同一个表上运行多个附加查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！