Query/Report Help for 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. I am pretty new to Access and need help.

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.
Also the table name where i house the Events Info is: tbl_FV_Events
the fields are EventDt, EventTypeCd, EventOutcomeCd, CR

Also as an FYI:
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

I was told to use a query 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]
 
lettyg82 -

Here is the same query with your field names. Copy/Paste this into a SQL
View window:

SELECT E1.CR, E1.EventTypeCd, E1.EventDt, E2.EventTypeCd,
E2.EventDt, E2.EventDt - E1.EventDt As Days_Between From tbl_FV_Events AS E1
INNER
JOIN tbl_FV_Events AS E2 ON E1.CaseNumber = E2.CaseNumber Where
E1.EventOutcomeCd =
"CP" and E1.EventDt is not null AND E1.EventTypeCd = "AA" AND
E2.EventOutcomeCd = "CP"
and E2.EventDt is not null AND E2.EventTypeCd = "PH"
 
Thank you Daryl for your prompt response. That code seems to work however,
where does it calculate the number of dates between AA and PH?
Or is that a different code that I should enter?



Daryl S said:
lettyg82 -

Here is the same query with your field names. Copy/Paste this into a SQL
View window:

SELECT E1.CR, E1.EventTypeCd, E1.EventDt, E2.EventTypeCd,
E2.EventDt, E2.EventDt - E1.EventDt As Days_Between From tbl_FV_Events AS E1
INNER
JOIN tbl_FV_Events AS E2 ON E1.CaseNumber = E2.CaseNumber Where
E1.EventOutcomeCd =
"CP" and E1.EventDt is not null AND E1.EventTypeCd = "AA" AND
E2.EventOutcomeCd = "CP"
and E2.EventDt is not null AND E2.EventTypeCd = "PH"

--
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. I am pretty new to Access and need help.

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.
Also the table name where i house the Events Info is: tbl_FV_Events
the fields are EventDt, EventTypeCd, EventOutcomeCd, CR

Also as an FYI:
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

I was told to use a query 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]
Click to expand...
Click to expand...
 
I apologize i was in the wrong query, the SQL did not work. When i run the
query it asks for to enter a parameter Value "E1.CaseNumber". I went in and
changed wherever CaseNumber is to CR and then when i run it says type
mismatch in criteria expression.

lettyg82 said:
Thank you Daryl for your prompt response. That code seems to work however,
where does it calculate the number of dates between AA and PH?
Or is that a different code that I should enter?



Daryl S said:
lettyg82 -

Here is the same query with your field names. Copy/Paste this into a SQL
View window:

SELECT E1.CR, E1.EventTypeCd, E1.EventDt, E2.EventTypeCd,
E2.EventDt, E2.EventDt - E1.EventDt As Days_Between From tbl_FV_Events AS E1
INNER
JOIN tbl_FV_Events AS E2 ON E1.CaseNumber = E2.CaseNumber Where
E1.EventOutcomeCd =
"CP" and E1.EventDt is not null AND E1.EventTypeCd = "AA" AND
E2.EventOutcomeCd = "CP"
and E2.EventDt is not null AND E2.EventTypeCd = "PH"

--
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. I am pretty new to Access and need help.

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.
Also the table name where i house the Events Info is: tbl_FV_Events
the fields are EventDt, EventTypeCd, EventOutcomeCd, CR

Also as an FYI:
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

I was told to use a query 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]
Click to expand...
Click to expand...
Click to expand...
 
Lettyg82 -

Can you post the SQL you have now so we can help? I assume EventDate is a
true date field, not a text string?

--
Daryl S


lettyg82 said:
I apologize i was in the wrong query, the SQL did not work. When i run the
query it asks for to enter a parameter Value "E1.CaseNumber". I went in and
changed wherever CaseNumber is to CR and then when i run it says type
mismatch in criteria expression.

lettyg82 said:
Thank you Daryl for your prompt response. That code seems to work however,
where does it calculate the number of dates between AA and PH?
Or is that a different code that I should enter?



Daryl S said:
lettyg82 -

Here is the same query with your field names. Copy/Paste this into a SQL
View window:

SELECT E1.CR, E1.EventTypeCd, E1.EventDt, E2.EventTypeCd,
E2.EventDt, E2.EventDt - E1.EventDt As Days_Between From tbl_FV_Events AS E1
INNER
JOIN tbl_FV_Events AS E2 ON E1.CaseNumber = E2.CaseNumber Where
E1.EventOutcomeCd =
"CP" and E1.EventDt is not null AND E1.EventTypeCd = "AA" AND
E2.EventOutcomeCd = "CP"
and E2.EventDt is not null AND E2.EventTypeCd = "PH"

--
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. I am pretty new to Access and need help.

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.
Also the table name where i house the Events Info is: tbl_FV_Events
the fields are EventDt, EventTypeCd, EventOutcomeCd, CR

Also as an FYI:
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

I was told to use a query 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]
Click to expand...
Click to expand...
Click to expand...
 
