How create a table for a date range report

[jegupta]

Hi, I'm working on a work scheduling database for a local utility company. They are requesting a report which will display all of the scheduled work by day for a user-inputed date range.



All of the work is stored in a table by WO#, work start date & end date.
WO start date end date
1234 1/20/2014 1/25/2014
2345 1/1/2014 2/15/2014
4567 1/26/2014 1/31/2014

The end user will enter a report start & end date from a form (ex: start 1/23/2014 end 1/30/2014)

The report should display the following:
1/23/2014: WO 1234, 2345
1/24/2014: WO 1234, 2345
1/25/2014: WO 1234, 2345
1/26/2014: WO 2345, 4567 etc...

I was going to try creating a temp table & for each date, cycle through the records and copy each one that applies to the temp table. I'm just not sure how to get started or if there is an easier way to do this. Any help or suggestions would be greatly appreciated!! Thanks, Julie

[June7]

Unless you want to create a table of dates, temp table is probably the way to go. However, since the number of work orders is unknown and could conceivably exceed field limit of 255, consider concatenating the work order numbers into a single string for each date.

This will involve opening a recordset object of the source data and also one for the temp table, using looping structure with If Then conditionals to build the concatenated string.Then add string to field in the temp recordset object and update the table.

[NTC]

I would simply make a query on your table using the start/stop criteria as entered by the user wanting the report: (ex: start 1/23/2014 end 1/30/2014)

that is simple enough - call it DateRangeQ and double check that it runs correctly and returns the correct records as you test different dates. You could use parameter prompts for start/end - although I prefer using date fields (unbound text fields) in a form that holds the button to open the report. But in any case this step isn't too complicated.

Once DateRangeQ is working - - then it really is a matter of display. You are showing a non normalized display:
1/23/2014: WO 1234, 2345
1/24/2014: WO 1234, 2345

that is very excel-like; data is not stored in databases side-by-side separated by commas. So what you really should do is use a standard report, which allows grouping, and set up to group by day - - and you will get:

