Introduction

Welcome! Import, Composite Keys, Export Tables

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 23. In this course we will discuss remote data entry by creating a standalone database for use on the road, importing transactions from sites like PayPal while preventing duplicate records, and setting up composite keys to prevent multiple entries based on more than one field. We will also work with exporting and importing data involving multiple related tables using XML. Additional topics include reviewing the flow of Access courses, following along with the sample database, and how to get help or access supporting materials and forums on AccessLearningZone.com.

Navigation

Prev: X23
Next: Lesson 01

Keywords

Access Expert, remote data entry, synchronize database, import PayPal transactions, prevent duplicate imports, composite key, primary key vs composite key, export XML, import XML, bank transaction import, related tables, duplicate detection, sales records

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 23, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we will begin by learning about remote data entry. This is where we'll build a second database that our sales reps or our techs can take on the road with them on their laptops. Whether or not they have internet access, they can enter new contact records or sales records or service records or whatever, while they are on the road. Then, when they get back to the main office, they can synchronize that information with the main server.

Next, we'll learn how to import transactions from a banking website or a credit card website. The example that we'll use in class will be PayPal because it's pretty universal, whereas most banks can be slightly different in their formats. We'll learn how to prevent importing the same data twice using the transaction IDs, and we'll see how to set up a query to catch duplicate entries.

Next, we'll learn about composite keys. We've learned about primary and single keys before, where you can prevent duplicates based on one field, while a composite key allows you to prevent duplicate data in your table based on multiple fields. For example, if you have multiple stores and each store has to submit a daily sales report, you wouldn't want to have two reports from the same store on the same day, so that's where a composite key will help.

Finally, we've seen how to import and export data based on one table. In this lesson, we're going to see how to export and import multiple tables with their related information using XML. This is great if you want to send someone information from your database who may or may not be using Access, and you have multiple related tables that you need to send them.

This class follows Access Expert 22. In that class, we covered fixing non-relational data, updating our vendor pricing based on new information, and fixing our order entry system. It's strongly recommended that you watch this class and all the ones before it before continuing on with today's class.

I will be using Access 2013 in today's class. What is covered today should work just fine with Access 2007 and 2010. Of course, if you come across something that is significantly different, please let me know.

The material in today's class is completely new. I've never covered this before in my 2003 series. If you are using Access 2003, you can try to follow along with the lessons covered in today's class, but I can't guarantee that everything will work just fine. But of course, if you are using 2003 or earlier and you do not want to upgrade to 2013, which I strongly recommend, and you're having problems, please feel free to contact me and I'll do my best to walk you through it. I do not have a copy of 2003 still installed on any of my machines, but I do kind of remember what the menus looked like. I'm pretty sure most of the stuff in today's class should work okay with some minor tweaking.

My courses are broken up into Beginner, Expert, Advanced, and Developer level classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the expert level classes, which you are in now. When you finish all the expert level classes, the advanced classes will cover event programming and macros, and the developer classes will cover Visual Basic for Applications. Each group of classes is broken down into multiple levels, Level 1, 2, 3, and so on.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more. You can find details on all of these seminars and more on my website at AccessLearningZone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you're watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked, as well as my responses to them, and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion.

If you are not watching these lessons on my website, you can still visit the student forums later by visiting AccessLearningZone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from class.

If you get stuck or don't understand something, watch the video again from the beginning, or tell me what's wrong in the student forum, and I'll do my best to help you.

Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you'll see that it is real easy to use.

I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at AccessLearningZone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database. One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access.

You will find there is a sample database for each of my courses on my website.

Now, let's take a few minutes and go over exactly what we're going to cover in today's class.

In lesson one, we're going to create a remote copy of our database that users can use for data entry to add new contacts and such while they're not in the office and do not have internet connection.

In lesson two, I'm going to show you how to import PayPal transactions into your Access database.

In lesson three, we're going to learn about composite keys, which is creating a key for a table that's based on multiple fields. So you can prevent duplicate values across multiple fields in the same table, for example, having a store with the same city and state as another store.

So far, all of our importing and exporting has dealt with single table data, one table at a time. In lesson four, I'm going to show you how to transfer multiple tables, in other words, tables with the related records from other tables like customers and orders, using XML.

Quiz

Q1. What is the main purpose of building a second database for remote data entry in Access?
A. To allow users to enter data while offline and sync later with the main server
B. To give managers additional reporting capabilities
C. To automate database backups
D. To generate email alerts for new records

Q2. When importing PayPal transactions into Access, what key method is taught to prevent importing the same data twice?
A. Comparing transaction IDs
B. Checking transaction dates
C. Verifying account numbers
D. Comparing customer names

Q3. What is a composite key in Microsoft Access?
A. A key made from multiple fields to prevent duplicate data combinations
B. An encrypted password for database access
C. A macro that generates primary keys
D. A temporary index used for sorting only

