如何用随机序列中的B值更新A表中的空字段 [英] How to update-populate empty fields in A table with values from a B with a random sequence
问题描述
我正面临一个巨大的问题！我是希腊雅典中央警察局的警务人员，我正在尝试创建一个数据库，该数据库每天自动为200名-300名警务人员创建工作时间表.
我创建了3个表:
A)包含300名军官及其ID的所有信息(作为连接到表C的主键)以及其他信息(例如地址，当他们转移给我们时的信息)的表.将其称为表A"
B)一张桌子，上面有官员可能需要的所有可能的地方
例如，列A [NOT THE HEADER]中的记录是"Police patrols"，列B:我们将为该服务提供服务的人数"，为此我创建了一个查询(带有统计表，因此它可以重复许多记录)如我所愿，例如，如果我需要30个人巡逻，它将在表C中创建此服务的30条唯一记录，并且我已经为日期设置了输入，因此它可以在表C中自动输入日期. >
C)表C:这里我有信息，例如由查询创建的带有工作时间的所有服务，以及将分配给特定地点的人员ID的列.
I am facing a huge problem! I am a police officer in a central police department in Athens Greece and i am trying to create a database that will automatically create Work Schedules for about 200 -300 police officers every day.
I have created 3 tables:
A) A table that contains all the information of 300 officers with their ID (as a primary key which is connected to |Table C") and other information such as address, when they were transferred to us etc . Call it "Table A"
B) A table with all the possible places that officers may needed
For Example a record in Column A [NOT THE HEADER] is "Police patrols" , Column B: "number of people we will put to that service" for which i created a query (with a tally table so it can repeat as many records as i want, so for example if i need 30 people on patrol it will create 30 unique records of this service in Table C and also i have set a input for the date so it can automatically input the date on the Table C.
C) Table C : Here i have information such as all the services that were created by the query with work time and also a column of the id of the officer that will be assigned to a specific place.
现在，我被困在尝试以随机顺序更新表C，特别是ID号列，以及表A中所有警察的ID号列.(我还没有到达随机部分但是，每次我更新表C时，它只会从表A的第一条记录中获取ID，而忽略了所有其他记录，因此它会将相同的ID号放入我需要的30个位置，并且不会显示所有的记录表A)
Now i am stuck trying to update, with a random sequence, the table C, specifically the id number column, with the id number column of all the police officers from Table A. (I haven't reached the random part just yet as every time i update the Table C it gets only the ID from the first record from Table A ignoring all the other records, so it puts the same id number to the 30 places i would need and it doesn't show all records from Table A)
WHERE (((TableC.DateofDuty)=[Date]));
我很幸运地尝试了此操作，因为表C中的每条记录都从表A中获得了相同的ID.
I tried this with no luck as for every record in table C it gets the same id from Table A.
我也尝试过这个:
UPDATE TableC LEFT OUTER JOIN TableA ON TableC.ID= TableA.ID
SET TableC.ID = TableA.ID
WHERE (((TableC.DateofDuty)=[Date]));
UPDATE TableC SET TableC.ID= TableA.ID
WHERE (((TableC.DateofDuty)=[Date]));
或
UPDATE TableC LEFT OUTER JOIN TableA ON TableC.ID= TableA.ID
SET TableC.ID = TableA.ID
WHERE (((TableC.DateofDuty)=[Date]));
在第一行代码中，它更新了所有我想更新但具有相同ID的记录.在第二条记录中，它询问我是否要填充特定的地方，比如说100个我想要的地方，我按是"并且没有ID更新.
In the first line of code it updates all the records i wanted to be updated but with the same ID. In the second record it asks if i want to populate the specific, lets say 100, places i want, i press yes and no id is updated.
我也尝试过内部连接，但没有运气
I have also tried Inner Join with no luck
非常感谢您的帮助
我刚刚发现我的邮件被标记为不足，因此 我会再说一些细节
I just noticed my message was flagged as insufficient so i will put some more details
这是需要填充/更新的表: 表C
This is the table needed to be populated/Updated: TABLE C
Service Duty Date ID
ΜΕΤΑΓΩΓΕΣ 24/06/2019
ΜΕΤΑΓΩΓΕΣ 24/06/2019
ΜΕΤΑΓΩΓΕΣ 24/06/2019
从表A
(Primary ID) Police Rank
250311 ΤΑΞΙΑΡΧΟΣ
288066 ΑΡΧΙΦΥΛΑΚΑΣ
288076 ΑΣΤΥΦΥΛΑΚΑΣ
到目前为止，我已经使用了以下代码
So far i Have used the following code
UPDATE [TABLE C] INNER JOIN [TABLE A] ON [[TABLE C].ID = [TABLE A].ID
SET [TABLE C].ID= [TABLE A].ID
WHERE ((([TABLE A].DATE)=[INPUT DATE]));
哪怕我在输入中输入正确的日期也更新0行(24/06/2019) 我什至把1/1/2019只是为了确保它不是引起问题的输入
Which updates 0 rows even though i enter the correct date on the input (24/06/2019) I even put 1/1/2019 just to make sure its not the input causing the problem
第二次尝试:
UPDATE [TABLE C] Left JOIN [TABLE A] ON [[TABLE C].ID = [TABLE A].ID
SET [TABLE C].ID= [TABLE A].ID
WHERE ((([TABLE A].DATE)=[INPUT DATE]));
提示我更新所有3条记录，但什么也不更新，因此即使它说它要更新，也不会更新.
which prompts me to update all the 3 records but updates nothing, so even though it says it is going to update, it does not.
第三次尝试
UPDATE [TABLE C] Right JOIN [TABLE A] ON [[TABLE C].ID = [TABLE A].ID
SET [TABLE C].ID= [TABLE A].ID
WHERE ((([TABLE A].DATE)=[INPUT DATE]));
但是它返回0条记录...
But it returns 0 records...
第4次尝试 我只有表C
4th try I have just the TABLE C
UPDATE [TABLE C] SET [TABLE C].ID= "Random Text"
WHERE ((([TABLE C].Date)=[input date]));
将输入到我输入的日期的ID恰好更新为随机文本" 但是，一旦我将Table A重新放回游戏中，它就会返回0值
Which updates the ID of the date i put into the input just fine to "Random Text" But as soon as i put Table A back in to the game it returns 0 values
只要我弄清楚，我就会继续尝试.我确信这一定是我真的很想念的东西.
I will keep trying as long a i figure it out. I am sure it must be something really dumb i m missing out.
推荐答案
所以我有点想通了 让我真的快速回顾一下 我们有:
So i kind of figured it out Let me really quick do a recap We have :
TABLEA
ID | POLICE RANK | FULL NAME |
____________________________________
288066 | Const. | Chris Meli |
273111 | Serg. | John Do |
231444 | Const. | Bill Park |
298432 | Const. | Joe Park |
_____________________________________
包含警务人员的信息，并连接到 TableC 中的 ID 字段，因此，即使从TableA上的连接，您也可以检查每个警员的职责分配给前几天.
which contains the info of the police officers and is connected to the ID field in TableC so even from the connection on TableA you can examine the duties every officer has been assigned to the previous days.
TABLEB
DUTY | Number of Police needed |
| for each service |
____________________________________
Patrol | 1 |
Guards | 1 |
Courts | 2 |
____________________________________
为了简单起见，我将数字1和2放在数字中.通常， TableA 会容纳超过250人，并且在 TableB 上会包含许多 Duty ，并且需要的警察数量将取决于日期和许多其他因素.
I put the number 1 and 2 just for the sake of simplicity. Normally TableA will contain more than 250 people and on TableB will be many Duties and the number of police needed will vary depending on the date and many other factors.
TABLEC
ID | DUTY | DATE |
____________________________________
| | |
| | |
| | |
| | |
_____________________________________
TableC将从TableA(ID)，TableB(Duty)填充，并且输入日期i将通过以下附加查询进行调度
TableC will be populated From TableA (ID),TableB (Duty) and an input for the date i will be scheduling with the following appending query
INSERT INTO TABLEC ( DUTY, DATE, ID )
SELECT TABLEB.DUTY, [INPUT DATE], TABLEA.ID
FROM TABLEA, TABLEB INNER JOIN n ON n.n <= TABLEB.[Number of Police needed for each service];
(n是一个数字表，n.n是一个具有10000个数字的列，因此请不要关注它) 现在，附加查询向我返回了我需要的结果，但是发生的是，它会乘上所需的位置并与警官的ID相乘. 所以不要这样:
(n is a numbers table and n.n is a column that has like 10000 numbers so don't pay attention to that) Now the appending query returns me the results i need but what happens is , it kind of multiplies the position needed with the officer's ID . So instead of having this:
TABLEC
ID | DUTY | DATE |
____________________________________
288066 | Patrol | 23/06/2019 |
273111 | Guards | 23/06/2019 |
231444 | Courts | 23/06/2019 |
298432 | Courts | 23/06/2019 |
_____________________________________
我有这个
TABLEC
ID | DUTY | DATE |
____________________________________
288066 | Patrol | 23/06/2019 |
288066 | Guards | 23/06/2019 |
288066 | Courts | 23/06/2019 |
288066 | Courts | 23/06/2019 |
273111 | Patrol | 23/06/2019 |
273111 | Guards | 23/06/2019 |
273111 | Courts | 23/06/2019 |
273111 | Courts | 23/06/2019 |
231444 | Patrol | 23/06/2019 |
231444 | Guards | 23/06/2019 |
231444 | Courts | 23/06/2019 |
231444 | Courts | 23/06/2019 |
298432 | Patrol | 23/06/2019 |
298432 | Guards | 23/06/2019 |
298432 | Courts | 23/06/2019 |
298432 | Courts | 23/06/2019 |
_____________________________________
有没有一种方法可以自动连接TableA.ID和TableB.DUTY?
Is there a way to connect TableA.ID and TableB.DUTY without being multiplied automatically?
这篇关于如何用随机序列中的B值更新A表中的空字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
