更改查询以访问不同的表 [英] Changing query to access different tables

问题描述

我不确定这是一个简单的解决方案，但我想我会问。我/ b $ b经常发现自己有一个我想重用的查询，但是有一个不同的数据源。这些数据源通常具有相同的

字段名称。查询选择了字段的子集，因此选择*

实际上不是一个选项。


是否有一种简单的方法可以更改查询的来源，是在

设计网格还是SQL显示？我想我可以将SQL复制到

写字板中并使用查找和替换，但似乎Access应该以某种方式提供

。

例如，如果我有以下查询从2003

数据中读取：

SELECT [tbl_MD_03] .LastName，[tbl_MD_03] .FirstName ，

[tbl_MD_03] .Address1，[tbl_MD_03] .City，[tbl_MD_03] .State，

[tbl_MD_03] .ZipCode

FROM tbl_MD_03 WHERE（（[[tbl_MD_03] .State）=" WV"））;


我想要的是一个快速的方法来说服它选择相同的/>
字段，但来自tbl_MD_04（或tbl_MD_02等）。


有什么想法吗？

谢谢，

Mary


我试图寻找解决方案，但是没有

I''m not sure there''s an easy solution to this, but thought I''d ask. I
often find myself with a query which I''d like to reuse, but with a
different datasource. These datasources generally have identical
field names. The queries select a subset of the fields, so "Select *"
is not really an option.

Is there an easy way to change the source of a query, either in the
design grid or SQL display? I suppose I could copy the SQL into
WordPad and use find and replace, but it seems Access should provide
some way to do this.

For an example, if I have the following query that reads from the 2003
data:
SELECT [tbl_MD_03].LastName, [tbl_MD_03].FirstName,
[tbl_MD_03].Address1, [tbl_MD_03].City, [tbl_MD_03].State,
[tbl_MD_03].ZipCode
FROM tbl_MD_03 WHERE ((([tbl_MD_03].State)="WV"));

What I''d like is a quick way to now convince it to select the same
fields, but from tbl_MD_04 (or tbl_MD_02, etc.).

Any ideas?
Thanks,
Mary

I tried searching for a solution to this, but didn''t

推荐答案

ma*****@wvnet.edu （Marizel）在留言中写道：< 49 ****** ********************@posting.google。 com> ...

ma*****@wvnet.edu (Marizel) wrote in message news:<49**************************@posting.google. com>...

我不确定这是一个简单的解决方案，但我想我会问。我经常发现自己有一个我想重用的查询，但是有一个不同的数据源。这些数据源通常具有相同的字段名称。查询选择了字段的子集，因此Select *
实际上不是一个选项。

是否有一种简单的方法来更改查询的来源，无论是在
设计网格还是SQL显示？我想我可以将SQL复制到
写字板并使用查找和替换，但似乎Access应该提供一些方法来实现这一点。

例如，如果我有以下查询从2003
数据中读取：
SELECT [tbl_MD_03] .LastName，[tbl_MD_03] .FirstName，
[tbl_MD_03] .Address1，[tbl_MD_03] .City，[tbl_MD_03] .State，
[tbl_MD_03] .ZipCode
FROM tbl_MD_03 WHERE（（（[[tbl_MD_03] .State）=" WV"））;

我想要什么是一种快速的方法来说服它选择相同的
字段，但来自tbl_MD_04（或tbl_MD_02等）。

任何想法？
谢谢，
Mary

I''m not sure there''s an easy solution to this, but thought I''d ask. I
often find myself with a query which I''d like to reuse, but with a
different datasource. These datasources generally have identical
field names. The queries select a subset of the fields, so "Select *"
is not really an option.

Is there an easy way to change the source of a query, either in the
design grid or SQL display? I suppose I could copy the SQL into
WordPad and use find and replace, but it seems Access should provide
some way to do this.

For an example, if I have the following query that reads from the 2003
data:
SELECT [tbl_MD_03].LastName, [tbl_MD_03].FirstName,
[tbl_MD_03].Address1, [tbl_MD_03].City, [tbl_MD_03].State,
[tbl_MD_03].ZipCode
FROM tbl_MD_03 WHERE ((([tbl_MD_03].State)="WV"));

What I''d like is a quick way to now convince it to select the same
fields, but from tbl_MD_04 (or tbl_MD_02, etc.).

Any ideas?
Thanks,
Mary

听起来像是一个有缺陷的设计，因为你应该有一个

表，其中有一个标记记录的额外字段。如果你这样做，那么你可以把它变成一个参数化的查询，然后你就可以开始运行了。按原样，您必须执行以下操作：

1.在代码中打开querydef.SQL

2.使用Replace函数替换旧的每个出现的事务/>
带有新表的表名。

3.运行查询。 （我想你可以将它保存为新的查询...）


嗯...确信你的设计有点过了吗？如果您只是在表中添加了一个额外的字段，那么您可以在未绑定的表单上执行类似点

的查询以收集参数，然后打开您的

查询。无需代码。

Sounds like a flawed design, because you should probably have a single
table with an extra field that flags the records. If you do that,
then you could just turn this into a parameterized query, and you''d be
off and running. As is, you have to do something like:
1. open the querydef.SQL in code
2. use the Replace function to replace each occurence of the old
tablename with the new one.
3. run the query. (I guess you could save this as a new query...)

Umm... convinced your design is a bit off yet? If you just included
one extra field in your table, you could just do something like point
your query at an unbound form to gather parameters and then open your
query. No code required.

Marizel写道：

Marizel wrote:

是否有一种简单的方法可以在
中更改查询来源设计网格还是SQL显示？我想我可以将SQL复制到
写字板中并使用查找和替换，但似乎Access应该提供某种方式来执行此操作。

Is there an easy way to change the source of a query, either in the
design grid or SQL display? I suppose I could copy the SQL into
WordPad and use find and replace, but it seems Access should provide
some way to do this.

您是直接使用查询，还是作为表格的数据源和

报告？如果是后者，只需创建您需要的所有查询，并且您可以手动或以编程方式更改表单和报告的数据源。

Are you using the queries directly, or as datasources for forms and
reports? If the latter, just create all the queries you need, and you
can manually or programtically change the datasource for forms and reports.

ma*****@wvnet.edu （Marizel）在留言中写道：< 49 *** ***********************@posting.google。 com> ...

ma*****@wvnet.edu (Marizel) wrote in message news:<49**************************@posting.google. com>...

我不确定这是一个简单的解决方案，但我想我会问。我经常发现自己有一个我想重用的查询，但是有一个不同的数据源。这些数据源通常具有相同的字段名称。查询选择了字段的子集，因此Select *
实际上不是一个选项。

是否有一种简单的方法来更改查询的来源，无论是在
设计网格还是SQL显示？我想我可以将SQL复制到
写字板并使用查找和替换，但似乎Access应该提供一些方法来实现这一点。

例如，如果我有以下查询从2003
数据中读取：
SELECT [tbl_MD_03] .LastName，[tbl_MD_03] .FirstName，
[tbl_MD_03] .Address1，[tbl_MD_03] .City，[tbl_MD_03] .State，
[tbl_MD_03] .ZipCode
FROM tbl_MD_03 WHERE（（（[[tbl_MD_03] .State）=" WV"））;

我想要什么是一种快速的方法来说服它选择相同的
字段，但来自tbl_MD_04（或tbl_MD_02等）。

任何想法？
谢谢，
Mary


好​​的，经过一些更有用的思考......你为什么要将你的数据按年分成不同的表格？别。只需将它全部放在

ONE表中，其中包含Year字段或其他内容。那么整个事情就是这么简单。

很容易就太荒谬了。


将所有标准放在表格上的未绑定文本框/组合框中

然后就是这样做：

SELECT [tbl_MD_03] .LastName，[tbl_MD_03] .FirstName，
[tbl_MD_03] .Address1，[tbl_MD_03] .City，[tbl_MD_03 ] .State，
[tbl_MD_03] .ZipCode
FROM tbl_MD_03 WHERE（（（[[tbl_MD_03] .State）= Forms！[frmSearchCriteria]！[cboState]））;

I''m not sure there''s an easy solution to this, but thought I''d ask. I
often find myself with a query which I''d like to reuse, but with a
different datasource. These datasources generally have identical
field names. The queries select a subset of the fields, so "Select *"
is not really an option.

Is there an easy way to change the source of a query, either in the
design grid or SQL display? I suppose I could copy the SQL into
WordPad and use find and replace, but it seems Access should provide
some way to do this.

For an example, if I have the following query that reads from the 2003
data:
SELECT [tbl_MD_03].LastName, [tbl_MD_03].FirstName,
[tbl_MD_03].Address1, [tbl_MD_03].City, [tbl_MD_03].State,
[tbl_MD_03].ZipCode
FROM tbl_MD_03 WHERE ((([tbl_MD_03].State)="WV"));

What I''d like is a quick way to now convince it to select the same
fields, but from tbl_MD_04 (or tbl_MD_02, etc.).

Any ideas?
Thanks,
Mary
Okay, after a little more useful thought... why are you partitioning
your data by year into different tables? Don''t. Just chuck it all in
ONE table with a Year field or whatever. then this whole thing is so
easy it''s ridiculous.

Put all your criteria in unbound textboxes/comboboxes on a form and
then just do something like this:
SELECT [tbl_MD_03].LastName, [tbl_MD_03].FirstName,
[tbl_MD_03].Address1, [tbl_MD_03].City, [tbl_MD_03].State,
[tbl_MD_03].ZipCode
FROM tbl_MD_03 WHERE ((([tbl_MD_03].State)=Forms![frmSearchCriteria]![cboState]));

然后在表单上放一个按钮来打开查询。完成。

then put a button on your form to open the query. Done.

这篇关于更改查询以访问不同的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！