Help with an expression to arrange engagement dates for client cases

[DinoBaggio]

Hi all!



I have a question about how you experts think I should set up a part of my database to track engagements. I have designed a table for engagements and put in their EngagementID and Engagementtype. In that table I then entered the three engagement types that need to be recorded, -- These are.. 4 week engagement, 8 week engagement and 12 week engagement.

I then made another table that is CaseEngagement- (I called it CaseEngagement because I also have a casetable and everything links back to the caseID) So in the CaseEngagement table I have CaseID to link back to the Casetable and then a lookup field to find the engagement type that is going is to be recorded and then I have the scheduled engagement date, whether it was met field and when it was met field.

I dont know if this is the best way I should have designed this because really a client MUST attend their engagements, unless they leave the service early. Really every client, unless they leave early, must meet their engagement every 4 weeks from the date they where originally caseloaded.

I have two questions about this.. firstly, what do you people think of the design I have described above, and secondly, is it possible that because I already have a caseload date field in my original case table that I could design a field that automatically enters the next engagement date as it will be 4 weeks from the orginal caseload date and then the next engagement will 4 weeks after the previous engagement.

Any ideas people on how to set an expression to do this and if it would be best to simply design a table that provides fields for the three engagement like this :

CaseID, 4 week engagement scheduled date, 4 week engagement met, 4 week engagemnt met date, 8 week engagement scheduled date, 8 week engagement met, 12 week engagement scheduled date, 12 week engagement met Expressions would be needed for the scheduled date?

If anyone understands what I mean I would really appreciate the help.. I have likely worded the above very badly to understand but I am only a beginner and dont really know how best to word it.

Thanks everyone if anyone could help!!

[jzwp11]

CaseID, 4 week engagement scheduled date, 4 week engagement met, 4 week engagemnt met date, 8 week engagement scheduled date, 8 week engagement met, 12 week engagement scheduled date, 12 week engagement

Each engagement date should not be a field, but rather a record in a table.

Since a person (I assume) has many engagements, that describes a one-to-many relationship which by normalization (click here for more on normalization) rules requires the engagement dates to be records in a table.

and then a lookup field to find the engagement type that is going is to be recorded and then I have the scheduled engagement date, whether it was met field and when it was met field.

Although Access has the capability of having lookups (combo/list boxes) at the table level, it is generally not a good idea to have them because of the issues they can cause. See this link for more on the problems table-level lookups can cause.

I have designed a table for engagements and put in their EngagementID and Engagementtype. In that table I then entered the three engagement types that need to be recorded, -- These are.. 4 week engagement, 8 week engagement and 12 week engagement.

The above is OK but I would include another field to hold the number (4, 8, 12). You can use this to calculate when the next engagement is due


tblEngagementTypes
-pkEngageTypeID primary key, autonumber
-txtEngagementType
-longEngage (long number integer datatype field)


You will have 3 records in the above table; 1 foe each engagement type 4, 8, 12 week


Then for your CaseEngagement table

tblCaseEngagement
-pkCaseEngageID primary key, autonumber
-fkCaseID foreign key to your Case table
-fkEngageTypeID foreign key to tblEngagementTypes
-dteEngage (date of the engagement)

Once you put in 1 record for a case you can calculate the dates of the next 3 engagements You will eventually put in 3 additional records for the other engagements, but do you want to capture the actual dates or the expected dates (based on the calculated values)

[DinoBaggio]

Thankyou very much for getting back to me so quick jzwp11, and thankyou for all the information and links above, it really is appreciated, though you have made me worried about my database design now hehe as I have used a few lookup wizards to lookup and link different tables. Is that really bad design?

I am very sorry that I am only now replying to your answer, but I am only back to work today and have begun trying to implement what you have said above however I dont understand what the LongEngage field is??

I am also a little unsure what you mean by putting in a field to hold the number 4, 8, 12? I really really appreciate you help and the way you have went to the time to even type out what tables and fields you suggest, but I am just a little unsure about what you mean. Probably just because I am such a beginner.

At the minute my databsae table, CaseEngagement basically relates back to the case table through the CaseID, then another field looks up the name of the engagement the user wants to record, and really this is very basic as the user simply selects from a combo box that looks up the engagement table and chooses which engagement the client is going to meet. the names of the engagement are just simply 4 week, 8 week, 12 week.

