Start and End Dates For Repeat Locations

[JamesBond]

Hi. I have a table which records the various locations an employee works in, at any time. The employee can move back and forth between locations. The table details are as follows.



Table "tblEmployees"

Fields
"EmployeeID", a unique alphanumeric identifier for each employee.
"EmployeeName", text.
"RecordDate", date field for start date at any particular location.
"Location", Text field to describe location employee is working.

Currently we have a record for every day for the employee as follows

EmployeeID	EmployeeName	RecordDate	Location
123abc	Mr.A	01/01/2015	Dublin
123abc	Mr.A	02/01/2015	Dublin
123abc	Mr.A	03/01/2015	Dublin
123abc	Mr.A	04/01/2015	Dublin
123abc	Mr.A	05/01/2015	Kerry
123abc	Mr.A	06/01/2015	Kerry
123abc	Mr.A	07/012015	Kerry
123abc	Mr.A	08/01/2015	Galway
123abc	Mr.A	09/01/2015	Galway
123abc	Mr.A	10/01/2015	Galway
123abc	Mr.A	11/01/2015	Kerry
123abc	Mr.A	12/01/2015	Kerry
123abc	Mr.A	13/01/2015	Kerry
123abc	Mr.A	14/01/2015	Dublin

I am trying to build a query which returns the first date for each visit to each location as follows

EmployeeID	EmployeeName	RecordDate	Location
123abc	Mr.A	01/01/2015	Dublin
123abc	Mr.A	05/01/2015	Kerry
123abc	Mr.A	08/01/2015	Galway
123abc	Mr.A	11/01/2015	Kerry
123abc	Mr.A	14/01/2015	Dublin

I tried using, First and Max in the Totals in the query, but this only returns the first time a Location is recorded, it does not return the date when the location is visited more than once.
Any assistance would be very much appreciated

[amrut]

I am trying to build a query which returns the first date for each visit to each location as follows

I tried using, First and Max in the Totals in the query, but this only returns the first time a Location is recorded, it does not return the date when the location is visited more than once.

\
Above two are a bit confusing ? what do you need exactly ? Post required sample result.
Use Min to get the first date in a aggregate query.

[JamesBond]

Hi thank you for your response

I made a mistake I should have said I used First and Min in the totals but it did not return All the data I require.

I included the data from the table in my initial message and I also included the result I want to achieve.

If you can assist me I will really appreciate it

[orange]

James,

I'm going to suggest that your table structure is causing you problems. You have everything in one table. And that will cause you difficulty.

Consider that you have Employees who may visit different Locations, and according to your data - an Employee only Visits 1 Location on a single date. If you look at the issue from this perspective. I suggest 3 tables:
and these relationships:

                     junction table
Employee ---->VisitedLocation<-----Location


Where 
Employee
employeeId  PK
EmployeeName
otherEmployeeInfo

Location
LocationId PK
LocationNmae
otherLocationInfo

VisitedLocation   a junction table
VisitLoctionId  PK
EmployeeID  
LocationID  
VisitStartDate
VisitEndDate
OtherVisitSpecificInfo eg PurposeOfVisit

The fields in green are elements of a unique composite index (to prevent duplicates).
These fields together uniquely define a VisitLocation record.

Structuring your database makes querying easier. One thing in one table.

Here is the result using the query and table design in the attached jpg.

EmployeeName
LocationName
VisitStartDate

Mr A.
Dublin
01/01/2015

Mr A.
Galway
05/01/2015

Mr A.
Kerry
08/01/2015

Mr A.
Kerry
11/01/2015

Mr A.
Dublin
14/01/2015

Good luck.

[JamesBond]

Hi Orange, thank you very much, I split the data into three tables and it worked out fine. Can I ask you one more question please. The employees in question are independent contractors and as such work away from the office. They can work in one location for a number of consecutive days, they can then decide to take a couple of days off before starting in a different location. I do not have a VisitEndDate field in the database and at this point it is not possible to add in a field or to change the way the employees report their work. is there a query I can run which will return the last VisitStartDate (in a batch of consecutive dates where the location remains the same) as the VisitEndDate, because the in the next record the Location field has changed. I hope I am explaining this well enough and again thank you for your help

[orange]

If the "employees/contractors" report their work in the format you have, then it's possible to massage/modify that
data and append it to your 3 table set up.
I would probably use the input as recordset and process it with some vba ( or you could probably do it with a series of queries).
With some looping and logic, you could determine (for each employee) the first day at Location X. If next record is same location, check the date etc. What happens if the employee takes a few days off in the middle of his/her work?

Do you record anything for the days off?

[JamesBond]

Hi Orange, if the the employee takes a few days for example due to illness, we would replace that employee with a second employee. When the first employee was well again he/she would start in a differenent location. So basically you can ignore an employee being out for a couple of days, we treat that as if it was an employee on vacation who starts in a new location when they returrn. Many thanks