Q4. Why would you use composite keys in a sales report system with multiple stores?
A. To prevent more than one report from the same store on the same day
B. To hide store data from users
C. To allow unlimited daily submissions from all stores
D. To merge sales data from multiple dates

Q5. What is a key advantage of importing and exporting data using XML in Access?
A. Allows transfer of multiple related tables and compatibility outside of Access
B. Only works with single tables
C. Automatically secures the database
D. Speeds up query processing

Q6. Which versions of Access are specifically mentioned as compatible with the material in this lesson?
A. Access 2007, 2010, and 2013
B. Access 1997, 2000, and 2003
C. Access 2003 only
D. Access 2016 and later only

Q7. What should students do if they do not understand something in the video?
A. Watch the video again or ask for help in the student forum
B. Skip the lesson and move to the next one
C. Reinstall Access
D. Delete the database and start over

Q8. According to the lesson, what is the recommended approach to mastering the examples provided in the video?
A. Watch the entire lesson first, then follow along by building the sample database
B. Immediately apply lessons to other projects
C. Only watch half the video before trying an example
D. Focus only on theoretical understanding

Q9. What is recommended for students who get stuck while working through the example databases?
A. Download the finished database from the website for reference
B. Abandon the course
C. Ask their local IT department to fix the issue
D. Upgrade to a newer version of Access

Q10. What follows Access Expert level classes in the curriculum described by the instructor?
A. Advanced classes covering event programming and macros
B. More beginner classes
C. Project management seminars
D. Web development classes

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-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 Microsoft Access Expert Level 23. I'm Richard Rost, and in this class, we're going to cover several important advanced topics that have not been addressed in my previous series.

We start off by learning about remote data entry. In this segment, I will demonstrate how to build a separate database that your sales reps or technicians can install on their laptops and take with them on the road. The goal here is to allow users to add new contact, sales, or service records regardless of whether they have an internet connection. Once they return to the main office, they can synchronize those new records with the main database server, making sure everything stays up to date.

Next, I am going to show you how to import transaction data from sites like banking platforms or credit card portals. For class purposes, we will use PayPal as our example since its format is fairly standard for most users. We will address the problem of duplicate data by relying on transaction IDs, which help us avoid importing the same transaction twice. I'll also show you how to create queries that help catch duplicate entries, so you can keep your data clean and consistent.

After that, we will explore composite keys. You are probably already familiar with primary keys and single-field keys, which help to prevent duplicate data in your tables based on one field. A composite key allows you to prevent duplicates across several fields, which is useful for scenarios like accepting daily sales reports from multiple stores. With a composite key, you can ensure that no store submits two reports for the same day, maintaining the integrity of your data across multiple criteria.

Following that, I'll guide you through the process of exporting and importing data involving multiple related tables using XML. Up to now, we have worked only with single-table imports and exports. The XML approach is perfect for sending data from several related tables, such as customers and their orders, to someone else - even if they do not use Access. This allows you to preserve relationships between your data tables during the export and import processes.

This class is a follow-up to Access Expert 22, where we discussed cleaning up non-relational data, updating vendor pricing with new data, and revising our order entry system. If you have not already taken that class, I highly recommend you do so before tackling the material in this session.

Today, I'll be working with Access 2013, but the topics we cover should also work with Access 2007 and 2010. Each version has slight differences, so if you run into any discrepancies, let me know and I'll try to help you out. I should also note that none of the material in this class was covered in my older 2003 series. If you are still working with Access 2003, you are welcome to follow along, but be aware that there may be features that do not match up exactly since I no longer have a copy of Access 2003 installed to confirm every detail for you. Still, most of what we do should translate with some adjustments.

Let me remind you of the course structure. My lessons are divided into four main levels: Beginner, Expert, Advanced, and Developer. At the Beginner and Expert levels, you will cover all the fundamentals as well as more detailed techniques like those presented here. When you move on to the Advanced classes, we move into programming events and working with macros. At the Developer level, you will start with Visual Basic for Applications. Each group is split into further levels, for example, Expert Levels 1, 2, 3, and so on, so you can build your skills step by step.

In addition to my standard courses, I offer specialized seminars that focus on specific topics or skills. Some examples include building web-based databases, creating calendar-style forms and reports, securing your databases, working with images and attachments, managing work orders and running a service business, setting up accounts payable solutions, exploring the SQL language, creating loan amortization schedules, and many others. The list of these specialized seminars and their details is available on my website for you to review.

If you have any questions about the topics we cover in this class, I encourage you to post them in the student forums. If you are viewing this class from the AccessLearningZone.com online theater, the student forum should appear next to the class video. Here you will see past questions, my responses, and comments from other students. I recommend taking a look through those discussions as you begin each lesson. Feel free to join in with your own questions or comments. If you are not on the website, you can always visit AccessLearningZone.com/forums later to participate.

