Date Difference

  • Thread starter Thread starter lettyg82
  • Start date Start date
L

lettyg82

Good Morning. I am trying to figure out how to calculate the number of days
between two dates. I have a table callled events. That table has event
date, event type, event outcome.

For example a defendant will have an Advisement Date (AA as stored in the
table) and say a Preliminary Hearing date (PH). When i enter information I
will enter the date, event type and the event outcome will be (PD for
Pending, CN for Continued, or CP for completed). For example Case Number
10-12345 will have an AA on 01/05/10 which is Completed and a Preliminary
Hearing on 02/05/10 which was Continued then another Preliminary Hearing date
on 02/19/10 which was completed. I want to be able to calculate in a report
the number of days between AA and PH date that are completed dates. Is the
way that I have the table set up going to allow me to do this or do i have to
change the labels and have those specific hearings as field names.

Please help
 
Lettyg82 -

Your query would be like this:

SELECT E1.CaseNumber, E1.[Event Type], E1.[Date], E2.[Event Type],
E2.[Date], E2.[Date] - E1.[Date] As Days_Between
From Events AS E1 INNER JOIN Events AS E2 ON E1.CaseNumber = E2.CaseNumber
Where E1.[Event Outcome] = "CP" and E1.[Date] is not null AND E1.[Event
Type] = "AA"
AND E2.[Event Outcome] = "CP" and E2.[Date] is not null AND E2.[Event Type]
= "PH"
GROUP BY E1.[CaseNumber]

If you only want this for a specific case number, then change the last line to
AND E1.CaseNumber = '10-12345'
or use a parameter or a form field to populate that criteria.
 
Thanks for the information Daryl. I am farely new to Access. Can you tell
me where exactly i should put this code in?

Thanks

Daryl S said:
Lettyg82 -

Your query would be like this:

SELECT E1.CaseNumber, E1.[Event Type], E1.[Date], E2.[Event Type],
E2.[Date], E2.[Date] - E1.[Date] As Days_Between
From Events AS E1 INNER JOIN Events AS E2 ON E1.CaseNumber = E2.CaseNumber
Where E1.[Event Outcome] = "CP" and E1.[Date] is not null AND E1.[Event
Type] = "AA"
AND E2.[Event Outcome] = "CP" and E2.[Date] is not null AND E2.[Event Type]
= "PH"
GROUP BY E1.[CaseNumber]

If you only want this for a specific case number, then change the last line to
AND E1.CaseNumber = '10-12345'
or use a parameter or a form field to populate that criteria.

--
Daryl S


lettyg82 said:
Good Morning. I am trying to figure out how to calculate the number of days
between two dates. I have a table callled events. That table has event
date, event type, event outcome.

For example a defendant will have an Advisement Date (AA as stored in the
table) and say a Preliminary Hearing date (PH). When i enter information I
will enter the date, event type and the event outcome will be (PD for
Pending, CN for Continued, or CP for completed). For example Case Number
10-12345 will have an AA on 01/05/10 which is Completed and a Preliminary
Hearing on 02/05/10 which was Continued then another Preliminary Hearing date
on 02/19/10 which was completed. I want to be able to calculate in a report
the number of days between AA and PH date that are completed dates. Is the
way that I have the table set up going to allow me to do this or do i have to
change the labels and have those specific hearings as field names.

Please help
Click to expand...
Click to expand...
 
Also the table name where i house the Events Info is: tbl_FV_Events
the fields are EventDt, EventTypeCd, EventOutcomeCd, CR

tbl_FV_CaseInfo is the ONE table and all my other tables are the MANY
tables. CR is the parent field on tbl_FV_CaseInfo, where CR in tbl_FV_Events
is the child field.

Please help.

lettyg82 said:
Thanks for the information Daryl. I am farely new to Access. Can you tell
me where exactly i should put this code in?

Thanks

Daryl S said:
Lettyg82 -

Your query would be like this:

SELECT E1.CaseNumber, E1.[Event Type], E1.[Date], E2.[Event Type],
E2.[Date], E2.[Date] - E1.[Date] As Days_Between
From Events AS E1 INNER JOIN Events AS E2 ON E1.CaseNumber = E2.CaseNumber
Where E1.[Event Outcome] = "CP" and E1.[Date] is not null AND E1.[Event
Type] = "AA"
AND E2.[Event Outcome] = "CP" and E2.[Date] is not null AND E2.[Event Type]
= "PH"
GROUP BY E1.[CaseNumber]

If you only want this for a specific case number, then change the last line to
AND E1.CaseNumber = '10-12345'
or use a parameter or a form field to populate that criteria.

--
Daryl S


lettyg82 said:
Good Morning. I am trying to figure out how to calculate the number of days
between two dates. I have a table callled events. That table has event
date, event type, event outcome.

For example a defendant will have an Advisement Date (AA as stored in the
table) and say a Preliminary Hearing date (PH). When i enter information I
will enter the date, event type and the event outcome will be (PD for
Pending, CN for Continued, or CP for completed). For example Case Number
10-12345 will have an AA on 01/05/10 which is Completed and a Preliminary
Hearing on 02/05/10 which was Continued then another Preliminary Hearing date
on 02/19/10 which was completed. I want to be able to calculate in a report
the number of days between AA and PH date that are completed dates. Is the
way that I have the table set up going to allow me to do this or do i have to
change the labels and have those specific hearings as field names.

Please help
Click to expand...
Click to expand...
Click to expand...
 
I replied to your more recent posting...
--
Daryl S


lettyg82 said:
Also the table name where i house the Events Info is: tbl_FV_Events
the fields are EventDt, EventTypeCd, EventOutcomeCd, CR

tbl_FV_CaseInfo is the ONE table and all my other tables are the MANY
tables. CR is the parent field on tbl_FV_CaseInfo, where CR in tbl_FV_Events
is the child field.

Please help.

lettyg82 said:
Thanks for the information Daryl. I am farely new to Access. Can you tell
me where exactly i should put this code in?

Thanks

Daryl S said:
Lettyg82 -

Your query would be like this:

SELECT E1.CaseNumber, E1.[Event Type], E1.[Date], E2.[Event Type],
E2.[Date], E2.[Date] - E1.[Date] As Days_Between
From Events AS E1 INNER JOIN Events AS E2 ON E1.CaseNumber = E2.CaseNumber
Where E1.[Event Outcome] = "CP" and E1.[Date] is not null AND E1.[Event
Type] = "AA"
AND E2.[Event Outcome] = "CP" and E2.[Date] is not null AND E2.[Event Type]
= "PH"
GROUP BY E1.[CaseNumber]

If you only want this for a specific case number, then change the last line to
AND E1.CaseNumber = '10-12345'
or use a parameter or a form field to populate that criteria.

--
Daryl S


:

Good Morning. I am trying to figure out how to calculate the number of days
between two dates. I have a table callled events. That table has event
date, event type, event outcome.

For example a defendant will have an Advisement Date (AA as stored in the
table) and say a Preliminary Hearing date (PH). When i enter information I
will enter the date, event type and the event outcome will be (PD for
Pending, CN for Continued, or CP for completed). For example Case Number
10-12345 will have an AA on 01/05/10 which is Completed and a Preliminary
Hearing on 02/05/10 which was Continued then another Preliminary Hearing date
on 02/19/10 which was completed. I want to be able to calculate in a report
the number of days between AA and PH date that are completed dates. Is the
way that I have the table set up going to allow me to do this or do i have to
change the labels and have those specific hearings as field names.

Please help
Click to expand...
Click to expand...
Click to expand...
 
Back
Top