SELECT E1.tbl_FV_Events.CR, E1.tbl_FV_Events.EventTypeCd,
E1.tbl_FV_Events.EventDt, E2.tbl_FV_Events.EventTypeCd,
E2.tbl_FV_Events.EventDt, E2.tbl_FV_Events.EventDt-E1.tbl_FV_Events.EventDt
AS Days_Between
FROM tbl_FV_Events AS E1 INNER JOIN tbl_FV_Events AS E2 ON
E1.CR=E2.tbl_FV_Events.CR
WHERE E1.tbl_FV_Events.EventOutcomeCd="CP" And E1.tbl_FV_Events.EventDt Is
Not Null And E1.tbl_FV_Events.EventTypeCd="AA" And
E2.tbl_FV_Events.EventOutcomeCd="CP" And E2.tbl_FV_Events.EventDt Is Not Null
And E2.tbl_FV_Events.EventTypeCd="PH";

I added the table names after E1. and E2. Not sure if that is correct.

Not sure if this is of importance or not but EventTypeCd pulls from Lookup
table tbl_LU_EventType and EventOutcomeCd pulls from tbl_LU_EventOutcome.



Daryl S said:
Lettyg82 -

Can you post the SQL you have now so we can help? I assume EventDate is a
true date field, not a text string?

--
Daryl S


lettyg82 said:
I apologize i was in the wrong query, the SQL did not work. When i run the
query it asks for to enter a parameter Value "E1.CaseNumber". I went in and
changed wherever CaseNumber is to CR and then when i run it says type
mismatch in criteria expression.

lettyg82 said:
Thank you Daryl for your prompt response. That code seems to work however,
where does it calculate the number of dates between AA and PH?
Or is that a different code that I should enter?



:

lettyg82 -

Here is the same query with your field names. Copy/Paste this into a SQL
View window:

SELECT E1.CR, E1.EventTypeCd, E1.EventDt, E2.EventTypeCd,
E2.EventDt, E2.EventDt - E1.EventDt As Days_Between From tbl_FV_Events AS E1
INNER
JOIN tbl_FV_Events AS E2 ON E1.CaseNumber = E2.CaseNumber Where
E1.EventOutcomeCd =
"CP" and E1.EventDt is not null AND E1.EventTypeCd = "AA" AND
E2.EventOutcomeCd = "CP"
and E2.EventDt is not null AND E2.EventTypeCd = "PH"

--
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. I am pretty new to Access and need help.

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.
Also the table name where i house the Events Info is: tbl_FV_Events
the fields are EventDt, EventTypeCd, EventOutcomeCd, CR

Also as an FYI:
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

I was told to use a query 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]
Click to expand...
Click to expand...
Click to expand...
 
Lettyg82 -

The E1 and D2 are aliases of the table names, so you don't need to put the
table names there. That is what the "AS E1" and "AS E2" do. I have removed
them here. What do you get when you run this? I can't see how you would be
asked for a parameter for E1.CaseNumber, as that is not in the query.

You say that EventTypeCd pulls from tbl_LU_EventType, and EventOutcomeCd
pulls from tbl_LU_EventOutcome. I assume the codes you provided (CP, AA, and
PH) are the keys to the table, and the descriptions are in the lookup tables.
If you want the descriptions on the report instead of the codes, then we can
address that after you get the basic query working.

What happens when you run this code (I just took out the tablenames, as E1
and E2 are the table aliases)?

SELECT E1.CR, E1.EventTypeCd,
E1.EventDt, E2.EventTypeCd,
E2.EventDt, E2.EventDt - E1.EventDt AS Days_Between
FROM tbl_FV_Events AS E1 INNER JOIN tbl_FV_Events AS E2 ON
E1.CR = E2.CR
WHERE E1.EventOutcomeCd = "CP" And E1.EventDt Is
Not Null And E1.EventTypeCd = "AA" And
E2.EventOutcomeCd = "CP" And E2.EventDt Is Not Null
And E2 .EventTypeCd = "PH";
 
Cheryl thank you thank you so much for all your help. It finally worked.

I had to delete a relationship between [tbl_FV_Events] and
[tbl_LU_EventOutcome]. Then delete the primary key of [tbl_LU_EventOutcome]
as it shouldnt of had a primary key and that had a direct relationship
between the EventOutcomeCd and that primary key. i then went back and into
[tbl_FV_Events] and under field name EventOutcomeCd created a look up again
from [tbl_LU_EventOutcome]. Once I did this and tried the code again it
worked.

I can not thank you enough. You are truly a genius! Thank you so much!!
 
Great - I'm glad you got it working.

--
Daryl S


lettyg82 said:
Cheryl thank you thank you so much for all your help. It finally worked.

I had to delete a relationship between [tbl_FV_Events] and
[tbl_LU_EventOutcome]. Then delete the primary key of [tbl_LU_EventOutcome]
as it shouldnt of had a primary key and that had a direct relationship
between the EventOutcomeCd and that primary key. i then went back and into
[tbl_FV_Events] and under field name EventOutcomeCd created a look up again
from [tbl_LU_EventOutcome]. Once I did this and tried the code again it
worked.

I can not thank you enough. You are truly a genius! Thank you so much!!
Click to expand...
 
Back
Top