How can I group records for the same EmployeeID that falls between any specified dates? For example: an Employee may travel from Boston to New York and from New York to Maryland and from Maryland to Boston as final destination. The start work day is 08/01 and the end work day is 08/05. Travel from Boston to New York for business trip from 08/01-08/03 for one client, then from New York to Maryland from 08/03-08/04 for a second client and on 08/05 back to Boston.
In this case, there are two work orders that we enter in the DB. The trip is to book from 08/01-08/05 for all the above destinations. But basically there are two different work orders that are grouped under one request.
One of the reports I have list everyone's work order for the whole year. But when I run this report. The two above work orders will fall apart from each other because they are two different records unless I sort by travel date. If I do this, I am still not able to add grouping option for the two records to obtain total.
But since I have to calculate cost for each trip, the above method will not work. But what I want is to have the ability to add up the cost for all trips like that together. If the trip is single, I will be able to add the cost for that trip, if it is multiple, I will be able to group them together to add the cost. I added check box to identify the multiple destination. But when I group based on the check box and EmployeeID, if there is another set of multiple trips, all they add together and they miss up the number. I want to be able to group records by Employee and by dates that falls between travel date and return dates for the same employee.
Any suggestion will be appreciated!!!!
In this case, there are two work orders that we enter in the DB. The trip is to book from 08/01-08/05 for all the above destinations. But basically there are two different work orders that are grouped under one request.
One of the reports I have list everyone's work order for the whole year. But when I run this report. The two above work orders will fall apart from each other because they are two different records unless I sort by travel date. If I do this, I am still not able to add grouping option for the two records to obtain total.
But since I have to calculate cost for each trip, the above method will not work. But what I want is to have the ability to add up the cost for all trips like that together. If the trip is single, I will be able to add the cost for that trip, if it is multiple, I will be able to group them together to add the cost. I added check box to identify the multiple destination. But when I group based on the check box and EmployeeID, if there is another set of multiple trips, all they add together and they miss up the number. I want to be able to group records by Employee and by dates that falls between travel date and return dates for the same employee.
Any suggestion will be appreciated!!!!