As said above though, I want to change the design so that it can help schedule next engagements. It would be nice to record the expected next engagement date for schedule purposes and then the actual date that it is met on for accurate recording purposes. Though if it is not possible to do so, then the expected dates would be the only one I could calculate otherwise I would maybe be best just leaving my design as it is because it allows the user to enter manually their expected scheduled meeting date and then the actual. Really I would just like to calculate the expected date for the three expected engagements and then leave a field beside that for the actual date met.

I hope this makes sense and answers the information you asked for above.

I really, really am so grateful for your help and thankyou for taking the time our to help me with the above.

[jzwp11]

...have made me worried about my database design now hehe as I have used a few lookup wizards to lookup and link different tables. Is that really bad design?

Yes, having the lookups in the tables will cause issues, so I would definitely recommend getting rid of them and just store the key value from the related table.

what the LongEngage field is?? I am also a little unsure what you mean by putting in a field to hold the number 4, 8, 12?

Having the actual number (that corresponds to the number of weeks for each future engagement) allows for easy calculations of when the next engagement is due. If you just used the text "4 week engagement", you cannot do any calculations on the text itself. You could use some functions to extract the number 4 from the text, but I would just have a numeric field that holds the number 4.

At the minute my databsae table, CaseEngagement basically relates back to the case table through the CaseID, then another field looks up the name of the engagement the user wants to record, and really this is very basic as the user simply selects from a combo box that looks up the engagement table and chooses which engagement the client is going to meet. the names of the engagement are just simply 4 week, 8 week, 12 week.

It is fine to have lookups (combo/list boxes) on forms and it wil work the same even if you do not have the lookup in the table.

It would be nice to record the expected next engagement date for schedule purposes and then the actual date that it is met on for accurate recording purposes.

One of the general rules of database is that calculated values (such as the future engagement dates) are not stored but calculated on the fly when you need them. So in other words you can display the future engagment dates on a form, but you would only store the actual engagement date in the table. You can still do comparisons of the actual date to the calculated date to see if people are keeping on track with their engagements.

[DinoBaggio]

Thanks again for the reply and help,

Do you mind taking a little a look at the attached screenshot I saved in a word doc of the relationships in my database. Just for your opinion on its structure. I know it will probably be quite amaturish but I just want to be sure I am at least on the right track with my design.

As for the engagements, I will also provide a screenshot to show you how I have originally designed a form for that before trying to design it the way you have instructed, which I will in a minute, I just want to make sure you understand the way I have orginially tried it.

Many thanks for everything. !!

[jzwp11]

You have several normalization issues with your table structure that need to be addressed first. As such, we need to set aside any form questions because as we change the table design any forms you have created will no longer work.

