Separating first initial and last name into separate fields and then relating them back.

[PilotHog]

I have a single field in a table that has existing data with names in the form of "J. Bloggs". What I would like to do is create a new table that has two fields, "FirstName" and "LastName". Initially the new table will be populated by the data in the existing table and so FirstName will only have the initial rather than a full name. In the future I would then like to update the initials in the FirstName field to the actual first name of each person and have that update the original table.



So far I have created the new table with the two fields and populated them with the data from the original table. My problem now is that I want to create a relationship that links the combined FirstName LastName fields with the single field of the original table.

Can this be done? If so, how?

[davegri]

I would have added the FirstName and LastName fields to the original table. Can you do that?
If the original FullName is always (no exceptions) in the form of N. NNNNNNN, then a one time update query can be run against the table to populate the new fields.

[ArviLaanemets]

Did you copy names too into new table? Because the simpliest would be to add an empty FirstName field to your old table, rename the Name field as LastName, and then to run an update query to fill the Firstname column with initials form LastName, and to replace entries in LastName field with last names without initial an dot from same column.

Something like (on fly, and it assumes you have always a space after dot, otherwise you have to search for lastname also the dot, and then Trim() the whole expression)

UPDATE YourTable (FirstName, Lastname)
SET Firstname = Iif(InStr(".",LastName)>0,Left(LastName,InStr(".",LastName)),Null), LastName = Iif(InStr(".",LastName)>0,Right(LastName,Len(LastName) - InStr(" ",LastName)),LastName)

[PilotHog]

Thanks for the responses. I want a separate table to hold a list of contacts. i'm starting with a bunch of old data and have used a query to populate the table of contacts. I guess I can split the names into initial/last name in the original table. I would want the combined initial/last name to be linked with the table of contacts though, can I do that?

[ArviLaanemets]

Of-course you can. But you have to decide, which approach is best for your need.

P.e. you have a persons table like tblPersons: PersonID, FirstName, LastName, ...

When every person has only 1 contact of every contact type (e-mail address, phone number, ...), and you are sure you will never have several contacts of same type per person, then you can have contacts directly in tblPersons:
tblPersons: PersonID, FirstName, LastName, PhoneNo, EMail, ...

Otherwise you must have a separate contacts table:
tblContacts: ContactID, PersonID, ContactType, ContactText, [ContactGroup], [IsActive], ...
It assumes that all those contacts can be stored in single field ContactText as text. So addresses don't qualify - you need a separate table for them.
ContactType determines, is this a phone number, or e-mail address, or fax number, or post box number, or whatever)
Optional ContactGroup you use, when you want to differ p.e. between work and home contacts.
Optional IsActive you use, when you want to preserve obsolete contacts in your database, but at same time you want to exclude them when selecting a contact p.e. for some report.

[PilotHog]

Sorry, "Contacts" was a poor choice of words, it's really just people and I was referring to the people table.

The primary table is a pilot logbook with date of flight, aircraft, route, crew, flight time, plus a few other bits and pieces. The existing data dates back 25 years. The current goal was to normalise the crew names by sending them to a separate "people" table and to then relate that table back to the existing field in the logbook.

Reading your responses I think I will separate the crew names in the logbook to first name / last name as that seems easier to relate to a people table with separate first / last name fields. I figure I can combine first and last names to one column in any reports I do.

I'm also unclear exactly how to do it, but suspect the relationship needs to be many to many as each flight can have many crew and each crew member can fly on many flights.

[orange]

Further to the posts you have received, I suggest you document (write down) exactly what you are trying to do --in simple terms.
It seems you are trying to solve something, for some reason - but neither is clear to readers. As with most issues, get a solid handle on the requirements, gather and analyze the facts. Create a paper model and test it against your requirements using some sample data and scenarios. Ensure you can access the data you need to satisfy the requirement. Now you have a model that meets the requirement --- a blueprint for the database design.

I would not recommend that you work with a physical Access database until you have a clear statement of requirements.
Mock up a sample if a "fix/workaround" to an existing database is needed. Get the mockup working before jumping in to an operational database.

You know your situation better than readers. You have been given various pieces of advice. Re-read them; and build a plan based on your requirement.

Good luck with your project.

[ArviLaanemets]

Reading your responses I think I will separate the crew names in the logbook to first name / last name as that seems easier to relate to a people table with separate first / last name fields.

Names make a poor identifier.
1. There easily can be several people with same forename and last name (a classic example for such cases is "John Smidth");
2. The are probably women in crew, and they tend to get married ant take husband's last name;
3. Sometimes people don't like his her name, and change it.

So you need an Employees table with some unique ID for this employee, generated when employed, remaining unchanged for all time, and never used for any other employee hired.
And having all crew members listed in logbook is also not the best idea. Consider the data structure as:
tblFlights: FlightiID, AircraftID, CrewID, FlightDate, FlightDestination, ...
tblAircrafts: AircraftID, ...
tblCrews: CrewID, FlightID (a new crew is defined for every flight), ...
tblCrewEmployees: CrewEmployeeID, CrewID, EmployeeID, ...
tblEmployees: EmployeeID, FirstName, LastName, ...

And then you have a flight log table
tblFlightLog: FlightLogID, FlightID, ... (various log details). All info about flight and crew is included with FlightID.

[PilotHog]

Thanks for those suggestions ArviLaanemets.

The ID for names will just have to be an auto number. These are people going back 25 years to my first instructor. They don't belong to any one company and some consist of just a surname. Current crew have an employee number but the database has to work for all, not just the most recent.

I see how splitting crews to a separate table is ideal and will work it that way. Unfortunately this is something I am doing with a little bit of spare time around family and work so it's a slow process. I'd just become a bit frustrated that each of the electronic logbook programs I had tried didn't quite do what I wanted (for example, there was one that was perfect in every respect except it had a 250 character limit in a field for notes which meant it was not possible to import all of my existing data.)

Anyway. Thanks all for your help. I will keep plugging away at it.

[orange]

PilotHog,

I think you would do yourself considerable good by working through a tutorial or 2 in addition to
"plugging away at it".

For practical experience / learning spend 45-60 minutes and work through one or two of these tutorials from RogersAccessLibrary.
Class info system
Catering Business
Widgets

Good luck.

[PilotHog]

Thanks. I've been reading through Roger's stuff.