Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2007
    Posts
    17

    Unanswered: Taking two columns combining them into a third

    I have a database that i inhereted from a co-worker that was supposed to be an access guru. After many troubling hours of fixing the database, making good relations, and general "fixing" here is where I am.

    The database has a record of part - time employess, which has a field for Last Name, and a field for First Name. Great for adding data via forms, but when it comes to associating this record to say a job they have been assigned to it instantly becomes complicated, in my mind, because now I have to associate two fields in every table (last name and first name) instead of just one (name).

    What I want to do first is take all existing records and take the last name fields and first name fields and put them in a filed called name, in this format:

    Hicks, Jasen

    Thoughts?

    Secondly, is when the user is added or updated using the form I want it to update the field name, based on the seperate inputs from a last name input box and a first name input box.

    Too hard? Not worth it? Better ideas on how this works? I am not an access guru by anymeans and my work with PHP and MySQL makes me dangerous more than good.

    Thanks everyone!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    "Secondly" is exactly the problem.
    there are no triggers in Access so you end up chasing your tail trying to capture every possible edit to First/Last and then update your derived field. sooner or later your derived field will be out of sync with the First/Last fields

    the basic principle is not to store derived data unless you have a very good reason. your's does not seem to be compelling enough to justify the complication & risk & overhead of storing derived data.

    see if you can achieve the result you want for presentation (screens, reports, etc) directly from the existing data using a query:

    SELECT LastName & ", " & FirstName AS FullName, thisField, otherField FROM thatTable ORDER BY LastName, FirstName

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2007
    Posts
    17
    So it is ok then to have two reference between table 1 (part time workers) and table 2 (work assignments) based on last name and first name with no issues?

    Ill keep it going the way it currently is setup, it seems to be working for now. Thanks for the help/insight. This project is a big educational procss for me, so i anticipate many, many more questions for you expert types!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    what happens if you have 2 employees with the same name..?

    What you want to do is add a primary key to the employee table, which would then become your foreign key in the assignments table. The primary key can even be an arbitrary integer (access has autonumbers which may be just right for you here).

    Then you can change "John Smith" to "Captain Andrews" with no data integrity problems what so ever
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24
    Quote Originally Posted by georgev
    what happens if you have 2 employees with the same name..?

    What you want to do is add a primary key to the employee table, which would then become your foreign key in the assignments table. The primary key can even be an arbitrary integer (access has autonumbers which may be just right for you here).

    Then you can change "John Smith" to "Captain Andrews" with no data integrity problems what so ever
    OMG, that brings back horrible memories...

    One of the first databases I ever created back when I was a platoon leader in the Army... It was a pretty nifty thing... had separate tables for weapons qualifications, awards, health data, etc. etc...

    Long story short, I had the Fname, Lname as the PK... It worked fine.... until folks got married or divorced... Took me a while to figure out why all of the subforms & reports stopped working once I changed their last names... Ugh...

    Yes, I learned my lesson on that one!

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So what is uniquely identifying your employee records now..?
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2007
    Posts
    17
    For the employee dBase just the primary key which is an auto number.

  8. #8
    Join Date
    Dec 2007
    Posts
    17
    Friz... funny story... this dbase is actually for our reservist support in the Navy for our office!

    <-- LT Submarine Nuke

  9. #9
    Join Date
    Dec 2007
    Posts
    5

    Lt Hicks

    Check your PM I am willing to walk you through it over the phone. I am admin for Division on Camp Lejeune.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •