storing historical data in Access

Hi.  I am creating an Access database to store information about
employees, such as their job title and address info.  However, I also
need to store their historical job title and address info as it
changes.  I have other tables in the database to store info on their
different benefit plans.  In the employee info table, I have their
Employee ID (the number that the company assigns to each employee) as
the primary field.  So, it will not let me add duplicate entries for
each employee.  I thought about making the primary key an autonumber
field but I need the primary key to be the employee ID so the other
tables can be linked to this one.  Is there any way I can have Access
store the previous records from a table and output it in a report
format?  Or is there a better way to do this that I'm not thinking
of?  I used to be very good with Access but haven't used it for a
couple years, so I'm trying to re-familiarize myself with it again.

Thanks,
Megan

0
megbowlstrike
10/11/2007 7:30:15 PM
access 16762 articles. 3 followers. Follow

1 Replies
1420 Views

Similar Articles

[PageSpeed] 26

You need a compound key: EmployeeID and EffectiveDate.

You do this by select as many fields as you need for the primary key (up to 
ten), and then clicking on the Key button to set the Primary Key.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


<megbowlstrike@sbcglobal.net> wrote in message 
news:1192131015.090535.48680@k79g2000hse.googlegroups.com...
> Hi.  I am creating an Access database to store information about
> employees, such as their job title and address info.  However, I also
> need to store their historical job title and address info as it
> changes.  I have other tables in the database to store info on their
> different benefit plans.  In the employee info table, I have their
> Employee ID (the number that the company assigns to each employee) as
> the primary field.  So, it will not let me add duplicate entries for
> each employee.  I thought about making the primary key an autonumber
> field but I need the primary key to be the employee ID so the other
> tables can be linked to this one.  Is there any way I can have Access
> store the previous records from a table and output it in a report
> format?  Or is there a better way to do this that I'm not thinking
> of?  I used to be very good with Access but haven't used it for a
> couple years, so I'm trying to re-familiarize myself with it again.
>
> Thanks,
> Megan
> 


0
Douglas
10/11/2007 7:36:49 PM
Reply:

Similar Artilces:

How do I enable read access so I can open an access attachment 04-12-10
I just bought Microsoft Office 2007 enterprise for school. When I try to open an attached "ACCESS" file from my school ( so I can edit it for homework) a box with an EXCLAMATION POINT in it shows up and says "the file does not exist, or I don't have read access to the file.." how do I fix this so I can do my homework? One more thing, I have no problem opening an attachment that is a word document. It's strictly the access program I'm hving a problem with.. Please Help.. Sincerely, Sandy ~AKA~Ms. Confused :) Hi Sandy, I think that your p...

OleDb consumer where is my data source
I have generated an OleDB provider from MS VC++ 7 ATL templates. I have modified this using Lon Fishers tutorial! (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvc60/htm l/msdn_vc6oledbprov.asp) However when I come to generating a consumer (using MS VC++ 7 ATL templates) to test the provider, I get stuck. Lon's article talks how to generate consumers using VC++6 ATL. In VC++7 the wizard requires you to specify a data source. This is the part I am unsure about as I cannot seem to fine a data source for the provider as it reads from text files? Any suggestions??? Th...

Upgrading access 2003 apps deployed with package wizard
I am at my wits' end here, trying to make sense of the Access 2003 package wizard. Does the package it creates not support upgrading of a product in the field? In my testing, I always get a message "Another version of this product is already installed." etc. Which is true, but it seems to me it should not be necessary to uninstall the previous version in order to upgrade it. And in fact I can (apparently) successfully apply the update if I call msiexec directly and pass it the proper command line. But I want this to work with autorun, or by double-clicking setup.exe. Is t...

Exch 2003 store.exe terminates when ESM Replication Status selected
Exch 2003 SP1 on W2K SP4 - had been 5.5 until moving to Exch 2K swing server. 5.5 removed and Exch 2003 SP1 installed, seemingly OK. Weird problem - if I click on ESM->Administrative groups-> group ->Servers-> server->First Storage Group->Public Folder Store(server) and then click on Replication Status, after a delay I recieve a popup saying, "An error occurred while retrieving the requested information. The Microsoft Exchange Information Store service is not running or the Microsoft Exchange Server computer is unavailable. ID no: c103047d Exchange System Manager...

Regression
In Excel 2003, I used the Data Analysis Toolkit to perform a linear regression. Does anyone know how I see the variance of each datapoint away from the line of best fit? Tools - data analysis - regression (i dont know if there is an option in this process that will display the data i am looking for) Thanks. WA - The Regression tool has a checkbox for Residuals, in which case it provides Residuals (difference between actual Y and predicted/fitted Y) for each data point. - Mike http://www.MikeMiddleton.com "WA" <WA@discussions.microsoft.com> wrot...

