Re: Category splitting Access 2002

"The Facilitator" <fac682002@xxxxxxxxx> wrote in message
news:duq203t4otsmu450hr3g9gm355e2ssiocg@xxxxxxxxxx
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
pleaseNOOSpamKallal@xxxxxxx


.

Relevant Pages

  • Re: worksheet email address to outlook xpress contact
    ... First make a backup of you current Outlook Express email addresses, ... Within OE --- file, export address book, Text File (Comma Separated Values) ... You will want to add one more column to your Excel file, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Category splitting Access 2002
    ... space after the comma for gramatical purposes. ... Import tasks from Outlook into a table in Access. ... note I can't import Organizer/Requested By field in program mode due ... Seemd like a reasonably easy tasks, but as I am learning, ...
    (comp.databases.ms-access)
  • Re: Exporting OE6 Address Book
    ... In the Address Book Export Tool dialog box, click Text File (Comma ... You can save your Outlook Express Address Book in either Comma ... Separated Values or Text Files format. ... You could also try exporting the address book entries from the address book. ...
    (microsoft.public.windows.inetexplorer.ie6_outlookexpress)
  • Re: Outlook Contacts .pst file into Outlook Express
    ... "Katherine Arthurs" wrote in message ... > I have all my contacts in a Outlook .pst file but to my ... Outlook only imports comma ...
    (microsoft.public.outlook.contacts)