Access 2007-2019
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  
 
 
 

< 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: Recorded with Access 2013. Also use with 2007 and 2010. These lessons may or may not work with Access 2003.
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: $26.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
 

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

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
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