UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Managing Names, Access 2016    
 
   
Spikenaylor
post Jun 2 2018, 01:56 PM
Post#1



Posts: 87
Joined: 8-April 11



I have a table consisting of personnel names

How do you guys manage this type of table

Miss Jane jones

Stored in table as

ID title firstname surname active
100 miss Jane jones true

Gets married

Becomes
Mrs Jane Smith


Is the best method to set active to false

And add new row for new data and set active

Ie

100 miss Jane jones false
135 mrs Jane Smith true

I need historical data to reference miss Jane jones and new data to reference mrs Jane Smith

Thanks for looking and any advice would be greatefully received


Go to the top of the page
 
zaxbat
post Jun 2 2018, 02:05 PM
Post#2



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


I have never had to do this, but if I did I would use a a junction table. It adds one level of indirection to everything you do with names but I don't know a better way. Each person would have one entry in the junction table and then you could have any number of names from the name table pointing to one entry in the junction table.....so the junction table would turn into your name table but would not directly hold the names, rather would only serve as a means to find the names in the actual names table.
Go to the top of the page
 
RJD
post Jun 2 2018, 02:22 PM
Post#3


UtterAccess VIP
Posts: 9,913
Joined: 25-October 10
From: Gulf South USA


Hi: I think using two different IDs to refer to the same person is probably not a good idea. You might consider using a new table of former names, with a new ID (such as an autonumber), the foreign key of the original ID, the old name components and a date when the name changed. Keep the current record up-to-date. You can show this table in a subform on the person form, with all the history there. Now you have the current data plus all the historical data as well, all linked and shown. After all, the name may change more than once. Then any history should be easy to see and to get at for whatever purpose you might have.

Just an idea ...

HTH
Joe
Go to the top of the page
 
projecttoday
post Jun 2 2018, 02:37 PM
Post#4


UtterAccess VIP
Posts: 10,863
Joined: 10-February 04
From: South Charleston, WV


How do you know to pick the true one or the false one in an old report? You could add an effective-date field. Unfortunately, this means search programming for every program that uses this table. A lot of work. Very few systems have this feature. (The 2-table approach would also involve searching every time.)
Go to the top of the page
 
Spikenaylor
post Jun 2 2018, 03:05 PM
Post#5



Posts: 87
Joined: 8-April 11



the personnel table is updated when new person joins or changes name or leaves

The true false field indicates that person is currently active in the company

In another form that records the tasks a person completes contains a combo box listing current active personnel

Having the two ids works for new task data as only the I’d that is current can be selected

Reporting works as both ids will be shown in the report

The only issue will be collating to one person who has changed there name because it won’t link the two ids to the one person
Go to the top of the page
 
zaxbat
post Jun 2 2018, 03:07 PM
Post#6



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Depends on how often this happens. Here is a similar solution. Keep your table the way it is but add an oldname table so you can save off old names there but keep the most current name in the main name table. Most peeps will not have any entry in the oldnames table but some will. It can have the FK back to the mainnames table. So there would be no slowdowns unless you needed to reference the old names. But as long as you do not need that function very often....no problem.

If the app is set up correctly, the actual names are never stored anywhere else but rather only their primary key is used....so everything will keep working as long as you do not need to reference the old name....when you do it is a simple query into the oldnames table joining the primary names key to the oldnames foreign key.
This post has been edited by zaxbat: Jun 2 2018, 03:11 PM
Go to the top of the page
 
projecttoday
post Jun 2 2018, 05:01 PM
Post#7


UtterAccess VIP
Posts: 10,863
Joined: 10-February 04
From: South Charleston, WV


On second thought! I think Joe's idea of a subtable is correct. You have information about a person. You have multiple names of that person. One-to-many. The rest of the information about the person should only be entered and stored once. With the exception of the "current name", a field you could keep in the main table to avoid having to search in every use.
Go to the top of the page
 
RJD
post Jun 2 2018, 05:12 PM
Post#8


UtterAccess VIP
Posts: 9,913
Joined: 25-October 10
From: Gulf South USA


