Consecuitive Employment time employed

[Guest]

Hi,
I need help in showign the number of consecuitive days of employment between
several employment records for the same person.

I thought thats this should be a peice of cookie untill I realized that some
of the employment dates overlapped.

For example job 1 starts 1/1 and terminates 2/20 job 2 starts 1/15 and
terminated 3/1
or
Job 1 starts 1/1 and terms 2/20
Job 2 Starts 1/15 and terms 3/1
Job 3 starts 2/25 and terms 7/1
Job 4 Starts 5/1 and is still employed

I dont understand why people cant just stay employed, It would make my lifer
easier!!

Anyway what I need to show is over a period of time [report start] to
[report end] how many people had continious employment for 3 months and how
many people have continious employment for 6 months
I have [Hire date] and [Term date] fields to query and each employment is a
seperate record. Note:

My first thought was I could just find the numbers yof days employed and add
the sum, then see if it equaled the number of report days, but that dosent
work if someone has 2 part time jobs that overlap.
now I'm thinking I need to query to see if they are employed in periods and
ad the sum of the periods up, but that has an issue with gaps in employment

I'm at a loss, any suggestions would be welcomed

Thanks

 

[Tom Ellison]

Dear Dave:

I've faced this kind of difficulty before. The solution may be to turn the
problem around. Look for the most recent date the employee was NOT
employed.

Now, being NOT employed is found by looking at the day AFTER the end of each
employment period and seeing if it fall WITHIN any other period of
employment for that same employee.

Assuming you have not time component (in effect, all midnight) in these date
values, you can do this with a subquery using EXISTS.

SELECT Employee, [Term date] + 1
FROM YourTable T
WHERE NOT EXISTS
(SELECT * FROM YourTable T1
WHERE T1.Employee = T.Employee
AND T1.[Hire date] <= T.[Term date]
AND T1.[Term date] > [Term date])

This would give you the first day of unemployment form any series of
unemployed days. But what if someone finishes a job on Friday and starts
another on Monday following? Is that defined as a period of unemployment?

That can be fixed. If the [Term date] is a Friday, add 3 instead of 1. Use
2 when Saturday. Put this in place of the "1" in the first line of code
above. For holidays, it gets trickier.

All this will not tell you when someone was unemployed before the first
instance of their employment. I suggest using the MIN of each employee's
first Hire date to determine this if needed.

It will show that all employees are unemployed after the end of their most
recent employment. Perhaps the [Term date] of the current employment is
NULL when that employment has not ended. You could filter those out using
that fact.

Did this help? What more might you need?

Tom Ellison

Hi,
I need help in showign the number of consecuitive days of employment
between
several employment records for the same person.

I thought thats this should be a peice of cookie untill I realized that
some
of the employment dates overlapped.

For example job 1 starts 1/1 and terminates 2/20 job 2 starts 1/15 and
terminated 3/1
or
Job 1 starts 1/1 and terms 2/20
Job 2 Starts 1/15 and terms 3/1
Job 3 starts 2/25 and terms 7/1
Job 4 Starts 5/1 and is still employed

I dont understand why people cant just stay employed, It would make my
lifer
easier!!

Anyway what I need to show is over a period of time [report start] to
[report end] how many people had continious employment for 3 months and
how
many people have continious employment for 6 months
I have [Hire date] and [Term date] fields to query and each employment is
a
seperate record. Note:

My first thought was I could just find the numbers yof days employed and
add
the sum, then see if it equaled the number of report days, but that
dosent
work if someone has 2 part time jobs that overlap.
now I'm thinking I need to query to see if they are employed in periods
and
ad the sum of the periods up, but that has an issue with gaps in
employment

I'm at a loss, any suggestions would be welcomed

Thanks

 

[Guest]

Tom, Thanks for answering
I'm not a SQL guy, but I think i see what you are trying to do so maybe if
we work this in words I can figure this out in my dinky mind.
My table name is: "Employment Information"
My fields in the table are: SSN (AKA:Social Security Number), Hire date,
Term date, wages, hours

I think what you are saying is I need to create a query from my table
"Employment Information" that includes the ssn, Hire Date, Term date and a
new calculated field that shows Term date +1. I Call this query T1

Now I need to create another query that includes The Table "Employment
Information" and the Query I just made called "T1". and I join the with a 1
to 1 relationship by the SSN of each. Now I include all the fields from
both the table "employment Information" table and the Query "T1". In the
Cirteria of [T1]![Hire date] i put "<=[Employment Information]![Term Date]"
and in the criteria of [T1]![Term Date] I put ">[Employment
Information]![Term Date]
I name this query "Tom's Cool Query"

Am I close?

