Is this the best way to handle changing records?

[teresamichele]

Hi!

I'm building a database that will contain all of our time entry records, and then writing reports so we can see the data in a way that is useful (as opposed to the massive Excel spreadsheets we get now that no one reads). It will be set up to be able to sort by employee, manager, director, etc.

Here's my problem, how I think I can fix it, and a request for someone to tell me if there's a better way to do this.

Let's say John Doe works for Jane Smith right now. His current Employee Number is 12345. All is well.

Then John gets promoted and now works for Bob Thompson. His employee number doesn't change. His name doesn't change. His boss *does* change.

If I change his organizational structure in his line on my Employees table, it's going to update ALL of his records - not just the ones post-promotion. This is not good, because I want the historical records to be accurate.

What I was thinking I could do would be to give an AutoNumber ID to everyone, and then have the "new" John Doe be a new line item. The downside to this is when I import the information from Excel, I'd have to add that in...for several hundred people. It'd only be about an hour or two a month, but it seems like a step that could be avoided but I don't know how!

Any thoughts - or is this as good a solution as I'm going to find for this annoying little problem? Thank you!

[jgelpi16]

What if you created a record change field? Have that field default to either Date() or Now() - depending on what you want to show. Then you say for any record on or after the field date change the employee's structure. Does that make sense?

[teresamichele]

What if you created a record change field? Have that field default to either Date() or Now() - depending on what you want to show. Then you say for any record on or after the field date change the employee's structure. Does that make sense?

It sort of makes sense. I get the idea behind it and yes, it sounds like it would work, but I don't get how to do that. Is it something done in VBA, the Employee table, or somewhere else?

Thank you!

[jgelpi16]

What I usually do is create a separate field at the end of my table. For this post we'll call it "UpdateDate". In the design view of the table change the default value of the field UpdateDate to Date() - If you want just the date to populate OR Now() to populate Date and Time in the field. Make sure when you create the field you set it as a Date/Time field vs. Text or Number. Keep in mind this will only work for records creating going forward. For any records already existing in the table you'll need to run an update query to update the UpdateDate field.

Does that make sense?

[SteveH2508]

Surely this is a Data Dependancy issue?

You want to store the time entry, the employee and the boss at that time. That time entry record needs an EmployeeID and a 'Boss' of that record so the BossID should be stored in the time entry record, not the Employee record.

[teresamichele]

What I usually do is create a separate field at the end of my table. For this post we'll call it "UpdateDate". In the design view of the table change the default value of the field UpdateDate to Date() - If you want just the date to populate OR Now() to populate Date and Time in the field. Make sure when you create the field you set it as a Date/Time field vs. Text or Number. Keep in mind this will only work for records creating going forward. For any records already existing in the table you'll need to run an update query to update the UpdateDate field.

Does that make sense?

It makes sense up until the end.

Let's say John was promoted Jan 1st but I didn't know until today. I change it so that from here on out, the data will say he's under the correct person.

However, if I can't figure out Update Query, it wouldn't be bad to manually update that. This won't be a super-common happening, but it will happen from time to time. I'm just trying to avoid an "AAACK!" moment a few months from now!

Thank you for your help!

Teresa

[teresamichele]

Surely this is a Data Dependancy issue?

You want to store the time entry, the employee and the boss at that time. That time entry record needs an EmployeeID and a 'Boss' of that record so the BossID should be stored in the time entry record, not the Employee record.

The problem with this is that it still has me modifying every record I import, since they don't automatically have their bosses on them. If it were a few people, it wouldn't be a big deal, but we're talking several hundred.

[jgelpi16]

Ok, so create a field on the end of your table that says something the affect of "ChangeDate", whatever you want. When you create the field go to the field properties at the bottom and set the default value to Now(). Make sure the field is set to Date/Time not Text. From this point forward every time you enter a record into the table that field will automatically populate with the date and time the record was created. For the fields already in the table you will either need to go back and manually enter dates OR just leave them as is and just have the dates populate from this point forward.

[teresamichele]

Ok, so create a field on the end of your table that says something the affect of "ChangeDate", whatever you want. When you create the field go to the field properties at the bottom and set the default value to Now(). Make sure the field is set to Date/Time not Text. From this point forward every time you enter a record into the table that field will automatically populate with the date and time the record was created. For the fields already in the table you will either need to go back and manually enter dates OR just leave them as is and just have the dates populate from this point forward.

