redesign of Access Tables

Not quet sure what you want to do there. Is it the tables that's feed the form from your previous post that needs changing or some new tables

From 'this' post, the tables in the database feed the form.  That is correct.  It's the tables that need to be redesigned and relinked beginning with the database version g found at the link above.?

The problem with redesigning the tables is that you have already spend a lot of time on the form and the query. This is like working back to front. Making changing to the tables will most definitely create error in the codes.

I guess you can get around this by first running an object dependancies check. Then you will have to make sure that you don't delete or rename fields from the tables and queries that your forms depend on.

Then you will have to determine what information you want to store in the database other than the ones already there and start designing the tables for them.

My guess is that you are correct.  It will create ALOT of inconsistency.  My hope was that I could, with the help of a fancy object renaming tool, and the help of a more experienced person, resolve the dependencies.  It would likely require more than is available on EE.  Real-time chat and desktop sharing that is not allowed.

I will not be able to help you with object renaming tool. There are a lot of guys with much much more experience than me on EE and they may be able to help you with that.

What I can do it:

• help you identify dependancies
• help you with the layout and relationship for your new tables
• new queries and forms
  

Unfortunately, I can't offer the holding your hand format you are suggesting but I think you are knowledgable enough to tackle this via the normal EE format. Don't underestimate yourself

I think you hit on it exactly.  "help you with the layout and relationship for your new tables".

This is key!  Without this first step, I have nothing to resolve.

If I can come up with what is needed in the tables, then I can point all the dependencies to it.  I know how to use the rename tool, have done it many times and it works like a champ.  If I had someone who can help me figure out what to rename, then I think I could accomplish it.

I didn't see any info in your profile.

I have been using access for about 5 years. Just started to use vba about a year ago. I should have started earlier.

Regarding your dependancies, it is not hard to figure them out. Just right click on the form in the database window and from the dialog box click object dependancies. This should give you all the objects that the form depands on and the ones that depands on it.

I think you hit on it exactly.  "help you with the layout and relationship for your new tables".

This is key!  Without this first step, I have nothing to resolve.

I normally start with a wise list of what I want the database to do. This is a list that you'd create in a word document.

Then figure out what informtion the database will need to do what you want it to do.

Only then can you start putting fields in tables.

the list is at www.dropio.com/tasksdb

based on the three items listed, i need some assistance with the best design for the tables.

open/close dtstamp, activate/deactivate dtstamp, task detail, named detail

specifications now more clearly outlined www.dropio.com/tasksdb

I think I'll pass on this one. I hope you get someone that is prepared to the desktop sharing exercise.

If you don't then my advice is that you start from fresh and forget about the form. Well you do need to remember the function that you will like it to perform at the end.

You do not need to store information about lenght of each active. This can be calculated. From what I have read I believe you need the following tables

tblTask with the following fields

fldTaskID   fldTask  fldTaskNotes

tblActivity with the following fields

fldActivityID   fldTaskID   fldStart   fldStop   fldActivityNotes

tblDailyNotes

fldDailyNotesID  fldDate   fldDailyNotes

This should be enough to product all the information you need.

SB9...  I wrote up your recommendations.  Any notes you can put in for the why and how are greatly appreciated www.dropio.com/tasksdb.

pictures of what sb9's recommendation.  Please make any further comments if you can to help me understand the reasoning behind your proposed picture.  www.dropio.com/tasksdb picture

picture and spread both there...   just picture here

you will also need

fldComplete  fldActive in tblActivity

set the datatype as yes/no and format them as on/off

Information on Why.

I take it that at the end of the day what you want the database to tell you is:
How long did it take to complete a task?
How long has a task been ongoing for?
What task are active on a particular day?
When was a task done?

If you stop thinking database for a moment and go back to once upon a time long long ago when we still used pen, and diary or notebook to keep this type of information. All you would record is the time started and time stopped (time include dates). Everything else can be worked out from these observation. In the same was your database table should only record "observation"  (baseline information) and let the query deal with the deductions.

We also need to be on the same wavelength as regards definition. In the structure that I recommended:

Task is the Name of a particular action
Activity is just an event that consist of a particular task.

This means that each time someone starts a particular activity you have to stipulate which task is being conducted during this activity.

I follow you where you say "everything else can be worked out from these observations".  I couldn't agree more with 'some' of where the current redundancy occurs.  However, I am not sure that is true with activate/deactivate which is where a better design might come in.

That is what makes this particularly confusing because in this case, I need to track the dtstamp for every time (up to multiple times daily) that a particular task was opened or closed AND every time (up to multiple times daily) that a particular task became active or inactive.  I think you are telling me that the later can be "worked out" but I am not clear on how that happens.

1. Are you saying that the later can be worked out (y/n)?
2. Does that mean only two dtstamps (instead of four) need to be in the tables?

If that's true, that may make keeping "accumulated time" for activate/deactivate, difficult, and particularly difficult still would be the addition and subtraction of those times as is currently done with frmAddTask.

Yes and yes.

The Activity table (tblActivity) I am proposing keeps a history of all the activities. What task was performed, when did it start and when did it stop.

I think there is a need to step back from the database and look at the real life events that will be observed and then entered in the database.

If I've got it right, you will have a group of individual performing various task at various interval said:

8:00 check email
8:30 go to a meeting
9:30 compile xyz project report
10:45 attend to a customer
11:00 write and send urgent fax  
11:15 compile xyz project report
12:00 Lunch break
13:00 check email
13:30 compile xyz project report
14:30 go to a meeting

ect.......

Had you recorded all that in a diary, and you wanted to know how much time you spend on a task, you would work out the time spend for each instant that task was performed and add them together, right. Or I'm I being too simplistic

What is the purpose of the addition substraction. What are you adding and substracting.

Is there a diferrence between "open and close" and "active and inactive"

I think so yes...  this is perfect, so you could still have a timer and add/subtract time it seems.  This is great news!  

Ok so to better understand, you would have support for the following:

events:
8:00 check email (opened, which has the effect of making it active)
8:30 go to a meeting (activated, which has the effect of first deactivating check mail) .. and so on
9:30 compile xyz project report
10:45 attend to a customer
11:00 write and send urgent fax  
11:15 compile xyz project report
12:00 Lunch break
13:00 check email
13:30 compile xyz project report (activated again, which has the effect of deactivating check mail)
13:35 compile xyz project report (closed, which has the effect of first deactivating)
14:30 go to a meeting

You would also have the support for add/subtract active time, correct?

Is there a difference between "open and close" and "active and inactive"?


Oh yes!

here is a revised purpose and scenario:

purpose:  
time - track each time a task is opened/closed (up to several times daily)  
time - track each time a task is  activated/eactivated (up to several times daily)  
time - show a timer on the entry form  (frmAddTask) for new tasks  
time - add/subtract time of activity from a currently active task.    
     This would be used in order to correct  a 'timer  accidentally left running' senario..  
detail - add detail as desired, and unrelated to time, for any day  
detail - add detail as desired, and unrelated to time, for any task  
detail - add detail as desired, and unrelated to time, for any named item  
report - report data based on selected date range  
important - there is a distinct difference between open/close and activate/deactivate  
   
senario  

• 8am enter 'get coffee' enter ' which has the effect of 'open' and 'activate'  
• 9am enter 'compile project A' which has the effect of deactivating 'get coffee', opening 'compile project A' and activating 'compile project A'  
• 11am enter 'go to lunch'  which has the effect of deactivating 'compile project A' and activating 'go to lunch'  
• 1pm adjust time of 'go to lunch back 30 minutes which rolls back the active counter for lack of a better term  
• 1pm enter 'compile project A' which prompts the question "would you like to rollback to x min ago?"  
• The effect is to first deactivate 'go to lunch, perform rollback based on answer, and open 'compile project A''.  
• 5pm click done for project A which first deactivates it, and then closes it.  
• Click report (choose daily , weekly, or select date range).  

"You would also have the support for add/subtract active time, correct?"

At this stage I have not figured out what exactly is this function. Why do you need and add/substract button.

Please define the difference between "open and close" and "active and inactive" with respect to the task and the activity

Is open and close the same as start/completed?

In this case you will need the following fields

fldTaskID (primary)   fldStart (or open)   fldComplete (or close) in a new table tblActiveTask

fldTaskID will lookup to fldTaskID in tblTask

and for a relationship with fldTaskID (foriegn Key) in tblActivity

That's a one-many relationship. One task to many activities where each activate/deactivate marks the start and end of an activity.

The timer will be used to "feed" the active/deactivate and start/complete fields