I am confused about 2 things if I am,
1) In the SQL statement you sent there is a "Where Not Exists". I think Im
missing that part
2) Why did i create the Term date +1 if its not used in the query?

If it helps I do know how to convert the SQL to Table view

Thanks
Dave

Dear Dave:

I've faced this kind of difficulty before. The solution may be to turn the
problem around. Look for the most recent date the employee was NOT
employed.

Now, being NOT employed is found by looking at the day AFTER the end of each
employment period and seeing if it fall WITHIN any other period of
employment for that same employee.

Assuming you have not time component (in effect, all midnight) in these date
values, you can do this with a subquery using EXISTS.

SELECT Employee, [Term date] + 1
FROM YourTable T
WHERE NOT EXISTS
(SELECT * FROM YourTable T1
WHERE T1.Employee = T.Employee
AND T1.[Hire date] <= T.[Term date]
AND T1.[Term date] > [Term date])

This would give you the first day of unemployment form any series of
unemployed days. But what if someone finishes a job on Friday and starts
another on Monday following? Is that defined as a period of unemployment?

That can be fixed. If the [Term date] is a Friday, add 3 instead of 1. Use
2 when Saturday. Put this in place of the "1" in the first line of code
above. For holidays, it gets trickier.

All this will not tell you when someone was unemployed before the first
instance of their employment. I suggest using the MIN of each employee's
first Hire date to determine this if needed.

It will show that all employees are unemployed after the end of their most
recent employment. Perhaps the [Term date] of the current employment is
NULL when that employment has not ended. You could filter those out using
that fact.

Did this help? What more might you need?

Tom Ellison

Hi,
I need help in showign the number of consecuitive days of employment
between
several employment records for the same person.

I thought thats this should be a peice of cookie untill I realized that
some
of the employment dates overlapped.

For example job 1 starts 1/1 and terminates 2/20 job 2 starts 1/15 and
terminated 3/1
or
Job 1 starts 1/1 and terms 2/20
Job 2 Starts 1/15 and terms 3/1
Job 3 starts 2/25 and terms 7/1
Job 4 Starts 5/1 and is still employed

I dont understand why people cant just stay employed, It would make my
lifer
easier!!

Anyway what I need to show is over a period of time [report start] to
[report end] how many people had continious employment for 3 months and
how
many people have continious employment for 6 months
I have [Hire date] and [Term date] fields to query and each employment is
a
seperate record. Note:

My first thought was I could just find the numbers yof days employed and
add
the sum, then see if it equaled the number of report days, but that
dosent
work if someone has 2 part time jobs that overlap.
now I'm thinking I need to query to see if they are employed in periods
and
ad the sum of the periods up, but that has an issue with gaps in
employment

I'm at a loss, any suggestions would be welcomed

Thanks

 

[Tom Ellison]

Tom, Thanks for answering
I'm not a SQL guy, but I think i see what you are trying to do so maybe if
we work this in words I can figure this out in my dinky mind.
My table name is: "Employment Information"
My fields in the table are: SSN (AKA:Social Security Number), Hire date,
Term date, wages, hours

I think what you are saying is I need to create a query from my table
"Employment Information" that includes the ssn, Hire Date, Term date and a
new calculated field that shows Term date +1. I Call this query T1

Now I need to create another query that includes The Table "Employment
Information" and the Query I just made called "T1". and I join the with a
1
to 1 relationship by the SSN of each. Now I include all the fields from
both the table "employment Information" table and the Query "T1". In the
Cirteria of [T1]![Hire date] i put "<=[Employment Information]![Term
Date]"
and in the criteria of [T1]![Term Date] I put ">[Employment
Information]![Term Date]
I name this query "Tom's Cool Query"

Am I close?

I am confused about 2 things if I am,
1) In the SQL statement you sent there is a "Where Not Exists". I think
Im
missing that part
2) Why did i create the Term date +1 if its not used in the query?

If it helps I do know how to convert the SQL to Table view

Thanks
Dave

Dear Dave:

I've faced this kind of difficulty before. The solution may be to turn
the
problem around. Look for the most recent date the employee was NOT
employed.

Now, being NOT employed is found by looking at the day AFTER the end of
each
employment period and seeing if it fall WITHIN any other period of
employment for that same employee.

Assuming you have not time component (in effect, all midnight) in these
date
values, you can do this with a subquery using EXISTS.

SELECT Employee, [Term date] + 1
FROM YourTable T
WHERE NOT EXISTS
(SELECT * FROM YourTable T1
WHERE T1.Employee = T.Employee
AND T1.[Hire date] <= T.[Term date]
AND T1.[Term date] > [Term date])

