Results 1 to 5 of 5
  1. #1
    Illana is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    5

    Question First & Last Name separately or keep it as a one Full Name field

    Hi All


    Hope you will be keen on sharing your experience and suggest what is the best practice in recording names.


    I have currently [First Name] and [Last Name] separately in tblClients and use concatenate query to get a Full_Name.
    In other table where we record service provided to clients I use combo box with lookup to get the Full_Name and record service provided to particular client.




    I feel that often restricts functionality as there is no direct relationship between those two tables only via query(e.g. can't create one-to-many between tblClients and tblActions). I'm trying to work out if I should stop splitting name in Clients table. I'm only afraid this can cause other issues, like not being able to sort clients by their surname, etc.


    Perhaps there is an easy solution to that, but I'm lacking imagination and knowledge here :-/ Hope you can help.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you should be using autonumber primary keys and linking on that, not on names - although I hear what you say about there being no link between clients and actions

    As far as whether to keep names split between first and last names, that depends on how you search or list names

    if not split and you search or want to list alphabetically then Fred Smith will come before Juliet Bravo - and if you use ...Like '*Smith' then you are not using indexing so your search will be sequential and therefore slow.

    if split, you have more flexibility for searching and can list alphabetically e.g.

    Bravo, Juliet
    Smith, Fred

  3. #3
    Illana is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    5
    Thanks for your reply Ajax. You make me quickly realise, that I definitely need split names as we look for clients' details based on their surname/to see whole families.

    However, that leaves me with question how to record Actions and how to set up relationships.
    I would like to create a form to view Client's name and in a sub-form all Actions delivered to that particular client.
    I reckon, I need one-to-many but can't see a way of linking it together.
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    put a clientID field in your action table and remove your concatenated field. Then just link to client_information table. You don't need the concatenate_client table from what I can see.

    Also looks like the same can be done for staff

    Also, be consistent with your naming convention - sometimes you use underscore, sometimes you don't, sometimes you have spaces, sometimes you don't. If you are using spaces because that is what the user will see and it looks more 'natural', use the caption property in the field properties - that is what it is for.

  5. #5
    Illana is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    5
    Perfect! Ajax you may laugh, it crossed my mind what you have suggested but was afraid to do it, as it is too simple to be true.
    It works! and have First & Last Name as a display :-)

    Oh consistency... initially, when setting db up, I had no clue what I was doing but completely agree with you - must change it and use caption option properly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-17-2016, 06:48 PM
  2. Email Individual Reports separately
    By vickster3659 in forum Programming
    Replies: 29
    Last Post: 09-21-2015, 12:29 PM
  3. See full field name in a table
    By crobaseball in forum Access
    Replies: 4
    Last Post: 05-05-2015, 01:03 PM
  4. Replies: 12
    Last Post: 04-17-2014, 06:54 PM
  5. Full Name in field
    By Bakar in forum Access
    Replies: 1
    Last Post: 01-17-2011, 05:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums