Best approach to creating name strings for use thoughout database - calculated field or query? 3

[Moss100]

Hello, I am creating a database which will store names which I will need to use in various areas and in various formats.

I will store the names in separate fields - Title, FirstName and LastName

I would like to concatenate these as follows:

1. ShortName: Title &" "& Lastname
2. LongName: Title &" "& FirstName & " "& Lastname

I can do this by either using a calculated field in the table
or
by creating a query which I can link to other tables or queries as required throughout the database.

It seems that the query approach may take longer to run and slow things down.

Although the table calculated field seems easiest at first, I am planning to maybe migrate the back end tables to SQL server at some point and I 'think' that the calculated fields may cause problems with this.

I would much appreciate any advice as to how others approach this.

Many thanks Mark

 

[dhookom]

I would calculate on the fly and not store the calculated value.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

It most cases it is better to use queries instead of the calculated table fields (that started in Access 2010). Some reasons

1. Expressions are limited
2. Backwards compatibility (and I think compatibility with SQL Server)
3. The calculated results are not reliable. If you change the Expression after data has been entered into the table, the existing results may not be updated correctly (though new records are edits are updated), so you cannot rely on the results. A compact/repair does not recalculate, so there is no obvious way to repair the bad results.
4. You cannot index calculated fields
5. There is no performance benefit to be gained from using them.

Doing concatenation in a query is extremely fast, so I would not worry about performance.

 

[1DMF]

There is no problem with calculated fields in MS SQL, I use one for a legacy column in one of my apps.

Though I created it in MS SQL, so don't know how MS Access would migrate if created in MS Access first.

However, I agree with Duane & MajP, concatenation for 'Fullname' takes no time at all.

Also does every record in your DB have a title? If it isn't a mandatory field and you use straight SQL concatenation you will end up with a space at the start of the result unless you use code to deal with this.



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

Also does every record in your DB have a title? If it isn't a mandatory field and you use straight SQL concatenation you will end up with a space at the start of the result unless you use code to deal with this.

In Access this can be fixed by using the + operator
Title +" "& FirstName & " "& Lastname

Null + string = null
Null & string = string
so
Null + " " & John
= Null & John
= John without the space

 

[Moss100]

Thank you for all your replies and help.

I have amended the code to make sure the spacing is correct.

The reason I want to generate the names is that I use the names in numerous forms etc and would like one place to be able to call the names from, instead of building the string each time. Also I was thinking this would make editing the name format easier if required in the future. I have around 8 different name formats, so building them on the fly becomes tiresome.

From the above, I think using a query to calculate the names may be the best approach as it will avoid upsizing problems and allow for more copmplcated expressions if required in the future.

If there are any other comments I would welcome these, but in the meantime many thanks all.

Mark

 

[MajP]

I would think you have some type of personnel table. It would have a PK. You simply need to make the eight queries for each of the formats and then reuse them. They simply need the PK and the formatted name. You can then use those queries whenever you want and if you need more fields from the personnel just join those queries to the personnel table by the PK.

 

[1DMF]

Also remember if the you try to concatenate using SQL and any of the fields have a NULL value, you will end up with NULL as the result.

In MS Access you can use Nz or in MS SQL Coalesce and then the Trim command when using the field.. E.G.

    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("SELECT [b]Nz[/b](Title,'') + ' ' + Firstname + ' ' + Lastname AS FullName FROM Contacts", dbOpenSnapshot, dbSeeChanges)
    
    Do While Not rs.EOF
        Debug.Print [b]Trim[/b](rs.Fields("FullName"))
        rs.MoveNext
    Loop

    Set rs = Nothing

... or do the concatenation in code as MajP has it.






"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Moss100]

Hello - back again.

I thought I was making progress, but now I've hit a brick wall.

I made a query based on a table called Clients. I named this query, qry_Client_Names

The query included Client_ID and then a number of strings to build the names.

The query runs fine and I am able to update other none string fields in it no problem.

I then made another query which is based on the same table along with my query qry_Client_Names

When I add the table Clients and the qry_Client_Names, Access automatically shows the link between the Client_ID fields of the table and the query.

When I run the query everything works fine EXCEPT I am unable to update the records.

Why is this and is there any way around it?

Many thanks again for your help and patience.

Mark


Again if I run this the fields are updatable.

If I create a

 

[dhookom]

So your final query has the Clients table in it twice? Can you provide some SQL views?

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Hello,

For ease I have created a smaller query based on my vendor table to show the SQL:

This uses table: tbl_SA_Vendors and query: qry_SA_Calc_Vend (which is also based on the table, tbl_SA_Vendors)