This would give you the first day of unemployment form any series of
unemployed days. But what if someone finishes a job on Friday and starts
another on Monday following? Is that defined as a period of
unemployment?

That can be fixed. If the [Term date] is a Friday, add 3 instead of 1.
Use
2 when Saturday. Put this in place of the "1" in the first line of code
above. For holidays, it gets trickier.

All this will not tell you when someone was unemployed before the first
instance of their employment. I suggest using the MIN of each employee's
first Hire date to determine this if needed.

It will show that all employees are unemployed after the end of their
most
recent employment. Perhaps the [Term date] of the current employment is
NULL when that employment has not ended. You could filter those out
using
that fact.

Did this help? What more might you need?

Tom Ellison

Hi,
I need help in showign the number of consecuitive days of employment
between
several employment records for the same person.

I thought thats this should be a peice of cookie untill I realized that
some
of the employment dates overlapped.

For example job 1 starts 1/1 and terminates 2/20 job 2 starts 1/15 and
terminated 3/1
or
Job 1 starts 1/1 and terms 2/20
Job 2 Starts 1/15 and terms 3/1
Job 3 starts 2/25 and terms 7/1
Job 4 Starts 5/1 and is still employed

I dont understand why people cant just stay employed, It would make my
lifer
easier!!

Anyway what I need to show is over a period of time [report start] to
[report end] how many people had continious employment for 3 months and
how
many people have continious employment for 6 months
I have [Hire date] and [Term date] fields to query and each employment
is
a
seperate record. Note:

My first thought was I could just find the numbers yof days employed
and
add
the sum, then see if it equaled the number of report days, but that
dosent
work if someone has 2 part time jobs that overlap.
now I'm thinking I need to query to see if they are employed in periods
and
ad the sum of the periods up, but that has an issue with gaps in
employment

I'm at a loss, any suggestions would be welcomed

Thanks

 

[Tom Ellison]

Dear Dave:

I am not saying what you think I was saying. Sorry. I believe you are
looking at the query designer view of the query I gave you. It can be
confusing for queries that are not simple.

In my opinion, you must look only at the SQL view. Of course, you may not
be accustomed or skilled at this. Well, if you want to understand what I'm
doing, you will probably need to learn. Sorry if that sounds tough.

The query designer is probably a good tool for beginners. It makes a mess
of things when they start to get advanced. Indeed, there are queries that
cannot be represented in this view at all (UNION queries especially) and
there are query features like subqueries that are only represented in this
view as code.

In my example, the Term date + 1 is used to find the day AFTER the end of a
period of employment. The intent is to find out if this date lies within
any other period of employment for that same employee. It is displayed in
the results to represent the first day in which that employee is unemployed.
However, I would disagree with you. It is used in the query, even though it
is not RETURNED by the query. Again, the design view interferes with your
understanding here. Also, I'm not sure I remember how to put a NOT EXISTS
into the designer. I haven't used it seriously in many years now. We
cannot exchange information based on the query design view in the newsgroup
without excessive explanations. We need to discuss and build everything
according to the SQL view. Once we've got that working, you can go back and
study the design view if you wish, to see how it could have been done there.

In the SQL view we are dealing with text. We can both cut and past this in
our newsgroup messages. It is not only easier to discuss this way, but this
is actually the best way to be able to look at a complex query.

I'd be glad to work with you some more on this. Please do not post just
portions of the query you have. Go to the SQL view and post the whole
thing. I'll study that and get back to you. First, however, try to get it
as close to what I provided as you can.

Tom Ellison

Tom, Thanks for answering
I'm not a SQL guy, but I think i see what you are trying to do so maybe if
we work this in words I can figure this out in my dinky mind.
My table name is: "Employment Information"
My fields in the table are: SSN (AKA:Social Security Number), Hire date,
Term date, wages, hours

I think what you are saying is I need to create a query from my table
"Employment Information" that includes the ssn, Hire Date, Term date and a
new calculated field that shows Term date +1. I Call this query T1

Now I need to create another query that includes The Table "Employment
Information" and the Query I just made called "T1". and I join the with a
1
to 1 relationship by the SSN of each. Now I include all the fields from
both the table "employment Information" table and the Query "T1". In the
Cirteria of [T1]![Hire date] i put "<=[Employment Information]![Term
Date]"
and in the criteria of [T1]![Term Date] I put ">[Employment
Information]![Term Date]
I name this query "Tom's Cool Query"

Am I close?

I am confused about 2 things if I am,
1) In the SQL statement you sent there is a "Where Not Exists". I think
Im
missing that part
2) Why did i create the Term date +1 if its not used in the query?

If it helps I do know how to convert the SQL to Table view

Thanks
Dave

Dear Dave:

