Import w Followup
By Richard Rost
3 years ago
Import New Leads & Make a Follow Up Record
In this Microsoft Access tutorial I'm going to teach you how to import new leads into your customer table and then make a follow up record in your follow up table.
Pre-Requisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, import leads, import with follow up record, import related records
Intro In this video, I will show you how to import new leads from an Excel file into your Microsoft Access customer database and automatically create corresponding records in your follow up table for each new lead. We will walk through the process of importing data from Excel, using append queries to add the leads to your customer table, flagging new records, creating follow up entries with another append query, and using an update query to clear the new record flag once the process is complete. This tutorial will help you streamline lead management and follow-up tracking in Microsoft Access.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In this video, I'm going to show you how to import new leads, like new customers or whatever, and at the same time, make a record in your follow up table for each of those new customers.
This is something I get asked all the time. Someone says, well, I buy lists of leads from a mailing list company or whatever. Maybe you went to a trade show and you got a list of people that you typed up that you met. You want to import those people into your customer table, but at the same time, add a record into your follow up table so that you can give them a call.
Before we get started, if you haven't watched my series on follow ups, go watch that first. You'll understand what a follow up is. Basically, it's a separate note that you can use to track people that you have to call back. Those are follow ups. There are six parts to that series. Really, you only need part one. I'm not going to call this follow ups part seven because this is kind of new material that I'm just using the follow ups database for. If you want to watch this, go watch this.
You will also need to know how to import data from Excel, which I cover in this video. I'm going to do it quickly, but I'm not going to go over all the details. If you want to learn more about importing, go watch this. We're going to also use an append query, which is adding data from one table to another one. Very important. And we're going to use an update query to change records in a table. If you haven't watched any of these videos, go watch them now. Then come on back. I'll wait for you. These are all free videos. They're on my website and my YouTube channel. You'll find links to them down in the description under the video. Go click on them and come on back.
So here I've got my follow ups database. It's this guy. If you watched my other videos, you're well familiar with this database. There's the customer form. There's the follow ups form. You got all the follow ups in there.
You click all. This is something I added in one of the later parts. You can see all the ones in the future.
But each customer can have multiple contacts. Those contacts can be marked follow ups. And that's our follow up list.
So you went to a trade show or you bought a mailing list, whatever, and they give you an Excel file with a bunch of new leads in it.
Funny story - I use ChatGPT to generate this list for me. Normally I just sit here and type in a bunch of names, like Star Trek names or whatever, but I was just playing around with ChatGPT. I recorded a whole separate video on that. It was quite interesting. It did all the formatting for me, so it saved me a lot of work, and that's where I think the future of AI is going to be. I'll put a link to that video down below. You can go watch that as well. I just released it today.
Anyway, so here's the data that I got. The first thing we're going to do is import this into my database.
You can connect directly to the spreadsheet if you want to, using a link. That's possible, especially if this is something you get on a regular basis. Personally, when I'm doing imports like this, I prefer to import the data into a table directly. That's just the way I like to work with it.
If you've got an Excel spreadsheet sitting on a server somewhere that everyone else is updating in real time and you want to link to it so you don't have to keep importing it over and over again, that's fine. But for situations like this, where you just get a list from time to time and you want to import that data, just do an import.
We're going to start with External Data, New Data Source, From File, Excel. Import the data, click Browse. It's on my desktop and it's going to be... where is it... new leads, right there. We're going to hit Open.
There it is. First row does contain the column headings. I noticed this mistake afterwards. GPT actually only gave me 14 records and a header row. So I yelled at it, and it fixed it. I literally said, you only gave me 14 rows and a header, and it goes, oh, I'm sorry. I put the updates on my website.
Next, this is where you're going to fix your column headers. Try to get these to match what's in your database. Like I got first name, no space. Last name, no space. City and state are the same. Phone number, I think, in my database is just phone. Age - I don't have an age field, but that's okay. I'm going to change that. The import likes to make all numbers doubles. It can be a long integer. The rest of these are all short text. Make sure you check your data types.
Next, I'm only going to be importing these and I don't care about an autonumber, so I'm just going to say no primary key. Doesn't matter.
Next, import to table. Let's call this my import T or leads T, whatever you want to call it. I don't care.
Now if you're going to be doing this on a regular basis, you can save the import steps. I'm not, so I'm just going to close it.
Here's my import table. There you go. I have found in my experience that it's a whole lot easier to work with this information once you get it from Excel into an Access table. Don't try to do work directly to an Excel spreadsheet with a link. It's just easier if you do this. Trust me.
At this point, you might want to check for duplicates. There's really no data here that I could use except maybe phone number, or if you want to do a first name, last name match, but it all depends on you. If it's a small community, first name, last name might work, address might work, phone number is a better number, email address is what I use. I'm not going to waste time covering that here. I got a whole other video, of course, to teach you how to check for duplicates, so go watch this video and learn how to do that.
Assuming this list is clean, the next thing we're going to do is import this list into the customer list using an append query. Append every record from this table into that table.
Let's make the append query: Create - Query Design, switch it to an Append Query. Where are we appending to? Our customer table, that's where the records are going into. Where are the records coming from? The import table. Bring that into the query. So the record source for the data is coming from and going to our customer table.
Now, if the fields all matched one-to-one, these were named exactly the same, you could just bring down the star, but you can't because we don't have an age field in the other table. So bring these down one at a time. First name, last name, I noticed that they match right here in the append-to field, city, state, and phone. Age - I don't have an age field. I don't really care about the age field. If you want it, go add it to your customer table.
But we're not quite done yet. I need some way of indicating that these are new records, that I just imported them. Someone just beamed in. So what I'm going to do is add some kind of a flag field like isNew or something to my customer table, so that I can mark that during this import.
Let's save this and come back to it. I'm going to save this. We'll call this my Import1Q. We'll come back to it in a minute.
Let's go back to my customer table, design view. Let's go down to the bottom here and at the very end, let's add isNew and we'll make that a yes/no field. This field will indicate these are all new records that we just imported.
If you want, when people manually add new records to the customer table, for those to be marked new, so the next time you do this they all get a follow up, then great. Set the default value to yes here and then any new record will become yes. But I don't want to do that personally. No, I only want this to be for these imports.
Save that and now you'll see that everyone's got an isNew field over here, but they should all be marked no.
Now let's go back to our query that we're building.
When I run this import, I want to come over here and say, way down the bottom, isNew, and I want to set that value to Yes or True and Access marks it as Expression1, that's fine. Doesn't matter. Save it and let's close that.
Now let's run the query. Double click. I've got my warnings turned off. You might see some warnings like "Access is about to append 14 records," whatever.
Now looking at your customer table, you can see if you scroll down, there's all your new records. It's 14 of them down there. You have all the data: first name, last name, all new autonumbers.
Now we got the customers. Now we're going to do another append query, but this time we're going to use CustomerT as our record source, and we're going to say for every customer that is marked isNew, I want to append a record into the follow ups table, the contact table, to make a contact for these guys.
So let's go Create - Query Design, append a table, we're appending into our FollowUps table, which they're stored as contacts. Where's the data coming from? This time it's coming from our customer table. Those records are in there, but we need a criteria. isNew has to be true.
If you just want to see the records at this point without actually running the append, switch over to datasheet. Now we've got to have at least one destination field in here. So let's put in CustomerID as the destination field, and that's going to be appended into the CustomerID in the contact table. That's how it knows who the follow up is for.
Now let's switch over to this. You can see 14 records, perfect.
What other fields do we want to go into that contact table? You can find that list of fields right down here in the append to list. Drop this down and figure out what you need.
ContactID is an autonumber. We don't have to worry about that. CategoryID, that's important. What are our categories? If you didn't watch that lesson in the Follow Up series, the categories look like this: Pre-sales, Sales, Service, Follow Up, and Other. This would be pre-sales; this would be Category 1. So for CategoryID, I'll make that 1 - pre-sales.
Let's take a look. Drop this down. CustomerID, we got already. ContactDate - let's put today's date in there, the date that the contact was made. Just put in date(), open and closed parentheses.
We're moving right along here. Drop the box down. ContactDate. How about a description? Let's put in "New Lead" like that - in quotes. That's Expression3. "New Lead" will go into the Description field. I know it's a new lead sales call, or you can put "got them from the trade show," whatever.
Notes - if you want to put some notes in here, great. I'm not going to bother.
FollowUp is a yes or no value, so we'll put a yes in there.
Then, the FollowUpDate - when do you want to call them? Let's go tomorrow. Date()+1.
There you go. I'll save this as my Import2Q.
So you're going to run one - that brings them into the customer table, the customers, and then two makes the follow ups.
I'm going to close this. Now, check your contact table right now. You shouldn't have all those extra follow ups in there. If you go to your follow ups list, you'll see that's all there is.
That's it.
Ready? Let's run that second query now. Double click.
It's done.
Check your follow ups list. Now they're for tomorrow, so you don't see them yet. Hit "All." There they are. There's all your new leads and the pre-sales follow ups. Check all that out. You can open up... Terrien.
We're not done yet. We're not done. A couple things to do still.
Next, we've got to clear them of being new. We got the customer thing here. Here's all the customers. We got to get rid of that isNew mark. So that's going to be an update query.
Create - Query Design. Change it to an Update Query. Bring in CustomerT. Bring in isNew. You can set the criteria as true and update to false. It doesn't really matter. At this point, we just want to update everybody to false. At this point, we're done doing our import. We want to mark everybody as not new.
Save that. This will be Import3Q. It shows up down here because Access is grouping these. It puts all the update queries together. It puts all these together. That's fine. That's why what I'll do when I'm working on something like this is I'll set this to Import; it's all your import-related stuff together.
Turn that off.
Save it. Close it. Run it. Import3. Double click.
Let's check our customers. Slide down. Slide to the right. Yep. All those are off.
Now, if you want, you could make a fourth import queue, Import4Q, which would be a delete query, which you could then go in and delete the records out of here. That's fine, too. I covered delete queries in my delete query video. There you go - delete queries. You can even automate the whole thing with a little VBA. You can click one button to import the list, run all those queries in succession, run the delete query, and then pop up your follow-ups list. So that all can be automated with a little bit of VBA.
If you want to learn more stuff like this, I got a whole series of classes right in the middle of my Expert series, where I cover lots and lots of things to do with these things called action queries. You got update queries, that's Level 13. You got more update queries, you got append queries, you got tracking student attendance using macros to run queries. You can do macros, or you can do VBA. I show you macros in this class. You can use delete queries, make-table queries, there's union, there's all kinds of queries. I got four or five, maybe six hours of just action query training, and these are all taught one lesson after the next, showing you how to do stuff in order. Doesn't jump around a lot like these Fast Tips videos do.
So, there you go. There's your importing new leads and making a follow up record. That wasn't that hard. Like I said, it's Legos. I've shown all of these things in different videos already, even the free videos. So it's just taking the different Legos that I've already shown you and putting them together in different ways. That's all Access is - append queries and update queries and importing data, and now you just put it together in a way that works.
What are some ways that you've done things like this? Let me know, post something down in the comments below. If you have any questions or things you want to see like this, even if it's a topic I've covered before but you want to see me put the Legos together differently, let me know.
I guess you could consider this the follow ups part 6.5 maybe.
There's your Fast Tip for today. I hope you learned something. I'll see you next time.Quiz Q1. What is the main goal of the video tutorial?
A. To import new leads into Access and add a follow up record for each lead
B. To create a sales dashboard in Access
C. To automate email marketing with Access
D. To export customer data from Access to Excel
Q2. What is a follow up in the context of this video?
A. A record of a completed sale
B. A separate note tracking people you need to call back
C. A payment reminder
D. A customer address update
Q3. Why does the instructor recommend importing data into a table instead of linking directly to the Excel spreadsheet?
A. It is more secure
B. It allows easier work and manipulation of the data in Access
C. It saves memory space
D. It prevents data loss
Q4. Before importing new leads, what should you check for in your data?
A. Duplicates, such as matching phone numbers or emails
B. Formatting errors in Access
C. If customers have email addresses
D. Only complete mailing addresses
Q5. What type of query is used to add new records from the import table to the customer table?
A. Update query
B. Make-table query
C. Append query
D. Delete query
Q6. Why is an isNew field added to the customer table during the import process?
A. To track if the customer has updated their phone number
B. To identify which records were just imported as new leads
C. To store the customer's loyalty status
D. To record the customer's age
Q7. What is the next step after importing data into the customer table?
A. Running an append query to add a follow up/contact record for each new customer
B. Deleting the imported data
C. Running a report on the sales activity
D. Sending emails to all customers
Q8. In the append query to add follow ups, what value is used for CategoryID for new leads?
A. 4 - Service
B. 2 - Sales
C. 1 - Pre-sales
D. 5 - Other
Q9. What function is used to set the follow up date to tomorrow in the append query?
A. Now()
B. Date()
C. Date()+1
D. Today()+2
Q10. Why do you run an update query after creating the follow up records?
A. To send notification emails
B. To mark all imported customers as not new by setting isNew to false
C. To delete customer addresses
D. To export the data back to Excel
Q11. What kind of query can be used to remove records from the import table after the process?
A. Append query
B. Update query
C. Make-table query
D. Delete query
Q12. How can you further automate the entire process in Access?
A. By writing VBA code to run all queries and steps with one button
B. By creating multiple linked spreadsheets
C. By exporting reports to PDF
D. By making a new primary key in every table
Answers: 1-A; 2-B; 3-B; 4-A; 5-C; 6-B; 7-A; 8-C; 9-C; 10-B; 11-D; 12-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on how to import new leads, such as prospective customers, into your Access database and automatically create a corresponding record in your follow up table for each of those new entries.
This is a common question that comes up. Often, people will purchase lists of leads from other companies or collect contacts at events like trade shows. The end goal is to bring those contacts into your customer table and simultaneously make sure each one gets added to your follow up list so you can remember to reach out to them.
Before tackling this process, I recommend working through my existing series on follow ups. This will ensure you understand the whole concept and use of follow up records in Access. For this task, you really only need to have watched part one, since this lesson covers new material and builds on the existing follow up database.
You should also know how to import data from Excel, since that's the most common way to receive lead lists. I have a separate video where I cover data import from Excel in more detail if you need a refresher. In addition, you'll need to be comfortable using append queries, which let you add records from one table to another, as well as update queries, which let you change data already in your tables. If any of those concepts are unfamiliar, I encourage you to check out my free videos on those topics before moving forward.
Let me outline the steps I use. I start by opening the follow up database that we've worked with in other lessons. The customer and follow up forms are already set up, so you can see how new customers generate various follow up entries.
After collecting your leads, either from an event or purchased list, you'll likely receive them in an Excel file. In my example, I even used AI to generate a list for demonstration, which turned out to be a timesaver and quite interesting.
The first task is to import the Excel data into Access. You have the option to link directly to the spreadsheet, and that can be useful in environments where the file is regularly updated. However, I personally prefer to import these leads into a dedicated Access table. It's more straightforward to work with and reduces potential errors.
During the import process, try to match the Excel column headings to your Access table fields where possible. For example, fields like first name or phone number might need slight adjustment. If there are extra columns, like an age field you don't need, that's not a problem. You might also need to adjust data types at this stage, especially for numeric fields.
Once imported, take a moment to check for duplicate records. The best identifier for duplicates is usually an email address, but sometimes phone number, or even a combination of name and address, will suffice. If you're unsure about best practices for duplicate checking, I have a separate video that covers the topic thoroughly.
Presuming your list is clean, the next challenge is to move these new leads into your main customer table. This is handled with an append query. If your column names match exactly, moving the data is a simple matter. If not, just map the fields accordingly during the query setup. In my demo, I mention the idea of adding an 'isNew' yes/no field to the customer table. This flag helps you keep track of which customers were just imported so you can handle them accordingly.
After the append query moves the new leads into the customer table and assigns the 'isNew' flag, we shift focus to the follow ups. The next step is a second append query. This query looks at every customer marked as 'isNew' and creates a corresponding follow up record for each. Here you can set default values; for example, you might assign a certain category to these contacts (such as 'pre-sales'), enter the import date, write a brief description or note indicating where the lead came from, and set the follow up flag as true. You can even specify the date for the follow up, such as making the task due tomorrow.
After the new leads and their follow up records are in place, you will want to clear the 'isNew' flag from the customer records so they do not keep appearing as new in future queries. This is done with an update query that sets the 'isNew' field to false for all customers. You could include one more optional step: use a delete query to remove the imported records from the temporary import table now that they have been copied into your main tables.
For those interested in streamlining the process further, it is possible to automate all of these steps using VBA so that importing leads, running the required queries, and updating the necessary tables happens at the push of a button.
If you want more in-depth instruction on action queries and how to use them effectively, I cover these topics extensively in the Expert series on my site. There, you will find detailed lessons on update queries, append queries, and how to automate a variety of tasks using macros or VBA.
So that's the process of importing new leads and setting up automatic follow up reminders in Access. As always, it comes down to using the right tools—importing, appending, and updating your data. You can combine these core concepts in creative ways to solve just about any problem.
I'd love to hear how you handle similar challenges, or if you have a different approach. Feel free to share your experiences in the comments, and let me know if there are other combinations or workflows you'd like to see explained in future videos.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Importing leads from Excel into Access
Cleaning and reviewing imported data
Mapping Excel columns to Access table fields
Handling missing or extra fields during import
Creating an import table for lead data
Adding a flag field to mark new records
Building an append query to add leads to the customer table
Flagging imported customers as new using a Yes/No field
Appending follow up records linked to new customers
Setting category and date fields in follow up records
Creating dynamic descriptions for follow up contacts
Scheduling follow up dates automatically
Using update queries to reset isNew flags
Optional deletion of temporary import data
Overview of automating the import and follow up process |
