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  

News      User Comments     History     Notify Me

6/26/2009 1:08:35 PM
Access: Saving Historical Data
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

Here is a great question that I get asked all the time. I get it so frequently that I decided to share my answer here with all of you.

Q: Hi Richard. I have just completed your Access 204 tutorial. I found it to be most enjoyable and very helpful. I am working on a database that I had already created before I discovered your valuable service. Can I ask you please to recommend another of your tutorials? My problem this time is I have a products table that contains items and prices. Each client order is set up by means of a form and a subform related to this table. If I amend a price in the products table it affects all my records historically! What have I done wrong?

A: This is a CLASSIC example of why you need a separate table to save historical data like this. It's the same thing with customer info - you pick a customer for an order and you can link back to the customer table to get the address, but what if that customer moves? You look up the order six months from now to find where it was shipped, but the customer has moved and the address changed. You need to store that data in the ORDER table.

For product prices, you need to store the SALE PRICE of the item at the time of the sale in the ORDER DETAILS (line items) table. This way when the price changes in the product table for the future, it doesn't mess up the previous data for all of your orders.

I cover this in my 300-series dealing with designing the Order Entry system. I specifically cover THIS example in Access 302. But in order to benefit the most from these lessons, I would recommend starting with Access 301 so you get all of the background on CORRECTLY building the Order Entry tables (products, orders, line items, etc.)

Hope this helps. Enjoy!

Permanent Link
Keywords: access tips order entry historical data product prices
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 8+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:

3/26/2020Sorted Combo Box Values
3/23/2020Browsing and Selecting Files
3/14/2020New Access Group
3/6/2020NEW Access 2019 for Beginners
8/15/2019Last Chance for Access CDs
8/1/2019Customer List Form Template
8/1/2019New Template Access Customer Database
7/30/2019Microsoft Access Developer 13
6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12

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