I've faced this kind of difficulty before. The solution may be to turn
the
problem around. Look for the most recent date the employee was NOT
employed.

Now, being NOT employed is found by looking at the day AFTER the end of
each
employment period and seeing if it fall WITHIN any other period of
employment for that same employee.

Assuming you have not time component (in effect, all midnight) in these
date
values, you can do this with a subquery using EXISTS.

SELECT Employee, [Term date] + 1
FROM YourTable T
WHERE NOT EXISTS
(SELECT * FROM YourTable T1
WHERE T1.Employee = T.Employee
AND T1.[Hire date] <= T.[Term date]
AND T1.[Term date] > [Term date])

This would give you the first day of unemployment form any series of
unemployed days. But what if someone finishes a job on Friday and starts
another on Monday following? Is that defined as a period of
unemployment?

That can be fixed. If the [Term date] is a Friday, add 3 instead of 1.
Use
2 when Saturday. Put this in place of the "1" in the first line of code
above. For holidays, it gets trickier.

All this will not tell you when someone was unemployed before the first
instance of their employment. I suggest using the MIN of each employee's
first Hire date to determine this if needed.

It will show that all employees are unemployed after the end of their
most
recent employment. Perhaps the [Term date] of the current employment is
NULL when that employment has not ended. You could filter those out
using
that fact.

Did this help? What more might you need?

Tom Ellison

Hi,
I need help in showign the number of consecuitive days of employment
between
several employment records for the same person.

I thought thats this should be a peice of cookie untill I realized that
some
of the employment dates overlapped.

For example job 1 starts 1/1 and terminates 2/20 job 2 starts 1/15 and
terminated 3/1
or
Job 1 starts 1/1 and terms 2/20
Job 2 Starts 1/15 and terms 3/1
Job 3 starts 2/25 and terms 7/1
Job 4 Starts 5/1 and is still employed

I dont understand why people cant just stay employed, It would make my
lifer
easier!!

Anyway what I need to show is over a period of time [report start] to
[report end] how many people had continious employment for 3 months and
how
many people have continious employment for 6 months
I have [Hire date] and [Term date] fields to query and each employment
is
a
seperate record. Note:

My first thought was I could just find the numbers yof days employed
and
add
the sum, then see if it equaled the number of report days, but that
dosent
work if someone has 2 part time jobs that overlap.
now I'm thinking I need to query to see if they are employed in periods
and
ad the sum of the periods up, but that has an issue with gaps in
employment

I'm at a loss, any suggestions would be welcomed

Thanks

 

[Chris2]

In the SQL view we are dealing with text. We can both cut and past this in
our newsgroup messages. It is not only easier to discuss this way, but this
is actually the best way to be able to look at a complex query.

I will heartily second this!

I'd be glad to work with you some more on this. Please do not post just
portions of the query you have. Go to the SQL view and post the whole
thing. I'll study that and get back to you. First, however, try to get it
as close to what I provided as you can.

Dave E,

In addition, post a description of your table structures . . . (The
below are only descriptions, and are not exact copies of DDL
syntax!)

Example:

MyTableOne
ColOne AUTOINCREMENT PK
ColTwo INTEGER NOT NULL DEFAULT 0
ColThree TEXT(10)

MyTableTwo
ColOne AUTOINCREMENT PK\
ColTwo INTEGER PK/-- Composite Primary Key
ColThree INTEGER FK -- MyTableOne ColOne

etc., etc., etc.

The Primary Key and Foreign Key notes are *critical*.


Plus Sample Data:

MyTableOne
ColOne, ColTwo, ColThree
1, 2, a
3, 4, b
5, 6, c

etc.


Desired Results

.. . . <whatever it is you want your query to produce>


You will that providing this information leads to solutions far more
quickly than text narratives.

Note: If you can write your own CREATE TABLE statements, so that
those who choose to work on this for you don't have to, that would
be great.


Sincerely,

Chris O.

 

[Guest]

Chris, I hope the information below is what you want, this is what the Access
Documenter has for the table.

I currently do not have a query designed for this but what I am trying to do
is create a query from all of the employment records that shows how long a
person has gone with out employment between jobs. My goal for this query is
to use it as part of a report that needs to show all people that have been
employed continiously for 90 days ofer a period of time (coutiniously means
no gaps in employment greater than 30 days). I also need to show the people
that have been employed 180 days coutiniously (Again defined as no gaps in
employment greater than 30 days.) over the same period of time

The report periods come from a form
Form name [Report Menu]
Start date field name [Beginning]
End date field name [To]

My problem is compairing one Employment record term date to the next records
Employment hire date. so that I can see how many days the person was
unemployed, or transversly if in a 180 day or 90 day period if they were
"continously" employed.

