Link to home
Start Free TrialLog in
Avatar of verpit
verpitFlag for United States of America

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
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

I don't think you'll be able to automate this, unless you have been 100% diligent in always using the FULL table.field style of definition, and I'm betting you haven't (most people don't).

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.
May I ask what is wrong with having the same name in two different tables?
name = field name
Avatar of verpit

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 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.
Avatar of verpit

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
Avatar of GRayL
GRayL
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
<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.EmployeeTakingOrderID

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>.
Avatar of verpit

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??
Avatar of verpit

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.
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.
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

Open in new window

Avatar of verpit

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.

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?
Avatar of verpit

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.
Avatar of verpit

ASKER

closed in interest of clarity