Thanks, Robert. I have used this approach in similar situations with good effect. If the developer puts a command button (to run a procedure) on the main form to transfer the name set to the subtable, with the relevant key (ID) and current date, then the main table can be updated with the new name information. No manual entry required for the subtable and minimum data corrections in the main table. And, yes, of course, your point on the rest of the data - stored in the main table.

An exception to just the names, etc. in the subtable, might be if a history of addresses, etc. is also required. I did this in a charity food pantry application where we wanted to keep some history of addresses as well, for other analyses in the db about servicing areas, client verification, etc.

Regards,
Joe
Go to the top of the page
 
projecttoday
post Jun 2 2018, 05:29 PM
Post#9


UtterAccess VIP
Posts: 10,863
Joined: 10-February 04
From: South Charleston, WV


So if the woman was

Name/Date

Mrs. Mary Johnson/01-01-2018
Mrs. Mary Smith/01-01-2013
Miss Mary Jones/01-01-2009

The above would be the sub table. Then the current name, "Mrs. Mary Johnson" would also appear in the (current) name field in the main table. Maybe breaking the rules, maybe not if you see it as "current name". But you wouldn't have to search every time you need the name, although reports wouldn't be historically accurate without the search.

There could be other fields in the main table that would need their own historical sub table.
Go to the top of the page
 
GroverParkGeorge
post Jun 3 2018, 07:03 AM
Post#10


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

What is the purpose of the database? It may make a difference in how you deal with this?

Are you modeling an personnel type of application wherein you need the total history of each person in whom you have an interest? In other words, does it matter to the way you use the data that you can go back 1 year, 5 years or 10 years and retrieve a snapshot of people at that point in time?

Or, are you modeling an application in which transactional data related to a person is of importance, but only need to have their current identifying information available? I.e. a sales application in which the history of product sales matters, but not what a person's name happened to be at any given time?

A better example of how this can make a difference would be other types of data which can be more volatile, such as phone numbers or addresses. Do you need a complete history of all phone numbers ever used by your people? Or do you only need to be able to call them TODAY?

In short, the right solution depends on what you need to be able to produce from the database application.
Go to the top of the page
 
Jeff B.
post Jun 3 2018, 08:14 AM
Post#11


UtterAccess VIP
Posts: 10,267
Joined: 30-April 10
From: Pacific NorthWet


As George has explained, "how" depends on "what".

More info, please ...
Go to the top of the page
 
projecttoday
post Jun 3 2018, 09:09 AM
Post#12


UtterAccess VIP
Posts: 10,863
Joined: 10-February 04
From: South Charleston, WV


I think we have a pretty good solution here with the sub table. I wanted to add 1 thing, spike: The active field has nothing to do with the name change. They are just 2 different things. The name history is about the person. Whether or not the person is active is about the person. But neither has any bearing on the other. (Of course you're not likely to track name changes of those who have left the company.) So trying to get the current name by filtering on active could invite problems. Just thought I'd mention that.

I recommend using an inactive date rather than a flag so you'll know when they went inactive.
Go to the top of the page
 
Spikenaylor
post Jun 6 2018, 03:14 AM
Post#13



Posts: 87
Joined: 8-April 11



many thanks for all the advice

Jane Jones is stored in the employee table, in my case the active tick is set as she currently works for the company

the main data entry form allows the user to pick an employee to record whatever task against them, in the combobox, only the personnel that are active are available

When Jane Jones changes her name to Jane Smith

I could:
1. update the employee table same record from Jane Jones to Jane Smith. (All historical data will now reference Jane Smith)
2. create a new record for Jane Smith set active and set Jane Jones to unactive (All historical data will reference Jane Jones, new data will reference Jane Smith)
3. store the current name in the data entry form, this goes against the rules doesn't it


Entering new data is not a problem as that is why I am using the Active option, this does not allow older names or people are are unactive - Ie no longer working here or name has changed to be able to be selected in the data entry form

viewing the date is not a problem as all data will be shown, the active option is not used when viewing all data, we want to see all records for all people regardless of if they no longer work here

the problem happens when you want to look at the data for Jane Smith, but also include all records for Jane Jones, as they are the same person

hope this makes sense



Go to the top of the page
 
projecttoday
post Jun 6 2018, 06:22 AM
Post#14


UtterAccess VIP
Posts: 10,863
Joined: 10-February 04
From: South Charleston, WV


You're not including the best solution among your choices: a subtable with a history of the names (see Joe's post no. 3). This will allow reports to use whatever name was in effect at the time of the report.
Go to the top of the page
 