This is an example of the data
SSN Hire date Term date
11111 1/1/05 2/1/05 *
11111 1/7/05 4/1/05 * note the overlap in
employment
11111 7/1/05 9/1/05
11111 11/1/05 null

This only indicates one participant SSN, but there would be several others
in the same table


As for the Primary key question from what I see below there dosent seem to
give any information about my primary key, but in the table design view the
primary key is the field [Update ID] these are the descriptions, Type:Auto
Number, size: Long Integer,new Values:increment, Index: yes (no duplicates)

***** This is Table field Discription per the Documenter *****
C:\Documents and Settings\dave\Desktop\Database Develop Thursday, December
08, 2005
Table: Employment Information Page: 1
Columns
Name Type Size
title Text 50
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Data Updatable: False
DisplayControl: Text Box
GUID: Long binary data
Ordinal Position: 1
Required: False
Source Field: title
Source Table: Employment Information
UnicodeCompression: True
SSN Long Integer 4
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Data Updatable: False
DecimalPlaces: Auto
DisplayControl: Text Box
GUID: Long binary data
Ordinal Position: 2
Required: True
Source Field: SSN
Source Table: Employment Information
UPATE ID Long Integer 4
AllowZeroLength: False
Attributes: Fixed Size, Auto-Increment
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Data Updatable: False
GUID: Long binary data
Ordinal Position: 3
Required: False
Source Field: UPATE ID
Source Table: Employment Information
DATE OF UPDATE Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False

C:\Documents and Settings\dave\Desktop\Database Develop Thursday, December
08, 2005
Table: Employment Information Page: 2
ColumnOrder: Default
ColumnWidth: Default
Data Updatable: False
Format: General Date
GUID: Long binary data
Ordinal Position: 4
Required: False
Source Field: DATE OF UPDATE
Source Table: Employment Information
REFERAL DATE Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Data Updatable: False
Description: CURRENT REFERAL DATE
GUID: Long binary data
Ordinal Position: 5
Required: False
Source Field: REFERAL DATE
Source Table: Employment Information
EMPLOYER Text 50
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Data Updatable: False
Description: PARTICIPANTS CURRENT EMPLOYER
DisplayControl: Text Box
GUID: Long binary data
Ordinal Position: 6
Required: False
Source Field: EMPLOYER
Source Table: Employment Information
UnicodeCompression: True
EMP PHONE Text 50
AllowZeroLength: False
Attributes: Variable Length
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Data Updatable: False
Description: EMPLOYERS TELEPHONE
DisplayControl: Text Box
GUID: Long binary data
Ordinal Position: 7
Required: False
Source Field: EMP PHONE

C:\Documents and Settings\dave\Desktop\Database Develop Thursday, December
08, 2005
Table: Employment Information Page: 3
Source Table: Employment Information
UnicodeCompression: False
HIRE DATE Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Data Updatable: False
Description: DATE OF PARTICIPANT HIRE
GUID: Long binary data
Ordinal Position: 8
Required: False
Source Field: HIRE DATE
Source Table: Employment Information
TERM DATE Date/Time 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Data Updatable: False
Description: DATE PARTICIPANT LEFT EMPLOYER
GUID: Long binary data
Ordinal Position: 9
Required: False
Source Field: TERM DATE
Source Table: Employment Information
$ PER HR Currency 8
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Data Updatable: False
DecimalPlaces: Auto
Description: PAY RATE
Format: $#,##0.00;($#,##0.00)
GUID: Long binary data
Ordinal Position: 10
Required: False
Source Field: $ PER HR
Source Table: Employment Information
HOURS PER WEEK Long Integer 4
AllowZeroLength: False
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default

C:\Documents and Settings\dave\Desktop\Database Develop Thursday, December
08, 2005
Table: Employment Information Page: 4
ColumnWidth: Default
Data Updatable: False
DecimalPlaces: Auto
Description: TOTAL HOURS PER WEEK
DisplayControl: Text Box
GUID: Long binary data
Ordinal Position: 11
Required: False
Source Field: HOURS PER WEEK
Source Table: Employment Information
BENIFITS Text 50
AllowZeroLength: False
Attributes: Variable Length
BoundColumn: 1
Collating Order: General
ColumnCount: 2
ColumnHeads: False
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
ColumnWidths: 420;2310
Data Updatable: False
Description: BENIFITS (A,B,C,D)
DisplayControl: Combo Box
GUID: Long binary data
LimitToList: False
ListRows: 8
ListWidth: 2730twip
Ordinal Position: 12
Required: False
RowSource: "A";"Paid Sick and or Annual Leave";"B";"Health Insurance
Only";"C";"Health Ins and Sick Leave";"D";"None Of The
Above";"E";"Unknown (please avoid)"
RowSourceType: Value List
Source Field: BENIFITS
Source Table: Employment Information
UnicodeCompression: True
**********
If there is anyting else you need let me know
thanks for your help