Mailbox Data
I have 2 mailboxes. One of them is fairly new and has a different last name. I had the old account forward all new emails to the new account, but how do I move the mailbox data from the old account to the new one? Is there a tool I can use, or maybe I can just move the files? Please help. Robert 2 ways... 1. Open old account, copy all the mail to PST file. Then open new account, copy from PST file back into the mailbox 2. Use Exmerge. -- This posting is provided "AS IS" with no warranties, and confers no rights. OR if you wish to include a script sample in your post ...

Multi Store Vouchers
I do not see a way for this to work. I want to be able to give out store credit to our customers but there does not seem a way to make this work for multiple stores. In a single store setup everything is stored in the Voucher table. These vouchers are uploaded during the HQ synchronization but each store does not have access to other stores voucher's. Does anyone have any insight on how I could get this resolved? Thanks, Dan I take it you are trying to issue Store Credit for returns, not actually trying to implement a voucher system for gift cards/certificates. If this is corre...

Writing data back to main form
I have a form that pops up when a certain value is selected in an Option Group on my main form. The user enters free from data in an unbound box on the popup form, then submits the "comment" through a background macro. The macro should write the "comment" back to the main form via a SetValue command, however, the macro is halted because it can't find the original form, which has remained open. Is SetValue the correct command to use in the macro? ...

changing thwe width of data labels?
I have a horizontal bar chart with labels on each bar. They are positioned in the centre of the bar. The labels are quite long e.g. 'Marks for the analysis stage of the project', but would easily fit inside a bar. The trouble is, Excel insists on wrapping the text into a smaller rectangle so some labels are on two or even three lines and overlap the top and bottom of the bar. Can I change the width of the data label box so that the text will fit on one line? Howard Howard - Unfortunately, Excel doesn't allow you to change the size of a data label (or a chart title or ...

Access can't find form "frmWarning" in macro or code
The error message is actually a little longer than the subject. The form name is referred to in code as Forms("frmWarning"). The name is NOT misspelled. and it is a valid form name. "Kruppy" <Kruppy@discussions.microsoft.com> wrote in message news:331E7802-56F0-40AE-8FEE-4194486F0CB7@microsoft.com... > The error message is actually a little longer than the subject. The form > name > is referred to in code as Forms("frmWarning"). The name is NOT misspelled. > and it is a valid form name. Is the form open? Only open forms are mem...

Stored Procedure error message
Hi, I have written scipt in Dex to create tables. After I have added chunk file, tables are creating properly. But the issue is after login into Great Plains I am getting the following error "The Stored Procedure GrantAccess() of form Sql maintenance : 69: pass through Sql returned the following results :DBMS :15151, Microsoft Dynamics GP:0" Any Idea Please... Jack Hi Friends, I got the solution my slef and issue has resolved.. Thanks, Jack "Jack" wrote: > Hi, > > I have written scipt in Dex to create tables. After I have added chunk file, > table...

Looking for Access expert in Phoenix for paid work
I need assistance with enhancing my small work database used for contract tracking. Would like to find someone locally who can assist. Please contact me at 602-381-9655. Thank you. mela, This newsgroup is to assist those wanting to do the work themselves. Your request would do better posted http://www.utteraccess.com/forums/postlist.php?Cat=&Board=34 When you do post it might also be helpful to give a brief synopsis of what you want done. You might also check your local Universities and Colleges, there might be a *board* you can post to. -- Gina Whipp ...

HQ/Store date mismatch
We just deployed our 3rd store and eveyrthing was working fine, transactions were being uploaded and everything. Then, the store ops DB computer got moved, and I guess at some point someone changed the date on the computer to be a month in advance. Now, all of our transactions from 4/25/2006 to 4/27/2006 are actually in the DB as 5/25/2006 to 5/27/2006. I just discovered the problem and changed the date on the Store Ops machine, but I don't know what the best way to fix these transactions is, or if it is even possible. What I'd like to do is go change all of these transac...

access external hard drive
i turned xp in adminstrator to make my files private . how do i undo now i am not able to update my external flash drive or ex hard drive Your question may be better answered in a newsgroup appropriate to your problem. This newsgroup is dedicated to the Microsoft Access database product. The Microsoft website may have misdirected you. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "ray" <ray@discussions.microsoft.com> wrote in message news:D1FCB833-F1CC-430D-84B1-93DCBE9E1EF0@microsoft.com... >i turn...

last user to access a file
Windows 2003 server and Win XP Pro workstation. Is there a way to know who last accessed a file? Thanks. ...

