Storing large arrays in a table

[Stubert]

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?

[Albert D. Kallal]

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.OpenR ecordset("tblCh ildTable")

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

Set rst = CurrentDb.OpenR ecordset(strSql )
Do While rst.EOF = False
Call StripOut(rstChi ld, 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(strSkillD ata, ",")
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

[Albert D. Kallal]

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

[Stubert]

On 20 Apr, 12:02, "Albert D. Kallal" <PleaseNOOOsPAM [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
pleaseNOOSpamKa [e-mail]

Albert,

[Stubert]

On 20 Apr, 12:02, "Albert D. Kallal" <PleaseNOOOsPAM [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
pleaseNOOSpamKa [e-mail]

Albert,
Many thanks for your quick reply
i didn't even think about using the Memo Data type
That will solve my problem
Many thanks

[paii, Ron]

I did something similar with a menu system and ran into the same problem. Un
normalized data is the problem. You have a M-M relationship between Employee
and Training, setup 3 tables, tblEmployee, tblTraining and
tblEmploeeTrain ing.

tblEmploeeTrain ing has 3 fields
[EmployeeID] from tblEmploee
[TrainingID] from tblTraining
[Completed] Y/N

You will no longer need a function to lookup what training each employee
needs or has received. You can build forms and reports showing training for
each employee and which employee needs or has had each type of training, and
lookups will be almost instant.

"Stubert" <s.******@bdf.l td.ukwrote in message
news:11******** *************@b 75g2000hsg.goog legroups.com...

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?

[Gord]

On Apr 20, 7:16 am, Stubert <s.mac...@bdf.l td.ukwrote:

On 20 Apr, 12:02, "Albert D. Kallal" <PleaseNOOOsPAM [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
pleaseNOOSpamKa [e-mail]

Albert,
Many thanks for your quick reply
i didn't even think about using the Memo Data type
That will solve my problem
Many thanks

Please note that Albert, by mentioning memo fields in his desire to
provide you with as comprehensive an answer as possible, has given you
a way to perpetuate a *terrible* design choice. In other words, those
comma-delimited lists are still a BAD idea, they can now simply become
larger and more unruly.

If this system of yours is really of any consequence, you have two
choices:

1. You can fix the design now (as described by Albert and Ron), or

2. You (or your successor, after you get fired) can fix the design
later.

It's your call....

[Stubert]

On Apr 20, 10:23 am, Gord <g...@kingston. netwrote:

On Apr 20, 7:16 am, Stubert <s.mac...@bdf.l td.ukwrote:

On 20 Apr, 12:02, "Albert D. Kallal" <PleaseNOOOsPAM [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
pleaseNOOSpamKa [e-mail]

Albert,
Many thanks for your quick reply
i didn't even think about using the Memo Data type
That will solve my problem
Many thanks

Please note that Albert, by mentioning memo fields in his desire to
provide you with as comprehensive an answer as possible, has given you
a way to perpetuate a *terrible* design choice. In other words, those
comma-delimited lists are still a BAD idea, they can now simply become
larger and more unruly.

If this system of yours is really of any consequence, you have two
choices:

1. You can fix the design now (as described by Albert and Ron), or

2. You (or your successor, after you get fired) can fix the design
later.

It's your call....- Hide quoted text -

- Show quoted text -

Ron
It's actually a relationship between Job Title and Training not
employee and training.
The idea being that we can have a set amount of training for a
specific job title so that when an employee arrives we give him/her a
Job title and all the training modules are automatically given to that
employee. So the problem was how to store a Job title with it's
associated training Modules.
You are right of course Normalisation is the problem.
Albert,
Your idea, if understand it correctly (and i probably don't), would
still have a lot of duplicated data. If i had one record fro every Job
Title/Training Module number would get round the comma delimited list
problem but would end up with a really large table.
Gord
I'm glad you are not my boss but you are right Albert gave me an easy
way out which i have implemented for the time being however i would
like to improve it.
The comma list is not a good design, i agree, but it does work and it
works quickly there are no other issues with it apart from the one i
mentioned above. Any more suggestions will be greatly accepted

[paii, Ron]

"Stubert" <s.******@bdf.l td.ukwrote in message
news:11******** **************@ e65g2000hsc.goo glegroups.com.. .

On Apr 20, 10:23 am, Gord <g...@kingston. netwrote:

On Apr 20, 7:16 am, Stubert <s.mac...@bdf.l td.ukwrote:

On 20 Apr, 12:02, "Albert D. Kallal" <PleaseNOOOsPAM [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
pleaseNOOSpamKa [e-mail]

Albert,
Many thanks for your quick reply
i didn't even think about using the Memo Data type
That will solve my problem
Many thanks

Please note that Albert, by mentioning memo fields in his desire to
provide you with as comprehensive an answer as possible, has given you
a way to perpetuate a *terrible* design choice. In other words, those
comma-delimited lists are still a BAD idea, they can now simply become
larger and more unruly.

If this system of yours is really of any consequence, you have two
choices:

1. You can fix the design now (as described by Albert and Ron), or

2. You (or your successor, after you get fired) can fix the design
later.

It's your call....- Hide quoted text -

- Show quoted text -

Ron
It's actually a relationship between Job Title and Training not
employee and training.
The idea being that we can have a set amount of training for a
specific job title so that when an employee arrives we give him/her a
Job title and all the training modules are automatically given to that
employee. So the problem was how to store a Job title with it's
associated training Modules.
You are right of course Normalisation is the problem.
Albert,
Your idea, if understand it correctly (and i probably don't), would
still have a lot of duplicated data. If i had one record fro every Job
Title/Training Module number would get round the comma delimited list
problem but would end up with a really large table.
Gord
I'm glad you are not my boss but you are right Albert gave me an easy
way out which i have implemented for the time being however i would
like to improve it.
The comma list is not a good design, i agree, but it does work and it
works quickly there are no other issues with it apart from the one i
mentioned above. Any more suggestions will be greatly accepted

You would have 2 relationship tables, one holding the training required for
each job title which would be copied to the employee's relationship table
when they are assigned a job title.

[Stubert]

On 20 Apr, 17:36, "paii, Ron" <p...@packairin c.comwrote:

"Stubert" <s.mac...@bdf.l td.ukwrote in message

news:11******** **************@ e65g2000hsc.goo glegroups.com.. .

On Apr 20, 10:23 am, Gord <g...@kingston. netwrote:

On Apr 20, 7:16 am, Stubert <s.mac...@bdf.l td.ukwrote:

On 20 Apr, 12:02, "Albert D. Kallal" <PleaseNOOOsPAM [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
pleaseNOOSpamKa [e-mail]

Albert,
Many thanks for your quick reply
i didn't even think about using the Memo Data type
That will solve my problem
Many thanks

Please note that Albert, by mentioning memo fields in his desire to
provide you with as comprehensive an answer as possible, has given you
a way to perpetuate a *terrible* design choice. In other words, those
comma-delimited lists are still a BAD idea, they can now simply become
larger and more unruly.

If this system of yours is really of any consequence, you have two
choices:

1. You can fix the design now (as described by Albert and Ron), or

2. You (or your successor, after you get fired) can fix the design
later.

It's your call....- Hide quoted text -

- Show quoted text -

Ron
It's actually a relationship between Job Title and Training not
employee and training.
The idea being that we can have a set amount of training for a
specific job title so that when an employee arrives we give him/her a
Job title and all the training modules are automatically given to that
employee. So the problem was how to store a Job title with it's
associated training Modules.
You are right of course Normalisation is the problem.
Albert,
Your idea, if understand it correctly (and i probably don't), would
still have a lot of duplicated data. If i had one record fro every Job
Title/Training Module number would get round the comma delimited list
problem but would end up with a really large table.
Gord
I'm glad you are not my boss but you are right Albert gave me an easy
way out which i have implemented for the time being however i would
like to improve it.
The comma list is not a good design, i agree, but it does work and it
works quickly there are no other issues with it apart from the one i
mentioned above. Any more suggestions will be greatly accepted

You would have 2 relationship tables, one holding the training required for
each job title which would be copied to the employee's relationship table
when they are assigned a job title.- Hide quoted text -

- Show quoted text -

Ron,
That's what i have done. But i think you are suggesting that the table
which holds the training required for each job title has a record for
each Job Title / Training module pair. We have currently around twenty
five job titles and Over 200 training modules. Now i know not every
job title will have every module but that still amounts to a lot of
records. In my table i have tewnty five records. One for each of the
job titles and a column with the commas seperated list. I thought that
was more compact. However your suggestion may make it easier to work
with.
Thanks for your reply