Help creating a history...

[kalebson]

Hi,

I am trying to create a historical record of Employees at our business. What I would like to do is have a query that will create a begin date and create that date for each day starting w/ like 1/1/06 and continue thru Date()-1. For example:

Jane Doe and John Doe started employment on 1/1/06. Jane Doe was employed until 1/5/06. So using their "Hire Date" I would like the query to do this..

Created Name Hire Date Termination

1/1/06 Jane Doe 1/1/06 1/5/06
1/1/06 John Doe 1/1/06
1/2/06 Jane Doe 1/1/06 1/5/06
1/2/06 John Doe 1/1/06
1/3/06 Jane Doe 1/1/06 1/5/06
1/3/06 John Doe 1/1/06
1/4/06 Jane Doe 1/1/06 1/5/06
1/4/06 John Doe 1/1/06
1/5/06 Jane Doe 1/1/06 1/5/06
1/5/06 John Doe 1/1/06
1/6/06 John Doe 1/1/06
1/7/06 John Doe 1/1/06

So in this case Jane Doe will show up every day in the "Employee List" until she was terminated. Is it possible to loop a date like this in a query or will I have to make an estranged table with a list of dates? TIA for any help.

 

[The_Doc_Man]

Date enumeration is not done efficiently with queries such as you describe. You can step through dates in VBA to generate entries in a table if you must, but such a table is not normalized.

Sadly, that format isn't making sense to me, either. Are you trying to build a calendar showing on which days a given person was employed? You can probably do that, but holy guacamole, that is a really inefficient format if you start showing lots of people in the same display or report.

I guess part of the problem I'm having is that I see no purpose to this report, which is terribly presumptuous of me and I know that. But if I can't grasp the problem, I can't solve it very easily.

 

[kalebson]

I think I got a solution for this I appreciate the info. We just needed a way to show a history of when a rep comes and goes. and so we dont drop their stats if they arent currently in the system. It a nutty process for sure.