Dave
****************

 

[Chris2]

Chris, I hope the information below is what you want, this is what the Access
Documenter has for the table.

I currently do not have a query designed for this but what I am trying to do
is create a query from all of the employment records that shows how long a
person has gone with out employment between jobs. My goal for this query is
to use it as part of a report that needs to show all people that have been
employed continiously for 90 days ofer a period of time (coutiniously means
no gaps in employment greater than 30 days). I also need to show the people
that have been employed 180 days coutiniously (Again defined as no gaps in
employment greater than 30 days.) over the same period of time

The report periods come from a form
Form name [Report Menu]
Start date field name [Beginning]
End date field name [To]

My problem is compairing one Employment record term date to the next records
Employment hire date. so that I can see how many days the person was
unemployed, or transversly if in a 180 day or 90 day period if they were
"continously" employed.


***** This is Table field Discription per the Documenter *****

The documenter produces information that is practically impossible
to read, which is why I did not mention it.

C:\Documents and Settings\dave\Desktop\Database Develop Thursday, December
08, 2005
Table: Employment Information Page: 1
Columns
Name Type Size
title Text 50
SSN Long Integer 4
UPATE ID Long Integer 4
DATE OF UPDATE Date/Time 8
REFERAL DATE Date/Time 8
EMPLOYER Text 50
EMP PHONE Text 50
HIRE DATE Date/Time 8
TERM DATE Date/Time 8
$ PER HR Currency 8
HOURS PER WEEK Long Integer 4
BENIFITS Text 50

This is an example of the data
SSN Hire date Term date
11111 1/1/05 2/1/05 *
11111 1/7/05 4/1/05 * note the overlap in employment

Ouch! Well, I had thought it was going to be easy.

Eliminating overlapping dates! You don't ask for the easy things,
do you!

<snip>

My apologies, I didn't use your column and table names. I filched
off of some existing example tables I already had. This should
still be fully adaptable to your purposes.


Project to count the number of consecutive days of employement.

Tables:

CREATE TABLE Employees
(EmployeeID INTEGER
,SSN CHAR(10)
,FName TEXT(72)
,LName TEXT(72)
,CONSTRAINT pk_piEmployees
PRIMARY KEY (EmployeeID)
)

CREATE TABLE Jobs
(JobID INTEGER
,JobName TEXT(72)
,JobDescription TEXT(255)
,CONSTRAINT pk_piJobs
PRIMARY KEY (JobID)
)

CREATE TABLE EmploymentHistory
(EmploymentHistoryID INTEGER
,EmployeeID INTEGER
,JobID INTEGER
,StartDate DATETIME
,EndDate DATETIME
,CONSTRAINT pk_EmployeeHistory
PRIMARY KEY (EmploymentHistoryID)
,CONSTRAINT fk_EmploymentHistory_Employees_EmployeeID
FOREIGN KEY (EmployeeID)
REFERENCES Employees (EmployeeID)
,CONSTRAINT fk_EmploymentHistory_Jobs_JobID
FOREIGN KEY (JobID)
REFERENCES Jobs (JobID)
)


Sample Data:


Empoloyees:

1, 1112223333, Jay, Smith
2, 4445556666, Dee, Smith
3, 7778889999, Jeff, Smith

Jobs:

1, Sewer Cleaner, You don't want to know
2, Sewer Backup Supervisor, Still bad
3, Sewer Supervisor, Not much better
4, Sewer Backup Inspector, Lousy
5, Sewer Inspector, Not really that good
6, Sewer Management, King of all he surveys (uh oh . . .)

EmploymentHistory

1, 1, 1, 01/01/2000, 01/01/2001
2, 1, 2, 06/15/2000, 12/31/2000
3, 1, 3, 01/01/2001, 12/31/2001
4, 1, 4, 03/15/2001, 10/15/2001
5, 1, 5, 01/01/2002, 12/31/2002
6, 1, 5, 01/01/2004, 12/31/2004
7, 2, 5, 01/01/2001, 12/31/2004
8, 3, 6, 01/01/2000, null



Desired Results

#1

EmployeeID, StartDate, EndDate
1, 01/01/2000, 12/31/2002
1, 01/01/2004, 12/31/2004
2, 01/01/2001, 12/31/2004
3, 01/01/2000, 12/09/2005

#2

EmployeeID, ConsecutiveDaysEmployed
1 1462
2 1461
3 2170


This first query gets pretty wide, I tried shoving things over to
the left as far as reasonable, and I apologize for any line-wrapping
that might occur.

