verpit
asked on
Help renaming a field in a table
The two (2) identically named table fields that need renaming are ActivationDate. I have used the Find And Replace tool often by Rick Fisher. It's wonderful but can't help me here due to the two names being identical. Once I have renamed them so that I can tell them apart, and removed the dependencies as needed, I can potentially rid myself of one of them.
The goal here is simply to rename one of them without harming the dependencies.
Either
1. The field ActivationDate in tblAllTasksLastActivity should be renamed to LastActOrDeactDT. OR
2. The field ActivationDate in tblAllTasksEachActivity should be renamed to EachActDeactDate
The database is house at http://www.dropio.com/tasksdb
The goal here is simply to rename one of them without harming the dependencies.
Either
1. The field ActivationDate in tblAllTasksLastActivity should be renamed to LastActOrDeactDT. OR
2. The field ActivationDate in tblAllTasksEachActivity should be renamed to EachActDeactDate
The database is house at http://www.dropio.com/tasksdb
May I ask what is wrong with having the same name in two different tables?
name = field name
ASKER
I've been told I should delete one. since they have dependencies I must sort out, I needed to rename them first so that I could better sort that out.
>I've been told I should delete one.<
I don't follow that line of reasoning. There are a zillion cases where there identical field names in different tables - EmpID for example - would exist in tblEmployees, tblSalaries, tblWeeklyLabor, etc. It is the table/field combination that makes up part of a dependency - not just the name. Every time you reference a field in a table in a query, form, report, or code, it should be with the table name or alias and field name combination not just the field name. Where is your advisor coming from?
I don't follow that line of reasoning. There are a zillion cases where there identical field names in different tables - EmpID for example - would exist in tblEmployees, tblSalaries, tblWeeklyLabor, etc. It is the table/field combination that makes up part of a dependency - not just the name. Every time you reference a field in a table in a query, form, report, or code, it should be with the table name or alias and field name combination not just the field name. Where is your advisor coming from?
I would tend to agree with GRayL ... I have common fields in many tables, and so long as it's clear which one I'm working with then I don't see the need change them. It can be a little confusing at times, especially when you build a query with multiple tables, but so long as you alias your columns in that query you should be fine.
ASKER
Actually Gray, I thought it was you in a previous thread. You laid out some specifics re: cleanup that I obviously didn't get a good grasp on. I'm attempting some of that.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So you're now pointing the gun at me! ;-)
In the previous question, I think I said change the field name to ActDeactDT. Then in the query, where you generate separate activation and de-activation fields in the same record, rename then to ActivateDT and DeactivateDT. But my recall may be wrong.
In the previous question, I think I said change the field name to ActDeactDT. Then in the query, where you generate separate activation and de-activation fields in the same record, rename then to ActivateDT and DeactivateDT. But my recall may be wrong.
<But I say even more confusing in a multi-table query where you now have to remember how you refer to employee number in tblSalaries, because it is a different name in tblEmployees as it would be in all the other tables carrying it.>
Right ... when you can't remember that tEmployees.EmployeeID is supposed to be related to tOrder.EmpID ... or even worse, tOrder.SlsPsnID (SalesPersonID ... and don't laugh, I ran into that one just a few weeks back). I'd much rather see a relationship like:
tEmployee.EmployeeID = tOrder.EmployeeID
rather than
tEmployee.EmployeeID = tOrder.EmployeeTakingOrder ID
The only time I find like named fields to be troublesome is when you get to REALLY generic names, like tEmployees.dDate vs tCustomers.dDate in the same query. I look back at some of my early databases and cringe when I think of how much work I've caused myself over the years <g>.
Right ... when you can't remember that tEmployees.EmployeeID is supposed to be related to tOrder.EmpID ... or even worse, tOrder.SlsPsnID (SalesPersonID ... and don't laugh, I ran into that one just a few weeks back). I'd much rather see a relationship like:
tEmployee.EmployeeID = tOrder.EmployeeID
rather than
tEmployee.EmployeeID = tOrder.EmployeeTakingOrder
The only time I find like named fields to be troublesome is when you get to REALLY generic names, like tEmployees.dDate vs tCustomers.dDate in the same query. I look back at some of my early databases and cringe when I think of how much work I've caused myself over the years <g>.
ASKER
Anyone need a headache? I have one at www.dropio.com/tasksdb where the relationships need fixing in the worst way. I'm too scared to try. I'm sure I'll mess something up with my experience thus far.
Let's get one thing clear. I thought the first record in table tblAllTasksEachActivity would indicate that a task had been activated. The next record for that task would indicate it was deactivated. The next, activated, the next deactivated, for the same taskID, etc. We finally get down to a point where a task has been opened, but not deactivated, in which case the deactivation datetime is deemed to be Now(). When I look at that table it is full of errors. The order the records indicating the tasks and their respective activation and deactivation is not important. The ORDER BY will take care of that.
That is why I re-named the date time ActDeactDT, and the checkbox ActDeact. BTW, I looked at your downloaded mdb, and no relationships have been set??
That is why I re-named the date time ActDeactDT, and the checkbox ActDeact. BTW, I looked at your downloaded mdb, and no relationships have been set??
ASKER
When you say "full of errors" and then that the ORDER BY will take care of it, this is great! It's changed since we spoke in the previously accepted answer.
I'm not even experienced enough to understand the changes you are suggesting or to make them. That's my fault, not yours. I really appreciate any assistance you can provide with it.
I'm not even experienced enough to understand the changes you are suggesting or to make them. That's my fault, not yours. I really appreciate any assistance you can provide with it.
For the same TaskID, there cannot be two checks or two unchecks in the ActDeactfield (you are still calling it Active) in succession. The snippet below shows the table with the task moved to the left column and after highlighting Task and ActDeactDT, I clicked on the Sort Ascending icon on the task bar. It now shows several areas where there are two successive 0's or -1's under Active. If I understand your methodology correctly for each task it should be on,off,on,off, etc. For a given task, there need not be an off - correct?
Can you explain the ActivationDate field? As it matches the date portion of the ActDeactDt field, in my mind it serves not purpose and should be deleted. If you can fix the data so that it shows the proper on/off seqence in the Active field. It will be easy to get the accumulated hours each task has been active - as I showed you in the previous question.
Can you explain the ActivationDate field? As it matches the date portion of the ActDeactDt field, in my mind it serves not purpose and should be deleted. If you can fix the data so that it shows the proper on/off seqence in the Active field. It will be easy to get the accumulated hours each task has been active - as I showed you in the previous question.
Task ActDeactDT Active ActivationDate
Apple 07 Oct 2008 09:00:26 -1 07 Oct 2008
Apple 07 Oct 2008 09:00:30 0 07 Oct 2008
Apple 07 Oct 2008 09:18:39 0 07 Oct 2008
Banana 07 Oct 2008 09:00:30 -1 07 Oct 2008
Banana 07 Oct 2008 09:00:33 0 07 Oct 2008
Banana 07 Oct 2008 09:18:40 0 07 Oct 2008
Banana 07 Oct 2008 09:18:43 -1 07 Oct 2008
Banana 07 Oct 2008 09:18:56 0 07 Oct 2008
Grapefruit 07 Oct 2008 09:00:38 -1 07 Oct 2008
Grapefruit 07 Oct 2008 09:00:41 0 07 Oct 2008
Grapefruit 07 Oct 2008 09:00:53 -1 07 Oct 2008
Grapefruit 07 Oct 2008 09:00:57 0 07 Oct 2008
Grapefruit 07 Oct 2008 09:18:42 0 07 Oct 2008
Orange 07 Oct 2008 09:00:33 -1 07 Oct 2008
Orange 07 Oct 2008 09:00:38 0 07 Oct 2008
Orange 07 Oct 2008 09:00:57 -1 07 Oct 2008
Orange 07 Oct 2008 09:01:04 0 07 Oct 2008
Orange 07 Oct 2008 09:18:45 0 07 Oct 2008
Peach 07 Oct 2008 09:00:44 -1 07 Oct 2008
Peach 07 Oct 2008 09:00:53 0 07 Oct 2008
Peach 07 Oct 2008 09:18:41 0 07 Oct 2008
Pear 07 Oct 2008 09:00:41 -1 07 Oct 2008
Pear 07 Oct 2008 09:00:44 0 07 Oct 2008
Pear 07 Oct 2008 09:01:04 -1 07 Oct 2008
Pear 07 Oct 2008 09:15:48 0 07 Oct 2008
Pear 07 Oct 2008 09:18:19 -1 07 Oct 2008
Pear 07 Oct 2008 09:18:43 0 07 Oct 2008
Pear 07 Oct 2008 09:18:52 0 07 Oct 2008
Pear 07 Oct 2008 09:18:56 -1 07 Oct 2008
Pear 07 Oct 2008 09:19:02 0 07 Oct 2008
ASKER
Thanks for your help GrayL. You wrote "Can you explain the ActivationDate field?"
No I can't. This is what I don't understand and need help with.
What I 'am' able to articulate about the design is from the perspective of what I wanted to accomplish in the frmAddTasks. Any of the items in the tables can be changes as far as I am concerned, so long as the overall 'objective' isn't changed.
OVERALL OBJECTIVE
INPUT INTENDED DESIGN: The form (frmAddTasks)
1. Top section contains items that have not yet been clicked 'done'.
2. Bottom section contains items that have been 'done' but can be 'undone'
3. Clicking on a task and entering a task: Both items have the effect of ensuring the task is 'undone', and the effect of 'reactivating' it from either the top or bottom as if a checkmark was placed in the box.
4. When reactivated, the task moves to the top.
5. Only one task can be active at a time.
6. Activating or reactivating one task through whatever method (click or checkmark), has the effect deactivating the previous task.
OUTPUT INTENDED DESIGN: The form (frmGroupAndTotal)
1. Select date range
2. Populate totals for time spent on each task.
I do not know if this helps explain any of the fields in the table but may give a better overall picture.
No I can't. This is what I don't understand and need help with.
What I 'am' able to articulate about the design is from the perspective of what I wanted to accomplish in the frmAddTasks. Any of the items in the tables can be changes as far as I am concerned, so long as the overall 'objective' isn't changed.
OVERALL OBJECTIVE
INPUT INTENDED DESIGN: The form (frmAddTasks)
1. Top section contains items that have not yet been clicked 'done'.
2. Bottom section contains items that have been 'done' but can be 'undone'
3. Clicking on a task and entering a task: Both items have the effect of ensuring the task is 'undone', and the effect of 'reactivating' it from either the top or bottom as if a checkmark was placed in the box.
4. When reactivated, the task moves to the top.
5. Only one task can be active at a time.
6. Activating or reactivating one task through whatever method (click or checkmark), has the effect deactivating the previous task.
OUTPUT INTENDED DESIGN: The form (frmGroupAndTotal)
1. Select date range
2. Populate totals for time spent on each task.
I do not know if this helps explain any of the fields in the table but may give a better overall picture.
Where ever you use ActivationDate, use DateValue(ActDeactDT) and throw away ActivationDate. You are storing redundant data. Do you see my point about successive on/offs and why there should not be two successive on's or off's in any one task. There should also never be more than one task without an off. In the sample I showed at http:#a22661151 Banana and Pear are the only two tasks entered correctly. The rest have two successive offs (0's). No tasks are active here. Are we on the same page?
ASKER
Yes on the redundant data. I definitely want to fix that. Latest update is at www.dropio.com/tasksdb On the successive on/off's, I am not sure I understand a better option as the way the form behaves is the desired behavior. I don't think you could keep from adding a click or additional step in the form.
ASKER
closed in interest of clarity
You could use F&R to generate dependency information and make the changes to one of the table fields, then use it again to make changes to all other mentions of that field, but I believe that's as far as you can go with it.