Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2014
    Posts
    6

    Unanswered: Major noobie question - combining first and last names in form

    I'm new to Access, so bear with me, please.

    I have one table with thousands of names. The first field is ContributorInfoT, where everyone receives their Autonumber ID. Every person has the ability in our workplace to complete a Hazard Elimination card, which contains things like Date, Time, Name, Company, etc. I have created a separate table called HazardEliminationT, which also has an Autonumber field, just because.

    Both of my tables have fields for FirstName and LastName. Is this a good idea, first of all?

    Secondly, I'd like to create a form wherein I can enter the Hazard Elimination card information in to HazardEliminationT. The thing is, I need to be able to click in the Name box on my form and have a joined First and Last name (which is pulled from ContributorInfoT. I just can't for the life of me figure it out. I'd like it to autocomplete.

    Any thoughts on this?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    store the first & lastname as separate columns (values)
    they a\re easy enough to merge back into one 'value' (but dont' STORE that merged value.

    why store as separate elements?
    well its easy enough to create a single compound value
    soemtimes you may want to show data as forename lastname, soem,times the toher way round. ferisntance you may want a report in surname order, but dispaly the names in the report as forename, surname. if you store it as a compound value it can be tricky, unless say you store as surname, forename and then that measn you can't display it (easily as humans would recognise as forenames surname...
    ..y'get the picture

    in a combo box set the orwsource to be the person ID, surname & "; " & forename
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2014
    Posts
    6
    Thanks for your help

    I'm still not sure it's working like I need it to.

    If I want to enter a Hazard Elimination card into HazardEliminationT, I want to go into my form and then click in the Name box.. If I type John Doe, I want it to pull it from my two name fields in ContributorT and auto populate while I'm typing. Is this possible? Seems that it can find the first name, but not the last name.

  4. #4
    Join Date
    Nov 2014
    Posts
    6
    I think I figured it out.

    I used a query and edited the field to read: Expr1: ContributorInfoT.[FirstName] & " " & [LastName] and it seems to be working.

    Thanks for your help!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a combo box to select the person
    a combo box is 'the' GUI way of selecting a known value.
    in the cpombo box you have two columns (the person ID and the persons name)
    the ID is hidden, the name isn't. the reason for usign a combo box is so that you can type in soemthing and the combo box will automatically search through its contents to find the closest values.
    to do this I'd suggest 1st, create a query based on your persosn table
    this query should have the person ID and a composite of lastname and forename as the second value. drag the person ID to the first column, drag the surname to the next column. then edit the second column so that
    Code:
    'PersonsName': lastname & "; " & forename
    ..assuming that lastname and forename are the columns in your persosn table
    set the sort by to ascendign of this second column
    save the query
    edit your form
    put a combo box on the form, use the combo box wizard and specify the query you've just saved.

    store the person ID in your HazardEliminationT NOT the persons name

    decalre a RI realtionship between HazardEliminationT and your psersons table


    before ytou proceed any further read about normalisation. the fundamentals are you don't store the same piece of information twice, don't have repeating information in a table. in this case have a table for persosn, whose primary key os the foreign key in your other tabels. whenever you need to identify a person in any other tabel EXCEPT the person table identify them as the ID not by name. why?

    well its bad design. think on this what happens if sya one of the persosn changes their surname... do you have to edit every single reference to that person in all tables and change it, or do you just change the name in the persons table
    computers are thick as pig (*&^. they can't tell the similarity between JOHN EDWARD SMITH and John E SMITH (or John e smith)... let alone typos.
    have a look at these (they are plenty of others sources out there)
    http://r937.com/relational.html
    http://www.tonymarston.net/php-mysql...se-design.html
    http://www.informationqualitysolutio...tionPoster.pdf
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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