Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  

< Previous: Access Expert 22

Next: Access Expert 24 >

Access Expert Level 23

Expert Microsoft Access Tutorial - 1 Hour, 40 Minutes
In Access Expert 23 we will build a database for remote data entry. This will allow our sales reps or service technicians to add contact records on the road. We will learn how to import bank transaction data. We will learn about multi-field composite keys, and how to transfer related tables using XML data. Topics include:
  - Remote Data Entry Database
  - View Info, Add Contacts Remotely
Synchronize With Main Server

  - Import Bank Transactions
  - Prevent or View Duplicates
  - Multi Field Composite Keys
  - Prevent Duplicate Sales Reports
Transfer Relational XML Data

  - Exporting Customers With Orders
  - Maintain Relationships via XML Files

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.


Access Expert Level 23
Description: Access Expert Level 23
Versions: This class was recorded with Microsoft Access 2013. The material is valid for Access 2007 up to 2021. Access has not changed that much over the years.
Pre-Requisites: Access Expert Level 22 strongly recommended. This class builds on the import/export topics covered in the last couple of classes.
Running Time: 1 Hour, 40 Minutes
Cost: $28.99

Today we will begin by building a project that takes advantage of the data import/export lessons we've been studying over the last few classes. We will build a remote data entry database for our sales reps or service technicians to take on the road with them. They may or may not have Internet access while on calls, so we want them to be able to view customer information and previous contacts, and to be able to add new contacts while they're on the call. Then, when they return to the office, they can click one button to synchronize their records with the main office database (upload all new contacts, and download any new or changed customer information). It's a great solution for anyone in this situation. I'll use contacts in class, but the same technique can be used for orders, inventory, or whatever else you want to make available remotely.

remote data entry database


Next, we'll learn how to import bank transaction data into our database. This will allow you to maintain your own transaction listing, or perhaps build a check register database to track expenses. We will use data from Paypal in class, because it's a fairly popular, world-wide system. Every bank is different in the format of the data that they provide for download, but using the techniques in this lesson you should be able to manipulate things for your bank.

import bank data


In lesson three, we will learn about multi-field composite keys. These allow us to index values in multiple table fields. This means that you can prevent duplicate values across these fields. For example, you may have a bunch of stores that have to report daily sales figures. You wouldn't want the same store reporting sales for the same day twice. A composite key will prevent this.

composite keys


Finally, we're going to learn how to use XML files to export information from our database to be shared with other databases while maintaining the integrity of our relationships. For example, if you want to give someone else a list of all of your customers and their orders, we can use an XML export to give them our customer, order, and order details tables all in one file with the IDs and relationships preserved. This is very powerful for sharing data.

xml multi table export


This is the 23rd class in the Access Expert series. There are a lot of good, practical examples of how to use the import techniques of the previous few classes in this one - plus a lot more. Of course, if you have any questions about whether or not this class is for you, please contact me.


Complete Outline - Access Expert Level 23

00. Introduction (7:58)

01. Remote Copy For Data Entry (36:44)
Copy PC Resale Main Database
Customize for Remote Use
Lock Customer Form
Don't Allow Edits, Deletions, Additions
Only Allow Additions on Contact Form
IsRemote Yes/No Field
Link to Main Database Tables
Not Connected to Network Error Message
Append New Records to Server
Delete Data in Remote Database
Copy All Records from Server to Remote
Macro to Perform Synchronization

02. Import Paypal Transactions (25:37)
What is Paypal
Download Paypal History File
Import Paypal Data Into Access
Set up my Transaction Table
Prevent Duplicate Records
03. Composite Keys (15:44)
Prevent Duplicate Values Multiple Fields
Create Store Locations Table
Do Not Want Two Stores in Same City
Indexed, No Duplicates
Indexes Dialog Box
Create the Composite Index
Also Called a Compound Index
Make Sure Store Doesn't Report Sales Twice
Create StoreSalesT
Prevent Duplicate Store Sales Figures
Allowing Duplicate Values
Find Duplicates Query Wizard
SQL Statement Explained Briefly

04. Transfer Multiple Tables XML (7:23)
Send Multiple Tables with Related Data
Exporting Customers with Orders
Export XML Menu Select Tables
Import Tables Into a New Database

05. Review (7:29)


Keywords: Remote Data Entry, Import Paypal Transactions, Composite Keys, Transfer Relational XML, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, synchronize with main server, prevent or view duplicates, multi field keys, export customers with orders


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 5/22/2024 11:38:41 AM. PLT: 0s