Can 1 field in a table grab info from 2 other fields in the same table?

[Wilcox]

Hi There,

I'm new to access and trying to build a simple customer database for my massage business. I am trying to do the following.

Client Name
First Name
Last Name

I want the client name field to auto populate by combining the first name and last name fields. I want to use the client name field in other other tables for reference as I build the rest of the database. Invoicing and medical records.

Please let me know if this makes sense or can be done.

Cheers,
Wilcox

[missinglinq]

This is a calculated field, so you want it to be Unbound. Anytime you need it simply re-calculate it.

In Form Design View, place the textbox for ClientName on the form, then goto Properties and in the Control Source use

=[FirstName] & " " & [LastName]

Welcome to Bytes!

Linq ;0)>

[liimra]

Solution/

You can add a lookup column to the table but this is not the solution. One key to understanding Access/Database and becoming an expert is reading although sometimes it can be very annoying. I understand based on your post that you are new to access and here I will try to mention the basic requirements of the database you need.

The solution is to create a number (autonumber is even easier) field and call it ID. Then you can easily show the full name of the client on any form or report based on his/her ID. The ID will be the primary and unique field in client's table.

Understanding relationships among tables is very important at this stage. As you might know, you may not add everything about clients in one table as this will simply not work. In the following example, I will assume that you just need to invoice clients for clarity purpose. You would have to create one table for storing Clients' info and this will be the base table and another table for invoicing. I think you might need the following information (columns) for the first table:
ID (unique number and you make it primary key), Date Joined, First Name, Last Name, DOB, Category, Gender, etc.. all the necessary information you need, and try to add as much useful criteria as possible as this will be very useful for future need.

Anyways, after you have created this table you will have to create another table for invoicing and the fields can be:

ID (will be linked to ID field in the base table), Date of Invoice, Amount, Description.

Now if you are using Access 2007 as I do, you close both tables and choose Database ---> Relationships (From Menu). Choose --> Show Table from Menu --> and Then add both tables to the view. At this point all what you have to do is to drag ID from Client Info Table (Where this field is the Primary Key - unique number) to the ID field in the other table. Edit relationship window will pop up now. Now tick the three boxes (Enforce Referential Integrity, Cascade Update Related Fields, Cascade Delete Related Fields).

Note that by doing this you can't add an invoice for non existing client (Each client is defined by the unique number called ID).
Now, you can track all invoices given to a specific client through his/her ID. Note that you can add other tables such as medical history..etc in the same way.

Unique ID for each client is a must, but after you created everything correctly you can choose clients based on their names only. There are many methods to do that.

This is just the start and the most important steps. After you understand this, you can move into visualization and you can create forms and reports that will view this information in a useful impressing way.

I hope this will be helpful,
Regards,
Ali