复制记录的问题 [英] Problem of copying records
问题描述
在表格中，我现在拥有属于特定ID的记录数量
如果我在此表格的某个文件中输入一个值我希望它是
复制在属于此特定ID的所有记录中。例如
ID名称
--------------------------
1 xyz
1
1
1
2
2
2
当我输入xyz时对于任何一个ID＃1的记录。我想要所有其他记录（ID＃1）的名称字段为xyz。所以在
之后......表格看起来像这样
ID名称
--------- -----------------
1 xyz
1 xyz
1 xyz
1 xyz
2
2
2
我知道这是表格本身不可能..但是我认为它可以在一个表格中做到这一点。但我不知道如何
欢呼
雷克斯
< blockquote>''********代码启动**********
const cQuote ="""" 那是两个引号
me！Control.DefaultValue = cQuote&我！Control.Value& cQuote
''********代码结束**********
http://www.mvps.org/access/forms/frm0012.htm
Rex写道：
在一个表格中，我现在拥有属于特定ID的记录数量
如果我在此表的某个文件中输入一个值，我希望它是在属于此特定ID的所有记录中复制的
。例如
ID名称
--------------------------
1 xyz
1
1
1
2
2
2
当我输入xyz时对于任何一个ID＃1的记录。我想要所有其他记录（ID＃1）的名称字段为xyz。所以在
之后......表格看起来像这样
ID名称
--------- -----------------
1 xyz
1 xyz
1 xyz
1 xyz
2
2
2
我知道这是表格本身不可能..但是我认为它可以在一个表格中做到这一点。但我不知道如何
欢呼
雷克斯
你可以有一个按钮表单来调用和更新查询。让我们说
你的表名是Table1。您想要更新NameFld价值
的一 for id = 1.
Dim strSQL As String
Dim strText As String
Dim lngID As Long
Dim dbs作为数据库
strTest =" One" ''值更新
lngID = 1''寻找的关键
''创建动态sql语句
strSQL = UPDATE Table1 SET Table1.NameFld =''" &安培; _
strTest& &安培; ''' &安培; _
" WHERE Table1.ID =" &安培; lngID
set dbs = Currentdb
使用dbs
。执行strSQL
msgbox"已更新" &安培; .RecordsAffected& "记录
结束了
set dbs = Nothing
Rex写道：
我知道这在表格本身是不可能的..但是我认为它可以在一个表格中做到这一点
。但我不知道如何
你*不想*在表格中这样做。为什么不？看看你的架构。
无论你有1或4或50条ID为1的记录，名字都是*总是*
将是xyz，或者不管你有什么将其编辑为将来。 （名称不是
顺便说一句专栏的好名字，因为它是一个保留的关键字。）
这是多余的数据。你和ID之间有一个1：1的关系，
意味着它可以进入一个单独的查询表（这个孩子的父级
表），任何一个值都可以作为外键放在当前表中。
也就是说，如果你想使用代理键。否则，您可以删除
数字ID列并保留当前表中的Name列自然键
并忘记创建单独的父表，除非有
需要从当前表移动到
父表的其他属性才能将其标准化。
如果你在规范化导入的电子表格和
表单的中间只是转换数据的步骤之一，那么你可以使用单个更新查询来设置相应的
在当前表中使用等值连接的所有
记录中的值如下：
更新tblCompanies AS C1 INNER JOIN
tblCompanies AS C2 ON C1.ID = C2.ID
SET C1.CoName = C2.CoName
WHERE（ISNULL（C2.CoName）= FALSE）;
然后您可以提取记录以创建查找/父表格
a make table查询如下：
SELECT DISTI NCT ID，CoName INTO tblCoNames
FROM tblCompanies;
但我的建议是使用此
结构转换导入的电子表格切断中间步骤，只需创建
查找/父表，并使用这样的make表查询（不需要先将
分配给空列的值）：
SELECT DISTINCT ID，CoName INTO tblCoNames
FROM tblCompanies
WHERE（ISNULL（CoName）= FALSE）;
如果你想使用代理键，那就是。如果您愿意，请跳过ID列
自然键。
-
通过AccessMonster.com发布的消息
http：//www.accessmonster。 com / Uwe / For ... ccess / 200610/1
In a table, I have number of records belonging to a particular ID now
if I enter a value in one of the fileds of this table I want it to be
copied in all the records belonging to this particluar ID. for example
ID Name
--------------------------
1 xyz
1
1
1
2
2
2
When I enter "xyz" for any one of the record of ID# 1.. I want all the
other records (with ID# 1) to have the name field as "xyz" so after
this.. the table would look like this
ID Name
--------------------------
1 xyz
1 xyz
1 xyz
1 xyz
2
2
2
I know this is not possible in table itself.. However I presume it
would be possible to do this in a form. But I dont know HOW
cheers
Rex
''******** Code Start **********
const cQuote="""" ''Thats two quotes
me!Control.DefaultValue = cQuote & me!Control.Value & cQuote
''******** Code End **********
http://www.mvps.org/access/forms/frm0012.htm
Rex wrote:
In a table, I have number of records belonging to a particular ID now
if I enter a value in one of the fileds of this table I want it to be
copied in all the records belonging to this particluar ID. for example
ID Name
--------------------------
1 xyz
1
1
1
2
2
2
When I enter "xyz" for any one of the record of ID# 1.. I want all the
other records (with ID# 1) to have the name field as "xyz" so after
this.. the table would look like this
ID Name
--------------------------
1 xyz
1 xyz
1 xyz
1 xyz
2
2
2
I know this is not possible in table itself.. However I presume it
would be possible to do this in a form. But I dont know HOW
cheers
Rex
You could have a button on a form to call and update query. Let''s say
your table name is "Table1". You want to update "NameFld" to the value
of "One" for id = 1.
Dim strSQL As String
Dim strText As String
Dim lngID As Long
Dim dbs As Database
strTest = "One" ''value to update
lngID = 1 ''key to look for
''create dynamic sql statement
strSQL = "UPDATE Table1 SET Table1.NameFld = ''" & _
strTest & & "'' " & _
"WHERE Table1.ID = " & lngID
set dbs = Currentdb
With dbs
.Execute strSQL
msgbox "Updated " & .RecordsAffected & " records"
End WIth
set dbs = Nothing
Rex wrote:I know this is not possible in table itself.. However I presume it
would be possible to do this in a form. But I dont know HOWYou *don''t* want to do this in a form. Why not? Look at your schema.
Whether you have 1 or 4 or 50 records with an ID of 1, the Name is *always*
going to be xyz, or whatever you edit it to be in the future. (Name is not a
good name for a column by the way, as it''s a reserved keyword.)
That''s redundant data. You have a 1:1 relationship between ID and Name,
meaning it can go into a separate lookup table (the parent for this child
table) and either value can be placed in the current table as the foreign key.
That is, if you like to use surrogate keys. Otherwise, you can drop the
numeric ID column and keep the Name column natural key in the current table
and forget about creating a separate parent table, unless there are
additional attributes that need to be moved from the current table to the
parent table to normalize it.
If OTH you are in the middle of normalizing an imported spreadsheet and the
form is just one of the steps in transforming the data, then you could
instead use a single update query to set the corresponding values in all
records with an equijoin on the current table like this:
UPDATE tblCompanies AS C1 INNER JOIN
tblCompanies AS C2 ON C1.ID = C2.ID
SET C1.CoName = C2.CoName
WHERE (ISNULL(C2.CoName) = FALSE);
And you could then extract the records to create the lookup/parent table with
a make table query like this:
SELECT DISTINCT ID, CoName INTO tblCoNames
FROM tblCompanies;
But my recommendation is to transform an imported spreadsheet with this
structure by cutting out the intermediate step and just create the
lookup/parent table with a make table query like this (without first
assigning values to the empty columns):
SELECT DISTINCT ID, CoName INTO tblCoNames
FROM tblCompanies
WHERE (ISNULL(CoName) = FALSE);
If you like to use surrogate keys, that is. Skip the ID column if you prefer
natural keys.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1
这篇关于复制记录的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
