Form

[RTorres899]

I am building my first database and having problems with populating fields in a subform. I have created a table with client that has certain fields. I also created another field that has employees and activities with dates. I am trying to link the client with the employees and activities with dates. Can someone assist please?

Thank you,

 

[CJ_London]

Please can you clarify

I also created another field that has employees and activities with dates

Do you mean a table? Is this one table for employees and another table for activities?

Suggest you post your current tables with field names and types so we can help you

 

[RTorres899]

CJ,

Good morning. Yes, I meant to say table. A table for activities, employees and cases.

I have attached them as excel sheets. Basically, I am trying to build a database where I can enter my cases which will be in a form but in a subform have all the activities listed and dates. Of course, one of those activities days would be an alert that I will address later.

If you are able to assist me with database, I will be grateful. I have attached the database to see how bad I screwed things up.

I appreciate the help.

Raymond

 

[CJ_London]

I'm about to go away and busdy clearing my desk but I can give you some pointers.

I've had a look at your db and spreadsheets and have the following comments:

1. Try to avoid using spaces and non alpha numeric letters (such as *:-\/#) in your field and table names - they all have other meanings and can cause problems when writing queries.
2. use field names which are relevant - e.g. Ackgmt Letter Reminder is actually a date field
3. Avoid using lookups in your tables (e.g. Attorney Assigned), it will only cause grief in the future and potentially can slow the performance of the database. Users should not be looking at tables/queries directly, so limit the lookups to forms and reports. Note that removing them now will not affect your existing forms and reports. Having said that, I might use a value lookup where there is a) a small number of options, b) it is single column and c) they will rarely change - such as your loan types field in the cases table.
4. not sure on the legal processes, but the way you have set up the cases table only allows for one set of events - what if you receive more than one set of documents, have miltiple followups with the client etc? I presume this is to do with your event type table?
5. Attorney table - no need for the full names field - this can be calculated when required -as you would do for the defendant name in the cases table


Turning now to your subform

What you are missing in your events table is a link to your cases table - you need to add a field (call it CaseID) of type long which is populated with the caseID of the case the event relates to