Query:

EliminateOverlappingDates:

SELECT EH1.EmployeeID
,EH1.StartDate
,MIN(EH2.EndDate) AS EndDate
FROM
(SELECT EH01.EmployeeID
,EH01.StartDate
FROM EmploymentHistory AS EH01
WHERE NOT EXISTS
(SELECT *
FROM EmploymentHistory EH001
WHERE EH001.EmployeeID = EH01.EmployeeID
AND EH001.StartDate < EH01.StartDate
AND Nz(EH001.EndDate, CDate(Date())) >=
(EH01.StartDate - 1)
)
) AS EH1
INNER JOIN
(SELECT EH02.EmployeeID
,Nz(EH02.EndDate, CDate(Date())) AS EndDate
FROM EmploymentHistory AS EH02
WHERE NOT EXISTS
(SELECT *
FROM EmploymentHistory AS EH002
WHERE EH002.EmployeeID = EH02.EmployeeID
AND EH002.StartDate <=
Nz(EH02.EndDate + 1, CDate(Date()))
AND Nz(EH002.EndDate, CDate(Date())) >
Nz(EH02.EndDate, CDate(Date()))
)
) AS EH2
ON EH1.EmployeeID = EH2.EmployeeID
AND EH1.StartDate < EH2.EndDate
GROUP BY EH1.EmployeeID
,EH1.StartDate

Results #1

EmployeeID, StartDate, EndDate
1, 01/01/2000, 12/31/2002
1, 01/01/2004, 12/31/2004
2, 01/01/2001, 12/31/2004
3, 01/01/2000, 12/09/2005



ConsecutiveEmploymentDays:

SELECT EOD1.EmployeeID
,SUM(DateDiff("d", EOD1.StartDate - 1, EOD1.EndDate))
AS ConsecutiveDaysEmployed
FROM EliminateOverlappingDates AS EOD1
GROUP BY EOD1.EmployeeID


Results #2

EmployeeID, ConsecutiveDaysEmployed
1 1462
2 1461
3 2170


Well, it isn't pretty, but it gets the job done.


Sincerely,

Chris O.

 

[Chris2]

Chris, I hope the information below is what you want, this is

what
the Access

Documenter has for the table.

I currently do not have a query designed for this but what I am trying to do
is create a query from all of the employment records that shows how long a
person has gone with out employment between jobs. My goal for this query is
to use it as part of a report that needs to show all people that have been
employed continiously for 90 days ofer a period of time (coutiniously means
no gaps in employment greater than 30 days). I also need to show the people
that have been employed 180 days coutiniously (Again defined as

no
gaps in

employment greater than 30 days.) over the same period of time

The report periods come from a form
Form name [Report Menu]
Start date field name [Beginning]
End date field name [To]

My problem is compairing one Employment record term date to the next records
Employment hire date. so that I can see how many days the person was
unemployed, or transversly if in a 180 day or 90 day period if they were
"continously" employed.


***** This is Table field Discription per the Documenter *****

The documenter produces information that is practically impossible
to read, which is why I did not mention it.

C:\Documents and Settings\dave\Desktop\Database Develop

Thursday,

December
08, 2005
Table: Employment Information Page: 1
Columns
Name Type Size
title Text 50
SSN Long Integer 4
UPATE ID Long Integer 4
DATE OF UPDATE Date/Time 8
REFERAL DATE Date/Time 8
EMPLOYER Text 50
EMP PHONE Text 50
HIRE DATE Date/Time 8
TERM DATE Date/Time 8
$ PER HR Currency 8
HOURS PER WEEK Long Integer 4
BENIFITS Text 50

This is an example of the data
SSN Hire date Term date
11111 1/1/05 2/1/05 *
11111 1/7/05 4/1/05 * note the overlap in employment

Ouch! Well, I had thought it was going to be easy.

Eliminating overlapping dates! You don't ask for the easy things,
do you!

<snip>

My apologies, I didn't use your column and table names. I filched
off of some existing example tables I already had. This should
still be fully adaptable to your purposes.


Project to count the number of consecutive days of employement.

Tables:

CREATE TABLE Employees
(EmployeeID INTEGER
,SSN CHAR(10)
,FName TEXT(72)
,LName TEXT(72)
,CONSTRAINT pk_piEmployees
PRIMARY KEY (EmployeeID)
)

CREATE TABLE Jobs
(JobID INTEGER
,JobName TEXT(72)
,JobDescription TEXT(255)
,CONSTRAINT pk_piJobs
PRIMARY KEY (JobID)
)