0how do I display wide range of data e.g. with data from 195 to 2.
I need to know how can I display wide range of data from 195 to 2.5 m on the same chart Maybe, with a log scale? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <FF07154A-B77E-4F66-9694-1D8240B48B82@microsoft.com>, YSL@discussions.microsoft.com says... > I need to know how can I display wide range of data from 195 to 2.5 m on the > same chart > > ...

Outlook XP autofill To field
Outlook XP ... When typing in names in the To field of new messages Outlook will start to show names that have either already been typed, sent to or appear in the Contact list. Where is this "autofill" list kept? Is it user accessible? I'd like to get rid of some of the names that are not already in my Contacts. You can just use the down arrow key to highlight a name that is incorrect or whose address has changed and press delete. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding ...

Average Sales per Store
Need help modifying the RMS "Average Sales per Store" report that is found in CustomerSource. Would like to add filters for Department and Category if possible. I have tried to modify it myself, but I'm a beginner at this and I keep coming up short. One other thing that may be useful is if it can summarize by date also - would be nice to see how the values change over the course of a week or so. "Jason Kelton" wrote: > Need help modifying the RMS "Average Sales per Store" report that is found in > CustomerSource. Would like to add filters for D...

merge multilple data values
How do I merge multilple data values ie: in columns js1234 to .jpg to read: js1234.jpg Thanks Jennifer, You can combine two cells with a formula like =A1&B1 -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Jennifer Burnel" <jenburnel@babooshka.net> wrote in message news:h3rpb.294$Ux1.162@newsfep3-gui.server.ntli.net... > How do I merge multilple data values ie: > in columns > js1234 to .jpg > to read: js1234.jpg > > Thanks > > ...

I am trying to get access to display leading zeros and auto numbe
we use a sequential number system that begins new each month but we need it to hold 4 places 0001, 0002. etc. I would like to know if Access will do this. "Donna" <Donna@discussions.microsoft.com> wrote in message news:484176CC-EFDE-48BB-BF18-C5BEFD695710@microsoft.com... > we use a sequential number system that begins new each month but we need > it > to hold 4 places 0001, 0002. etc. I would like to know if Access will do > this. Yes, but ... You can't use the built-in autonumber field to do what you want. Therefore you'll need so...

Passing data between view and dialog
Hi, I need help on the passing of data between a view and a dialog. My problem definition is as follows. I have a view class which is derived from the CView class.In this view class i have a list of bytes arranged in rows and columns.When i select a single byte or a group of bytes and select edit option it opens up a new dialog box.The new dialog box contains the same data as that of the view arranged in rows and columns.The problem is i want to highlight the selected items in the new dialog box that were selected in the view class. Can anybody help me on this. Regards Arun Have a method...

Error Message with Data Validation
I have setup data validation on a cell to force the user to choose from a drop down list of words. How can I make the data validation function put up an error message if the user picks one of the choices on the list and there happens to be no date entered in another totally different cell under another column (this other cell I'm referring to does not have any data validation setup for it)? - Chet You can't get an error message since it is not an error in your data validation, you could use a cell next to the validation and use a formula there and have that formula return a me...

mail merge with Word and Access Query
I am trying to create a Word mail merge with an Access database query that has a parameter (asks for a unique date). When Word asks for "Select Recipients" I go to my database and the dialog box lists the database along with all of my Queries EXCEPT QUERIES THAT HAVE A PARAMETER so I cannot select the parameter query that contains the list of recipients that I want send to . How do I get the parameter query to be listed so I can select it to be the target of my Mail Merge? ...

text and data in same cell
can u put a formula and text in the same cel -- Message posted from http://www.ExcelForum.com Yes - through concatenation. =string&TEXT(value,format) or =TEXT(value,format)& string, or any combination string and value can be cell references. You might want stick a &" "& in the middle for separation. Hope this helps. >-----Original Message----- >can u put a formula and text in the same cell > > >--- >Message posted from http://www.ExcelForum.com/ > >. > im not quite following this can u put it in terms a 3 year old ma understa...

Xmlhttprequest
Hi, I'm trying to find a way to use XMLHttpRequest to recieve an xml file and then use XMLHttpRequest to send an xml file back based on the xml file I received. Can anybody help? Thanks in advance rwiegel@iastate.edu wrote: > I'm trying to find a way to use XMLHttpRequest to recieve an xml file > and then use XMLHttpRequest to send an xml file back based on the xml > file I received. Can anybody help? If you receive XML with XMLHttpRequest then you get a responseXML DOM document that you can manipulate as needed and pass to the send method to send it back. Or create a ...