Access - Edit Build Info to a Existing Query


I have 4 fields that I need help with:

Zip: I need this to only export the first 5 numbers.
SSN: I don't want this to print with any spaces or dashes (-).
First Name: Anything after the first name I don't want it to print. Example John S. - I only want John.
Last Name: Same as the first name I don't want anything after the last. Example Smith JR - I only want Smith.

This query already exist, I am not looking to re invent the whole thing or use in depth visual basic, just simple formulas I can add to the criteria or build sections.

Thank you!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
from your previous post, you were ask to upload sample data, but you disregard the request.

good luck with your project.
WO2015Author Commented:
I did, and also asked another question but no response in a week. Thank you.
Jeffrey CoachmanMIS LiasonCommented:
You posted some SQL...
what we need is an an actual sample "Database"
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Jeffrey CoachmanMIS LiasonCommented:
In the most basic sense, you could use a query roughly like this:

SELECT Left([FullName],InStr([FullName]," ")-1) AS FirstName, Replace([ssn],"-","") AS RawSSN, Left([Zip],5) AS First5Zip
FROM YourTable;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
simple sample
WO2015Author Commented:
Ok, I will have to mess with it tomorrow. I am not familiar with SQL just editing the actual table with design view using the build. My database is way to large and has personal information, I cant upload it, sorry.  If that is something that's needed I will just have to work with that I have or google it if the above database doesn't help.  Thank you so much, I will try this tomorrow.
Eric ShermanAccountant/DeveloperCommented:
Just make a small sample db with a table and the 5 fields showing a couple of sample records of how the data looks now.  You could do this in all of 10 minutes time and will get you a much faster/accurate solution.  Once you see the SQL then you can use the same concept in your live data using the query builder design mode on those fields.

I would recommend that you drop/empty the SSN field from your table.  You are facing a huge liability if someone gets that database.
Eric ShermanAccountant/DeveloperCommented:
<<< I would recommend that you drop/empty the SSN field from your table.  You are facing a huge liability if someone gets that database.>>>>

Kind of why I specified sample data of a few records ... meaning no real records and only a few.  

XXXXXXX for phone, ss#, etc. with fake street, first and last names.

I'm not talking about sample data.  I'm talking about the production set of data.
WO2015Author Commented:
We are not facing any liability for having SSN in our databases. We have client audits often and this is expected in our industry in order to provide data to them.
People sometimes ask for sample databases so they can write queries that you can simply copy and paste.  In this case, since we are not searching for a bug, there is no need for a copy of the database.  However, you will have to translate the proposed SQL to what you actually need based on column names.  Jeff's example answers your question.
I am not familiar with SQL just editing the actual table with design view using the build
You can't do what you are asking without it.  No database that you build for other people should ever expose raw tables or queries to the user so I'm assuming this is for your own use only.

If you want some one to edit Jeff's sample so you can copy and paste it, you'll need to provide the table name and all the column names you want included in the query.
WO2015Author Commented:
Thanks for everyone's comments, I am going to accept Jeff's answer because it worked for two of my items and I did find a solution for the SSN and ZIP. It seems I caused a stir which was not my intention, just trying to figure something out which I have, thank you for all your input have a great day.
One of my clients uses medical equipment that has managed its data with a PC-resident Access database.  My primary client contact complained that their quality data extract took a long time to complete.  When I looked into it, I found that the mdb file was copied to the optical disk and then individual non-quality records were deleted with queries (in a loop, of course).

The problem was that all the patient data remained in the mdb file.  I created a replacement QC export that only took less than a minute to complete and the resulting mdb file was a small fraction of the size of the one the vendor was creating.  Moreover, no patient information was ever written to the disk and mailed to the vendor.
Jeffrey CoachmanMIS LiasonCommented:
OK thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.