[Stuartjacks]

Is it possible to automatically fill a field in accesss 2007 from two earlier fields.
For example if the [Surname] field has "Smith" in it and the [FirstName] field has "John" in it can access automatically fill the [FullName] field with "John Smith"
I'm very new to Access and can't seem to see if this is possible or not

[ScottGem]

Yes its possible, but its not something you want to do. Before I explain, you need to understand some terms and concepts. A field is a logical division of a record in a table that holds a discrete piece of data. When designing a table you need to create fields that store the smallest piece of data. So a person's name would be broken into at least first and last name fields. Generally I use 5 fields for people names; Salutation (Mr, Ms, etc.) First, Middle, Last and Suffix (Jr, Sr, etc.). A control is an object on a form or report that displays data. A control can display the content of a field in a table (or a column in a query) or it can display the result of an expression. Similarly, a column in a query can display either a field in a table or the result of an expression.

So, you do not want to create a field in a table that has the full name. That is redundant data. A principle of relational database design is to eliminate redundant data. If you want to DISPLAY a person's full name, you do that using an expression. To do so, you would set the ControlSource property of a control on a form or report to:

=[Firstname] & " " & [Lastname]

or you can add a column to a query:
Fullname: [Firstname] & " " & [Lastname]

You can also use an expression like:
[Lastname] & ", " & [Firstname]

to display the name in the format Smith, John a common way to list full names for sorting.

[Stuartjacks]

Thanks for your reply. I'm interested in the part about adding a coloumn in query as the reason for my question is I have a query that finds [Surname] but want to be able to find people with the same surname, thus I thought of using a field made from [Surname] & [Firstname] in order to find each individual record as required. My data is stored with separate fields in a manner similar to your suggestion.
So please could you explain the part about "adding a column in a query"?
Thanks very much.

[ScottGem]

I did:
Or you can add a column to a query:
Fullname: [Surname] & ", " & [Firstname]

Just enter that in the top row of a blank column on your query. But you don't need to do that to find duplicates. You can simply sort. Put the Surname and Firstname fields into your query in that order and set the sort for each, it will sort first on surname and then on Firstname. Or you can use the Find Duplicates Query wizard.