在表中存储大型数组 [英] Storing large arrays in a table

问题描述

我有一个培训模块数据库，用于存储有关员工的信息

以及他们执行或需要执行的培训。此数据库中的一个

表存储了基于职位的
需要进行的培训。因此，如果一名清洁工加入公司，我们知道他们需要进行全面的培训和清扫培训。我没有确定如何存储这些信息，但这就是我想出来的结果和

，因为你会看到我已经达到了限制。现在我知道我想出的解决方案可能非常差，但它有一段时间了。这是

我的解决方案。

我创建了一个表，其职位名称为一列，另一个

列，用于存储逗号分隔列表每个

模块的索引号。当我需要添加模块或检查需要哪些模块

作为职位时，我只需解析逗号分隔列表，将其放入

数组并读取数组。这工作正常，直到我在文本字段中达到255 char

限制。在Access中实现这一点的聪明和正确的方法是什么？

解决方案

你会简单的将该逗号列表移出另一个表格，并将其与

联系回

您的职位名称表（反过来又与员工表相关）。


您可以降低一点标准化水平，并为他们拥有的每项培训技能反复重复职称

.


而且，如果你为这些工作创建一个实际工作指示表的表格，那么你，或者你的用户可以从一个组合框中选择数字

显示您的用户的折扣（它将使traiing和使用

yoru appcation更加用户友好）。

代码将非常像：
< br $>
Public Sub ProcessToChild（）


Dim rst作为DAO.Recordset

Dim rstChild作为DAO.Recordset


Dim strSql作为字符串


Debug.Print" running .."

设置rstChild = CurrentDb.OpenRecordset（" tblChildTable"）


strSql =" select id，JobTitle，Skills from MyTable" &安培; _

"技能不为空


设置rst = CurrentDb.OpenRecordset（strSql）

执行rst。 EOF = False

Call StripOut（rstChild，rst！ID，rst！ChapterData）

rst.MoveNext

Loop

rst.Close


MsgBox完成


结束子


Public Sub StripOut（rst为DAO.Recordset，lngID为Long，strSkillData为

String）


Dim vBuf As Variant

Dim i作为整数

Dim strSkill As String


vBuf = Split（strSkillData，"，"）

i = 0到UBound（vBuf）

''

rst.AddNew

rst！Parent_id = lngID

rst！Skill = vbuf（i）

rst.Update

Next i


rst.Update


结束Sub

-

Albert D. Kallal（访问MVP）

加拿大艾伯塔省埃德蒙顿
pl ***************** @ msn.com

由方式，您可以将文本字段更改为备注字段，并允许

65000个字符...


问题是如何搜索，或者建立了关于这些东西的报告???


如果你按照我的建议使用表格，那么你可以使用报告排序和

分组选项来产生计数技能......不必用代码来做

所以....


-

Albert D .Kallal（接入MVP）

加拿大艾伯塔省埃德蒙顿
pl ***************** @ msn.com

4月20日，12： 02，Albert D. Kallal， < PleaseNOOOsPAMmkal ... @ msn.com>

写道：

顺便说一句，您可以将文本字段更改为备忘录字段，这允许

65000个字符...


问题是你如何搜索或建立关于这个东西的报告？


如果您按照我的建议使用表格，那么您可以使用报告排序和

分组选项来产生技能计数......不必使用代码要做

所以....


-

Albert D. Kallal（访问MVP）

加拿大艾伯塔省埃德蒙顿

pleaseNOOSpamKal ... @ msn.com

Albert，

I have a training module db that stores information about employees
and what training they have carried our or need to carry out. One
table in this database stores what training needs to be carried based
on a job title. So if a cleaner joins the company we know that they
need the sweeping up training and the mopping up training. I wasn''t
sure how to store this information but this is what i came up with and
as you will see i have hit a limitation. Now i know the solution i
came up with is probably very poor but it worked for a while. this was
my solution.
I created a table that has the job title as one column and another
column which stores a comma delimeted list of index numbers for each
module. When i need to add a module or check what modules are needed
for a job title i just parse the Comma delimeted list, put it into an
array and read the array. This worked fine until i hit the 255 char
limit on the text field. What would be the clever and proper way to
implement this in Access?

解决方案

You would simply move out that comma list to another table, and relate it
back to
your job title table (which in turn relates back to the employee table).

You could reduce the level of normalizing a bit, and repeat the job title
over and over for each training skill they have.

And, if you createa table of the actually job descrtipons for those job
numbers, then you, or your users could select the number from a combo box
that displays the disctrions for your users (it would make traiing and use
of yoru appcation much more user friendly).
The code will be much like:

Public Sub ProcessToChild()

Dim rst As DAO.Recordset
Dim rstChild As DAO.Recordset

Dim strSql As String

Debug.Print "running.."
Set rstChild = CurrentDb.OpenRecordset("tblChildTable")

strSql = "select id, JobTitle, Skills from MyTable " & _
"where Skills is not null"

Set rst = CurrentDb.OpenRecordset(strSql)
Do While rst.EOF = False
Call StripOut(rstChild, rst!ID, rst!ChapterData)
rst.MoveNext
Loop
rst.Close

MsgBox "done"

End Sub

Public Sub StripOut(rst As DAO.Recordset, lngID As Long, strSkillData As
String)

Dim vBuf As Variant
Dim i As Integer
Dim strSkill As String

vBuf = Split(strSkillData, ",")
For i = 0 To UBound(vBuf)
''
rst.AddNew
rst!Parent_id = lngID
rst!Skill = vbuf(i)
rst.Update
Next i

rst.Update

End Sub
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

By the way, you can change a text field to a memo field, and that allows
65000 characters...

The problem is how do you search, or built reports on this stuff???

If you use a table as I suggested, then you can use the reports sorting and
grouping options to produce counts of skills...an not have to use code to do
so....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

On 20 Apr, 12:02, "Albert D. Kallal" <[e-mail]>
wrote:

By the way, you can change a text field to a memo field, and that allows
65000 characters...

The problem is how do you search, or built reports on this stuff???

If you use a table as I suggested, then you can use the reports sorting and
grouping options to produce counts of skills...an not have to use code to do
so....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

Albert,

这篇关于在表中存储大型数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！