SassyTX
post Jul 18 2019, 05:18 PM
Post#15



Posts: 5
Joined: 17-July 19



Speaking from personal experience, two separate entries does not work well. Our company uses first two letters of last name+last 4 of SSN as our "Badge #". Over 25 years ago, I started at the company as say BR1234. After a few years, I divorced, became AB1234. A few years later, I remarried, became SO1234. An employment history search will only show one of those three if searching by Badge #. As a result, they have returned me to AB1234 no matter what my last name is. In addition, I have a coworker who has also changed names several times AND has left and returned to the company several times. Again, using the Badge#, we could not tell her true employment history and therefore eligibility for certain benefits. To mucky up the water more, we changed accounting systems in 1999 which caused info to be saved "prior to 1999" and "after 1999". Our workers average 29 years with the company.

Don't ever use this formula (first two letters of last name+last 4 of SSN) as an identifier as payroll has also encountered multiple employees with the same first two letters of last name and same last 4 of SSN. We've even recently run into a new scenario - a resident alien working for years with provided SSN - no problem. Employee gained citizenship and someone in the system thought it would be a great idea to change their SSN. Not only that, the employee comes from a culture that uses both parent's last names. As resident alien - ID card said "John Smith Jones", as citizen - ID Card now reads "John Jones Smith".

I don't have an answer on how to resolve other than to only use ONE record. I have a "Notes" field to keep track of the changes.

Good luck!
Go to the top of the page
 
WildBird
post Jul 18 2019, 08:24 PM
Post#16


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


I would keep the same ID for each person. And as others have said, have another table with the ID and any new name etc in that table, along with date stamp etc.

As someone else said, DO NOT use a hybrid ID like a part of SSN and initials or whatever. No guarantee of that being unique. Current project, for emergency contacts, we were told to use the first 3 letters of the relationship type, and the employees ID. Idiots completely failed to think that a person may have 2 sons, 2 daughters, 2 mothers, 2 fathers....

Anyway, things liek this, have a good think about it, as whatever you come up with now, is hard to change later.

Good luck!

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
MadPiet
post Jul 18 2019, 09:21 PM
Post#17



Posts: 3,206
Joined: 27-February 09



Maybe this article will help:
https://en.wikipedia.org/wiki/Slowly_changi..._2:_add_new_row

Slowly Changing Dimensions are normally in data warehouses (star schema database). But anyway, the basic idea is that each Customer record will have its own unique ID as well as a code/ID field (a foreign key of sorts) that points at the original version of the customer in the same table. It takes a bit to get used to, but once you understand that the incrementing ID for the same customer is just pointing at "Customer at state [x]", then it makes more sense. It lets you group customers by their attribute values at a point in time, e.g., when a customer lived in CA, his sales from that time period belong in the CA sales aggregate. When he moves to another state, his sales belong to a new state's sales. It's a bit mind bending sometimes. but if you're interested, look up Type 2 Slowly Changing Dimensions and maybe Ralph Kimball (he wrote The Data Warehouse Toolkit).
Go to the top of the page
 
gemmathehusky
post Jul 19 2019, 07:16 AM
Post#18


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


I think I would be inclined to have an extra memo field in the table for "previous names", rather than go to the bother of having a second table.
I would probably store the old names as Miss;Jill;Jones;15/07/2018 (date it was changed);Fred (who changed it);Married (reason)

Using a standard set of fields within the memo field, and a standard separator makes it amenable to displaying in a combo box/list box, by just making the field the row source.



--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th August 2019 - 11:40 PM