SELECT tbl_SA_Vendors.Vend_ID, tbl_SA_Vendors.Vend_1_Surname, qry_SA_Calc_Vend.Vend_ID, qry_SA_Calc_Vend.Vend_Sort_Name
FROM tbl_SA_Vendors INNER JOIN qry_SA_Calc_Vend ON tbl_SA_Vendors.Vend_ID = qry_SA_Calc_Vend.Vend_ID;

The actual SQL I am using for 'interest' is below:

Thanks Mark

SELECT tbl_SA_Vendors.Vend_ID, tbl_SA_Vendors.Vend_Date_Added, tbl_SA_Vendors.Vend_Date_Updated, tbl_SA_Vendors.Vend_Status, tbl_SA_Vendors.Vend_Company_Name, tbl_SA_Vendors.Vend_1_Sal, tbl_SA_Vendors.Vend_1_Forenames, tbl_SA_Vendors.Vend_1_Surname, tbl_SA_Vendors.Vend_1_Tel_1, tbl_SA_Vendors.Vend_1_Tel_1_Notes, tbl_SA_Vendors.Vend_1_Tel_2, tbl_SA_Vendors.Vend_1_Tel_2_Notes, tbl_SA_Vendors.Vend_1_UK_Mobile, tbl_SA_Vendors.Vend_1_UK_Mobile_Notes, tbl_SA_Vendors.Vend_1_Fax, tbl_SA_Vendors.Vend_1_Fax_Notes, tbl_SA_Vendors.Vend_1_Email, tbl_SA_Vendors.Vend_1_General_Notes, tbl_SA_Vendors.Vend_Web, tbl_SA_Vendors.Vend_2_Sal, tbl_SA_Vendors.Vend_2_Forenames, tbl_SA_Vendors.Vend_2_Surname, tbl_SA_Vendors.Vend_2_Tel_1, tbl_SA_Vendors.Vend_2_Tel_1_Notes, tbl_SA_Vendors.Vend_2_Tel_2, tbl_SA_Vendors.Vend_2_Tel_2_Notes, tbl_SA_Vendors.Vend_2_UK_Mobile, tbl_SA_Vendors.Vend_2_UK_Mobile_Notes, tbl_SA_Vendors.Vend_2_Fax, tbl_SA_Vendors.Vend_2_Fax_Notes, tbl_SA_Vendors.Vend_2_Email, tbl_SA_Vendors.Vend_2_General_Notes, tbl_SA_Vendors.Vend_Address_Name, tbl_SA_Vendors.Vend_Address_No, tbl_SA_Vendors.Vend_Address_1, tbl_SA_Vendors.Vend_Address_2, tbl_SA_Vendors.Vend_Address_3, tbl_SA_Vendors.Vend_Address_4, tbl_SA_Vendors.Vend_Address_Post_Code, tbl_SA_Vendors.Vend_Notes_General, tbl_SA_Vendors.Vend_1_Proof_Identity_Held, tbl_SA_Vendors.Vend_1_Proof_Identity_Held_Last_Updated, tbl_SA_Vendors.Vend_1_Proof_Address_Held, tbl_SA_Vendors.Vend_1_Proof_Address_Held_Last_Updated, tbl_SA_Vendors.Vend_2_Proof_Identity_Held, tbl_SA_Vendors.Vend_2_Proof_Identity_Held_Last_Updated, tbl_SA_Vendors.Vend_2_Proof_Address_Held, tbl_SA_Vendors.Vend_2_Proof_Address_Held_Last_Updated, qry_SA_Calc_Vendors.*, qry_SA_Calc_Vendors.Vend_ID, qry_SA_Calc_Vendors.Vend_Sort_Name, qry_SA_Calc_Vendors.Vend_Display_Name, qry_SA_Calc_Vendors.Vend_1_Name_Short, qry_SA_Calc_Vendors.Vend_1_Name_Long, qry_SA_Calc_Vendors.Vend_2_Name_Short, qry_SA_Calc_Vendors.Vend_2_Name_Long, qry_SA_Calc_Vendors.Vend_Joint_Name_Short, qry_SA_Calc_Vendors.Vend_Joint_Name_Long, qry_SA_Calc_Vendors.Vend_Letter_Name, qry_SA_Calc_Vendors.Vend_Letter_Name_Dear, qry_SA_Calc_Vendors.Vend_Address_Single_Line, qry_SA_Calc_Vendors.Vend_Address_Multi_Line
FROM tbl_SA_Vendors INNER JOIN qry_SA_Calc_Vendors ON tbl_SA_Vendors.Vend_ID = qry_SA_Calc_Vendors.Vend_ID;