Hello, i'm new to this forum and i was wondering if anyone could please assist with the following:
have a db table in the following format
Table Name: Absence_Entries_Detail
Name--Abscence type--StartDate-- End Date
Person 1 --Vacation -- 1/1/2014-- 1/4/2014
Person 2 --LOA -- 2/1/2014-- 2/2/2014
What i need is to split the single record to multiple records for each date in the specified date range( as seen bellow). This need to be done in a new table so that the original raw data is not changed.
Name /Abscence type/ Date
1 Vacation 1/1/2014
1 Vacation 1/2/2014
1 Vacation 1/3/2014
1 Vacation 1/4/2014
2 LOA 2/1/2014
2 LOA 2/1/2014
The final result should include the name, the abscence type and the specific date
I've tried several solutions but have been unable to get them to work.
I am familiar with SQL statements but have never used vba in access, so any step by step assistance would be greatly appreciated.
Can you say more about your overall purpose, since it seems an unusual solution that requires one record for each unit of measure of some arbitrary substance, in this case time. Like, you would never create one record for each dollar in an accounting context. How is it useful to create one record for each day in measuring time?
The reason i need a day by day instance of each item is so it can be cross referenced later on with other raw data's that also come in a day by day instance. Say for example if the attendance table ( in day to day format) says that you did not come to work on 1/1/2014 but that day the Absence_Entries_Detail table says that you had a vacation therefore this is a justified absence. There are several reports that show what occurs each day and they can not process date ranges and i don't have the ability to manipulate these reports so its crucial that i breakdown the raw data that currently exists. I've seen several VBA codes but am not sure how to adapt them to my needs or how do i get the code to run.
Cross-referenced? But you would check the table for the ID of the Person, correct? Then you can easily see if records exist, and if so do a comparison. But isn't it more work to compare to four records rather than one? To see if a date falls in a range is easy . . .
Code:
If StartDate <= TestDate AND TestDate <= EndDate Then
. . . but you want to compare to a single date in four different records?
It seems to me that what you want to do is harder, AND it's going to take more work to get there, AND it's going to mean supporting two different versions of the same raw data. So those are some costs, and I don't understand the payoff? How will you be closer to a solution?
Thank you for your responses so far.
Beside cross referencing the information which is a validation process that i do. This data is also uploaded to a tool, and currently i am doing it manually item by item and it takes a lot of time since i know this could be automated. The system only allows items to be entered day by day and i have no way of changing the tool. I would really appreciate it if i could get assistance in creating vba code that could help me with this.
OK, well what kind of help do you need writing VBA? Do you know how to run code? Do you know how to write a loop? And then write a loop for each day between two dates? And you'll need to insert a record for each loop, so do you know how to programmatically insert a record into a table?
I have only written code in Excel. But i do not know how vba works in access. I know how to program for, do while and do until loops but i have never done these in access let alone created records in access based on code. If you could help me in creating the code that would be great
OK, so I'm not going to write it for you, but I'll tell you what you need to do step by step, and you can tell me where you get stuck, and we'll troubleshoot, OK?
1) Create a form. Put a button on the form. Run code on the button click. Show a message box that says, "Hello World!"
2) Next step is put two textboxes on the form, one for start date, one for end date. Enter a date in each one. Now, on button click, run a loop once for each day between the start and end dates in the textboxes. Be able to show the date for that loop. Debug.print or MsgBox
3) open a recordset of the source data. Use that for your start and end dates. Run the loop between the dates in your source data.
4) using your loop and your recordset, insert a new dated record for each loop iteration.
I actually have a function in a tool I'm finishing up that does precisely this: you submit to it a range of values, and it returns an array with the range broken out. (I do this because we're given info with a service code, a fee, and a range of modifiers, but the internal system that processes these claims requires each service code/modifier combination to be entered individually.)
If this sounds like what you need, I can post the code I used, although you'll need to modify it pretty heavily for your purposes. It will include a number of related functions.
Edit - I leave work in ten minutes, and will be visiting my father in the hospital all evening, so the soonest I can provide this will be tomorrow.
Thank you for your response. That is exactly what i need to do ! It seems that i have the same situation because the system that i need to feed the data to also requires individual entries. I would appreciate it if you could share your code !
Thanks so much for the assistance
Unless this system requires separate data actually be STORED for each period of absence, you're actually storing the data correctly as-is, and I would only use a splitter for queries, reports, and exports.
First off, here's a routine that will break a date range out into individual dates. You can choose to include or exclude weekends, but if you want to exclude holidays, that's going to be a bit more complex.
Code:
Public Function SplitDateRange(ByVal StartDate As Date, _
ByVal EndDate As Date, _
Optional ByVal IncludeWeekends As Boolean = False) As Date()
' ************************************************************
' Created by : Scott L. Prince
' Parameters : Start date
' : End date
' : Whether or not to include weekends in the output
' Returns : An array containing each individual date in the provided range
' Date : 3-20-14
' Remarks :
' Changes :
' ************************************************************
On Error GoTo SplitDateRange_Err
Dim aDateList() As Date
Dim DateCount As Long
Dim DateVal As Date
'Defaults
DateVal = StartDate
'Cycle through each date to be included.
Do Until DateVal = EndDate + 1
If Not (Weekday(DateVal) = vbSaturday And Not IncludeWeekends) And _
Not (Weekday(DateVal) = vbSunday And Not IncludeWeekends) Then
'Increment DateCount by 1.
DateCount = DateCount + 1
'Resize aDateList() to include one more item.
ReDim Preserve aDateList(1 To DateCount)
'Add DateVal to aDateList(DateCount).
aDateList(DateCount) = DateVal
End If
'Increment DateVal to the next day.
DateVal = DateVal + 1
Loop
SplitDateRange = aDateList
SplitDateRange_Exit:
Exit Function
SplitDateRange_Err:
MsgBox "Error encountered in procedure SplitDateRange!" & vbCrLf & vbCrLf & _
"Error:" & vbTab & vbTab & Err.Number & vbCrLf & _
"Description:" & vbTab & Err.Description, vbCritical, "ERROR"
Resume SplitDateRange_Exit
End Function
This will provide you an array listing each individual date in the range.
To actually duplicate the records, what I would suggest is the following:
Create a table. Name it something like tblDateRange. There should be three fields:
One should have the identifier for your employees (I assume you use employee ID number)
The second would have absence reason
The last should be a date field (I'd recommend ShortDate format).
Also, depending on whether these are permanent changes to the records or only used for reports/exports, you will need either the final destination table (for the former), or a blank 'raw data' table for the latter. In the second case, it would need: Name, AbsenceReason, and Date.
Finally, build an append query that includes:
The source data (ID plus name and whatever else you want in the final data that's not already in tblDateRange)
the IDs, reasons, and dates from tblDateRange
Set it to update to the final destination table.
Link the two ID fields together in a one-to-many
Save this query.
Now:
create a function that opens up a recordset with the info you want to break out - in the case you provided, it would be ID and/or name, plus absence reason, start date, and end date for each person for whom you're doing this.
Delete all data from tblDateRange.
Loop through this recordset, and for each entry:
Run the split date function
Open a query based on tblDateRange.
Loop through each value in the returned array, and add to tblDateRange the employee's ID, absence reason, and the date for each value in the array.
Execute the append query I mentioned above.
At this point, you'll have either a permanent table full of broken out PTO dates (which I still recommend against), or a temporary data table that you can use as a basis for reports and output.
You WERE told this wasn't going to be simple.
Anyway, if you have questions about any of this, ask and someone will help. Also, if you do end up doing this my recommended way, keep in mind you'll want to compact the database fairly regularly, because constant deletion of records will bloat the database if you don't compact it.