There is no start/complete.  I am calling them open/close AND activate/deactivate for the purpose of the table.  The reason for this is that when thinking of the tasks and the design, I don't think of them as completed in that sense.  I may have a task named get coffee one day and 'different' task with a different ID named get coffee on another day.  So for the purpose of the table (not necessarily the check boxes on the form), I need simply open/close, activate/deactivate.  Here is a slightly more complicated scenario:

 
scenario:  

• 8am enter 'get coffee' which has the effect of both open and activate  
• 9am enter 'attend staff mtg' which has the effect of deactivating 'get coffee', opening 'attend staff mtg' and activating 'attend staff mtg'  
• 11am enter 'go to lunch'  which has the effect of deactivating 'attend staff mtg', and activating 'go to lunch'  
• 1pm adjust time of 'go to lunch back 30 minutes which rolls back the active counter (for lack of a better term) back 30 minutes  
• 1pm enter 'resume staff mtg' which prompts the question "Would you like to roll back to 'go to lunch' which adjusted to 30 minutes ago ending 12:30?"  The effect is to first deactivate 'go to lunch' based on the newly adjusted time', open the new 'resume staff mtg' task based on the newly adjusted time of 12:30, and activate the new 'resume staff mtg' task as of 12:30.  
• 3pm click done for for get coffee  which closes the already inactive 'get coffee' task  
• 3pm click done for 'attend staff meeting' which closes the already inactive 'attend staff mtg' task  
• 3pm click done for 'resume staff meeting' which deactivates, and closes the 'resume staff mtg' task  
• 4pm enter develop daily report which opens and activates a task named 'develop daily report'  
• 5pm click report frmAddTask, and choose daily , weekly, or select date range, and; choose named detail item(s) to include in the report as needed  
• 6pm click 'get coffee' which deactivates the task named 'develop daily report', reopens 'get coffee', and reactivates 'get coffee'.  
• 6pm click done for 'get coffee' and done for 'develop daily report' which closes 'the already deactivated 'develop daily report', and deactivates and closes the task get coffee 'get coffee'.
• Any task can be re-opened and/or re-activated and vice versa provided more than 12 hours have not past.  
  
• After 12 hours, they will disappear but only as far as the frmAdd tasks is concerned.  In the tables there may be no effect here.
  

"3pm click done for for get coffee  which closes the already inactive 'get coffee' task" whao! that's a 7hr coffee break since you opened at 8am.

Please consider if you really want to mix projects with recurrent activities.

The reason I say that is that you may want to know how many days it took to complete project A. This is diferrent from how long it took to complete project A. The first is [Close] - [Open] (I still beleive that Start/Complete are better terms but it's your project) while the latter is Sum([DeActivate] - [Activate]).

I don't think you really want to know how many days you had coffee break, specially the 8hr coffee breaks

AS you can see we are spending a lot of time just analysing the actual project itself. Understanding the project and having a clear insight into what information you want at the end of the day is extremely important and needs to be done long before you draw the first table.

I see that now....  It's vital!

I hate that I dove in without a clearer picture.  Thank you SO MUCH for for helping with the table design.

I don't think you really want to know how many days you had coffee break, specially the 8hr coffee breaks

I might actually want to calculate how many days had tasks with the word coffee but that would be way down the road and the table provides support for it.  I would not ever want to know the distance between open/close as they are 'all' opened and closed within any given 24 hr period.  The idea is not to be able to come back on day two and re-open a task from day one but to start a new one with the same or similar name.

Now I might want to know how long "each coffee" break lasted for a day.  For instance activate at 8:15 and deactivate at 8:20, then activate at 4:20 and deactivate at 4:27 would produce a 12 minute total for that task during the day.  Open/Close is really useless data in that scenario but 'may be?' needed to keep the frmAddTasks (open on top, closed on bottom).

Is there a better way?

I notice in your tblTrackDT you have open,close,activate,deactivate in the same table. Noting that a task will become active and inactive many times during its lifespan this setup will end up being problematic. The question you have to ask is what belongs to what.

Q1) what is being opened?
A) A task
Therefore open belongs to task

Q2) Will a task be opened only once?
If yes then Task and Open can be in one table
Else
They have to be in separate tables because they have a one-to-many relationship

You have to do this with all your "observables". Find what belongs to what and how they relate. Then build your table. Chances are you'll still find the need for some adjustments bu they will be manageable

 

OK excellent, so yes they will be opened more than once for both.

Based on that, can you draw out what you see so far?

tbl1
tbl2
tbl3: tsk, tsk, tsk; short_description of each

I really appreciate all your help.  You are fantastic to have spent so much time.

"Open/Close is really useless data in that scenario". You have hit the nail on the head. Unless you are referring to a finite project that spans of a few days/months and have a definite completion, I don't see where the open/close will be useful.

You can alway do a query to find when you first activated a task on a particular day, month,week or year. And the same for last deactivation which is your close.

Try to think of a scenario where open/close will be useful. If you can't think of any get rid of it

wow!   so there is a done button on the form so 'close' would only be necessary to move the task to the bottom of the form which 'I guess' doesn't need any effect in the table other than to deactivate and the 'move' is solely a function of the form.

If I am thinking about this correctly, with only activate/deactivate, can you lay out your current recommendation?

tbl: tsk, tsk, tsk; short_description of each
tbl: tsk...

tsk=fld

fld

I guess I am getting a bit over the top with my naming convention. I did not use to use them when I first started DB about 5yrs ago but since the beginning of this year I have started to learn VBA and found out how important it is to have a good naming convention.

There are the tables that I think you need

ltbTask with the following fields (This will be a lookup table for tblTask) NB: ltb = lookup table just to make them easier to spot when you have a DB with many tables. Also you can easily deduct that it contain the loookup for the task field.)

fldLookUpTaskID (AutoNumber, primary key)   fldLookUpTask (Text)  fldTaskNotes (Text, 255)

tblTask

fldTask (lookup, ltbLookUpTask.fldTaskID), fldTaskID (AutoNumber, primary key)  

tblActivity with the following fields

fldActivityID (AutoNumber, primary key)   fldTaskID (foriegn key)   fldStart   fldStop fldActivateDeactivate (yes/no format)   fldActivityNotes (text, 255)

tblDailyNotes

fldDailyNotesID  fldDate   fldDailyNotes

then create your relationship and post a copy of the DB

you will notice that I have not provide for the open/close field if you still insist thay they are necessary, the best place for them will be in tblTask

You are a godsend!  I think your right.  Much to my amazement, open/close isn't needed at all (only act/deact) I'll leave till tomorrow and think on it, post a final design for a final nod from you if you don't mind?  I'll accept following your nod, and  I'll have a tutoring session via collaborative space on Friday from from a coder found on rentacoder.com after which I hope, that I will have a much greater understanding of how to design a database worthy of end-userness !  

Thanks again.

Yes a coder will be handy. I am just a beginner at coding and there is a lot to learn.

I believe that you should be able to modify the two query in your original database so that they can work with the new table. And if you keep the name of the fields in the two query intact they should work with the form. But the coder might have diferrent ideas. Good luck

how much is the coder costing you

coder is from crossloop but contacted me outside the system (his request) and only wants $20 per hour.  I'm up for that!

Here is my current understanding of your redesign.  I'm confused about the lookup tables?

table.jpg

The task fldTask in tblTask looks up to fldTaskID in ltbTask

This is just to make sure that there is no typo in the fldtask in tbltask which will then generate false results in your query

when you create the fldTask in tblTask set the data type to lookup. then use the wizard to select ltbTask as your lookup table

Make sure limit to list in the lookup tab of the field property is set to yes

I'm still confused.  The way it looks to me in the screenshot, entering new tasks in frmAddTask would force a lookup?  I want the user of the database to simply type "get coffee" where that does not require a lookup.  Is this still possible?

lookup confusion

lookup.jpg

lookup confusion... updated screenshot

lookup.jpg

You can allow the use to type get coffee an it will create a new task in the lookup if it does not exist. It is good practice to have a message box that ask the user if the really want to create a new task. The reason for this is to avoid "get coffee" and "got coffee" becoming two different tasks. Your database needs to be user friend but you still need to exert some control over what the user does and have checks and balances to limit junk data entry.

Remember junk information entry = junk information output.

This site will give you an idea of what I am getting at

http://www.databasedev.co.uk/multi-field-not-in-list.html

I have also attached the file from the website that you can use to see how it can work





 
ActorsAndMovies.zip

Comments on your table properties

line 9: yes you are correct

line 12: yes it will lookup to ltbTask. Just select lookup as the data type and follow the prompt. It is as easy as ABC.

I note that the field on line 12 will also form a one-many relationship with tblTask.fldTask

line 19: using ID in table is just good practice but in this case I think you are right. Just make the date a primary key.

