Category splitting Access 2002

[The Facilitator]

Well, Outlook seems to be making my life more and more challenging.
Categories are added in one field and are delimmited via comma. Have
three questions on this one...
The first is right now my query will only check the first characters
in the field to confirm whether they match the original split I did
with the other code I showed under VBA breakout (you'll notice I used
the code in the part I show here for part two and three of this
question - thanks Bill, much appreciated.)
Is there a way to do a comparison with Access without doing any fancy
programming? In other words, can we get it to scan for a comma then
look for the alphanums after that until the next comma and do a
comparison that way? If so, then we can dispense with questions two
and three.
The second is a problem I have with the current code at the bottom
here, I am getting a "Loop without Do" error. It was working fine
until I added the If i>...Else parts to each category field. How can
I avoid this error? Please read the third question before answering
this one, as it may solve the issue without being too messy.

The third is how can I form a small loop with this...can I use cat to
join a number after the word Category as in the following pseudo?

Loop i from 0 to 9
new first string = "category"+i
make init.new first string = strCat(i)
if i is greater than the limits of the array strCat, break
end loop

thanks for al the help.

O

Sub TableSplittingCategories()

Dim init As DAO.Recordset
Dim strCat() As String
Dim i As Integer
Set init = CurrentDb.OpenRecordset("DBTasks")
Do Until init.EOF = True
strCat = split(init!Categories, ",")

init.AddNew
i = 0

init!Category0 = strCat(i)
i = i + 1
If i UBound(strCat) Then
init.Update
Exit Do
Else
init!Category1 = strCat(i)
i = i + 1
If i UBound(strCat) Then
init.Update
Exit Do
Else
init!Category2 = strCat(i)
i = i + 1
If i UBound(strCat) Then
init.Update
Exit Do
Else
init!Category3 = strCat(i)
i = i + 1
If i UBound(strCat) Then
init.Update
Exit Do
Else
init.Update

init.MoveNext

Loop

End Sub

[Albert D. Kallal]

You really should consider normalizing this data, and sending those "many"
values out to another table.

The reason is that now you have

value1,value2, value3 in ONE field

You are proposing (and wring code) to move the data to some fields

eg:

cat1, cat2, cat3...