CREATE TABLE EmploymentHistory
(EmploymentHistoryID INTEGER
,EmployeeID INTEGER
,JobID INTEGER
,StartDate DATETIME
,EndDate DATETIME
,CONSTRAINT pk_EmployeeHistory
PRIMARY KEY (EmploymentHistoryID)
,CONSTRAINT fk_EmploymentHistory_Employees_EmployeeID
FOREIGN KEY (EmployeeID)
REFERENCES Employees (EmployeeID)
,CONSTRAINT fk_EmploymentHistory_Jobs_JobID
FOREIGN KEY (JobID)
REFERENCES Jobs (JobID)
)


Sample Data:


Empoloyees:

1, 1112223333, Jay, Smith
2, 4445556666, Dee, Smith
3, 7778889999, Jeff, Smith

Jobs:

1, Sewer Cleaner, You don't want to know
2, Sewer Backup Supervisor, Still bad
3, Sewer Supervisor, Not much better
4, Sewer Backup Inspector, Lousy
5, Sewer Inspector, Not really that good
6, Sewer Management, King of all he surveys (uh oh . . .)

EmploymentHistory

1, 1, 1, 01/01/2000, 01/01/2001
2, 1, 2, 06/15/2000, 12/31/2000
3, 1, 3, 01/01/2001, 12/31/2001
4, 1, 4, 03/15/2001, 10/15/2001
5, 1, 5, 01/01/2002, 12/31/2002
6, 1, 5, 01/01/2004, 12/31/2004
7, 2, 5, 01/01/2001, 12/31/2004
8, 3, 6, 01/01/2000, null



Desired Results

#1

EmployeeID, StartDate, EndDate
1, 01/01/2000, 12/31/2002
1, 01/01/2004, 12/31/2004
2, 01/01/2001, 12/31/2004
3, 01/01/2000, 12/09/2005

#2

EmployeeID, ConsecutiveDaysEmployed
1 1462
2 1461
3 2170


This first query gets pretty wide, I tried shoving things over to
the left as far as reasonable, and I apologize for any line-wrapping
that might occur.

Query:

EliminateOverlappingDates:

SELECT EH1.EmployeeID
,EH1.StartDate
,MIN(EH2.EndDate) AS EndDate
FROM
(SELECT EH01.EmployeeID
,EH01.StartDate
FROM EmploymentHistory AS EH01
WHERE NOT EXISTS
(SELECT *
FROM EmploymentHistory EH001
WHERE EH001.EmployeeID = EH01.EmployeeID
AND EH001.StartDate < EH01.StartDate
AND Nz(EH001.EndDate, CDate(Date())) >=
(EH01.StartDate - 1)
)
) AS EH1
INNER JOIN
(SELECT EH02.EmployeeID
,Nz(EH02.EndDate, CDate(Date())) AS EndDate
FROM EmploymentHistory AS EH02
WHERE NOT EXISTS
(SELECT *
FROM EmploymentHistory AS EH002
WHERE EH002.EmployeeID = EH02.EmployeeID
AND EH002.StartDate <=
Nz(EH02.EndDate + 1, CDate(Date()))
AND Nz(EH002.EndDate, CDate(Date())) >
Nz(EH02.EndDate, CDate(Date()))
)
) AS EH2
ON EH1.EmployeeID = EH2.EmployeeID
AND EH1.StartDate < EH2.EndDate
GROUP BY EH1.EmployeeID
,EH1.StartDate

Results #1

EmployeeID, StartDate, EndDate
1, 01/01/2000, 12/31/2002
1, 01/01/2004, 12/31/2004
2, 01/01/2001, 12/31/2004
3, 01/01/2000, 12/09/2005



ConsecutiveEmploymentDays:

SELECT EOD1.EmployeeID
,SUM(DateDiff("d", EOD1.StartDate - 1, EOD1.EndDate))
AS ConsecutiveDaysEmployed
FROM EliminateOverlappingDates AS EOD1
GROUP BY EOD1.EmployeeID


Results #2

EmployeeID, ConsecutiveDaysEmployed
1 1462
2 1461
3 2170


Well, it isn't pretty, but it gets the job done.


Sincerely,

Chris O.

Dave E,

Are you out there?


Sincerely,

Chris O.

 

[Guest]

It appears that the general procedure is to get all the records related to an
employee. Then sort the records by start date in ascending order. go from
the first record to the last, and record the first start date of the first
record. Go to the next record and compare the start date with the previous
record's end date. If they are equal or overlap, go to the next record.
Continue until there is a gap. then record the end date of the previous
record. You have the start and end date of continues employment. Continue
until you reach the end of this person's records. Next record. I hope this
helps, since I don't know how to program in sql or access. By the way, can
you send me your program because I need to write an employment program.
Thanks.