Note another advantage of lookup table is the ability to change the name of a task so that the change occur in the whole database.

For example:

tblTask.fldTask only stores the number found in ltbTask.fldLookUpTaskID. What the user sees is the text from ltbTask.fldLookUpTask

If one day you want to start calling "get coffee" as "coffee break" all you have to to is edit "get coffee" in ltbTask.fldLookUpTask and all the "get coffee" in tblTask will automatically change.

If you typed...

get coffee
then
coffee with Joe

I do not want to have to pre-name each task or confirm anything.  The idea is not to reuse a task for longer than a day though if a similar task were typed "during" that day only, I wouldn't mind the checks and balances.

With your table, would "all tasks" regardless of name prompt for some kind of verification?

I think you are ready to start creating the tables. Do that in a new database. The queries and forms can be imported later

You can live out the prompt and varifications. You can also operate on a concept of not reusing the same task everyday. However, this limits the usability of your database. From experience, once you make a database, you keep coming up of new things you want it to do. The set-up I am proposing will allow you to meet these requests. There is absolutely no benefit in creating a database that can't do this and can't do that.

You can easily have a code that will deactivate all the activities at say 5pm. Your form could be defaulted so that it always open on today. In otherwords you can show what the user what they want to see without limiting the expansion capacity of your database. Design your database so that it meets the needs of today without impeding the needs of tommorrow.

"With your table, would "all tasks" regardless of name prompt for some kind of verification?" You can use a code that searches through the new entry for some words such as coffee, fun or whatever and omit the prompt.

Regrading "Coffee with Joe". The reason you have a comment field is so that you can put these additional information. Most of the time all you'd want to know is when or for how long someone had coffee. Who they had coffee with is an additional infomation that should be in the comment box.

ALWAYS REMEMBER that as a designer ou have to maintain control on your database. A well designed database is one that:

1) can be used by anyone capable of clicking file>open>OK;

2) maintain its integrity to that it can always generate useful and reliable information; and

3) have the capability to expand with more queries and forms so that it can provide information that you are not thinking of asking today.  

I'm working on the new DB based on your design as suggested.  What I don't want is a prompt on "every task" regardless?

attached is the newest screenshot with some questions.


table-design.jpg

send a copy of the excel file(in 2003 format) you have in the screen shot and I will make some comments on that. Just a bit more convenient

I need to setup a log so that I understand what changed since my last look at it.

I can use a different color for my comments

very good...  here it is

I think I made some mistakes but it's a first attempt.

database-v4.1a.mdb

What do you think about this one?

Very good.

I have put some comments in the the design view of the tables. Have a look and modify as required.

Then start setting the relationships between the tables. Read a bit on cascade delete and cascade update.  

I have also attached a handout I used for trainning collegues at work
database-v4.1a.mdb
Access-training-handout-2.pdf

Another handout. I stopped trainning after that one due to workload
Access-training-handout-3.doc

excellent.  Thank you!

Is there a way to keep a log of changes to the structure?  How do I set the foreign key for fldTaskID?  Would it be possible to add a note field for as a 'named' note.  I might like to keep track of the following for instance.

named detail - Named note called "special report x" named on the fly.  I meant to state this and didn't.

task type - Additionally, it occurred to me that I might also like to define types for tasks with the primary tasks being "general" or something.  Other tasks might include those that fall in category I will call WIFP.  This answers author Tim Ferris's question, "If I only got one or two things done today, would I feel productive", to be filled in at the end of each day for reference for the next day.  I could add other categories for special types of tasks later.  What do you think?

Foreign key is just a term used for the many side of the 1-to-many relationship. It will all come together when you set the rlationship

You may be able to keep a log of the changes. But I don't know who to. Try asking on EE, someone might know.

Ahn Hahn!! you are already starting to think of more things you want the database to do. That's why your design needs to be versatile.

Yes you can  add a note field for as a 'named' note or whatever else you want to add. The question is when to add them. I believe Handout 3 provides some useful guidelines for making this decision. You have to decide if the new fields should be in the exiting tables and if so which one. Otherwise they may need to be in another table and if so how are you going to link them to the relevant records in other tables in your database.

I guess I have not answered your question directly and there are 2 reasons for that:

1) I don't fully understand where you are heading with named detail. ie what does the named detail belongs to and what belongs to it. What is the nature of their relationship with other field 1-many or 1-1 or many-1 or many to many

2) It is a good thinking exercise for you. Telling you how to do everything will not help you as much as telling you what the reasonings are and letting you try to figure it out

Actually, due to time constraints I have to 'pick my battles' on what to think through at this time.  Simply 'having' the table structure needed to support the overall design is what I am after here and on second though, I think the 'named' detail thing can be forgotten at least for now.

I've heard this complaint from my co-workers.  "Often, I need to choose, where to spend my energy on a given day based on extraneous data that changes often, and sometimes I have difficulty just getting started".  What I think would help is to have space for certain types of "pick me" items without the component of tracking activate/deactivate, and entirely unrelated to tasks or day/task detail.  That is to say that picking one would simply be a matter of manually entering it or a portion of it as a task when ready.  No direct relationship between those items and individual tasks would be created.  An item could be one of my "pick me" items and never get picked from way down at the bottom of the list.  The "pick me" items could simply be re-ordered up/down in the list as needed.

example of some "pick me" items

get versed on xyz project
deal with bank mgr x
do training on x
find a golf cart repair facility
consider EE as a formal recommendation

example of new task using the first "pick me" item.
enter "spoke with John about xyz preliminaries"
talked for 15 minutes
click done

sb9,

Thank you!  very much for your instruction and taking the time to help me with the table design.  I can't tell you how valuable your input has been.

This tables sectioned out below do appear to keep track of three (3) types of notes?
I am not sure I understand the activity table if this is the case?  The activity table does appear to have support for both stop/start times of each activate/deactivate AND support for a 'named' detail you are also calling an 'activity'.  Would this be correct?

tblTask - contains original task data
fldTaskID
fldTask

ltbLookUpTask - ensures integrity of tblTask
fldLookUpTask
fldTaskNotes

tblActivity - provides two (2) functions ('named' activity note and activate/deactivate of tasks)
fldActivityID
fldTaskID
fldStart
fldStop
fldActivateDeactivate
fldActivityNotes

tblDailyNotes - note for each day
fldDate
fldDailyNotes

Here is my latest upload.  If my current understanding of the dual purpose of tblActivity is correct, I am not sure why there is no fldActivity field (only fldActivityID).

tblActivity - provides two (2) functions ('named' activity note and activate/deactivate of tasks)
fldActivityID
fldTaskID
fldStart
fldStop
fldActivateDeactivate
fldActivityNotes

I have also added my tblOpportunity which I need to be able to 'set' a limit for.  Say 10 records.

question:  

1. Is my description of tblActivity correct?
2. Is it possible to limit the number of records in tblOpportunity.

If these answers are yes, then I think my attached table structure (complete with descriptions) is ready to roll.  Please open and verify if possible.

database-with-new-table-v4.1c.mdb

1. Is my description of tblActivity correct?

Yes, but at this stage I am starting to wonder how the tblDailyNotes is going to fit into the database. I think that reinserting the flddateID in tblDailyNotes and inserting a new field fldDateID in tblActivity is required

2. Is it possible to limit the number of records in tblOpportunity.

I have never do that bet I believe you can. On possible way it to use a code at form level that counts the number of records in the table and lock the field that is used to input data to that table if the count is more than 10. There may be a way to do that at table level which I don't know yet. I have only been learning codes for a year as oppose to 5yrs of database design (tables,formsquery & macros which I don't use anymore)

Ready to Roll?? NO NO NO

You still have to do the following

1) Fix the lookup fields as I described in  comment 22801718
2) Set your relationships
3) tblOpportunity seems to be a stand alone table I don't see where it fits in the database. Remember you are creating a relational database, all these tables has to somehow fit together.

Your pick me items looks a lot like notes. Why not just throw them into the daily notes?

What you need to do now is fix the lookup fields as per comment 22801718 and set the relationship. Then we can start moving forward

The opportunity table is designed to be separate.  It is not designed to have any relationship at all to the tasks.  It is not designed to be part of the report.  It is designed only a "reference", when I get task block (for lack of better terminology) and wonder what to work at a particular time.  Let's say a person has two (2) hours till the next meeting and is in limbo on a project.  They can use that list to figure out what to do next.

to fix the lookup fields you said were in your comment and create the relationships, i need a step-by-step on that or some help if you don't mind.  I'm at a stand-still on my last update.

" Let's say a person has two (2) hours till the next meeting and is in limbo on a project.  They can use that list to figure out what to do next."