To ensure you get the most from this course, I suggest you watch each lesson through once to get an overview before you start working on your own. After that, replay the lesson and build the same sample database along with me, following every step as I demonstrate it. Don't try to apply these techniques to your own live projects right away - practice with the sample database until you feel confident.

If you run into trouble or do not understand something, watch the lesson again from the start, and if the problem persists, describe your issue in the student forums. I'll be happy to help.

Above all, keep an open mind about working with Access. It can seem challenging at first, but with practice, you'll find it quite user-friendly. I highly recommend you create the database I build in class, right alongside me. If you need an example to follow, you can download the completed database file from my website at AccessLearningZone.com/databases. Sometimes the best way to learn is by dissecting how someone else built their solution. That's how I originally learned - by taking apart the Northwind Traders database that comes with Access.

Every one of my courses includes a finished sample database on my website for you to review and learn from.

Let me summarize what we will be covering in today's class:

In the first lesson, we will create a remote version of the database that users can work with offline while entering new contacts and records out of the office.

Lesson two focuses on importing PayPal transactions into your Access database, teaching you how to prevent duplicate imports.

The third lesson covers composite keys - building table keys based on more than one field, which is very useful for ensuring no duplicates exist across multiple criteria such as a store in the same city and state as another store.

The fourth lesson expands your import and export skills from working with just one table to handling multiple related tables using XML, so you can transfer customer and order data together while maintaining their relationships.

A complete video tutorial with step-by-step instructions on everything discussed here can be found on my website at the link below. Live long and prosper, my friends.

Topic List

Building a remote data entry database for offline use
Synchronizing remote database data with main server
Importing PayPal transactions into Access
Preventing duplicate imports using transaction IDs
Setting up queries to detect duplicate entries
Creating composite keys in Access tables
Preventing duplicate records using composite keys
Exporting and importing multiple related tables using XML
Transferring related table data via XML

Article

Remote data entry in Microsoft Access can be an essential tool for businesses with sales representatives or technicians who need to enter data while away from the main office. Imagine your team is out on the road, sometimes without an internet connection, but still needs to collect important information such as new contact records, sales transactions, or service requests. By creating a separate remote database that they can take on their laptops, your team can enter new records while offline. Once they return to the office and reconnect to the main server, they can synchronize their entries, ensuring your central database stays up to date.

To set this up, start by creating a copy of your main Access database. This remote copy should only include the necessary tables and forms needed for data entry. You might want to exclude sensitive data or tables that users should not modify. After your sales reps collect data, you can import the new records from their remote database into your main database by using the Access import wizard. This process is simple if you enforce unique identifiers on your tables (such as AutoNumber primary keys or unique transaction IDs) to avoid duplicate data.

Speaking of importing data, another useful feature in Access is the ability to import transactions from banking websites or payment processors like PayPal. Most banks provide transaction exports in CSV or Excel format, but the format may vary from one bank to another. PayPal, however, uses a fairly consistent download format, making it a good example to work with. When importing transactions, one challenge is ensuring you do not import the same record more than once. To solve this, check for the transaction ID with each new record before appending it to your table. You can set up an Access query to identify duplicates before finalizing the import, so your data stays clean.

In database design, duplicate prevention is crucial. You have likely encountered primary keys before, which enforce uniqueness based on one field (such as CustomerID). However, some situations require a composite key, which enforces uniqueness based on more than one field. For instance, if you have multiple store locations and each store submits daily sales reports, you would not want to have two reports from the same store on the same day. In this case, you would define a composite key based on both the StoreID and the ReportDate. Access supports this by allowing you to set multiple fields as the primary key in table design view. Simply hold down the Ctrl key and select the fields you want, then click the Primary Key button.

Importing and exporting data is a regular task in Access, but working with related tables adds complexity. If you need to export or import data that involves multiple tables with relationships, such as customers and their related orders, you can use the XML feature in Access. This method allows you to export the data along with the relationships intact, making it possible to transfer a full set of related records to others, even if they do not use Access. To export data as XML, go to the External Data tab, choose Export, and select XML file. You can include related tables during this process. When importing, Access will recognize the relationships defined in the XML file and recreate them in your database.

As you work through these techniques, it is a good idea to build a sample database to practice before you implement these processes in your main projects. Following a step-by-step approach allows you to master each concept in isolation, making it easier to apply them to your specific business needs later on. If you run into any issues, review your steps, and do not hesitate to ask for help in forums or communities dedicated to Access users.

Remote data entry, importing external transactions, using composite keys for better data integrity, and transferring multiple related tables using XML are all powerful tools that make Microsoft Access even more versatile for your business. By learning and applying these skills, you will improve your database's flexibility, data accuracy, and the ability to share information, whether your team is in the office or out in the field.