Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  

News      User Comments     History     Notify Me

9/28/2013 9:14:15 PM
Microsoft Access Expert 14
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

Microsoft Access Expert Level 14 is 1 hour, 34 minutes long. This tutorial will continue our focus on Update Queries to manipulate data, and we'll begin learning about how to use Append Queries to copy records from one table to another. Topics include:

- More Update Queries
- Scrubbing (Cleaning) Data
- Data with Inconsistent Formatting
- Use Replace Function to Change Text
- CStr, Nz, InStr, Left, Right Functions
- Separate First & Last Names
- Fix Non-Relational Tables
- Append Queries
- Daily Student Attendence Table

Click here for more information on Access Expert Level 14, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 13. The next class in the series is Access Expert 15 which is now available.

Permanent Link
Course Link: Microsoft Access Expert 14
Keywords: access expert 14 update query update queries append query append queries replace hyperlinkpart nz i
Page Tag: whatsnew
Post Reply

Updating an expiration date Comment from Frank Fish @ 11/30/2014
OK, I found it; I needed to place [] around the field (as you showed in the video)
Show Just This Thread        Post Reply
Updating an expiration date Comment from Frank Fish @ 11/30/2014
Richard, I was updating a expiration date in my (backup)database, and used:

The result of the update removed the old date, and left the field blank. Did I miss something? are dates "updated" differently? Thanks FWFII
Show Just This Thread        Post Reply
Find function to find the space Comment from Bruce Reynolds @ 11/25/2014
Couldn't you use the find function to find the space in the FN calculated field in the query instead of SpacePos-1?

Reply from Alex Hedley:

There are usually more than one way to achieve the same goal.

Do you have an example where you've used FIND you can share?

There's the FIND Function that's a member of the Access.Module but it's not well documented from my quick research.
Show Just This Thread        Post Reply
Many To Many Update Queries Comment from Richard W @ 8/26/2014
I did it myself.  No inter!!  I'm "on vacation" (ha).  I had imported both the schools and teachers into separate tables.  I then exported them in to separate Excel spreadsheets with the ID numbers, I matched the names up (with some effort), changed the numbers by hand and reimported them into Access.  No perfect, but I am getting there.  What's more, my wife, for whom this is all being done, SEEMS to be using the not yet complete database successfully.  I don't tackle small problems to begin with. Actually, I have three separate databases in Act! that I am trying to consolidate into one in Access.  I think it will work, but NONE of it deals with products, vendors, etc, so I have to use my imagination to interpret what you teach into my situation!  But I think I can at about the third or fourth time through a lesson.

Reply from Richard Rost:

That's wonderful. Playing around with it like that is the best way to learn... watch my examples and then figure out how to apply them to YOUR project.
Show Just This Thread        Post Reply
Many To Many Update Queries Comment from Richard Wilson @ 8/21/2014
Richard, I have watched all the lessons on Many to Many Relationships, the Relationship Seminar, the Action Queries, etc and cannot find an answer to my problem.  I have a Many to Many situation in which teachers hold faculty positions at several Universities, and a University will have many teachers.  I have all the data which I have imported from Excel Spreadsheets into tables: SchoolT and TeacherT.  Now I need to get it straightened out via a junction table.  Out of about 750 Teachers and 300 schools there are about 100 teachers with at least two schools.  Can you point to the lesson in which you might have covered something that would help in this problem.  I can do it by hand, but that is tedious at best.  Thanks.

Reply from Richard Rost:

This all depends on how you have your data set up. You've got the schools and the teachers in their respective tables, but where do you have listed which teachers belong to which schools? If that's in a spreadsheet somewhere, you may be able to import it and then use an UPDATE QUERY to change the school name, for example, to an ID value, and then use an APPEND QUERY to add all of those to a junction table. Without seeing a sample of your data, I really can't tell. I will say that in most cases like this, you can't easily get away from some manual data entry. Hire an intern. :)
Show Just This Thread        Post Reply
Perhaps you already cover this somewhere but I wou Comment from Alan Lipps @ 8/18/2014
Perhaps you already cover this somewhere but I would like to know how to take column headings from an Excel file and place them in a single Access field.

for Example:

In Excel:
Id     Q1     Q2         Q3
1      5       6         5
2      6       5         6
3      5       5         5

(ID is the foreign key; Q 1-3 values can be any number--in my case between 0 and 9)

to (Access)
ID    Question     Score
1        1           5
1        2           6
1        3           5
2        1           6
2        2           5
2        3           6
3        1           5
3        2           5
3        3           5

Reply from Richard Rost:

You would need to do this with action queries. Set up the table, then you'll need APPEND queries for each of your fields.
Show Just This Thread        Post Reply
SQL to split First and Last name Comment from Alex Hedley @ 11/1/2013
Saw a nice piece of SQL to do this whilst surfing some forums:

Left([Names of employees], InStr([Names of employees]," ")) As FirstName,
Mid([Names of employees], InStr([Names of employees]," ")+1) As LastName
FROM Names;
Show Just This Thread        Post Reply
Separate First Last Names Comment from Deon @ 10/13/2013
Hi Richard,

Thanks for this advice. I'll now give this a serious go.



Reply from Richard Rost:

You're welcome.
Show Just This Thread        Post Reply
Separate First Last Names Comment from Deon Riley @ 10/13/2013
Hi Richard,

Your teaching makes so easy for me to split bad data and to replace and minipulate data; however, this next scenario frustrates me, which is:

I've inherited and now have to fix a Table with Staff data that looks like this:

ID StaffName      
1  DeonRiley
2  RicardoRosti
3  KarynJones
4  BevCramer
5  StevePlessis
6  JockHobbs
7  MelSmithy
8  PeachesGedore
etc. over 10000 records

As you can see the above bad data the First and Last names are joined into one string with no spaces or any delimeter except, the UCase letter of the surname.

What process would I use to Parse the first and second part of the name into separate FirstName and LastName fields.

Thank you for your help with this Richard.

Kindest regards,


Reply from Richard Rost:

That's not going to be easy, but it's not impossible. You would have to create a custom function in VBA and loop down the field looking for the first capital letter (either by ASC char value or with a string). Once you have that, you can use the rest of my techniques to split the field. If you can't figure out how to do it, let me know and I can put the function together for you. Probably take me about half an hour.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 4+4:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



You may want to read these articles from the 599CD News:

6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP