how to keep my old data

[chriswrcg]

I have touched on this topic before but I still do not fully understand how this all works. If I go in to my database today and enter in the information from a particular associate and hit save, all the data is saved in it's particular field. But if I go in and enter the data from yesterday the data I put in for today is lost.

example:


associateiD 1 (auto number)
AssociateName McGhee, Dean
Date: 10/3/2018
ClockIn: 2:00 pm
ClockOut: 10:00 PM

Now it is tomorrow and I want to add today's numbers so i change the date to 10/4/2018 and the clock in and clock out times. the associate ID number and name stay the same.
I want to build a daily record so that I can print daily, weekly, and monthly reports but by changing the three fields and saving I have lost the first days data.

How can I go in and change it every day but keep what I entered yesterday. How can Access archive each days data so I can go back and get it at any time? Will I just keep saving a new database each day? I am really confused.

[CJ_London]

sounds like you are missing a table. You need 2

tblAssociates
associateiD (auto number)
AssociateName

tblClocking
ClockingID (autonumber)
AssociateID long
ClockDate date
ClockIn date
ClockOut date

The associateID in tblClocking links back to associateID in tblAssociates to pick up the name

Note that Date is a reserved word and should not be used for a field name

[davegri]

It sounds like you are overwriting yesterday's record with today's data instead of creating a new record for today and leaving yesterday untouched.

[chriswrcg]

I know that this is going to be an eye rolling noob question but how do I create a new record for today without messing with yesterdays data?

[CJ_London]

go to a new record.

at the bottom of table/query/form you will see this



click on the icon circle in red

if this does not answer your question, provide a screenshot of what you are seeing

[chriswrcg]

Right now I have my Primary key set for auto number. This gives each of the people an individual associate number. If I click on new record, a new associate number will be generated for each day so that McGhee, Dean will have an associate number of 1 and then later 101 if I have 100 employees entered for the first day. Wont that mess everything up if all the relationships of all the tables is based on the auto number?

[CJ_London]

Wont that mess everything up if all the relationships of all the tables is based on the auto number?

see post #2. You need to read up on normalisation and database design

[davegri]

Attachment 35759

Here's a quick example of Ajax's suggestion.
The associate occurs once in the associate table.
The clockins occur as many times as necessary for each associate in the clockin table. That's a One-to-Many arrangement.
The main form record source is tblAssociates, while the subform source is tblClocking.