Date WO
1/23/2014: 1234
2345
1/24/2014: 1234
2345
(note I can't get the number column to line up straight once I save this post)

the layout is different but the data is correct. This is the vanilla, least complicated way to achieve your goal. To take it a step further and set up a display that is non normalized then the approach is either going to be cross tab or a union query which would be a topic for another post.

[June7]

NTC, don't understand how that can result in a line in report for each day.

[jegupta]

NTC,
I would prefer to stick with something simple & a query with a date range is what I started with. That part works fine. The part I'm struggling with is how to get the results to display on the report grouped by date for each day in the range.

I admit that the way I presented the data in the post wasn't great. The report really isn't one line per day with the work orders concatenated like that. Below is a better representation of the query results & what I would like the report to look like.

I used report start date = 1/20/2014, report end date = 1/25/2014

qry_date_range_report
Priority	WONumber	Start_Date_Job	End_Date_Job	Truck_No
1	11458928	12/20/2013	12/20/2014	4712
1	3372667	1/15/2014	1/27/2014	4131
1	3372665	1/15/2014	1/24/2014	4131
1	3351786	1/13/2014	1/31/2014	4319
1	3372662	1/15/2014	1/24/2014	4131

Here is an example of what the report should look like. For each day in the date range, there should be a list of WOs (there are more fields, like address, permit #, etc for each WO, but I didn't include below).

1/20/2014
WO	Truck No
11458928	4712
3372667	4131
3351786	4319
3372662	4131
1/21/2014
WO	Truck No
11458928	4712
3351786	4319
3372662	4131
1/22/2014
WO	Truck No
11458928	4712
3351786	4319
3372662	4131
1/23/2014
WO	Truck No
11458928	4712
3372665	4131
3351786	4319
3372662	4131

Is there a way to design the report using each date in the user-inputted date range?

Thanks,
Julie

[ItsMe]

Group on your date field. Include the date field on your "Date Field" header. Include labels for your WO and Truck No in the header also. Controls for WO and Truck No go in the detail section.

[June7]

Sorry guys, none of that will create a row for each day within the specified date range.

Have to create records by writing to a 'temp' table. Consider:

Sub Daily()
Dim rsData As ADODB.Recordset
Dim rsDataTemp As ADODB.Recordset
Set rsData = New ADODB.Recordset
Set rsDataTemp = New ADODB.Recordset
Dim dteStart As Date, dteEnd As Date, dteDay As Date
CurrentDb.Execute "DELETE FROM DataTemp"
rsData.Open "SELECT * FROM Data ORDER BY WONumber, Start_Date_Job;", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
rsDataTemp.Open "SELECT * FROM DataTemp;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
While Not rsData.EOF
    dteDay = rsData!Start_Date_Job
    While dteDay >= rsData!Start_Date_Job And dteDay <= rsData!End_Date_Job
        rsDataTemp.AddNew
        rsDataTemp!JobDate = dteDay
        rsDataTemp!WONumber = rsData!WONumber                
        rsDataTemp!TruckNo = rsData!Truck_No
        dteDay = dteDay + 1
    Wend
    rsData.MoveNext
Wend
rsDataTemp.Update
End Sub

This requires a VBA reference to Microsoft ActiveX Data Objects x.x Library
As for filtering by specified date range, do you want records where the job start and end are both within the range or if either is within the range?

If you want to do what I suggested earlier about concatenating the truck number values to a single string, review http://allenbrowne.com/func-concat.html

[ItsMe]

OP stated they misspoke when they mentioned "one line". My response is based upon the second half of post #5. Didn't really study the rest too much.

[jegupta]

June7,
Thank you! The data in the report has to include any work occurring during the date range, so either the start or end date has to be within the range. I've already built a daily report (which was much easier) that gets distributed to field supervisors so they know what jobs their crews (truck #s) are supposed to be working on for the day. Now they want a report showing work for multiple days.

I'll give the temp table a go & see what happens!

Thanks,
Julie

[June7]

To include date range filter (the ORDER BY isn't really necessary for this procedure) and not create daily records for the job days outside the given range, change code to:

Sub Daily()
Dim rsData As ADODB.Recordset
Dim rsDataTemp As ADODB.Recordset
Set rsData = New ADODB.Recordset
Set rsDataTemp = New ADODB.Recordset
Dim dteStart As Date, dteEnd As Date, dteDay As Date
dteStart = Me.tbxStart
dteEnd = Me.tbxEnd
CurrentDb.Execute "DELETE FROM DataTemp"
rsData.Open "SELECT * FROM Data  WHERE Start_Date_Job BETWEEN #" & dteStart & "# AND #" & dteEnd & _
"# OR End_Date_Job BETWEEN #" & dteStart & "# AND #" & dteEnd & "#;", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
rsDataTemp.Open "SELECT * FROM DataTemp;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
While Not rsData.EOF
    dteDay = rsData!Start_Date_Job
    While dteDay <= rsData!End_Date_Job
        If dteDay >= dteStart And dteDay <= dteEnd Then
            rsDataTemp.AddNew
            rsDataTemp!JobDate = dteDay
            rsDataTemp!WONumber = rsData!WONumber
            rsDataTemp!TruckNo = rsData!Truck_No
        End If
        dteDay = dteDay + 1
    Wend
    rsData.MoveNext
Wend
rsDataTemp.Update
End Sub

[NTC]

no it definitely will not J7. as you know in being highly skilled on this.......My approach is that when someone posts themselves as a novice - that the best advice is the simplest, stay within the core offerings of the Access product, and.......that the client really doesn't always know actually what they want and can't always dictate the presentation !! .... the motto that 'the customer is always right' is not my view !! as for the original post is for:

They are requesting a report which will display all of the scheduled work by day for a user-inputed date range.

As long as it meets that criteria is all that mattered .....

[Xipooo]

Hi, I'm working on a work scheduling database for a local utility company. They are requesting a report which will display all of the scheduled work by day for a user-inputed date range.

All of the work is stored in a table by WO#, work start date & end date.
WO start date end date
1234 1/20/2014 1/25/2014
2345 1/1/2014 2/15/2014
4567 1/26/2014 1/31/2014

The end user will enter a report start & end date from a form (ex: start 1/23/2014 end 1/30/2014)

The report should display the following:
1/23/2014: WO 1234, 2345
1/24/2014: WO 1234, 2345
1/25/2014: WO 1234, 2345
1/26/2014: WO 2345, 4567 etc...

I was going to try creating a temp table & for each date, cycle through the records and copy each one that applies to the temp table. I'm just not sure how to get started or if there is an easier way to do this. Any help or suggestions would be greatly appreciated!! Thanks, Julie

You can do this in a query, and set the start date/end date criteria to the text boxes on the form. WHERE tblName.StartDate >= [Forms]![FormName]![FromDate] AND tblName.EndDate <= [Forms]![FormName]![ToDate]




Ooops.. should have read thread all the way through.

[June7]

However, think the filter criteria still not right. It's more complicated.

Try (I can't test it right now):

WHERE Start_Date_Job BETWEEN #" & dteStart & "# AND #" & dteEnd & _
"# OR End_Date_Job BETWEEN #" & dteStart & "# AND #" & dteEnd & _
"# OR #" & dteStart & "# BETWEEN Start_Date_Job AND End_Date_Job "
" OR #" & dteEnd & "# BETWEEN Start_Date_Job AND End_Date_Job;",

I am curious how you built the report distributed to supervisors showing scheduled work.