The problem is that this really hardly helps. With 3 fields, how can you do
a simple count of how many categories are used? (you can't).

how can you simply search for a user a particular category? (again, the sql
is HUGE painfull).

where cat1 = "somevlue" or cat2 = "somevalue" etc. etc. etc.

So, by moving the data out of one field without commas, you get a bit better
database but NOT BY much...

You REALLY want to send this reaping data out to a normalized table. The
advantages are many:

** you can build a report, and if there is 1 category, or 15..the report can
expand/contract for each record display. With a bunch of fixed
fields...cat1...catn, you can't do this.

Sorting and grouping don't work for cat1...catN fields. however, if you have
a related table, then you can do counts, summary values of all
categories...with your setup, reporting writing is VERY difficult.

Further, when the fields are empty, and a user has one category...you don't
have 10 other blank fields. And, even more import..what happens if one user
has 11 categories...your forms, and now all the forms, code, data entry
systems will all have to be modified to handle 11 value. With a related
table, your design is not limited to a special number of values. this is
heart and soul of data normalizing......

So, moving data to cat1, cat2..etc still means that data manipulation,
summary reports and simply working the data will be next to impossible....

Keep the above in mind....

You code to accomplish your "bad" task can be re-written as follows:

Sub TableSplittingCategories()

Dim init As DAO.Recordset
Dim strCat() As String
Dim i As Integer
dim strSql as string

strSql = "select * from DBTaks where Cataegories is not null"

Set init = CurrentDb.OpenRecordset("DBTasks")

Do Until init.EOF = True

init.Edit
strCat split(init!Categories, ",")
for i = 0 to ubound(strCat)
init("Category" & i) = strCat(i)
next i
init.update

init.movenext
loop

init.Close

End Sub

Note how we use sql to "skip" any record with a blank Catagories field.
Also, is there EVER a balnk, or exptra spaces in the data?

red,green,blue

or do you ever have

red, green, blue

If you have extra spaces...you need to trim them out..or you making a even
larger mess.....

To trim, use:

init("Category" & i) = trim(strCat(i))

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

[Don Leverton]

"The Facilitator" <fa*******@yahoo.comwrote in message
news:9g********************************@4ax.com...

The second is a problem I have with the current code at the bottom
here, I am getting a "Loop without Do" error. It was working fine
until I added the If i>...Else parts to each category field. How can
I avoid this error?

To answer this question, it looks like you are missing ALL of your "End If"
statements

Nesting "If-Else-End If" 's within a loop get a little confusing sometimes.
If I could make the suggestion that you try indenting your code ...
errors like that are a little easier to find.

Do (Until / While)

If (something) Then
(plan A)....
Else
(plan B).....
End If

Loop

>
Sub TableSplittingCategories()

Dim init As DAO.Recordset
Dim strCat() As String
Dim i As Integer
Set init = CurrentDb.OpenRecordset("DBTasks")
Do Until init.EOF = True
strCat = split(init!Categories, ",")

init.AddNew
i = 0

init!Category0 = strCat(i)
i = i + 1
If i UBound(strCat) Then
init.Update
Exit Do
Else
init!Category1 = strCat(i)
i = i + 1

--- End If

If i UBound(strCat) Then
init.Update
Exit Do
Else
init!Category2 = strCat(i)
i = i + 1

--- End If

If i UBound(strCat) Then
init.Update
Exit Do
Else
init!Category3 = strCat(i)
i = i + 1

--- End If

If i UBound(strCat) Then
init.Update
Exit Do
Else
init.Update

--- End If

>
init.MoveNext

Loop

End Sub

[The Facilitator]

Ok, so then I should restructure. The problem is that the data is
coming in from Outlook in one field, comma separated and always has a
space after the comma for gramatical purposes.

I have already set up the categories in another table (although will
use the trim option to remove spaces, thanks for that) so that I can
pick from them to do a search by category.

This is the whole process:

1. Import tasks from Outlook into a table in Access. Can't use link
as I am missing a crucial field, namely the subject. On a related
note I can't import Organizer/Requested By field in program mode due
to it being 2002. May need that, but importing the table using the
Access import tool and then programming in the missing fields doesn't
seem to work due to subjects being mis-matched with the tasks they're
assigned to.
2. Confirm the creation of any new categories and place them in a new
table.
3. Be able to sort by time, category, due date, date completed, etc
and get reports of the same.

Oh yeah, one last qualifier, it has to be COMPLETELY automated as the
people who are going to be using this are not technically proficient.
I figured it was a given, but just to be sure, I am mentioning it.

Seemd like a reasonably easy tasks, but as I am learning (yet again),
it is NEVER as easy as expected.

Any help in any of these areas would be much appreciated.

O
On Wed, 21 Mar 2007 03:41:03 GMT, "Albert D. Kallal"
<Pl*******************@msn.comwrote:

>You really should consider normalizing this data, and sending those "many"
values out to another table.

The reason is that now you have

value1,value2, value3 in ONE field

You are proposing (and wring code) to move the data to some fields

eg:

cat1, cat2, cat3...

The problem is that this really hardly helps. With 3 fields, how can you do
a simple count of how many categories are used? (you can't).

how can you simply search for a user a particular category? (again, the sql
is HUGE painfull).

where cat1 = "somevlue" or cat2 = "somevalue" etc. etc. etc.

So, by moving the data out of one field without commas, you get a bit better
database but NOT BY much...

You REALLY want to send this reaping data out to a normalized table. The
advantages are many:

[Albert D. Kallal]

"The Facilitator" <fa*******@yahoo.comwrote in message
news:du********************************@4ax.com...

Ok, so then I should restructure. The problem is that the data is
coming in from Outlook in one field, comma separated and always has a
space after the comma for gramatical purposes.

Sounds good to me. I guess since we writing code to "parse out" the data
that is separated by "," (comma), then we might as well go all the way. The
change to the sample code is not a whole lot of work to write out to a
related table.

In place of sending category to fields cat1, cat2 etc etc etc, we simply
send that
data out to a related table. The code is not hard at all. (and, the
resulting flexibly
in terms of counting, or generating reports "grouped" by a particular
category becomes
far easer (we can't group by fields cat1, cat2...cat"N"....it too hard).

I have already set up the categories in another table (although will
use the trim option to remove spaces, thanks for that) so that I can
pick from them to do a search by category.

Great...again the above just shows how we want to avoid having cat1, cat2
etc for
our field names....too difficult to work with..and the above "goal" of
filtering by
these categories just shows we now *really* want to avoid those 9 or 10
fields.

This is the whole process:

1. Import tasks from Outlook into a table in Access.

I assume your using automaton to accomplish this, as then you can grab/use
all of the fields from outlook.

2. Confirm the creation of any new categories and place them in a new
table.

Well, perhaps you don't confirm...but just add the new categories.
Regardless, good idea
to check, or perhaps restrict the categories if that is needed.

3. Be able to sort by time, category, due date, date completed, etc
and get reports of the same.

Yes...that is *exactly* why we need to normalize this. So, not only do we
have a category, but now we have additional things like time, due data etc.
(did you place to have 8, or 10 due date fields also? and then 8 or 10 date
completed fields? Golly, we now have 4, or 5 sets of 10 repeating
fields..that 50 fields!!! (next to impossible to write reports for that)..

id Due Date Date compilted etc...

--related table "CatsForTask"
id task_id Category
(task_id would relate back to "id" in the task table)

That way, each task can have multiple categories......

Data normalizing says we don't need, nor want to have repeating data. The
beauty of normalize would mean that if you have 10 categories for a given
task, and you change the due date, then all 10 categories attached to this
task would NOT need to be updated.

In our case, it actualy less code writing to normaling out the catagories to
the current reocrd..so, that what I would do....

Sub TableSplittingCategories()

Dim init As DAO.Recordset
Dim strCat() As String
Dim i As Integer
dim strSql as string
dim rstCats as dao.RecordSet

strSql = "select * from DBTaks where Cataegories is not null"

Set init = CurrentDb.OpenRecordset("DBTasks")
set rstCats = currentdb.OpenrecordSet("CatsForTasks")

Do Until init.EOF = True

init.Edit
strCat split(init!Categories, ",")
for i = 0 to ubound(strCat)
rstCats.Addnew
rstCats!task_id = init!id ' set relatonal key
rstCats!Catagory = trim(strCat(i))
rstCats.Update
next i
init.movenext
loop

init.Close
rstCats.close

End Sub
So, you can see how the above simply writes out the cats (categories) to a
related table.....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com