That looks a lot like a task in some sort of low priority todo list.

 "The opportunity table is designed to be separate.  It is not designed to have any relationship"

Bad idea! Everything in the database has to relate to something else. There is no place for a stand alone table in a database.

My proposal is that you adopt the task category approach you mentioned earlier.

 This means that you will have a table called ltbTaskCategory with fields ltbLookUpTask  fldTaskCategory

You will also need to add another field in ltbLookUpTask called ltbLookUpTask

The opportunity will be one category and you will have some task that falls under this category.

"to fix the lookup fields you said were in your comment and create the relationships. I'm at a stand-still on my last update."

1) Do you know how to create a query from design view
2) There is a step-by-step instruction on how to create queries and relationship on handout 2 (see comment  22801718)

3) If you are still stuck after reading the handouts, let me know where exactly are you getting stuck  

I appreciate that but am about out of time.  I need to pay someone to "take the wheel" while I watch and make notes.  EE doesn't provide for that.

I believe some of the experts on EE such as LSMConsulting are database professionals. Have a look at their profile.

On the other hand are you charging a client for this database or your's just doing it because you know more about access than anyone at you workplace and thought that this would be a good idea.

If the latter is true then my advise would be to keep it simple. These tables are almost there (about a week's work). Obviously you may have to forego some of the bells and whistles but as I've said a database is never completed. It's always a work in progress.

 

>>a database is never completed. It's always a work in progress.

Well, yes and no. Your database should get to the point of functioning, and then any changes can be applied after that. You (or your client) need to have *something* that works.

btw: The reason for the "seemingly unnecessary" id fields in your tables for your PK: a PK should *NEVER* have any meaning outside its stated purpose, i.e., to uniquely identify a single row in a table.

Hi Badotz

You are 100% right about the the PK.  In fact the more I think about it the more I am convince that a PK in tblDailyNote will be required to create a relationship with the tblActivity.

I have never tried to use dates as PK and FK and I don't believe that it will work out very well in this case since in one we have dates and the otherone date and time.

>>I have never tried to use dates as PK and FK

Nor should you. Aside from the mismatch between formats, what if the date IS WRONG? In order to correct it, you have to change EVERY ROW in EVERY TABLE that references the WRONG DATE.

VBI.





* Very Bad Idea.

Badotz

Thanks for that. I've been teach verpit some good habits noting that as a self tough database developer (or sort of) I have been burn many times before with ill designed DB. I started of without using PK and naming conventions. That seemed ok, until you start asking more out of your database. Then I found out why the techno heads came up with these fancy names without space and PKs for every tables.

Cheers Mate


 

"Techno heads"?

"Fancy names"?

BUWAH-ha-ha-ha (evil laugh)

Scott Adams doesn't make up the Dilbert scenarios - he lived through them ;-)

Ok I may need to be educated there.
"Scott Adam"
"Dilbert scenario"

I did a quick googling  and came up with a few stuff indicating that Scott wrote a few books that he called the Dilbert Principle.

Can't quiet figure what you meant by your previous reference though

Dilbert is a comic strip about the idiocy of the Corporate Mentality in a technological company.

Your comment about "techno heads" and "fancy names" made me think of Dilbert and his world.

ok got it

You've been on EE for much longer than me, have you ever seen a thread as long as this one

Yes, but at EE, size does not matter.

There I was thinking that we were going for a new category of record

SB9....  Thanks again, what do you think here?

"Your pick me items looks a lot like notes. Why not just throw them into the daily notes?"
 I removed the tblOpportunity (which was basically a type of todo list.  I think I have corrected this with task categories.
 
 I don't think I quite completely understand the tables just yet but am getting there.  This will be necessary before moving on to relationships.  Please check out the current design with tblOpportunity removed if you can.  I think the best way to actually see this initially along with my other questions, is in the tables_and_fields.rtf doc found at www.dropio.com/tasksdb along with the latest mdb of the tables (version d).  It's got everything in a concise list with descriptions that is easy to read.  I need help with my understanding of the table descriptons (in black) and the questions and other items (in red)
 
 
 Of particular mention, are some duplicate fields which will make renaming them using RickFisher's renaming tool much more difficult (assuming it were ever necessary following this superb design!).  For this reason, I would like to have distinctly different field names even if the fields reside in different tables.
 
 Once I understand the table and get that right, I can move on to relationships.  Thank you again for any comments you can make about the notes in tables_and_fields.rtf and the current database found there.

I have added a few comments in the design view of the tables and answer you questions on the word doc.

"This will be necessary before moving on to relationships.  "

Each table hold a little bit of information and these information are useless on their own. Its only when the tables are join together by relationships that they all make sense.

Wrong! you need to move on to relationship to understand tables. Its a bit like the alphabete. I could never figure out why I needed to know the order of the alphabet (you don't need to know the order to spell right) unless I started to use a dictionary. In the same way everything will start to make sense once you set the relationship.

I have to go to work today and I will not be posting under later this evening (in about 9hrs).

What you need to do at this stage is start setting the relation and don't worry about the stuffs that are not quite clear. You need to move through the process to understand it. It will be impossible to understand what a house looks like if you stay in only one room.

So in short, you need to move on now. Start working on the relationship. Don't worry about the renaming tool, I don't see any need for it in such a small database. At this stage don't even think about the forms and query. You have create the tables and now you have to create the relationship.

Start working on the relationship




tables-and-fields.doc
db-with-new-table-design-v2-4-1d.mdb

understood about moving on...  I agree.  Except for the fields with duplicate names.  I don't like duplicate names for the fields.  Bad Idea..  I have not seen your notes here but my hope is that you made some about that because until I understand at least why a field MUST be a duplicate (and there are only 2 with that problem, then I have to figure that out first).

If anyone else wants to chime in here and help me finalize my descriptions of each table in the tables-and-fields.rtf file found at www.dropio.com/tasksdb , I would greatly appreciate it.

Duplicating the names across tables will make your life a hell of a lot easier when you start to write codes and create relationship. They are also helpful in creating queries. Believe me I used to use differrent name and experience has proven that this cause major headache and confusion later.

Trust me at this stage it's all good. Just move on and it will all become clear. Otherwise you are just going to stress yourself out for no good reason.

btw: you should keep duplicate data in your database to a minimum. Duplicate field names indicate duplicate data, is why I mention it.

Except, of course, PK names. I always use "id". FK are labeled "customer_id", "invoice_id", etc. That way I always know, when I examine a table, where the PK and FK(s) are.

sb9 and badotz...  Thanks for chiming in on the duplicate thing.  I just posted a new rtf file.  Please keep in rtf if possible.  Can you both take a look a the duplicates in v2a of the doc?  I tried to note them in red.  

Thanks again and hopefully, I can begin reading and creating the relationships soon.  You are both a tremendous help!

www.dropio.com/tasksdb

Have I got as much support as possible for future growth given the extent of it's currently stated purpose, in the design of the tables?  The reason I ask is because in the original database also posted at www.dropio.com/tasksdb, tblAllTasksEachActivity appears to be some kind of sub_field with +/- to the left of each row.

Please pardon me for not adding this into your RTF file, but I thought it would be too confusing, since I've made a few changes. Oh, and pardon me for making those changes ;-)

First off, that bit about referential integrity and PK fields is wrong. You link the tables by PK to FK; there is no problem with that.

Now let's see what I've done to your tables:

"tblTask"         has been changed to "task"
"ltbTaskCategory" has been changed to "catg"
"tblActivity"         has been changed to "activity"
"ltbLookUpTask"        has been changed to "note"
"tblDailyNotes"        is no longer required


task (was tblTask)
====
id            AutoNumber (PK)
task            Text
column            Integer
catg_id            Long (FK to "catg")


catg (was ltbTaskCategory)
====
id            AutoNumber (PK)
task_id      Long       (FK to "task")
note_id            Long (FK to "note")


activity (was tblActivity)
========
id            AutoNumber (PK)
task_id            Long (FK to "task")
started            Date/Time
stopped            Date/Time
active            Integer (0 = No, 1 = Yes)
note_id            Long (FK to "note")


note (was ltbLookUpTask)
====
id            AutoNumber (PK)
table_num      Integer (FK)
            1 = task
            2 = catg
            3 = activity
            4 = daily_note
table_id      Long (FK to table identified by "table_num" above)
dated            Date/Time
note_id            Long (FK to "note")


There are three master tables ("task", "catg", "activity") and one for text ("note"). I used shorter names in the examples above, and I will do so throughout the remainder of this document.

"task" is the driving table.

"catg" links one TASK to many CATEGORIES and stores the category description in "note". (If your tasks only have one category, this is overkill, but when the day comes and you need two categories for one task, you will look like a god/dess).

"activity" links one or more ACTIVITY to a TASK, just like "catg".

"ltbLookUpTask" has been changed to "note". Three columns were added. All of the notes for the different tables can be stored in this table.

You might be asking yourself, "Fine, but how in the world am I going to get any data into or out of this database?" The answer is, "Queries."

INSERT (or CREATE), SELECT (or READ), UPDATE and DELETE - commonly referred to as "crud" - provide secure and stable access to the data.

It is late, and that is about all I can muster for now.

Hi Badotz

Thanks for helping out. However, I strongly suggest that we stick to some naming convention that distinguish between fields,table,queries,textbox ect.. since there may be some coding invovled further down the line.

I can understand what you are trying to achieve by getting rid of ltbLookUpTask. That what I originally (comment  #22765659.html ) proposed but after extensive discussion with verpit it became obvious that a second layer was required for the database base to work the way verpit wants it to work.

If you go through the discussions you will find that verpit wants the task to activate and deactivate everyday and be discontinued at the end of the day. In other words they are not all tasks in the true sense of the word, a lot of them are more like activities. The layout I proposed provides a database that can handle such complexity  

Also I proposed the table dailyNotes because verpit wants to have some general notes that are not liked to any task or activities. They will just be note about that day. My plan was to make this table the recordsource of the mainform. Then it would create a one to many relationship with the activity table. I have to say that otherwise your proposal to put all the notes in one table looks very attractive. I note though that you still nake reference to table 4 = daily_note even you you had previous request the deletion of this table.

 

sb9 - given your excellent input re: badotz input, can you take what is required of badotz input and paste the appropriate changes.  This might make things a bit easier.  I might even add tagging and flagging later on but do not need to worry about that now.  just whatever changes based on his input would be fantastic!  Thanks again for your help.

>>naming convention

Now you opened a can of worms.

SQL statements act upon tables, retrieving rows of data. There is no need to name a table "tblThis" or "tblThat" - it is redundant.

It becomes an organizational nightmare. Plus, you have to remember, "LTB - oh yeah, that's a lookup table". And, "TBL - oh yeah, that's not a lookup table, but I can't remember what kind it is." If you name your tables correctly, then you know the purpose and type of every table.

Likewise, there is no need to prefix a column name with "fld" or "col" or *anything* - the column name is the column name, period. As with the tables, if you give your column names meaning, then you will know for what they are used.

>>I proposed the table dailyNotes because verpit wants to have some general notes that are not liked to any task (sic)

There is no need to add a "daily notes" table. By adding a date to the "note" table, daily notes can be fetched from the rest of the text. And if you leave the "task_id" zero, then it is disconnected from any task.

Further, there is no need for a "second layer" of anything. Proper database design requires thorough analysis of needs, and elimination of overlap and bloat. While it may not seem clear how all of this fits together, I know it will. I have done this for a very long time.

I am lost now for sure!  So badotz, with your plan, what I do not understand is that the notes are all in one table.  Not bad.

What about the comment from SB9? "If you go through the discussions you will find that verpit wants the task to activate and deactivate everyday and be discontinued at the end of the day. In other words they are not all tasks in the true sense of the word, a lot of them are more like activities. The layout I proposed provides a database that can handle such complexity"

Does your plan follow my purpose:
  1. Record start and stop times of individual tasks (up to several times daily for each).  
2. Keep various "categories" of tasks
3. Provide detail at the level of day, task, and stated activity.  
4. (added) Provide flags, and tagging for tasks.

Allow support for main form in the old database housed at www.dropio.com/tasksdb.

What do you think?

Before we go any further, I need to know what you are trying to do. Tables, columns and the like can come after we all understand what you are trying to accomplish.

I know you know, but there is so much material that I can't make out the forest for the trees. Please bear with me.

As I understand things now:

1. Tasks are engaged in every day
2. Tasks can have Categories
    a. Categories group tasks
3. Tasks can have Activities
    a. Activities are individual processes that apply to a single task
4. Tasks can have notes
5. Notes can exist without a task

Questions

1. Does a task expire at the end of the day?
2. How is a task terminated?
3. If a task is not completed by the end of the day, is it recreated the next (business) day?
4. What is the purpose of the category?
5. Is a task considered closed when all of its activities are closed?
6. If an activity is not completed by the end of the day, is it recreated the next (business) day?
7. There is no "start_date" or "end_date" for tasks. Are they required?

Observations

In the master tables, I like to include a flag called "status". Initially it is set to "0" (or Active). When you wish to delete, say, a task, you set the status flag to some value; I use "9". Then in your queries, when you present the active tasks, you exclude all tasks that have a status other than "0".

Why do this? Because as sure as the world is flat, some Poor User somewhere will delete something she should not have deleted, and she will call you in a cold sweat. You will calm her down, and then proceed to "undelete" her task. Its all about the god/dess thing.

Disk space is cheap. Never delete anything. Plan for disaster. Expect the worst to happen. Test your application as if it already has.

If you think the tables below will do what you want, say so. If there are things you don't understand, ask me to explain them.

Please do not assume I know what you want. Make me understand.

Tables follow:

//
//	task (was tblTask)
//	====
//	id		AutoNumber (PK)
//	task		Text
//	column		Integer
//	catg_id		Long (FK to "catg")
//	status		Integer
 
 
//	catg (was ltbTaskCategory)
//	====
//	id		AutoNumber (PK)
//	task_id	Long 	(FK to "task")
//	note_id		Long (FK to "note")
//	status		Integer
 
 
//	activity (was tblActivity)
//	========
//	id		AutoNumber (PK)
//	task_id		Long (FK to "task")
//	started		Date/Time
//	stopped		Date/Time
//	active		Integer (0 = No, 1 = Yes)
//	note_id		Long (FK to "note")
//	status		Integer
 
 
//	note (was ltbLookUpTask)
//	====
//	id		AutoNumber (PK)
//	table_num	Integer (FK)
//			1 = task
//			2 = catg
//			3 = activity
//			4 = daily_note
//	table_id	Long (FK to table identified by "table_num" above)
//	dated		Date/Time
//	note_id		Long (FK to "note")
//

Select allOpen in new window

OK Guys, here is my take on this

Naming convention

Naming convention is a question of personal preference. I am self thought and I have been designing DB for only 5 yrs. My earlier DB did not have prefix. But as they got more and more complicated it became important to know if a form is a main form(frm) or subform(sfr) and other distinctions. I have also grown a dislike for nonconventional abbreviations. I'd rather call the category field "Category" than "Cat" or "Catg" or Cgory" or "Ctgy" ect....

Jurging from Badoyz profile, he's been at it for donkey's years so I respect his experience. But at the end of the day it is verpit who will have to decide which one he/she adopts.

However, at this stage we have already started with a particular convention so I see no need to change, except for the fld prefix which as I noted before may be a bit of an overkill. All that is important is for verpit to know that there are various conventions out there.

Daily Notes

Yes that will work when combined with query. Plus as I said I like the idea of putting all the comments in one separate table. So I say we adopt this approach

Second Layer

I have mixed feelings about this one. General I would agree with badotz. As I pointed out my original proposal did not have that layer. It is confusing to say  a task looks up another task that looks up another task. But it soon became obvious that there are a lot of elements at play here. We have Task, Activities both of which needs lookups.

So my proposal is to follow this general line of thinking:

There are two types of activities. One is part of a task such as browsing EE, which is an activity that forms part of the task of creating a database. The other is a standalone activities such as coffee breaks. As I understand it a task have a starting point and an endpoint and completion point wher it produce some sort of deliverable. Activities don't have completion point they just have endpoint. Then you start again.

So taking Badotz input and my understanding of what verpit wants out of this database into consideration I propose the following:

ltbCategory
=========
CategoryID            AutoNumber (PK)
Category                Text      
Noteid                     Long (FK to tblNotes)

ltbTask
======
Taskid                    AutoNumber (PK)
task                         Text
NoteID                    Long (FK to tblNotes)
CategoryID            Long (FK to tblCategory)

ltbActivity
========
Activityid                    AutoNumber (PK)
Activity                       text
Noteid                      Long (FK to tblNotes)

tblNotes
====
Noteid                  AutoNumber (PK)
Dated                   Date/Time
note                      Text

tblActivity
========
id                            AutoNumber (PK)
Taskid                   Long, Index, required=no (FK to "ltbTask")
started                   Date/Time
stopped                 Date/Time
Activeid                  Long (FK to tbl ltbActivity)
Noteid                   Long (FK to tblNotes)

Verpit, I know that at this stage you are asking yourself how the hell is this going to work. I don't understand what each table does. Why are these two guys coming up with different approaches.

Answer

1) You will figure out how it works once you complete the relationship and build the queries

2) If you where to contract this bd out to 5 experts you would end up with 5 diffentent db. Most probably all five will work. Which one you prefer will be a matter of your personal preference.

Way Forward

Do the table and relationship. It is time to move on we are getting stagnant. There is not a lot more that you can learn without moving to the next step and the db is definitely not going to progress much further.

Badotz

"Before we go any further, I need to know what you are trying to do. Tables, columns and the like can come after we all understand what you are trying to accomplish."

That's the key. As I said verpit's definition of task is a little bit strange. As you can see it took an extensive discussion for me to understand what exactly verpit wanted to achieve. We don't want to go through that again please.

I reckon that the latest design that I proposed which took in some of your ideas will deliver what verpit wants to accomplish. We can always review the design after the first draft. believe me I am not proud. I am prepared to have any of my ideas dumped if a better one comes up as we move also. In fact I am sure between the three of us we will come up with something new.

However, this project is getting stale. I believe you called that a Dilbert scenario.

May I beg you to MOVE ON

Sorry the last sentence about MOVE ON was addressed to Verpit

Why do you continue with "lookup tables"? They serve no purpose that a query cannot provide, AND THEY CLUTTER UP THE DATABASE!!!

I'm still waiting to hear from verpit before doing anything further.

btw, sb9: I did not mean to step on any toes. If you want to do this, just say so and I will go away. Otherwise we will be at odds with our design issues, politics, religion, etc., and verpit will suffer.

Just say the word. Otherwise, gird thy loins for battle ;-)

Badotz

As I said a lot of this is a question of personal preference. I agree that us disagreeing on technical issues will not serve verpit any purpose. But I appreciate your contribution and woud like you to stay on. You have  made some valuable contributions and I am sure there is much more that you have to offer. However, as far as table design is concerned I'd say let's not change horse mid stream. We can make adjustments as we go also but dumping one design to adopt a completely new one does not make much sense to me. Unless the first one was completely unworkable which it is not.

So I will greatly appriciate if you agree for us to move along with  my lastest design. Create the relationship and queries. This will give verpit a better understanding of how things work and enable him/her to make a more active participation in which option to take as we move along.

I can't stress any more that the underlying message of  my comments over the last 48 hours has been that it is time to move forward.

Clear enough.

I am outta here. My paying job demands immediate attention.

Good luck!

Badotz

As a general side conversation, I'd like you to explain how a query can purform the function of a lookup table. For example if a client is in charge of licencing businesses in a particular district you may have a lookup table with the names of all the streets in that district. These street names are not going to change. You've have another table with the location of licences premises and the street address field of the premises location table will lookup to the street table. How are you going to use a query to replace the street table in that case.

Unless maybe you query the premises location table and then use that query as the row source for the combo box but this can be problematic if you get a premises on a previously vacant street

You are certainly right sb9.  I certainly do want to move on and would have had there not been some sense in at least considering the alternative objective being proposed.

sb9s proposal
pros:
sb9 'seems' to have a clearer sense of my objectives.
naming 'convention' seems to make sence
cons: can't get a clear picture of what the tables do without designing relationships first.

badotz:
pros:
database seems to make more sense to the layman, names appear clearer but so does placing fld and tbl in front
cons:
his understanding seems not quite right

The last con is the very reason we can't move forward and I have no template for how to get on the same page.  I'll start with a simplified purpose broken into examples, and then attempt to answer badotz question.

Purpose
  1. Record start and stop times of individual tasks (up to several times daily for each).

-get coffee activate/deactivate multiple times daily - no task ever finishes, just act/deact, though within certain categories of tasks a deactivation might be just like finishing or finalizing, or simply giving up on something.  In the main form, tasks are designed to not be shown on the next day.  That is to say that the "coffee break" task on mon, will become a new "coffee break" with the same name as soon as it is re-entered as opposed to reactivated.  You would have to play around with the old database form to get an idea of how this can work.  It is at www.dropio.com/tasksdb

-go to meeting - same thing
 
2. Keep various categories of tasks

categories: tasks can be uncategorized, or be named with categories of almost anything.  todo, general, pick_me, no category might appear on the main form with other categories appearing on different forms in different ways to be developed and added over time.
 
3. Provide detail at the level of day, task, and stated activity

- Day detail might include (surprise mtg with corporate)

- Task detail for "get coffee" might include "stopped by boss in hallway re: next year's proposal" to justify the 1-1/2 hour coffee break

- Named activity might be "results of proposal x" and be a short outline of an important proposal that one might one to include in a report.

I'd like to get a list of tables and fields with clear descriptions of what each does to understand either proposal better, if that would be OK?  Thank you both for putting so much effort into it.  If they could be in the same post, even better.

Verpit

Modify your exiting database to the following

ltbCategory
=========
CategoryID            AutoNumber (PK)
Category                Text      
Noteid                     Long (FK to tblNotes)

ltbTask
======
Taskid                    AutoNumber (PK)
task                         Text
NoteID                    Long (FK to tblNotes)
CategoryID            Long (FK to tblCategory)

ltbActivity
========
Activityid                    AutoNumber (PK)
Activity                       text
Noteid                      Long (FK to tblNotes)

tblNotes
====
Noteid                  AutoNumber (PK)
Dated                   Date/Time
note                      Text

tblActivity
========
id                            AutoNumber (PK)
Taskid                   Long, Index, required=no (FK to "ltbTask")
started                   Date/Time
stopped                 Date/Time
Activeid                  Long (FK to tbl ltbActivity)
Noteid                   Long (FK to tblNotes)

Then set the relationships PK-FK. You will notice that FKs have the same names as PKs in other tables. That's to facilitate the setting up of the relationship. It is a piece of cake.

I know that there are so many things that you do not understand at present. The reality is that the more you think about it the more confusing it will get. Just think about everythng else you have done in your lift and how much of a better understanding you gained from actually doing them. Database is the same. YOU HAVE TO DO ONE to understand how all the bits works.

sb9...  - So you are now abandoning the naming convention you spoke so highly of?
Can you look at my answers to badotz question and make sure you understand my objective?  I know this seems like overkill, but I just need to make the right decisions here.  Thank you very much for your repost and I promise, It looks like we are almost there.

Badotz.  here are the answers to your thoughtful questions

As I understand things now:

1. Tasks are engaged in every day - yes and several times daily
2. Tasks can have Categories - yes
     a. Categories group tasks - yes
3. Tasks can have Activities - no, notes can be named by the 'activity' they represent.
    a. Activities are individual processes that apply to a single task - no
 4. Tasks can have notes - yes
5. Notes can exist without a task - notes can exist for the day, task, or named activity
Questions
 
1. Does a task expire at the end of the day? - no never, only activate/deactivate
2. How is a task terminated? - it isn't, it is merely deactivated
 3. If a task is not completed by the end of the day, is it recreated the next (business) day? - yes, every time you enter a task, it is recreated (that day or another day)
4. What is the purpose of the category? - to define groups of tasks
 5. Is a task considered closed when all of its activities are closed? - A task does not have "activities".
6. If an activity is not completed by the end of the day, is it recreated the next (business) day? - no activities with respect to activate/deactivate, only tasks.
 7. There is no "start_date" or "end_date" for tasks. Are they required? only activate/deactivate

no no... notes should not be the record source of the main form...  I have no idea where that came from.  Tasks are the focus of the form if you have played with it.  What am I not getting here?

Verpit.

 I am not quiet abandoning the naming convention. I am keeping them for the tables and lookup tables. I'll also be using them for the queries, forms, textbox, labels, listbox, combo box, ect...

I am of the opinion that your definition of task is not quiet right. The design I am proposing will provide the functions that you want. The activity will activate/deactive. Some activity will be part of an ongoing task and some will not. For example discussing something with someone in the hallway is not a task. It's just an activity that may or may not be related to a task.

At the end of the day you may not use the database in this way but I believe that it is crucial that the database is designed to be able to do that if you choose to later down the line.

Finally let me reiterate that at this point you do not have much more to gain without moving to the next phase. We need to come up with a first draft which can then be adjusted as required
 
 

"no no... notes should not be the record source of the main form...  I have no idea where that came from.  Tasks are the focus of the form if you have played with it.  What am I not getting here?"

Don't worry about that comment, you will see how it is going to work when we get there. Remember you cannot understand how to write a book before learn how to write the words, then sentences then essays. I can see how all the bits and pieces will come together but don't believe that it will serve any purpose to go into the nitigrity at this stage since it WILL all clear up as we move along. Moving along is the only way for you to get it

I am going to work now and will be back on in about 9 hrs. Prepare the tables and try to set the relationships. Post them when completed. Then we will start to look at the form and creating the necessary links. We will hve to create a few queries to make this work. Don't worry about this at this stage just get the tables and relationships happening. Everything else is gonna be alright

OK very good, I'll go with your new design without the naming conventions.  Keep in mind that at the end of the day, the purpose stated is where I want to go.  If we aren't going there then I am to blame because I am allowing you to lead me blindly down 'your' path instead of mine.  Please ensure the following purpose is in the plan:

1. Record start and stop times of individual tasks (up-to several Xs daily for each).
2. Keep various categories of tasks
3. Provide detail at the level of:
  - day
  - task
  - stated activity

As long as we can accomplish this and continue to utilize the form as it is utilized today.  I'm very happy to move forward.  I'm developing new tables based on your recommendations and will post shortly.  This purpose and the use main form though is critical.

Long I do not understand as I use 2007 and have no such option.  default is text 255 which is what I am using for non-date fields.  FK?  no idea what that is.

sb9 redesign #2 posted at www.dropio.com/tasksdb

That's a good start.

FK is short for foriegn key. You will not find this term specified anywhere within access. It just means that this is the field that is going to form a relationship with the primary key (PK) of another table.

Long is short for long integer. When you set the data type of a field to number you get a choice of several field size under the field property eg byte, integer, long integer,single, double,decimal (you can see all these in the design view of the table).

However you did not see them because you had set these field type to text. That's probable because you think that they are going to hold texts. They are not going to hold text eventhough you will see text in them. They will store numbers but display text from another table. You are probably scratching you head by now and wondering how is this going to work. Don't worry, you are only three steps from finding out. (step 1: set relationship, step 2: set the lookup, step 3: insert some test data and see it work like a charm.)

Now for Step 1: Relationship
I have already change all the FK fields to number, long integer. use the one I have posted below for this step.

 I notice that you have put a couple of table in the relationship box but failed to set the relationship. I don't no if you tried but even if you did it would have not worked becuase your FKs where set to text. Now it will work

All you have to do is click on the FK and drag it to the PK. You will notice that the FK has the same name as the PK so there should be no confusion as to what goes with what.

Basically every plain text field with a name end with ID (eg Taskid,Noteid) will link with a bold text field having exactly the same name in another table.

Post the db back once you have done that or let me know if you have any problem getting it done.  

db-with-new-tbl-design-v2-4-1e.mdb

I wish to point out something.  I won't have too much time to work on this during the day but do want to mention why I am having such a hard time ignoring badotz entirely.  The plan is different than what he understood and he did not understand what I wanted out of the db.  I did notice some interesting things though in his tables.  There were basically three (3) things that looked to me like he had right (IMHO),

1. clear, concise names - It seems like his method here (error in layout aside and purpose aside) will make looking at the code much easier.  There is just some continuity to it the 'feels' interesting.  I am just a novice though.

2. all lowercase - easy code reading

3. types of notes - this seems much more in line with what I was after.  I understand that sb9 is clearly seeing something I am not and I am fully willing to follow that train of thought.

SB9.  Your effort is tremendous and I REALLY appreciate your help.

purpose:
1. Record start and stop times of individual tasks (up-to several Xs daily for each).
2. Keep various categories of tasks
3. Provide detail at the level of:
  - day
  - task
  - stated activity

I know I said I was leaving, but I cannot believe some of the things I am reading.

I have two questions for sb9, and some general observations.

Q1. Are you going to use Bound Controls for this, and if so, why?
Q2. How long are you going to work on this for free?


Observations

My understanding of the application notwithstanding, I can see problems in the database design.

I know you are anxious to get coding, sb9, but there a few items I simply cannot ignore:

-> PK names

The name for the primary key should be "id". Not "ActivityID". Why? Because when you reference the PK in a query, the SOURCE of the PK is evident. The VARIABLE in your CODE that refers to the PK of a table can be named that way, but for the DB itself, adding the table name to the PK name is pointless (and is harder to type).

I see "tblActivity" is designed along those lines, but the other four tables are not. Nice, consistent design.


-> Column Naming

Never, EVER, give a COLUMN name the same name as a TABLE. This will only cause confusion down the road. I am talking specifically about "ltbActivity" where the column "Activity" refers to the TEXT for an activity. RBI.

-> Column Names

verpit, this is your database. You can name the columns in your tables in any way you see fit. Do not blindly assume sb9 knows what you want. If you don't like his naming convention (or the lack of one), say so, and don't accept his version. At some point, you will be the one left holding the bag.

Also remember, verpit, that sb9 is doing this as a favor. He could disappear at any time; there is no bond between you, other than the tenuous thread of EE. I truly believe that you are getting what you paid for, and the end result may not be what you expect.

-> Conclusion

Designing a database is not an afterthought - it is the core of your application. How you get data into and out of the database, while not trivial, is not as important as the database itself. The database is the structure the application hangs upon.

While I am not a DBA, I do know a thing or two about database design, and what I see here needs work.

I completely agree with you that I must get this part right before I can continue.

I've offered to pay more than one individual here for tutoring via real-time collaboration.  I'd be very interested in paying for an hour of desktop sharing time, where someone could instruct and I can ask questions and make notes and changes along the way.  I am not allowed to talk about that here unfortunately and can't even give my screen name at the live.com msn thing out here.  

Given the newly clarified purpose, what would be your table design?  I think you may have looked at a fair amount of code.  Keep in mind that the database at www.dropio.com/tasksdb is what houses the original database and the form works very close to how I want it to work with a new design.

purpose reclarified:
1. Record start and stop times of individual tasks (up-to several Xs daily for each).
2. Keep various categories of tasks.
3. Provide detail associated with the following three (3) items.
  - day
  - task
  - stated activity

Badotz, your input regarding tables based on this purpose would be greatly appreciated in your foreposted format.  That was fantastically easy to read and exceptionally worded I thought.

SB9...  I really appreciate all your effort here also.  Your attention to this has been outstanding.  I promise I don't want to stall for the sake of stalling.

What you ask, werpit, is not unreasonable.

But Experts-Exchange is *not* Rent-A-Coder. Your requirements far exceed technical support.

There is no way I could impart to you in one hour what it has taken me decades to understand. It might take a week just to iron out all of the details of this project, but it might take less time. I tend to think of everything failing, and design accordingly.

Experts cannot solicit your business directly. There might be a link to a web page or an email address on an expert's profile page, but I cannot say for sure.

Sadly, while this is my idea of fun (really!), I do not work for free.

badotz I appreciate your finding issues with this..  please post an updated solution based on the more clearly defined purpose and my direct answers to your questions.   If you don't mind, it would be greatly appreciated.

No can do. I do not work for free.

Ok

Badotz

You made some interesting points let aside the tint of sarcasm.

As I mentioned before. One, I appreciate your input and respect you experience in the field. Two, Naming convention is a question of preference and convenience. Verpit will no doubt adopt his/her own style eventually. But while I am guiding this I have to use something that I feel will make it easier to guide him/her as we travel this road. I am 100% confident that we will arrive at a database that works. I cannot say that verpit will like the style.

To answer your other questions

Why use bounded control: I am not sure what you mean by this but I suspect you are refering to the query that I mentioned will be used for the forms. Yes the forms will have some controls that will be bounded to the query. It will also have some unbounded controls. I do not want to go down the line of using to much VBA an confusion verpit even more than he/she is already confused.

PK Names

Yes it is easier to write codes when the field names are shorter. But as I see it there is an advantages in indendicating what the id is for. This is extremely useful when a novice is trying to set relationship. They can easily see what goes with what.

Column name and table names: Yes them having the same names can be confusing. That's why I started of using the prefix fld for fields and tbl for tables. That takes away this element of confusion and automatically means that the tables and the column NEVER EVER have the same name.

How long I am going to work for free. Well I just like helping people. I have to admit this is going a little bit longer than I expected it to. Both you and verpit must have noticed from the tone of my previous post that I was starting to get very annoyed with the stale mate. I will be happier to go through this whole thing twice than get stuck on the starting block. I have agreed to help verpit make a database. But I am not agreeing on an unless discussion on technicalities.

Verpit this is for you: This database HAVE TO MOVE ALONG. Once we get to the end I will be quiet happy to go back through it and show you why we did things the way we did. I may not be a highly experience DB designer but I am a certified workplace trainer. When you train people you first go through the whole process. At this point the trainee is basically following blindly to quote yourself in a previous posting. Then the trainer go back and explain the process again from start to finish. The trainee has a better chance of understanding then because they have an overview of the whole thing. That's the way we have to procede and I am 100% confident that you will get a database that you fully understand much faster through this process than going the way we have so far.

Badotz: As I have said on a few occasion now, I appreciate the contribution that you can bring with your years of experience. And I'd be happy for you to keep contributing. However, I don't think that use debating on technicalities will help verpit. What I would ask of  you is to allow us to move through the process and assist with input that helps us move along. Once we get to the end, I will be happy to have a discussion with you about alternative ways of getting there.

 

Verpit


I don't see any progress in the db you have not done anything about the relationship. Either we start moving forward or I quit.

sb9: Even superior code cannot make up for poor design.

I will not debate you on methodologies. I will admit when I am wrong - and change - but only for the better.

I bring these items up to enlighten verpit, not to denegrate you.

But ultimately, this isn't about you - it is about verpit. If he is satisfied, then you have succeeded. Otherwise, why bother?

Badotz

You are right mate. Don't get me wrong, I actually enjoy your contributions. I would be have to keep having as long as it does not detract from the progress of the db.

Superior code cannot make up for bad design. Yes 100% agreed. You will notice that I did modify my initial design and created a notes table which was one of your recommendation. I well come such contributions and will take them onboard. Like you I am willing to admit when I am wrong and change accordingly.

Verpit has to be satisfied. Yes I agree. But as you say it is impossible to explain what have taken us years to understand in an hour. In the same token he cannot be satisfied that we are going in the right direction when he does not comprehend how we are going about getting there. That's why I am insisting that we move through the construction of the database at a faster paste. once we have done that he can have a better understanding of how things fit together. You will also have a better understanding of my design and be able to better pinpoint areas that can be improved. I am sure that even with your level of expertise, you'd rarely get a db 100% right the first time. There is always a first draft than a second and maybe even a third. If that is not true for you know than definitely it would have been much earlier in your career.

One thing that we both agree about is that is all about verpit. So if I did not say that before let me say it now: Welcome onboard teammate, let's get this database off the ground.

this latest one almost pushed me back to crossloop for a complete rethinking

sb9, Can you take the IDfield name into account and use just ID, and the idea behind not ever naming a table name the same as a field name and post an update.  I think that will work.  I don't think Badotz wants to throw any more wrenches in without proposing a specific solution to the wrench but I concur with his view on that unless you think of a specific reason why it must be to the contrary.

Can we fix:
1. id only as field names
2. no fields with the same name as a table

Then:
I'll state the purpose of each table, you concur, and then I think we can move forward using 'your' posted update.  I know this is alot to ask but your input is greatly appreciated.

Verpit

The database will work fine whether or not you change the name of the id field. But it will be confusing. I will tell you why.

ID only as field Name

When you start making links in the relationship box it helps to have something to guide you. Indicating what the id is for helps. A typical example is the tblActivity. This table forms relationship with tblNotes through NoteID, ltbAvtivity through ActivityID. If you used only ID in every table it will be easy to make mistakes and link the wrong things together. That's rarely a problem for an experienced designer. But as a novice you need all the help you can get.

 In a netshell; You may call everything ID if you choose to but I do not recommend it and believe that you will find it much harder to progress through if you change the ID names to just ID. Once you get more experience these stuffs will become second nature and you may not need to be so pedentic.

Column and table Names

Yes it is a very good idea not to give the column the same name as the table. My recommendation here is to revert to the my originally proposed naming system that used the prefix fld. This means that the column will never have the same name as the table because the table's name will always start with tbl or ltb. Example tblActivity can have a column called fldActivity.

I am going to attach an extract on naming convention that may help you better understand why I favour the naming style that I am proposing over that being proposed by Badotz. But as I say once you're a big boy and walk on your own, you can use whatever style you please. I just think that this style will be better to help you through. And I wish someone had shown it to me when I first started. Would have saved me a lot of headaches

Pages-from-Access-2003-VBA-Progr.pdf

Tell me, sb9, which query is easier to read:

SELECT tblActivity.ActivityID, tblActivity.Activity FROM tblActivity WHERE tblActivity.ActivityID=[@ActivityID];

or this:

SELECT id, activity_name FROM activity WHERE id = [@ActivityID];

Now your turn, verpit. Which is easier to read?

I also note that I said that I was going to quit if we don't start moving forward Now. This may sound rough but sometimes one has to be cruel to be kind. I really want to help you and you are not helping yourself much by refusing to progress through to the next stage so that you can understand why we are doing things the way we are.

Therefore, I will not be answering any further question until we have completed the relationship unless of course the question relate to the setting up of the relationship

I need to go to work now and I will be back this afternoon.

Badotz

I personally find the first one easier to understand. The second one may be easier to write.

But please let's not confuse verpit he is not there yet. If you want to have the discussion with me I will gladly give you my email address and we can discuss this outside this thread for verpit's sake

I see your point sb9 and since you are willing to help me and I am asking way too much from either of you to begin with, I will absolutely stick with your design.   I think you wanted me to do the relationships to help me understand which is fantastic and useful.  Thank you very much for all your effort.  I do think that badotz has a point with the easy to read thing but then not understanding it well, more is likely better.  If we are on the same page on tbl fld names not being the same, I'm fine.

Sb9, Can you post your latest database? I''ll take it and draw the relationships per your specifications.  Thanks again badotz for all your help and I think I will go with the naming 'convention' until I become an expert like you.

I'm setting relationships and simply clicking ok on the box shown.  I am using v2007 and can help you find it if needed.  

relationship-options.jpg

OKee..  Thanks again SB9 for your effort.  I have posted the updated version f.  It incorporates your latest table design which makes use of some of badotz ideas with your relationship links and your naming convention.  I see how the field names make sense now and I am sorry I had such a hard time moving on.

To give you some background.  It was previously suggested that we go with a certain table design and changed later after significant coding etc.  We came to find out during the middle of timer work by another part that the table didn't quite support what I was trying to accomplish.  I just need to make sure I avoid this collapse in the future with a well thought out design.  That was the reason for requesting the definitions of each table which is still not fully understood.  Your efforts have been tremendous and I really appreciate it.   I'd like to have a nice description next to each table for my own reference if possible but it does not have to happen now.

Thanks for your input.  I think I have probably made some mistakes but it's uploaded.

sb9..  what do you think of the latest?  I think the relationships are correct but still need your input.

I really appreciate your assistance at trying to train me.  Unfortunately, that's not really what I am after.  I don't want to be an expert at your level.  Only enough to rebuild the tables.  since we have accomplished that with your plan, in order for me to move forward with someone at crossloop, I will need to have a good understanding, descriptions of the tables and each field in order for someone else to understand the design.  It bugs me that you are running off for a week and badotz was right in that "I am the one now holding the bag".  I have to get a clear picture of the table that is there.  That's all I am after and I will be handing this off.

Well if all you wanted was a table design then its done. Some adjustment are required with the propoerties of some FKs and you can use the fldTaskID in ltbTask as a guide.

If you are going to do more work with someonelese they will easily understand the table description. It is self explainatory. That's the beauty of naming convention. They tell you a lot about what the object are for and gives you an idea of what the designer intended to use the object for. For example the fact that we have named some of the tables ltbXYZ will tell everybody that these tables will store data that other tables will be displaying. Their name of these tables tells everyone what data these ltb are storing. The fact that we've called the PK fldXYZid tells everyone that they are linked to fldXYZ in ltbXYZ and that they should display fldXYZ from ltbXYZ.

About holding the bag. You are always going to be left holding the bag unless you contract someone to construct and maintain your database.

If I was in your position I would construct something simpler that I can full understand. Then add the bells and whistle as I grow in experience. In fact that's exactly what I did with my first databases that consisted of only tables, forms,queries and macros. There was no naming conventions (which was a mistake) or VBAs.

exceptional work!

sb9 - thank you again for all your superb support.  I hope you weren't in it for the points because 1500 isn't nearly enough for what you did!   I hope you will find time to check out the follow-up post here >> https://www.experts-exchange.com/questions/23866945/existing-form-to-new-table.html

The tables look fantastic!

Suggestion: open a new question regarding database design and naming conventions. Just for grins, see what they think of "yours".

Thank you very much for your input Badotz.  I greatly appreciate it.

Sb9 - definitions would still help me a great deal.  Here are two messages regarding that issue.  The first is from me and the second is a response to me (thread here).

from me: The tables are the work of sb9 at the end of this related thread.  It is his design.  I was trying to get a good descriptions of each field before he left but was unable to.  According to tblActivity, it does look like fldStarted would give you the most recent activity for a given task.  That certainly seems logical.  You can see a text file of each field also at www.dropio.com/tasksdb.  Thanks again for your effort.

to me:  verpit, even if someone else designed the tables, it's your data and it's vital that you have a clear understanding of what each field means before you do any further development work on your database.

Thank you very much for any further explanations you can provide in the new database field descriptions.