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.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

[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" <[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,

[Stubert]

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,
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
tblEmploeeTraining.

tblEmploeeTraining 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.ltd.ukwrote in message
news:11*********************@b75g2000hsg.googlegro ups.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 <[e-mail]:

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,
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 <[e-mail]:

On Apr 20, 7:16 am, Stubert <[e-mail]:

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,
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.ltd.ukwrote in message
news:11**********************@e65g2000hsc.googlegr oups.com...

On Apr 20, 10:23 am, Gord <[e-mail]:

On Apr 20, 7:16 am, Stubert <[e-mail]:

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,
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" <[e-mail]:

"Stubert" <[e-mail] in message

news:11**********************@e65g2000hsc.googlegr oups.com...

On Apr 20, 10:23 am, Gord <[e-mail]:

On Apr 20, 7:16 am, Stubert <[e-mail]:

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,
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

[paii, Ron]

"Stubert" <s.******@bdf.ltd.ukwrote in message
news:11**********************@e65g2000hsc.googlegr oups.com...

On 20 Apr, 17:36, "paii, Ron" <[e-mail]:

"Stubert" <[e-mail] in message

news:11**********************@e65g2000hsc.googlegr oups.com...

On Apr 20, 10:23 am, Gord <[e-mail]:
On Apr 20, 7:16 am, Stubert <[e-mail]:

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,
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

At worst you would have 5000 records. If you can write VBA and use recordset
objects, a function to break off each training module ID and append a new
record with the job title ID, would not be hard to write. It would only need
to be run once. 5000 (3) column records would take very little space.

[Last Boy Scout]

Stubert wrote:

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?

Putting a comma separated list inside a field is violating 1st normal
form. You are not suppose to put tables inside of tables in normal
relational databases. This can be described as putting a table inside a
table. Another problem it may cause is that things can get misspelled
and what not and cause problems. Also lets say you ever wanted to make
a list of employees with a certain job skill or list each employee and
their job skills, you can see that doing that in a query may be somewhat
difficult. If your query had to search through every character in this
long field for "sweep" and someone spelled it Sweep or sweap, that might
cause problems.

I think perhaps just make one table that has the Job in it with a key
and one table that has the responsibilities in it and have a join table
that joins the job titles to the work Description. I am not quite sure
that is an ideal way to handle this but it is one possibility. While it
is possible to do this in this manner, I have never seen it done this
way. However, this is what you were trying to do with a comma separated
list.

[Stubert]

On 21 Apr, 14:30, Last Boy Scout <[e-mail]:

Stubert wrote:

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?

Putting a comma separated list inside a field is violating 1st normal
form. You are not suppose to put tables inside of tables in normal
relational databases. This can be described as putting a table inside a
table. Another problem it may cause is that things can get misspelled
and what not and cause problems. Also lets say you ever wanted to make
a list of employees with a certain job skill or list each employee and
their job skills, you can see that doing that in a query may be somewhat
difficult. If your query had to search through every character in this
long field for "sweep" and someone spelled it Sweep or sweap, that might
cause problems.

I think perhaps just make one table that has the Job in it with a key
and one table that has the responsibilities in it and have a join table
that joins the job titles to the work Description. I am not quite sure
that is an ideal way to handle this but it is one possibility. While it
is possible to do this in this manner, I have never seen it done this
way. However, this is what you were trying to do with a comma separated
list.- Hide quoted text -

- Show quoted text -

Last Boy Scout,
Thanks for your input. However My comma seperated list has no text in
it. It has ID numbers that are foreign keys pointing to the training
module table which has the description. The ID number is added
programatically when you tick a box on a matrix page which has a check
box for each job title. So there is no possibility of a spelling
mistake or even a wrong number being typed.

[Susie DBA [MSFT]]

large arrays?

do you mean TABLES?


On Apr 20, 3:23 am, Stubert <[e-mail]:

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?

[Susie DBA [MSFT]]

the clever and proper way to do this is to use Access Data Projects

they have a much better selection of datatypes

On Apr 20, 3:23 am, Stubert <[e-mail]:

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?

[Susie DBA [MSFT]]

and DAO?

don't listen to anyone talking DAO.
in fact-- SPIT on them

DAO is for retards, it hasn't been included with Office, Windows or
MDAC for the past decade


On Apr 20, 3:23 am, Stubert <[e-mail]:

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?

[Josetta]

What is Access Data Projects? I've never heard of this.

[Stubert]

On Apr 24, 12:20 am, "Susie DBA [MSFT]" <[e-mail]:

and DAO?

don't listen to anyone talking DAO.
in fact-- SPIT on them

DAO is for retards, it hasn't been included with Office, Windows or
MDAC for the past decade

On Apr 20, 3:23 am, Stubert <[e-mail]:

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?- Hide quoted text -

- Show quoted text -

Susie,
Thanks for your reply however your language is a bit strong and anyway
he's probably too far away to spit on.
When i said array's i was referring to a comma seperated list of
numbers that relate to Training Modules which i am storing in a table
which also has a job title related to this list of modules. I think
the answer has already been given that i should be using a table with
a record for every Job title / training module pair.