Before we get to the tables, it is generally recommended to not have spaces or special characters (?,\,/,,#,$,%,-,*, etc.) in your table or field names. You also want to avoid using any reserved words as table or field names. This site has a list of those reserved words. And I have already discussed the table-level lookups. Also, it is best to use a more descriptive field name for the primary key field of a table. Using "ID" will become very confusing later on if it is used in many tables.

Now to the normalization issues:

First, you have an employee table and a customer table where most of the field names are the same, so you are storing similar data. One of the first rules of normalization is that like data should be in 1 table. I would recommend a table for people. You can have a field in that table to distinguish customers from employees

tblPeople
-pkPeopleID primary key, autonumber
-txtLastName
-txtFirstName
-fkRoleID foreign key to tblRoles (must be a long number integer datatype)


tblRoles (holds records such as customer, employee)
-pkRoleID primary key, autonumber
-txtRoleName

You also have a series of fields for phone numbers of various types. So a person has many ways of contacting them, that describes a one-to-many relationship. So it would be best to have the contact info in a separate but related table. Each piece of contact info (i.e. a phone number) would be a record in that table

First a table that just holds the various contact methods home phone, cell phone, fax, e-mail


tblContactMethod
-pkContactMethodID primary key, autonumber
-txtContactMethodName


tblPeopleContactInfo
-pkPeoContactInfoID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkContactMethodID foreign key to tblContactMethod
-txtContactInfo (the actual phone number, fax number or e-mail address would go in this field)

You also have fields for county and country, but you also have another table (locations) with the same fields. I assume that there are opportunities for normalization here as well. I assume that there are many districts that fall into a county and there are many counties in a country. I also assume that there are some other jurisdications between a county and a country. You'll have to explain more about this in order to normalize it properly

In the cases table you have several fields such as caseload date/opened by, resolved date. These actually imply events related to a case which describes a one-to-many relationship

tblCaseEvents
-pkCaseEventID primary key, autonumber
-fkCaseID foreign key to Cases table
-dteEvent (date of the event)
-txtEventDescription (if the events are constants you could set the descriptions up in a separate table and just reference a foreign key here instead)
-fkPeopleID foreign key to tblPeople (the actual person tied to this event i.e. opened by, resolved by etc.)


In the EqualityMonitoring table, most of fields are applicable to a person (DOB, marital status, gender etc.). Those would typically go in tblPeople. Will a person have more than one disability? Do you need to track changes in marital status?

There are other issues, but I'll stop there. It is a lot to digest

[DinoBaggio]

wow, thankyou so much JZWP11,

The above is fantastic and the level of detail you have explained things is brilliant. Thank you so much for taking so much time to look and advise me on all of this.

I have done as you suggested above in a new database file.

I have created the tables as you described and I am now wondering if you are suggesting that the casecourses and caseemployment tables which I have originally in my original database should be simply made into the tblEventsDescription you said I could make. I think it might work like this because both have constant values that they are recognised by, for example the course table has 5 course types, each destinguished by a unique code. The same goes for the employment table.

However the problem arises in that although the course and employment types have constant values, they are only types and the clients will need to provide more detailed information about th exact programme they are undertaking with that course type and the exact employment details of the employment type they have went into. For example, a course type is Short Acc Course, their is a code for this part so their is a constant value to describe the event by, but the client needs to give the exact name of a short acc course they are undertaking and its start date, end date, etc. The same goes for employment, eg, Temp employ, has a code for its constant value description, but again the client needs to provide the organisation details and dates etc.

So would I put the employment and course codes in the tbleventsdescription and then add a few more fields to the tbCaseEvent. Would this work? I would probably just need a name field, and end date??

I really like and thankyou for your idea of the events, I think it has really made the database design a lot clearer for me. I think maybe the engagements problem I was talking originally about in the post at the very beginning would even be able to go in the Event and EventDescriptions table??

However another thing I am unsure of is, what is going to be in my case table then apart from a CaseID, CaseTitle??

As for the disability question you asked me, originally I had only felt the need to make it a yes or no section so that it could be a form for charts etc, but upon consideration and the great light you have shed on my work, I feel now that I should put a disability field in the people table and allow people to provide their disability issues, so yes I think there will be a one to many relationship here, but I'm not sure how best to design it since I can not store a list of disabilities simply because I have not been provided with information the categories of disability that a client could have.

With regard the locations, I will only need to deal with about 6 or 7 districts, each district does have towns within them, however I couldnt possibly list of all of these a table and instead the user would need to simply add the town that each client is from to the district.

i really really appreciate your help sir, and must say that it is a brilliant to see such a gentleman on this forum helping out a novice like myself!

[jzwp11]

I have created the tables as you described and I am now wondering if you are suggesting that the casecourses and caseemployment tables which I have originally in my original database should be simply made into the tblEventsDescription you said I could make

The case events deal directly with activities associated with the case, for example you open it, you close it, you reopen it etc.

It sounds like the courses and employment are related to what the person assoicated with the case does, so I believe it is a little different. Is a case only applicable to 1 person? If so, then I think your structure is OK for the casecourses. I, personally, would probably rename your Courses table to CourseTypes and likewise rename the key field as CourseTypeID. I would then carry that name over to the CaseCourse table. Similarly, I would rename the employmentID as employmenttypeID and the table employment as employmenttypes. Of course, that is my personal opinion, but I think changing the names more clearly defines the type of data in the table.

I think maybe the engagements problem I was talking originally about in the post at the very beginning would even be able to go in the Event and EventDescriptions table??

I believe you may be correct since the engagments are events related to the case.

However another thing I am unsure of is, what is going to be in my case table then apart from a CaseID, CaseTitle??

The case will still need to reference the customer of course. I also assume that the field priority will stay in the case table. I would probably spin off the employability barriers and applicable benefits into related tables rather than having the multivalue field (I'm not a fan of the multivalue fields). This assumes that the barriers can apply to many cases/people, and similarly for the benefits.

You would have a table that holds all applicable employability barriers and relate them back. The question is what/who do you relate them back to? Are they really barriers to the case or to the person? You'll have to address that one.

The same would hold for the benefits. Are benefits applicable to the case or the person? I am thinking that in this case they might be related to the case, but you will have to explain further.

As for the disability question you asked me, originally I had only felt the need to make it a yes or no section so that it could be a form for charts etc, but upon consideration and the great light you have shed on my work, I feel now that I should put a disability field in the people table and allow people to provide their disability issues, so yes I think there will be a one to many relationship here, but I'm not sure how best to design it since I can not store a list of disabilities simply because I have not been provided with information the categories of disability that a client could have.

If a person can have more than one disability, then it needs to be in a related table

tblPeopleDisabilities
-pkPeoDisableID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-txtDisability (or a foreign key to a table that holds a list of disabilities)

Of course you may not have a list of disabilities yet, but you could set up in your form to add a new disability to a person and at the same time populate a table that holds a list of disabilities. That takes a little code but is fairly straight forward. But since that is a form question, that should not concern us yet.

With regard the locations, I will only need to deal with about 6 or 7 districts, each district does have towns within them, however I couldnt possibly list of all of these a table and instead the user would need to simply add the town that each client is from to the district.

You could add the ones you encounter most often and then add others as you go similar to how disabilities would be handled as I mentioned above. But it is important to set up the tables correctly now.

[DinoBaggio]

I had been struggling with the benefits and barriers aspect of my database from the very beginning with regard whether they relate to client or case but really I suppose they are to do with the client as it is their barriers and benefits that make up the case, for example if a client is on certain benefits and has certain barriers like homeless when the come to the organisation for help the employee/mentor needs to take these things into consideration when deciding what employment is possible for them and what courses would suit them to improve their employability.

I think I have put you off a little with the field benefitsapplicable, that really was just the name that I gave that field because I couldnt think of a better one. Really the client comes to the organisation on benefits already and with barriers to employment. That is sort of the main point of the database, to build a portfolio of each client, give them a mentor, and the mentor help them into courses and employment, overcoming their barriers and getting them off benefits.

Would you agree therefore that they should relate to the tblPeople then??

As for the disabilities, yes they can have more than one disability so I will follow your instructions again for the design there.

All of this is fantastic jzwp11, thankyou so much for your help. Il get to work on all of it now and post back when I this part done. I am really starting to better understand my database now. really, thanks alot!

[jzwp11]

Would you agree therefore that they should relate to the tblPeople then??

It does sound like the barriers and benefits relate to the person. You will have to decide much like the disabilities whether each barrier/benefit is unique to each person or whether you can set up tables that hold the range of barriers and benefits and then just select those applicable to each person. You may also want a way for the mentors to capture in a related table, activities and/or progress the person makes with respect to each barrier or benefit. A typical structure may look like this


tblPeopleBarriers
-pkPeoBarrierID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkBarrierID foreign key to tblBarriers (or just a text field if barriers are unique to each person)


tblPeopleBarrierProgress
-pkPeoBarrierProgressID primary key, autonumber
-fkPeoBarrierID foreign key to tblPeopleBarriers
-txtProgress
-dteProgress (date field to denote when the progress comment was made)

[DinoBaggio]

Hi again jzwp11,

Thanks for the suggestions again!

I am back to work again today and would like to show you my progress now to see if I have done everything right. So could you please take a look at the attachment below and let me know what you think.

Also I am wondering whether I would be right if I added a PeopleID field to the tblCaseEmployment so that I could track the employer details?? I could add another role in the tblRoles to accomodate for employers and then add the ID to the tblCaseEmployment so that I can relate the employer to the caseemployment??

Would this be correct?

I was also wondering how I would deal with the CaseFunding table I had in my original database. That table was originally designed to keep track of funding that was applied for for each case. The organisation claims set sums of money for each client meeting their engagments, going onto one of the course types and going on to one of the employment types. I was thinking that this table would therefore relate to the events table, or is their a way that I could link or lookup what the client has done and maybe just make a funding table automatically for this based on what criteria the client has specifically met?

Does this make sense??

Many thanks for everything jzwp11, your help is fantastic.

[jzwp11]

Also I am wondering whether I would be right if I added a PeopleID field to the tblCaseEmployment so that I could track the employer details?? I could add another role in the tblRoles to accomodate for employers and then add the ID to the tblCaseEmployment so that I can relate the employer to the caseemployment??

Is the employer you refer to a person or an organization?

I am guessing, but you may need a table to hold the various organizations and relate the employer (the person) to that table and to your caseemployment table

tblOrganizations
-pkOrgID primary key,autonumber
-txtOrganizationName
-txtAddress

tblOrganizationPeople
-pkOrgPeopleID primary key, autonumber
-fkOrgID foreign key to tblOrganizations
-fkPeopleID foreign key to tblPeople

Then tblCaseEmployment would be changed to the following

tblCaseEmployment
-CaseEmploymentID primary key
-EmploymentTypeID foreign key to tblEmploymentType
-CaseID foreign key to tblCases
-JobRefNo
-StartDate
-EndDate
-fkOrgPeopleID foreign key to tblOrganizationPeople
-Sector

I have seen in some databases where people and organizations are combined into 1 table since many of the field could be similar.

tblOrganizations
-pkOrgID primary key, autonumber
-txtPrimaryName (the company name or the last name if an individual)
-txtSecondaryName (the first name of an individual)
-txtAddress


Personally, I have always treated companies/organizations separate from people, but you will have to make that evaluation for your database.

I was also wondering how I would deal with the CaseFunding table I had in my original database. That table was originally designed to keep track of funding that was applied for for each case. The organisation claims set sums of money for each client meeting their engagments, going onto one of the course types and going on to one of the employment types. I was thinking that this table would therefore relate to the events table, or is their a way that I could link or lookup what the client has done and maybe just make a funding table automatically for this based on what criteria the client has specifically met?

I'm not really sure on the funding, but it does sound like it would be related events (applied for funding, receiving funding). You will probably want a currency type field to capture the amount of funding applied for/received.

[DinoBaggio]

thanks again for such a detailed reply,

The employer is simply needed as a reference point for contact with the organisation that a client will go to. When a client leaves our organisation, we still need to keep track of them for a while and so need a point of reference in the organisation they have joined in the event we need to cotnact them.

Thankyou for the above structure ideas, I am just a little confused though on OrganisationPeople table with regard why their is a FK to the tblPeople? Does that relationship mean that I will have the employers details stored in the people table, with a RoleID relating to a rolename 'employer'. And then the OrganisationPeople table will simply contain the PeopleID from the Peopletable?Is this done just to keep things a little more together?

As for the funding, I think i will just treat them as events as you said.

Also, I am very curious as to whether I set up my location table correctly.

So, do you think I am ready now to begin designing forms?

And oncemore, thankyou so much for all your help sir, I really, really appreciate it!

[jzwp11]

Thankyou for the above structure ideas, I am just a little confused though on OrganisationPeople table with regard why their is a FK to the tblPeople? Does that relationship mean that I will have the employers details stored in the people table, with a RoleID relating to a rolename 'employer'?

Since it is an organization or company that employes the specific contact person with which you are working, it is best to relate the person to the organization/company first. So in answer to your question, yes, the employer (i.e. the contact person at the organization) is stored in tblPeople along with their role) and then related to the organization via tblOrganizationPeople. Then with respect to your case person, you relate both the employer contact person and the organization/company by using fkOrgPeopleID in the caseemployment table.

My apologies, I did not look closely at the location table.

I would probably have a table of cities/town and a table of districts. Assuming that a district has many cities (one-to-many relationship) and that a city name is only in 1 district (may or may not be the case, you will have to verify) the you would have this structure:

tblCities
-pkCityID primary key, autonumber
-fkDistrictID
-txtCityName


tblDistricts
-pkDistrictID primary key, autonumber
-txtDistrictName
-txtCountry

You could go further with the country but if you are only dealing with a couple then it is probably not necessary to normalize further.

Your tblPeopleLocations would then reference fkCityID rather than LocationID (when you specify the city, you get the district)

[DinoBaggio]

perfect jzwp11,

Thankyou so much for all your help. Do you think my design is ready for forms etc to made then. I think with table structures now bascially everything is accomodated for so am I ready for form design?

Thanks for everything!!!!!!