How to Combine "Short Text" Data?

Question

Hi - I am looking to combine two 'short text' bits of data into one 'short text.' More specifically, I want "First Name" to be united with "Last Name" to create the third bit of data, "Full Name."

I've seen lots of info out on the web to get this done, things like...    [First Name] & " " & [Last Name]     and     [First Name] + [Last Name]     ...but I'm not having any luck making it work as I'm not really sure how that fits into the screen capture below. What would be the actual, exact syntax? And where would I enter it? I've been trying to enter it under the data type for "Full Name," but clearly that's not right :)

Anyone able to advise?

(FYI: I am quite adept at Excel but am new to Access)

Thanks!

Kristine

Answer 1

There would be no pint adding this as a field to your table as this would simply be redundant information since it will already house the first and last names..  As such, you you would use the above syntax in your queries, forms and reports.  But no need to actually store that value.

Answer 2

Hi Daniel,

Thanks... so the reason I wish to combine them is to have the full names listed in a drop down list on a form. I thought it might be easier if the names were already combined before putting them into a drop down list, kind of thing? What do you think?

I found a VBA script for the above screen capture that I wanted to try out...

Thanks again,

Kristine

Answer 3

Daniel is quite correct. The full name field should SIMPLY NOT EXIST in your table; it's redundant, it's a waste of space, and worst, it leaves you open to anomalies such as "Deborah Green" getting married and becoming "Deborah Smith" - you would have to remember to update TWO fields.

Better is to keep the first and last names (and middle name, suffix such as Jr., and title) as individual fields, and base the combo box on a Query concatenating the values, rather than directly on the table.

Answer 4

Kristine,

As Daniel and John have noted, as a general rule we don't store calculated values. But you can easily do what you want using the Row source of the combobox (sometimes referred to as dropdown list). 

My standard RowSource for a combo that needs to select a person is:

SELECT PersonID, Lastname & ", " & firstname AS Fullname

FROM tablename

ODER BY Lastname, Firstname;

A couple of other tips if you don't mind.

Name your Primary Key fields tablenameID (ResumesID), don't just accept the default name of ID. This will make it clear what the link is when you use the field as a foreign key.

Don't use spaces in object names, this will come back to haunt you. Use camel notation (FirstName) or underscores (First_Name) instead. Use the Caption property when you need spaces in labels. 

Read up on Normalization, I can see at least three instances where you should be using child tables instead of LongText fields. For example: Technical Skills. You should have 2 tables here:

TL_Skills  The TL would indicate its a lookup table)

SkillID (PK autonumber)

Skill

You now have a many to many relationship. One applicant can have multiple skills and one skill can apply to multiple applicants. So you need a Junction table to model that relationship. Like:

TJ_ApplicantSkills   The TJ indicates a junction table

T_ResumeID (FK)

SkillID

ResumeID

You should also have child tables for References and Professional Designations, though I wouldn't use lookup tables for those so you don't need a junction table

For References I would use:

T_References

ReferenceID (PK Autonumber)

ResumeID (FK)

RefFirstname

RefLastname

RefStreetAddress

RefCity

RefState

RefZip

RefPhone

RefEMail

I also see you have a Posting table. That table should have an AutoNumber PK (PostingID) and that should be used as a Foreign key in T_Resume rather than the Short Text field you have for Job Posting Title.

Answer 5

Hi Scott,

All that info is good albeit much of it is over my head as I am just starting out :) Nevertheless, I have managed to get my drop down/combo box thing to work on the form and thus you will all be glad to hear that I did not make any further attempts to accomplish this in the table T_Resumes.

In that process of getting my query to work, I did update my field names as per camel notation. I also renamed the Primary Key fields so that they were a tad more identifying that just "ID."

On child tables, I won't be using that for now as I want to steer clear of, say, breaking out details for Technical Skills and References. This is largely due to the inconsistencies of 'data' I am attempting to pull from resumes submitted to us. For example, in the case of Technical Skills, there is an array of software people put down... Procad, AutoCAD, Navisworks, CADWorx, Solidworks, Primavera, ProArc, etc. ... 2D, 3D .... year and version .... and then whether or not that person thinks they are an expert, intermediate or novice user of any of those software applications. So for now, I prefer that to be just a couple of generic sentences taken from a resume and input into the long text field for "Technical Skills."

Maybe, Scott, there is a better way to enter a couple of sentences for data other than "Long text?"

As for the Postings table, I will probably flush that out next with things like where the job was posted, when it was posted, when the job post was taken down, what the text details of the job post was, etc. For starting out, I just wanted to have something to select from in the combo box without much thought into it at the start -- that's just me biting off things in small pieces that I can handle.

Remember, I am just starting out... that said, I can see how very easily one could get into real detail with this sort of thing. Lots to learn I know... like, "Foreign key?" I will definitely be looking these things up to learn about them.

One last question... I am quite comfortable with Excel, I am a graphic designer by training so I am adept in much of Adobe's software (albeit I am now transitioning into accounting taking accounting courses and learning Sage products, etc.), but I can't help but think I am better off tracking resumes, for example, in a db package such as Access instead of defaulting to Excel as I often do. In your opinion, am I right in thinking this? Or would Excel still be a better option? In which case, I might be wasting my time in Access?

Long-term, if I am able to get my Access skills to where my Excel skills are, there are other 'projects' I have in mind for the office.

Thanks so much, all!!

Cheers,

Kristine