Create a tracking table?

[bigtman07]

Hey everyone!! Since you all were such a big help with me figuring out my checkbox issues I have another challenge for you all. I'm going to explain this the best that I can, it may be a bit convoluted. So if you are confused let me know and I will attempt to clarify. So here goes nothin...



I have my database and my form all created and working. Reports and everything are in place. But we need to have a record of what changes have been made. Essentially what I want to make is a table that is essentially a log of all the changes that have occurred.
What I have is essentially a time clock database. So when someone enters our facility the guard will check them in and out. What I want is a list of all the ins and out for a day. So it will be a running list. Then I will create a query to return results for a report. But where I am having problems is creating a table that will house this data. So out of my main table I want a child table that will have certain data fromt he parent table. Mainly the time in and out along with the name of the person. There will be multiple repeat data entries in this table and I am ok with that. That is actually what we are wanting. Hope this all makes sense. Any help or direction would be great!! I tried using relationships but it isn't doing what I am wanting. I may not have them set up right.

[June7]

First you say you want a record of changes that have been made then you describe a time-clock log. These are two different things. A record of all the 'ins and outs' is just data entry by the guard or someone else entering data from a paper sign-in sheet. Shouldn't be very difficult.

tblLog
VisitorLast
VisitorFirst
TimeIn
TimeOut

Gets a little complicated if you want to maintain a list of people and assign them an ID.

What do you mean by 'out of my main table I want a child table that will have certain data from the parent table'. Child tables do not get data from parent table. Possibly you mean a log table that has a lookup to table of people.

[bigtman07]

I'm not sure how to explain myself lol. I have a table right now that has my data in it. Has the ID numbers for all the people entering and exiting the facility. I am essentially trying to duplicate it with just the data like you presented. That's where I got the parent and child table idea. Sorry if that was confusing. Right now the data is populated and filtered to a form by ID Number. After the guard checks them in I want to save the updated data to my contractor table that is all fine and dandy. But I also want it to save to another table at the same time. that make more sense?? So my log table for example would have:
FirstName, LastName, Company, TimeIn, TimeOut. These values are the same as my Contractor table. But every time the form is saved I want the log table to add a line so the log table would look like this;

FirstName LastName Company TimeIn TimeOut
Joe ______Smith __ Access__ 900 ___1200
Joe ______Smith___ Access __1300___ 1700


Does that make more sense?? Its hard to explain in text. I can see it in my head just relaying the info is difficult sometimes.

[orange]

You should probably include the EntryDate if you are keeping track of TimeIn/TimeOut.

[June7]

Saving same data to another table does NOT make sense to me.

[bigtman07]

Very well. Is there any way to keep a list of all the enter and exits you can think of without doing the same data in 2 tables? Right now when the person is checked in or out the date field i have will change but the previous entry will be lost. We want to save that previous date somewhere. This way we can go back and look to see if a person was here or not. The only way I could think of it was to create a new table and have all the records saved in that table. I'm open to any other ideas you may have. But it cannot be manual entries. It must be as automated as possible. That's why I was just going to save the same data in 2 tables. One thats the list and one that is just a temporary storage.

[June7]

Move to New Record row to enter new record and not overwrite previous records. This can be done with the intrinsic NavigationButtons at bottom of form or with code behind custom buttons. Automation can do only so much. Someone still has to push a button or select from a combobox or type a value into textbox. Your two tables idea does not change that.

Date fields can be set with a DefaultValue of Now() so that when a record is initiated by entering data into other fields (selecting from combobox or typing into a textbox) the date value will automatically populate.

I just don't know enough about your data and business process to be more specific. Is this data entry happening 'real time' at guard station or is someone entering data later from a paper login/logout sheet? You show the record set up with two fields for login and logout. This requires finding the record initiated at login and updating the logout time stamp (possibly by pushing a button that has code to populate the field with Now()).

[bigtman07]

It is being done in Real time. The guard checks a box to show if they are in or out. That action populates the time in or time out with the Now() command. with every check of the checkbox the time is updated. which is fine for the display. But what if we need to look back on friday and see if someone was there on monday?? We can't because the date is updated...

The issue is that our guard may be fudging times and since we have multiple ones we can't find who it is. So we are trying to eliminate as much of the guard factor as possible. That is the reason for the automation. So if there is a way to link the check box action to the new line creation in another table I think that would work.

[June7]

Could set the form to only allow new record entry. Set the DataEntry property to Yes and the form will not display existing records. It will show only records entered after the form is opened. Those new records can be navigated but records existing before the form opened will not be available. Of course, this requires that all records each day have both in and out times completed and the form must be closed. Require the guard to do a 'log out' procedure and in that process, close the form.

Also, this requires securing the db so that guard cannot work directly with tables. It doesn't matter how many tables you replicate data into, if guard is capable enough to figure out the data structure and has access to the raw data, they can do whatever they want.

[bigtman07]

That's what I would like to have done except in a new table. I can't figure out how to do that.

[June7]

Not sure what that means.

Maybe you need an audit log of your login/out table. Here is one example http://allenbrowne.com/AppAudit.html

You could use the info in the audit log to analyse who is modifying data. However, again, if user (guard) is capable, even this documentation can be hacked.

[bigtman07]

Ok the Audit is a good idea on the guard front. But it still won't help the contractor leaving and entering front. After re-reading your post from the other day that sounds more like what I want to do. I must have been looking too long at this thing my apologies.

Ok so if we back up here and go back to basics here. I have many contractors and each contractor comes in and out of the plant many times. Right now I know I have a horrible database design. Mainly because I haven't touched access in years and my work is wanting me to bring back what I know after a 3 year absence. So I know for you to help me you need to have all the info. So here goes...

I have a single table at the moment. ( I know horrible design) But this one table has all the info mainly because I couldn't get the tables to link properly. So I just put them all into one database and ran with it.
I have many contractors. But each contractor can only have one company. Each contractor can have multiple time in and time outs. So here is my thought...
If I can split my tables up to a names table a time table and a company table this may work better??? I'm going on a limb here. I'm surprised I have remembered as much as I have. But if we can't create a time log of some sort I'm going to have to redo this thing all over again.

If there is a way to use my current check box entry to put the persons name, time, and company into a daily report that shows all the in and outs that is exactly what I want. Which would work now if there was only one in and out entry. But since most of the contractors go out to lunch there will be 2 entries. I need to capture both those entries. I remember seeing a sample of that back from my access book but I can't find it.

But either way I need a running list of all enter and exits. So a person comes in and gets checked in an entry is created. The person leaves for lunch and entry is made. Person comes back another entry is made and so on. But I don't want to use my lookup table for that. I want a seperate list with all that information. Then I can make a query to pull only a days transactions.

[June7]

Yes, should split data into separate tables. That is a main principle of a relational database.

The tables would be joined by query to show all the related info in a report.

Have a form for entering the time stamp record. Select contractor from combobox (if this is new contractor and not in the list, ways to deal with that). Date field has DefaultValue of Now() so the date/time is automatically populated. Select In or Out from a combobox or a radio button in an Option Group control.

[bigtman07]

Can the selection be done by a search like feature?? Right now I have an unbound text box that filters to show only the desired record. I will work on separating the tables but I can't get the relationships to work. Keeps telling me i can't combine 2 different entry types. It makes no sense.

[June7]

I wondered why I had deja vu hit me. You might find this thread of interest https://www.accessforums.net/program...ion-39132.html

Different in that they want the in and out in the same record but the search code could be relevant to you.

Can't 'combine 2 different entry types' where? As in the table link? Primary key and foreign key fields must be the same type. If a primary key is an autonumber then its related foreign key field must be number (long integer).