Database is TOO slow

[jdraw]

I took a quick look at the latest database's relationships.
??Are Notes associated with Case or Task? I would have thought a Case can have 1 or more Tasks, and a Task can have 0 or many Notes.

This still lets related notes to be rolled up with Case.

Also noticed you no longer have a Region associated with a Task or Case.

 

[Badnapp]

Good question jdraw. Notes are associated with individual tasks, where many tasks can be tied to the same case. Therefore, a task can only have one note, but a case could have many tasks each with one note. The notes are only used to further differentiate tasks if the clerks work on a similar task over the course of the week for the same case. So notes would be directly associated to tasks.

On this note, I'm wondering how in the future I could add a "continue case" button, so that if the clerks are working on different tasks for the same case all week the "AssignedBy, DivisionofLaw, Subcategory, and CaseName" combo boxes would all be filled in, and they would just have to change the type of work and task notes and hours. But that's a question that can wait.

For now, I have two databases and I'm not entirely sure which one is better to work with lol. The first .zip that I posted is my first attempt at normalizing the data. The relationships make sense to me, but when I get into the forms there is a lot of work to do re: setting tbltasks as the new recordsource and such and I am having a lot of trouble stumbling through.

The second .zip that I posted is a combination of the above + access 2010's table analyzer. One of the main differences is that it uses a query to pull together all of the information and displays what used to be my table "tasks" as a query. I feel like it is rather efficient because everything like forms and reports etc still function perfectly, but there are a couple of things I notice. For one like you mentioned, by default the region's were associated to the clerks table. Also, Access created a table the exact same as "tblUser" and I can't figure out why. It appears to be almost the exact same table. I'm trying to iron out these little wrinkles in the meantime.

 

[Badnapp]

Still waiting to hear from my clerk re: speed

 

[jdraw]

Designers/programmers often use queries as record source of forms. This can be an efficiency thing also.

Consider a table that has 50 fields. If a specific form and activity only involved 6 fields, better to base the record source on a query involving only those 6 fields. No need to move the other 44 fields.

If you are working on a form that is specific to an individual item, then use a query to narrow the records down to the one you want.
There are forms where a multiple table query serves as record source.
You're sort of in the worst position at the moment---many of the questions you and I are dealing with relate to specifications (what should the database design include); but you have an operational database to manage. This is how the whole concept of Version 1, version 2 of applications get subdivided.


Any way let's see how the speed goes.

 

[Badnapp]

Well, yes and no as far as having an operational database. The clerks are all running "ClerksDatabase v1.1" which I distributed to them BEFORE I even started this thread. As far as they are aware, nothing has been altered.

On my personal C:\, I have about 15 versions right now in various stages that I sorted out this morning. 2/3 of them are old versions that I'm hanging onto in order to track progress. I have a back up of the operational database, plus separate backups (and backups of the backup lol) of the front and back end.

The two databases that I have in my "LastestDev" folder are the two that I uploaded in .zip form today. One uses the tbltasks that we created in order to pull the information together but there are a couple of things to clean up (TaskDetails and TaskList forms broken etc). The other database is the one that I ran the Table Analyzer tool on. That one runs like lightning and has converted the table "Tasks" into a Query "Tasks." There are a few points of confusion for me, for example I have duplicates for "Division of Law" which seems odd to me because the clerks should have had to select that value from a combo box. I must have had the property set to allow edits, because there are extra records for "NatureofWork" and "DivisionofLaw" and "Subcategories" that were not part of the original tables. Also I'm still a little bit confused about the relationship of Regions to other tables, so that's next on my plate I think.

 

[Badnapp]

The clerk that I chose as a test subject finally got around to testing the new front end and I want to share her response with you:

"I gave it a go late yesterday afternoon, but didn’t have time to enter all of my report, so hadn’t gotten back to you yet. I will say though that just from the entries I did enter, I was able to do so far more quickly. There wasn’t as significant of a delay as I tried to move from one entry to the other. In fact, the delay was essentially eliminated!

I do think that because it is not possible to enter a task that is ongoing over a number of days, this slows things down a bit as instead duplicate entries are necessary......."

So on behalf of me and the clerks thank you for your work so far!! I've had my fingers crossed for two days, my knuckles are quite sore, but I'm thrilled to announce that we may have solved this lag issue.

On my plate next is to try and brainstorm a way to allow for ongoing tasks. One theoretically simple solution that crossed my mind is to add a button to the form they use to enter task details. The button would do two things, save the record on the form and create a new record BUT the values in the combo boxes would not change. This way the clerk has created a new record but only has to change the values that are different (perhaps nature of work, notes, or casename).

The reason they are requesting this is that often times they will work on the same Case all week - ie court mon, memo tues, research wed, court thurs, emails and memos fri. I definitely think it is a feasible request, but as you mentioned to me earlier Jdraw it is very important to consider specifications BEFORE creating all of the tables and relationships (which I did not even understand fully until I encountered you lol) because at this point, it isn't as simple a request. Regardless I'm in good spirits and confident that I can master this program yet. Today is a grrrrreat day.

 

[jdraw]

Good stuff, glad to hear it.

Now, step back --you're still refining the original requirements --don't jump into How until you have documented and confirmed WHAT is needed.

I do think that because it is not possible to enter a task that is ongoing over a number of days, this slows things down a bit as instead duplicate entries are necessary......."

Perhaps Tasks should be reviewed to see if 1 task goes on for multiple days or not. If you want people/clerks to record Daily activity, then whatever that/those units of work are should be identified/defined slightly differently.
There are Tasks that are ongoing and they have " an effort" sort of measure.

eg : laid bricks for 6 hours
-stacked lumber for 5 hours
I don't know if this applies but consider it and discard from a point of (it doesn't work that way) if applicable.

If someone is working on an assigned case and they're doing research or reading as a Task, I could see that task taking more than 1 day. If that's reasonable, how would the boss want it reported? Once you find that out, go to design (with options perhaps).

Anyway, use your thinking time to make sure "the evolving requirements" are understood --- don't get hung up on Access per se.

Glad to hear there is progress.