Fill in Dates as Rows of a Query

[Dugantrain]

Hi all,
This one's a noodle-scratcher. I am currently developing an employee-tracking database. I have two Date variables which I have set up. The first is the Friday of the week that the employee was hired on (I just took the Hire Date and used a simple VBA function to determine the Friday date of that week). The second variable determines the Friday date of the current week (for example, it is currently 12/08/2003. This function will return 12/12/2003). Now that I have captured these two dates, I will need to write a query which will populate with ALL Friday dates between the Hiring Friday and the Current Friday, shown as rows which will eventually populate a combo box. Performance-wise, is there a cheap solution?

 

[dcx693]

I've never done this before, but...I suppose that each Friday is just 7 days apart, so you could just set up a VBA function with a loop to add 7 days to your hire date until you reach the current Friday. Write that data to a temp table and return it to the combo box. Performance-wise, I can't imagine this will be fast.

 

[Dugantrain]

Thanks for the comment, DCX. Right, I considered the loop/temp-table option, but as this will be filling a combo box which will have variable results based on the duration of time that an employee has been working, I was hoping for a cheaper solution than writing to the back-end and then feeding the combo every time a new employee is selected.

 

[Pat Hartman]

I hope you don't have any people who have been with the company for 20 years. I'm trying to figure out the point of this exercise and not getting very far.

 

[GumbyD]

Could you try updating the value list property in code? I am not sure if it would work, but you could create the string in code and then update the property. It might be a bit faster if you can get it to work.

GumbyD

 

[Dugantrain]

No, the Max employee has been here less than two years, else I'd just make an arbitrary cut-off point. See, for the past year or so, time-tracking has been kept up with hard copies of Excel Spreadsheets. All of these Spreadsheets will eventually be logged into this database and, to determine an employee's logged vs. unlogged weeks, I'd like to have the Combo populate with two columns: Week_Ending_Date and Hrs_Logged_Y_N (Even if an Employee worked 0 hours for a week, there should still be a record for that absence). If I can get a query of Week Ending Dates for an Employee and then Join this to the table which has Weekly Hours Logs on the Date Field, then this will be my solution. Sounds like the overhead may not be worth the effort, though.

 

[Dugantrain]

Latest brainstorm:
-On DB Open: Get Min Hiring Friday for all employees. Make-table tblAll_Fridays using VBA loop to populate between Min Hiring Friday and Current Friday.
-Time-tracking form: Inner Join this table to form's current employee on tblAll_Fridays.Week_Ending_Date>=tblEmployees.Hire_Date. Left Join this Result set to the Hours Log table on the Week Ending Date to determine whether or not a record exists and then, ultimately, fill the combo with this Result set.

This will cut over-head as I'm only making the table once (unless, of course, an employee is logged who's Hire Date is less than the current Min Hire Date, in which case I'll need to call the Make-table). However, the two Join types, the non-standard >= Join as well as the Left Join will probably still make performance suffer. Crap.