使用vba在访问中向现有表添加字段 [英] Adding a field to existing table in access using vba

问题描述

好的，所以我正在开发一个Access项目，我遇到了障碍，我需要你的专家帮助。


我有一个表（tblWorkerStats），带有字段：[Worker Code] ]，[工人姓名]，[1月16日]，[2月16日]，[MAr''16]，[4月16日]。所有月份名称字段都包含工作人员未在该月份及时处理的案例计数。我有另一个表（tblOverdue），其中包含字段：[工作代码]，[案例编号]，其中列出了当前月份未及时处理的所有单个案例编号。我们从远程服务器获取这个月度数据，只提供当月的数据。


所以我想做的是每个月在（tblWorkerStats）填充一个新字段来存储本月每名工人的案件数量。我该怎么做呢？

我认为我不能使用APPEND或UPDATE查询。我想做的是调用表单上的VBA事件过程，将来自（tblWorkerStats）的数据存储到rs1作为记录集。并将（qryCountOverdue）的结果存储到rs2作为记录集。


现在我希望rs2数据附加到rs1加入[worker code]。如果可能的话，我希望新的字段名称是当前的月份名称。


另一个复杂的问题是报告上每个月我只想显示统计数据过去3个月，由于过去3个月的字段名称每个月都会发生变化，我不知道如何才能实现这一目标。也许根据现场位置显示最后3个字段，但我不知道如何。


P.S.我是Access和VBA的新手，但我赶紧赶上你的帮助我希望我能学到更多东西。


谢谢

Ok so I am working on a Access project and I have hit a roadblock and I need your expert help.

I have a table(tblWorkerStats) with fields: [Worker Code], [worker name], [Jan ''16], [Feb ''16], [MAr ''16], [Apr ''16]. All the month name fields contain a count of cases that the worker has not processed in time for that month. I have another table (tblOverdue) with fields: [worker code], [case number] which lists all the individual case numbers that were not processed in time for that current month. We get this monthly data from a remote server and only provides data for the current month.

So what I am trying to do is populate a new field in (tblWorkerStats) each month to store the count of cases per worker for the current month. How would I go about doing that?
I do not think I can use a APPEND or UPDATE query. What I thought of doing is call on VBA event procedure on the form to store the data from (tblWorkerStats) into rs1 as recordset. And store results from (qryCountOverdue) into rs2 as recordset.

Now I want rs2 data appended to rs1 joined on [worker code]. Also if possible, I want the new field name to be the current month name.

And another complex problem is that on the report is that each month I only want to show stats for the last 3 months and since the field names of the last 3 months are going to change every month, I do not know how I would be able to accomplish that. Maybe display last 3 fields based on the field location ordinals but I do not know how.

P.S. I am very new to Access and VBA but I''m catching up very quick and with your help I''m hoping I''ll learn a lot more.

Thank you

推荐答案

如果我理解你的问题，你有几个重复的字段： - [Jan''16]，[Feb''16]，[MAr''16]， [4月''] ...


我怀疑这是你问题的本质。这不是你在MS Access中做事的方式，这更像你在Excel中的方式。


你在你的陈述中无意中自己指出了问题;另一个复杂的问题是每个字段中的数据都是相同的类型，因此它应存储在表中的一个字段中，而另一个字段用于标识数据。一旦你掌握了这一点，你会发现事情变得更加容易，你将有效地使用查询来获得你需要的结果。


所以总结一个相当长的答案，你需要更改数据库结构。你越早改变它就越好。

If I understand your question correctly you have several repeating Fields:- [Jan ''16], [Feb ''16], [MAr ''16], [Apr ''16]...

I suspect this is the essence of your problem. This isn''t the way you would do things in MS Access this is more like the way you would do them in Excel.

You inadvertently pointed out the problem yourself in your statement "And another complex problem is" The data in each of those Fields is of the same sort, therefore it should be stored in one field in a table with another field to identify the data. Once you grasp this, you will find things become much easier, you will use queries efficiently and effectively to get the results you need.

So to sum up a rather long answer, you need to change your database structure. The sooner you change it the better.

谢谢你的回复Tony。


我理解结构不是最好的，但我们得到的来自远程服务器的数据仅适用于当月。我们获得的数据是每个月的案例列表，我感兴趣的是每个月只存储案例数。


所以你建议的是我创造的一个单独的表格，包含字段，工作人员代码，案例数量和月份，我将识别月份的计数。这对我来说很有意义，我可能每个月都可以通过APPEND查询来完成这项工作，并使用格式（Now（），mmmm \''yy）为月份字段赋值。我对么？我是否能够在附加查询中使用聚合，因为我需要存储的是每个工作人员的案例数而不是整个案例列表。


另外，在报告中，如何在过去3个月中显示3个单独的列，其中数据来自同一个字段？我无法在逻辑上考虑这一部分。


再次感谢。

Thank you for your response Tony.

I understand the structure is not the best, however we get data from a remote server for current month only. And the data we get is a list of cases every month and what I''m interested in is only storing the count of cases each month.

So what you are suggesting is I create a separate table with fields, worker code, count of cases and month and i''ll identify the count with the month. That makes sense to me and I probably could accomplish that through a APPEND query every month and assign value to the month field with format(Now(),"mmmm\''yy"). Am I correct? Would I be able to use aggregates in a Append query since all I need to store is the count of cases per worker and not the whole list of cases.

Also,on the report, how would I show 3 separate columns for the last 3 months where data is coming from the same field? I am not able to think that part logically.

Thanks again.

我仍然认为你需要改变你的结构。您每月都会获得数据，按用户计算。


让我们说你下个月是[May''16]

你有5个用户--- ID'的1,2,3,4,5

计数--- 43,45,23,87,12

而不是将数据添加到新字段中，这是您永远不应该考虑的事情，然后将数据添加到表中的字段。表格结构如下： -


fldMonth --- fldUser --- fldCount

[May''16] --- 1 - ----------- 43

[5月''] --- 2 ------------- 65

[May''16] --- 3 ------------- 23

[May''16] --- 4 ------ ------- 87

[5月''] --- 5 ------------- 12

I still think you need to change your structure. You are getting the data in per month, count per user.

Let''s say you''re next month is [May ''16]
You have 5 user --- ID''s 1,2,3,4,5
Counts of --- 43,65,23,87,12
Instead of adding that data into a new field, which is something you should never really consider, then you add the data to fields in a table. The table structure would look like this:-

fldMonth --- fldUser --- fldCount
[May ''16] --- 1 -------------43
[May ''16] --- 2 -------------65
[May ''16] --- 3 -------------23
[May ''16] --- 4 -------------87
[May ''16] --- 5 -------------12

这篇关于使用vba在访问中向现有表添加字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！