I tried this and it changed all of the records for the person in question.

It might be how dates work for the imports? I call each fiscal month something like "Feb2011" for February of the 2011 fiscal year. Then I have, next to it, the date the month ends. Could how the database is dealing with dates be why this is changing all the records?

[jgelpi16]

Ok, I think I've mis-understood your dilemma a bit...

My method works for records added after the initial set of records are added. I think you are looking for a way to specify the date prior to appending the initial set of data to the table. Is that more accurate?

[teresamichele]

Ok, I think I've mis-understood your dilemma a bit...

My method works for records added after the initial set of records are added. I think you are looking for a way to specify the date prior to appending the initial set of data to the table. Is that more accurate?

Yes! I'm sorry!

When I said I was creating it - I mean I'm building it from scratch so that's why I'm trying to figure out all of this stuff *now* before I build it and then need to "fix" it - and the fix ends up taking more time than building the database in the first place.

The Excel sheet I currently get contains:

Employee Number
Employee Name
Fiscal Month Start Date
Fiscal Month End Date
Project Code
Project Name
Hours Worked

What I'm trying to do is to be able to upload this data *without changing it very much if at all* - but I need to be able to do that and have something built-in that allows me to deal with people who change positions.

Does that make sense or do you need more information?

[jgelpi16]

Hmmmm....This is hard for me to visualize without the "stuff" in front of me. Let me try to re-iterate what you just said so I can be sure I understand...

You have an Excel spreadsheet with employees in it:

EmplNum, EmlName, FiscMonthStart, FiscMonthEnd, ProjCode, ProjName, HrsWorked
001, Bob Jones, 01/01/11, 01/31/11, 035, TestProject, 35
002, Jane Smith, 02/01/11, 02/28/11, 027, ProjectSomething, 45
001, Bob Jones, 12/01/10, 12/31/10, 040, SecondProject, 27

You want to designate a different date for each one of the instances of employee 001?

[teresamichele]

Hmmmm....This is hard for me to visualize without the "stuff" in front of me. Let me try to re-iterate what you just said so I can be sure I understand...

You have an Excel spreadsheet with employees in it:

EmplNum, EmlName, FiscMonthStart, FiscMonthEnd, ProjCode, ProjName, HrsWorked
001, Bob Jones, 01/01/11, 01/31/11, 035, TestProject, 35
002, Jane Smith, 02/01/11, 02/28/11, 027, ProjectSomething, 45
001, Bob Jones, 12/01/10, 12/31/10, 040, SecondProject, 27

You want to designate a different date for each one of the instances of employee 001?

You were right on up till the last bit -but you probably mean what I meant, but worded it differently.

Basically, I want to have the spreadsheet you describe but let's say Bob worked for John for December but worked for Jane for January - when I run the report for all of Bob's hours for December and January, I want them to be for the correct managers.

That said, I've given this some thought, and I have An Idea.

Let's say I enter all my people's data. When I get the word Bob gets promoted, I *copy all of his data into tblHistoricalData* - and when I make my report, use both tblHistoricalData and tblCurrentData to bring everything together. If I let the "manager" field in tblHistoricalData be unlinked to tblEmployee so it doesn't auto-update - it should show everything correctly, right? It works in my head, but I think I'm going to make a fake database this morning and see if it actually does what I want!

[jgelpi16]

Ok, so there is another field in your Excel table that shows his manager. Are the records always in the order of newest to latest? (first manager to most recent manager). I think you are on to something with the historical table. You may want to consider making EmplNum a primary key field in your main table. Sorry I can't be of more help, I'm having a difficult job explaining but I do know what you are trying to accomplish. Hope this helps and let me know if you have more questions.

[teresamichele]

I figured it out!

What I will do is this:

Keep an "Employee Table" with the most current information.

Every month, I will import the file that only has the employee number, unmodified. I will then run a query to complete a table that will show every person's time with the correct director, etc.

I will then *export* that file into Excel and import into "Historical Data" - thus assuring the data is always up to date, historical records will be maintained, and I don't have to manually update every single file every month.

All of my searches will then be run off of "Historical Data" as opposed to trying to keep a historical table and an active table. Much simpler and cleaner this way. Plus, I can save the export/import steps so it'll take close to no time!

I'm so excited to have a fix for this!

Thanks so much for your help!