创建一个检查缺失值的访问数据库 [英] Creating an access database which checks for missing values
问题描述
嘿伙计们我是新手，事实上我甚至不是程序员，而是决定承担学习访问和创建数据库的任务。而且我在从excel导入和获取AXT209939300LT等序列号方面已经相当远，例如将它们剥离到209939300。所以这里我感到很困惑......我已经问了几次这个问题，但我已经多考虑了一下，我认为这是最简单的方法。
我有四个号码。它们都是相当大的数字，例如10001000.这些数字具有与它们相关的其他信息，但这对于这个问题并不重要，并且其他信息绝不能用作主键。这些数字应该在相应的其他三行中具有相关的相同数字，但该程序的目的是假设它们不是。但是，该程序还假设每个数字中至少有一个。这是因为这些数字对应于公司拥有的计算机，而这四个数字来自公司的四个部门，负责跟踪这些计算机。如果没有数字，则问题超出了本程序的控制范围，必须在物理上找到。由于每行中可能缺少值，因此这些值不能是主键。
例如：
Number 1 | 2号| 3号| 4号|
10001000 | 10001002 | 10001002 | 10001003 |
10001001 | 10001001 | 10001003 | 10001000 |
10001002 | 10001003 | 10001001 | 10001001 |
10001003 | 10001000 | 10001000 | 10001002 |
但是说第1号字段可能会丢失10001003，因为该公司的部分在他们购买库存或者没有连接到网络。
这可能发生在任何领域。
当我写这篇文章时，我想也许我可以创建一个主键但是从第1号中获取值并将它们放入一个临时密钥，我会称之为TempPK，并且在包含第一个数字的值后，我将检查第2个中的每个数字与TempPK中已有的数字之间的关系在TempPK中没有相应的值我会包含该值，并为Number 2的长度执行此操作（我想我必须通过创建一个自动编号来完成，我会用它来查找该场的最大值）。我会继续这个为3号和4号。从这里我不会有缺失值，因此我可以创建一个主键。
这似乎可能有用，但似乎喜欢它会严重减慢程序运行时间并给我的计算机带来很大压力。
有没有人有更好的想法可以提高运行效率？也许某种方式我可以用关系来完成这个？
也有人用更科技的方式来描述我在这里想做什么吗？我一直在寻找关于如何做到这一点的答案很长时间（但也许我没有以正确的方式进行搜索）并且一旦我想到了这一切，我想发布代码以便其他人碰到这个将能够解决这个问题。由于我不是贸易程序员，因此我可能会做出最好的贡献，因为我不太可能在这个网站上回答其他人的问题....虽然我是电子作曲家/制作人那边我觉得我可以给别人写一首主题曲lol
无论如何我离题了。
希望这也不会带你很长时间阅读。
干杯
Kosm？s
Hey guys I''m a newbie and in fact I''m not even a programmer but decided to take up the task of learning access and creating a database. And I''ve gotten pretty far in terms of importing from excel and taking serial numbers such as AXT209939300LT and just stripping them down to the number of 209939300 for example. So here''s where I am confused...and I''ve asked this question a couple of times but I''ve given it more thought and I think this is the easiest way to put it.
I have four numbers. They are all rather large numbers such as 10001000. These numbers have other information associated with them but this is not important for this question and the other information can, in no way, be used as a primary key. These numbers all SHOULD have associated equal numbers in the corresponding other three rows but the purpose of this program is to assume that they don''t. The program does, however, also assume that there is at least one of each number. The reason for this is because the numbers correspond to computers that the company owns and the four numbers come from four parts of the company that are responsible for keeping track of these computers. If there is not a number then the problem is beyond the control of this program and must be found physically. Since there could be missing values in each row, these values can not be a primary key.
Example:
Number 1 | Number 2 | Number 3 | Number 4 |
10001000 | 10001002 | 10001002 | 10001003 |
10001001 | 10001001 | 10001003 | 10001000 |
10001002 | 10001003 | 10001001 | 10001001 |
10001003 | 10001000 | 10001000 | 10001002 |
but say field Number 1 could be missing 10001003 because that part of the company missed it when they were taking an inventory or it''s not connected to the network.
This could happen in any of the fields.
While I was writing this out I thought perhaps I could create a primary key but taking the values from Number 1 and putting them into a temporary key that I would call something like ''TempPK'' and after including values from the first number I would check for each number from Number 2 against the numbers already in TempPK and where there is not a corresponding value in TempPK I would include the value and do this for the length of Number 2 (which I guess I would have to do by creating an autonumber which I would use to find the maximum value of the feild). I would continue this for Number 3 and Number 4. From this I would not have a missing value and I could therefore create a primary key.
This seems like it may work but it seems like it will severely slow down the program runtime and put a lot of strain on my computer.
Does anyone have any better ideas that would make this run more efficiently? Perhaps some way I can use relationships to complete this?
Also does anyone have a more techical way to describe what I''m trying to do here? I''ve been searching for a long time for an answer on how to do this (but perhaps I haven''t been searching in the right way) and once I figure this all out I want to post the code up so other people that run into this will be able to figure this out. Since I am not a programmer by trade it''s probably the best contribution I can make considering I will not likely be able to answer other people''s questions on this site....Although I am an electronic composer/producer on the side so I guess I could write someone a theme song lol
Anyways I digress.
Hopefully this didn''t take you too long to read.
Cheers
Kosm?s
推荐答案
这个很复杂的Kosmos，但是你已经明确地努力放回去了，如果我周末有空的话，我会考虑一下。
我可以''承诺任何事情，但如果可以，我可能会在这里发布一些问题以保证清晰。
PS。我很佩服你的风格;）
This is complicated Kosmos, but as you''ve clearly made an effort to ''put something back'', I will give it some thought if I get any time over the weekend.
I can''t promise anything, but if I can, I''m likely to post some questions here for clarity.
PS. I admire your style ;)
这是一个复杂的Kosmos，但是你已经明确地努力放回东西 如果我周末有空的话，我会考虑一下。
我不能保证任何事情，但如果可以，我很可能会在这里发布一些问题清晰度。
PS。我很佩服你的风格;）
This is complicated Kosmos, but as you''ve clearly made an effort to ''put something back'', I will give it some thought if I get any time over the weekend.
I can''t promise anything, but if I can, I''m likely to post some questions here for clarity.
PS. I admire your style ;)
谢谢Neo。非常感谢:)
我会告诉你，如果我碰巧在一天结束时完成它...虽然我非常怀疑。最初我担心效率，因为我会运行一个会询问大约500万个问题的模块......但它不会存储这些问题的答案......它目前只存储大约1,200个结果......因为我对编程语言并不熟悉，所以我认为这可能是计算机上的一个负载...但是虽然在询问它之后它可能会对计算机造成轻微的负担，但这似乎不会太糟糕了这是一个必不可少的过程......并且比创建一个非常慢的复合主键要好得多。
...但是我觉得我有一个我需要做什么，但现在我需要学习如何做的语言...所以任何帮助将不胜感激。
再次感谢
-Kosm？s
Thanks Neo. Much appreciated :)
I will let you know if I do happen to finish it by the end of the day...although I highly doubt that. Originally I was worried about efficiency because I would be running a module that would ask about 5 million questions...but it wouldn''t be storing the answers to those questions...it currently would only store about 1,200 results...since I am not so familiar with programming languages I thought that this might be a load on the computer...but although it may be a slight load on the computer after asking around it seems like this won''t be too bad and that it''s an essential process...and much better than creating a compound primary key which would be really really slow.
...but yeah I think I have the jist of what I need to do, but now I need to learn the language of how to do it...so any help would be appreciated.
Thanks again
-Kosm?s
如果我理解你正确你试图跟踪公司的哪些部分正在使用计算机。如果我误解你的问题，我道歉，这对你来说是浪费时间。
你的桌子在这种情况下应该是这样的
tblComputer
PK电脑
1 100001
2 100002
tblDepartment
PK部门
1会计
2人力资源
tblComputer_department
PK电脑部门
1 1 1
2 1 2
在上面的示例中，HR和Accounting都将使用计算机10001.您的PK应该是自动编号，因为您的计算机的号码已更改或出现错误在上面的例子中，您可以使用Computer_PK，Department_PK作为Computer_DepartmentPK，但这并不是必需的。
不会伤到你的脑袋想出真实的主键 - - 在大多数情况下，它们使用起来很危险。另外你应该阅读有关数据库规范化的内容。
一个来源是
http://www.tomjewett.com/dbdesign/dbdesign.php?page=normalize .php& imgsize = medium
，维基百科也有一些很好的提示。
If I understand you correctly you are trying to track what parts of the company are using computers. if i misread your question i apologize and this is a waste of time for you.
your tables in this case should look like this
tblComputer
PK Computer
1 100001
2 100002
tblDepartment
PK Department
1 Accounting
2 Human Resources
tblComputer_department
PK Computer Department
1 1 1
2 1 2
in the above example both HR and Accounting would be using computer 10001. your PK should be an autonumber incase your computer''s number is changed or a mistake was made typing it in. In the above example you could use Computer_PK, Department_PK for the Computer_DepartmentPK but this isn''t really necessary.
don''t hurt your head coming up with real-life primary keys - - for the most part they are dangerous to use. Also you should read about database normalization.
one source is
http://www.tomjewett.com/dbdesign/dbdesign.php?page=normalize.php&imgsize=medium
also, wikipedia has some good tips.
嘿伙计们我'ma newbie，实际上我甚至不是程序员，而是决定承担学习访问和创建数据库的任务。而且我在从excel导入和获取AXT209939300LT等序列号方面已经相当远，例如将它们剥离到209939300。所以这里我感到很困惑......我已经问了几次这个问题，但我已经多考虑了一下，我认为这是最简单的方法。
我有四个号码。它们都是相当大的数字，例如10001000.这些数字具有与它们相关的其他信息，但这对于这个问题并不重要，并且其他信息绝不能用作主键。这些数字应该在相应的其他三行中具有相关的相同数字，但该程序的目的是假设它们不是。但是，该程序还假设每个数字中至少有一个。这是因为这些数字对应于公司拥有的计算机，而这四个数字来自公司的四个部门，负责跟踪这些计算机。如果没有数字，则问题超出了本程序的控制范围，必须在物理上找到。由于每行中可能缺少值，因此这些值不能是主键。
例如：
Number 1 | 2号| 3号| 4号|
10001000 | 10001002 | 10001002 | 10001003 |
10001001 | 10001001 | 10001003 | 10001000 |
10001002 | 10001003 | 10001001 | 10001001 |
10001003 | 10001000 | 10001000 | 10001002 |
但是说第1号字段可能会丢失10001003，因为该公司的部分在他们购买库存或者没有连接到网络。
这可能发生在任何领域。
当我写这篇文章时，我想也许我可以创建一个主键但是从第1号中获取值并将它们放入一个临时密钥，我会称之为TempPK，并且在包含第一个数字的值后，我将检查第2个中的每个数字与TempPK中已有的数字之间的关系在TempPK中没有相应的值我会包含该值，并为Number 2的长度执行此操作（我想我必须通过创建一个自动编号来完成，我会用它来查找该场的最大值）。我会继续这个为3号和4号。从这里我不会有缺失值，因此我可以创建一个主键。
这似乎可能有用，但似乎喜欢它会严重减慢程序运行时间并给我的计算机带来很大压力。
有没有人有更好的想法可以提高运行效率？也许某种方式我可以用关系来完成这个？
也有人用更科技的方式来描述我在这里想做什么吗？我一直在寻找关于如何做到这一点的答案很长时间（但也许我没有以正确的方式进行搜索）并且一旦我想到了这一切，我想发布代码以便其他人碰到这个将能够解决这个问题。由于我不是贸易程序员，因此我可能会做出最好的贡献，因为我不太可能在这个网站上回答其他人的问题....虽然我是电子作曲家/制作人那边我觉得我可以给别人写一首主题曲lol
无论如何我离题了。
希望这也不会带你很长时间阅读。
干杯
Kosm？s
Hey guys I''m a newbie and in fact I''m not even a programmer but decided to take up the task of learning access and creating a database. And I''ve gotten pretty far in terms of importing from excel and taking serial numbers such as AXT209939300LT and just stripping them down to the number of 209939300 for example. So here''s where I am confused...and I''ve asked this question a couple of times but I''ve given it more thought and I think this is the easiest way to put it.
I have four numbers. They are all rather large numbers such as 10001000. These numbers have other information associated with them but this is not important for this question and the other information can, in no way, be used as a primary key. These numbers all SHOULD have associated equal numbers in the corresponding other three rows but the purpose of this program is to assume that they don''t. The program does, however, also assume that there is at least one of each number. The reason for this is because the numbers correspond to computers that the company owns and the four numbers come from four parts of the company that are responsible for keeping track of these computers. If there is not a number then the problem is beyond the control of this program and must be found physically. Since there could be missing values in each row, these values can not be a primary key.
Example:
Number 1 | Number 2 | Number 3 | Number 4 |
10001000 | 10001002 | 10001002 | 10001003 |
10001001 | 10001001 | 10001003 | 10001000 |
10001002 | 10001003 | 10001001 | 10001001 |
10001003 | 10001000 | 10001000 | 10001002 |
but say field Number 1 could be missing 10001003 because that part of the company missed it when they were taking an inventory or it''s not connected to the network.
This could happen in any of the fields.
While I was writing this out I thought perhaps I could create a primary key but taking the values from Number 1 and putting them into a temporary key that I would call something like ''TempPK'' and after including values from the first number I would check for each number from Number 2 against the numbers already in TempPK and where there is not a corresponding value in TempPK I would include the value and do this for the length of Number 2 (which I guess I would have to do by creating an autonumber which I would use to find the maximum value of the feild). I would continue this for Number 3 and Number 4. From this I would not have a missing value and I could therefore create a primary key.
This seems like it may work but it seems like it will severely slow down the program runtime and put a lot of strain on my computer.
Does anyone have any better ideas that would make this run more efficiently? Perhaps some way I can use relationships to complete this?
Also does anyone have a more techical way to describe what I''m trying to do here? I''ve been searching for a long time for an answer on how to do this (but perhaps I haven''t been searching in the right way) and once I figure this all out I want to post the code up so other people that run into this will be able to figure this out. Since I am not a programmer by trade it''s probably the best contribution I can make considering I will not likely be able to answer other people''s questions on this site....Although I am an electronic composer/producer on the side so I guess I could write someone a theme song lol
Anyways I digress.
Hopefully this didn''t take you too long to read.
Cheers
Kosm?s
这篇关于创建一个检查缺失值的访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
