Custom 'End Date' field

[wrightyrx7]

Hi all,

I have a table containing records for employees..each record contains a start date but no end date

The first field is employee number, then the other fields are the record data..

For each record I need an end date which will be the day before the start date of the next record as long as the employee number matches (hope that makes sense)

Can anyone help please?

 

[Ranman256]

No it doesn't make sense.
Is this a hire date?
You would have a query with start dates and end date IS NULL.
And a query
start date and end date in a range.

 

[wrightyrx7]

Im sorry... let me try give an example


Record 1 - Employee 1
Emp ID = 1
Start Date = 01/01/2015
Description = Admin

Record 1 would look at record 2 for this employee and determine the end date is 30/04/2015 (day before start date of record 2)


Record 2 - Employee 1
Emp ID = 1
Start Date = 01/05/2015
Description = Admin Manager

Record 2 would look at record 3 for this employee and determine the end date is 31/08/2015 (day before start date of record 2)



Record 3 - Employee 1
Emp ID = 1
Start Date = 01/09/2015
Description = Admin

Record 3 has no record to look at for an end date because the next record is for employee 2 (different employee) therefore the end date would be NULL.


Record 1 - Employee 2
Emp ID = 2
Start Date = 01/12/2015
Description = Admin

 

[plog]

I would use a DMIN(http://www.techonthenet.com/access/functions/domain/dmin.php) to get the smallest date that is larger than the existing record with the same EmpID.

 

[arnelgp]

untested:

select T1.[emp id], T1.[start date], T1.[description], (SELECT TOP 1 [START DATE] FROM (SELECT [START DATE] FROM table1 AS T3 WHERE T3.[emp id] = T1.[emp id] ORDER BY [START DATE]) AS T4 WHERE T4.[start date] > T1.[start date]) As [End Date] from table1 as T1

 

[wrightyrx7]

I would use a DMIN(http://www.techonthenet.com/access/functions/domain/dmin.php) to get the smallest date that is larger than the existing record with the same EmpID.

Thanks plog,

this would make sense. The end date would be the returned date minus 1 day. However i am struggling

In the example on the webpage this is where the employee number = "10248" how would I say where the employee number = the employee number of current record?

untested:
select T1.[emp id], T1.[start date], T1.[description], (SELECT TOP 1 [START DATE] FROM (SELECT [START DATE] FROM table1 AS T3 WHERE T3.[emp id] = T1.[emp id] ORDER BY [START DATE]) AS T4 WHERE T4.[start date] > T1.[start date]) As [End Date] from table1 as T1

Hi arnelgp,

I have give this a go but getting "Enter perameter value" on a couple of fields.

Thank you both again for your replies

 

[plog]

how would I say where the employee number = the employee number of current record?

You would be in a query, so you reference the field name, like so:

"[EmpID]=" & [EmpID]

 

[wrightyrx7]

You would be in a query, so you reference the field name, like so:

"[EmpID]=" & [EmpID]

plog that seems to have worked.

I have all the basics

DMin("StartDate", "Employees", "[EmpID]='" & [EmpID] & "'")

At the minute it just pulls out the smallest date of all records.

How do I get the "smallest date that is larger than the existing record with the same EmpID"

Thank you for your help with this...its much appreciated.

 

[plog]

How do I get the "smallest date that is larger than the existing record with the same EmpID"

You would need to add something to your criteria argument.

"[EmpID=" & [EmpID] & " AND ...

 

[wrightyrx7]

You would need to add something to your criteria argument.

"[EmpID=" & [EmpID] & " AND ...

Thanks plog,


I tried this but the end date is the same as the start date of current record.

End Date: DMin("StartDate","Employees","[EmpID]='" & [EmpID] & "' and [StartDate]>#" & [StartDate] & "#")

Any ideas?

 

[plog]

Are you sure? I made a quick test Employees table and pasted your code in without changes and it worked for me.

Are you storing a Time with StartDate?

 

[wrightyrx7]

Are you sure? I made a quick test Employees table and pasted your code in without changes and it worked for me.

Are you storing a Time with StartDate?

No there is no time, im confused..

So close to getting it

 

[plog]

Can you post your database?

 

[arnelgp]

SELECT T1.[emp id], T1.[start date], T1.[description], (SELECT TOP 1 T3.[start date]-1 FROM
(SELECT T2.[emp id], T2.[start date] FROM [Employees] AS T2 ORDER BY 1,2) AS T3 WHERE T3.[emp id] = T1.[emp id] AND T3.[start date] > T1.[start date] ) AS EndDate FROM [Employees] AS T1 ORDER BY 1,2;

 

[Galaxiom]

I would be pursuing arnelgp's solution rather than plog's. Using domain functions in queries where the function is applied to every record is hideously inefficient.

However I would discourage the use of field index numbers in the order by clauses. Someone unfamiliar with this largely deprecated syntax might later modify the order of the fields in the select clause and fail to notice.

 

[wrightyrx7]

SELECT T1.[emp id], T1.[start date], T1.[description], (SELECT TOP 1 T3.[start date]-1 FROM
(SELECT T2.[emp id], T2.[start date] FROM [Employees] AS T2 ORDER BY 1,2) AS T3 WHERE T3.[emp id] = T1.[emp id] AND T3.[start date] > T1.[start date] ) AS EndDate FROM [Employees] AS T1 ORDER BY 1,2;

Hi arnelgp,

This seems to work as we wanted! Thank you very much for you help

I would be pursuing arnelgp's solution rather than plog's. Using domain functions in queries where the function is applied to every record is hideously inefficient.

However I would discourage the use of field index numbers in the order by clauses. Someone unfamiliar with this largely deprecated syntax might later modify the order of the fields in the select clause and fail to notice.

Hi Galaxiom,

Does the 'order by' do anything to help this work? As you can see above it seems to be working, but dont want it to fail at a later date like you mentioned.

 

[Galaxiom]

Does the 'order by' do anything to help this work? As you can see above it seems to be working, but dont want it to fail at a later date like you mentioned.

I have not looked closely but the Order By affects which records are selected by TOP.

My point is that rather than the numerical references to the fields which could fail if another field were added into the Select:

Order By 1,2

It is better practice to use the field names:

Order By [Emp Id], [start date]

In this case it is unlikely to become a problem but using numeric field references is generally best avoided.

 

[wrightyrx7]

I have not looked closely but the Order By affects which records are selected by TOP.

My point is that rather than the numerical references to the fields which could fail if another field were added into the Select:

Order By 1,2

It is better practice to use the field names:

Order By [Emp Id], [start date]

In this case it is unlikely to become a problem but using numeric field references is generally best avoided.

Thank you for explaining, I have changed it now