Another Crosstab query question

[Guest]

I have a table say table A with customer information. I need to count each
pending record for each month where the ReceiveDate <= that month, but the
CompleteDate is null or > that month, and have it broken down by for each
month of a quarter.

In other words it should display something like:

Jan Feb Mar
12 5 8

Now I guess I need to use DateDiff from ReceiveDate and CompleteDate, but
somehow provide the first day of the quarter. I have a form that will
provide the criteria for the query say Forms!frmRpts.Text1, which will
display the first day of the quarter.


I hope I am thinking through this process correctly.

Thanks for any response in this matter.

 

[Tom Ellison]

Dear TT:

I would think that the test would be that the ReceiveDate be on or before
the end of each of these months and that the CompleteDate would be null or
on or after the first of that same month.

I could try to code and test this but, if my query is to be directly usable
by you, I should use the exact table and column names you have. So, if you
want that help, please do the following:

1. see if you agree with my first statement

2. provide the table and column names

3. Provide a bit of sample data and the results you expect it to produce

You will probably need a table that provides the months of each quarter as
well. I'll cook up something for that to show you as well.

Tom Ellison

 

[Guest]

Thanks for your response Tom,

This is a part of a bigger picture. I have a report with 10 subreports this
is one of those subreports.

My table is tblCustomer, the fields I will be messing with areCustomerID,
ReferralDateReceived, OpenDate, and DischargeDate.

On my form I have an option group that allows the user to select which
quarter they and also a combo box to select the year. Once they select these
items it populates a hidden text box called Text1. When they press the
submit button it will run the report with all the subreports and build the
proper Titles for the months. I was able to get everything working fine with
Allen's help this past weekend. However, they came back with a different
requirement. Now they want to display how many records were still pending
(no Opendate completed that month for that record) at the end of each month.

I see some of your logic. I guess I will need another hidden text box to
have it populated with the last day of the month for the first month of the
quarter. If and referrals were received before hand count those now look at
the OpenDate count only those that have a null value or have a date after
this month.

However, do I need to build 3 different queries and have them dynamically
fill in an unbound subreport for each of the 3 months?


This is something I was thinking of for the first month:

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.OpenDate)<DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))>=1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate) Is Null));

the next month I would replace ("m",1...
with ("m",2....
But I don't think that will work either.


Sample Data

CustomerID ReferralDateReceived OpenDate
12 9/7/05
13 9/7/05 9/8/05
14 9/8/05 10/1/05
15 9/8/05 10/15/05
16 9/8/05 11/3/05
17 9/8/05 10/15/05
18 10/2/05 12/03/05

Should expect to see
Qtrs
Oct Nov Dec
3 2 1


Anyway any suggestion will be greatly apprecited. I would be nice to be
able to do this in a crosstab, but I don't see how. I would assume for now
that they want to count the records that did not have an open date before the
end of the month.

 

[Tom Ellison]

Dear TT:

Help me see what's up with your sample data first, please:

CustomerID ReferralDateReceived OpenDate Months
Alternate
12 9/7/05
Oct, Nov, Dec Oct, Nov, Dec
13 9/7/05 9/8/05 -
none - - none -
14 9/8/05 10/1/05
- none -
15 9/8/05 10/15/05
- none -
16 9/8/05 11/3/05 Oct,
Nov Oct
17 9/8/05 10/15/05
- none -
18 10/2/05 12/03/05 Oct, Nov,
Dec Oct, Nov

I have added the months column above to show which months I would expect
each row to generate. But this would give Oct 6, Nov 3, and Dec 2, while
you show Oct 3, Nov 2, Dec 1. Is it the case that the range between
Referral and Open must span the last day of the month? Is that it? What
months would be included if the OpenDate were exactly on the last day of the
month?

I show this in the Alternate column, and it does give Oct 3, Nov 2, Dec1. I
guess this is it.

Now, for a new row:

19 9/9/05 10/31/05

Does this count as October or not. I'm testing the "boundary condition"
because that's often where misunderstandings occur.

I'm going to introduce a new table I expect to use:

MyQuarters:
Quarter MthEnd
"3/2005" 9/30/2005
"4/2005" 10/31/2005
"4/2005" 11/30/2005
"4/2005" 12/31/2005
"1/2006" 1/31/2006
etc.

I plan to use this in the next step. I'll respond when the questions are
answered so I have a chance to get it right the first time, and look good in
the process. : )

Tom Ellison

Thanks for your response Tom,

This is a part of a bigger picture. I have a report with 10 subreports
this
is one of those subreports.

My table is tblCustomer, the fields I will be messing with areCustomerID,
ReferralDateReceived, OpenDate, and DischargeDate.

On my form I have an option group that allows the user to select which
quarter they and also a combo box to select the year. Once they select
these
items it populates a hidden text box called Text1. When they press the
submit button it will run the report with all the subreports and build the
proper Titles for the months. I was able to get everything working fine
with
Allen's help this past weekend. However, they came back with a different
requirement. Now they want to display how many records were still pending
(no Opendate completed that month for that record) at the end of each
month.

I see some of your logic. I guess I will need another hidden text box to
have it populated with the last day of the month for the first month of
the
quarter. If and referrals were received before hand count those now look
at
the OpenDate count only those that have a null value or have a date after
this month.

However, do I need to build 3 different queries and have them dynamically
fill in an unbound subreport for each of the 3 months?


This is something I was thinking of for the first month:

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.OpenDate)<DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))>=1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate) Is Null));

the next month I would replace ("m",1...
with ("m",2....
But I don't think that will work either.


Sample Data

CustomerID ReferralDateReceived OpenDate
12 9/7/05
13 9/7/05 9/8/05
14 9/8/05 10/1/05
15 9/8/05 10/15/05
16 9/8/05 11/3/05
17 9/8/05 10/15/05
18 10/2/05 12/03/05

Should expect to see
Qtrs
Oct Nov Dec
3 2 1


Anyway any suggestion will be greatly apprecited. I would be nice to be
able to do this in a crosstab, but I don't see how. I would assume for
now
that they want to count the records that did not have an open date before
the
end of the month.

Dear TT:

I would think that the test would be that the ReceiveDate be on or before
the end of each of these months and that the CompleteDate would be null
or
on or after the first of that same month.

I could try to code and test this but, if my query is to be directly
usable
by you, I should use the exact table and column names you have. So, if
you
want that help, please do the following:

1. see if you agree with my first statement

2. provide the table and column names

3. Provide a bit of sample data and the results you expect it to produce

You will probably need a table that provides the months of each quarter
as
well. I'll cook up something for that to show you as well.

Tom Ellison

 

[Guest]

I am little confused of what you wrote or I guess the way it is displayed.
Either case let try to explain it a little better. Thye receive the
referral. However, sometimes they don't actually open the case until
sometimes later. So they need to show how many pending cases they have
during each month.

Let's count the ReferralReceiveDate on or before the end of the month and
any OpenDate that is null or after the last day of that month.

So by the example data that I provided if I was pulling the 2005 4th quarter
data I would have the following:


Oct =3
1 for the null since it was received before Nov, but not opened
1 for 11/3/05 since it was received before Nov, but opened after Oct
12 for 12/03/05 since it was received before Nov, but opened after Oct

Nov=2
1 for null since it was received before Dec, but not opened
1 for 12/03/05 since it was received before Dec, but opened after Nov

Dec=1
1 for null since it was received before Jan, but not opened

I hope this makes better sense.

Dear TT:

Help me see what's up with your sample data first, please:

CustomerID ReferralDateReceived OpenDate Months
Alternate
12 9/7/05
Oct, Nov, Dec Oct, Nov, Dec
13 9/7/05 9/8/05 -
none - - none -
14 9/8/05 10/1/05
- none -
15 9/8/05 10/15/05
- none -
16 9/8/05 11/3/05 Oct,
Nov Oct
17 9/8/05 10/15/05
- none -
18 10/2/05 12/03/05 Oct, Nov,
Dec Oct, Nov

I have added the months column above to show which months I would expect
each row to generate. But this would give Oct 6, Nov 3, and Dec 2, while
you show Oct 3, Nov 2, Dec 1. Is it the case that the range between
Referral and Open must span the last day of the month? Is that it? What
months would be included if the OpenDate were exactly on the last day of the
month?

I show this in the Alternate column, and it does give Oct 3, Nov 2, Dec1. I
guess this is it.

Now, for a new row:

19 9/9/05 10/31/05

Does this count as October or not. I'm testing the "boundary condition"
because that's often where misunderstandings occur.

I'm going to introduce a new table I expect to use:

MyQuarters:
Quarter MthEnd
"3/2005" 9/30/2005
"4/2005" 10/31/2005
"4/2005" 11/30/2005
"4/2005" 12/31/2005
"1/2006" 1/31/2006
etc.

I plan to use this in the next step. I'll respond when the questions are
answered so I have a chance to get it right the first time, and look good in
the process. : )

Tom Ellison

Thanks for your response Tom,

This is a part of a bigger picture. I have a report with 10 subreports
this
is one of those subreports.

My table is tblCustomer, the fields I will be messing with areCustomerID,
ReferralDateReceived, OpenDate, and DischargeDate.

On my form I have an option group that allows the user to select which
quarter they and also a combo box to select the year. Once they select
these
items it populates a hidden text box called Text1. When they press the
submit button it will run the report with all the subreports and build the
proper Titles for the months. I was able to get everything working fine
with
Allen's help this past weekend. However, they came back with a different
requirement. Now they want to display how many records were still pending
(no Opendate completed that month for that record) at the end of each
month.

I see some of your logic. I guess I will need another hidden text box to
have it populated with the last day of the month for the first month of
the
quarter. If and referrals were received before hand count those now look
at
the OpenDate count only those that have a null value or have a date after
this month.

However, do I need to build 3 different queries and have them dynamically
fill in an unbound subreport for each of the 3 months?


This is something I was thinking of for the first month:

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.OpenDate)<DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))>=1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate) Is Null));

the next month I would replace ("m",1...
with ("m",2....
But I don't think that will work either.


Sample Data

CustomerID ReferralDateReceived OpenDate
12 9/7/05
13 9/7/05 9/8/05
14 9/8/05 10/1/05
15 9/8/05 10/15/05
16 9/8/05 11/3/05
17 9/8/05 10/15/05
18 10/2/05 12/03/05

Should expect to see
Qtrs
Oct Nov Dec
3 2 1


Anyway any suggestion will be greatly apprecited. I would be nice to be
able to do this in a crosstab, but I don't see how. I would assume for
now
that they want to count the records that did not have an open date before
the
end of the month.

Dear TT:

I would think that the test would be that the ReceiveDate be on or before
the end of each of these months and that the CompleteDate would be null
or
on or after the first of that same month.

I could try to code and test this but, if my query is to be directly
usable
by you, I should use the exact table and column names you have. So, if
you
want that help, please do the following:

1. see if you agree with my first statement

2. provide the table and column names

3. Provide a bit of sample data and the results you expect it to produce

You will probably need a table that provides the months of each quarter
as
well. I'll cook up something for that to show you as well.

Tom Ellison


I have a table say table A with customer information. I need to count
each
pending record for each month where the ReceiveDate <= that month, but
the
CompleteDate is null or > that month, and have it broken down by for
each
month of a quarter.

In other words it should display something like:

Jan Feb Mar
12 5 8

Now I guess I need to use DateDiff from ReceiveDate and CompleteDate,
but
somehow provide the first day of the quarter. I have a form that will
provide the criteria for the query say Forms!frmRpts.Text1, which will
display the first day of the quarter.


I hope I am thinking through this process correctly.

Thanks for any response in this matter.

 

[Guest]

Sorry for the typos

12 for 12/03/05 since it was received before Nov, but opened after Oct

should have been

1 for 12/03/05 since it was received before Nov, but opened after Oct

Dear TT:

Help me see what's up with your sample data first, please:

CustomerID ReferralDateReceived OpenDate Months
Alternate
12 9/7/05
Oct, Nov, Dec Oct, Nov, Dec
13 9/7/05 9/8/05 -
none - - none -
14 9/8/05 10/1/05
- none -
15 9/8/05 10/15/05
- none -
16 9/8/05 11/3/05 Oct,
Nov Oct
17 9/8/05 10/15/05
- none -
18 10/2/05 12/03/05 Oct, Nov,
Dec Oct, Nov

I have added the months column above to show which months I would expect
each row to generate. But this would give Oct 6, Nov 3, and Dec 2, while
you show Oct 3, Nov 2, Dec 1. Is it the case that the range between
Referral and Open must span the last day of the month? Is that it? What
months would be included if the OpenDate were exactly on the last day of the
month?

I show this in the Alternate column, and it does give Oct 3, Nov 2, Dec1. I
guess this is it.

Now, for a new row:

19 9/9/05 10/31/05

Does this count as October or not. I'm testing the "boundary condition"
because that's often where misunderstandings occur.

I'm going to introduce a new table I expect to use:

MyQuarters:
Quarter MthEnd
"3/2005" 9/30/2005
"4/2005" 10/31/2005
"4/2005" 11/30/2005
"4/2005" 12/31/2005
"1/2006" 1/31/2006
etc.

I plan to use this in the next step. I'll respond when the questions are
answered so I have a chance to get it right the first time, and look good in
the process. : )

Tom Ellison

Thanks for your response Tom,

This is a part of a bigger picture. I have a report with 10 subreports
this
is one of those subreports.

My table is tblCustomer, the fields I will be messing with areCustomerID,
ReferralDateReceived, OpenDate, and DischargeDate.

On my form I have an option group that allows the user to select which
quarter they and also a combo box to select the year. Once they select
these
items it populates a hidden text box called Text1. When they press the
submit button it will run the report with all the subreports and build the
proper Titles for the months. I was able to get everything working fine
with
Allen's help this past weekend. However, they came back with a different
requirement. Now they want to display how many records were still pending
(no Opendate completed that month for that record) at the end of each
month.

I see some of your logic. I guess I will need another hidden text box to
have it populated with the last day of the month for the first month of
the
quarter. If and referrals were received before hand count those now look
at
the OpenDate count only those that have a null value or have a date after
this month.

However, do I need to build 3 different queries and have them dynamically
fill in an unbound subreport for each of the 3 months?


This is something I was thinking of for the first month:

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.OpenDate)<DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))>=1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate) Is Null));

the next month I would replace ("m",1...
with ("m",2....
But I don't think that will work either.


Sample Data

CustomerID ReferralDateReceived OpenDate
12 9/7/05
13 9/7/05 9/8/05
14 9/8/05 10/1/05
15 9/8/05 10/15/05
16 9/8/05 11/3/05
17 9/8/05 10/15/05
18 10/2/05 12/03/05

Should expect to see
Qtrs
Oct Nov Dec
3 2 1


Anyway any suggestion will be greatly apprecited. I would be nice to be
able to do this in a crosstab, but I don't see how. I would assume for
now
that they want to count the records that did not have an open date before
the
end of the month.

Dear TT:

I would think that the test would be that the ReceiveDate be on or before
the end of each of these months and that the CompleteDate would be null
or
on or after the first of that same month.

I could try to code and test this but, if my query is to be directly
usable
by you, I should use the exact table and column names you have. So, if
you
want that help, please do the following:

1. see if you agree with my first statement

2. provide the table and column names

3. Provide a bit of sample data and the results you expect it to produce

You will probably need a table that provides the months of each quarter
as
well. I'll cook up something for that to show you as well.

Tom Ellison


I have a table say table A with customer information. I need to count
each
pending record for each month where the ReceiveDate <= that month, but
the
CompleteDate is null or > that month, and have it broken down by for
each
month of a quarter.

In other words it should display something like:

Jan Feb Mar
12 5 8

Now I guess I need to use DateDiff from ReceiveDate and CompleteDate,
but
somehow provide the first day of the quarter. I have a form that will
provide the criteria for the query say Forms!frmRpts.Text1, which will
display the first day of the quarter.


I hope I am thinking through this process correctly.

Thanks for any response in this matter.

 

[Guest]

I added another hidden text box with end date of 1st of month reflecting the
option group and combo box for the year. Here is the SQL, but it there a way
to make this so I can use it in a crosstab instead of creating 3 separate
queries?

This is for the first month of the quarter.

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text2]) AND
((tblCustomer.OpenDate)>[Forms].[frmRpts].[Text2]) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))<1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text2]) AND
((tblCustomer.OpenDate) Is Null));

Dear TT:

Help me see what's up with your sample data first, please:

CustomerID ReferralDateReceived OpenDate Months
Alternate
12 9/7/05
Oct, Nov, Dec Oct, Nov, Dec
13 9/7/05 9/8/05 -
none - - none -
14 9/8/05 10/1/05
- none -
15 9/8/05 10/15/05
- none -
16 9/8/05 11/3/05 Oct,
Nov Oct
17 9/8/05 10/15/05
- none -
18 10/2/05 12/03/05 Oct, Nov,
Dec Oct, Nov

I have added the months column above to show which months I would expect
each row to generate. But this would give Oct 6, Nov 3, and Dec 2, while
you show Oct 3, Nov 2, Dec 1. Is it the case that the range between
Referral and Open must span the last day of the month? Is that it? What
months would be included if the OpenDate were exactly on the last day of the
month?

I show this in the Alternate column, and it does give Oct 3, Nov 2, Dec1. I
guess this is it.

Now, for a new row:

19 9/9/05 10/31/05

Does this count as October or not. I'm testing the "boundary condition"
because that's often where misunderstandings occur.

I'm going to introduce a new table I expect to use:

MyQuarters:
Quarter MthEnd
"3/2005" 9/30/2005
"4/2005" 10/31/2005
"4/2005" 11/30/2005
"4/2005" 12/31/2005
"1/2006" 1/31/2006
etc.

I plan to use this in the next step. I'll respond when the questions are
answered so I have a chance to get it right the first time, and look good in
the process. : )

Tom Ellison

Thanks for your response Tom,

This is a part of a bigger picture. I have a report with 10 subreports
this
is one of those subreports.

My table is tblCustomer, the fields I will be messing with areCustomerID,
ReferralDateReceived, OpenDate, and DischargeDate.

On my form I have an option group that allows the user to select which
quarter they and also a combo box to select the year. Once they select
these
items it populates a hidden text box called Text1. When they press the
submit button it will run the report with all the subreports and build the
proper Titles for the months. I was able to get everything working fine
with
Allen's help this past weekend. However, they came back with a different
requirement. Now they want to display how many records were still pending
(no Opendate completed that month for that record) at the end of each
month.

I see some of your logic. I guess I will need another hidden text box to
have it populated with the last day of the month for the first month of
the
quarter. If and referrals were received before hand count those now look
at
the OpenDate count only those that have a null value or have a date after
this month.

However, do I need to build 3 different queries and have them dynamically
fill in an unbound subreport for each of the 3 months?


This is something I was thinking of for the first month:

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.OpenDate)<DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))>=1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate) Is Null));

the next month I would replace ("m",1...
with ("m",2....
But I don't think that will work either.


Sample Data

CustomerID ReferralDateReceived OpenDate
12 9/7/05
13 9/7/05 9/8/05
14 9/8/05 10/1/05
15 9/8/05 10/15/05
16 9/8/05 11/3/05
17 9/8/05 10/15/05
18 10/2/05 12/03/05

Should expect to see
Qtrs
Oct Nov Dec
3 2 1


Anyway any suggestion will be greatly apprecited. I would be nice to be
able to do this in a crosstab, but I don't see how. I would assume for
now
that they want to count the records that did not have an open date before
the
end of the month.

Dear TT:

I would think that the test would be that the ReceiveDate be on or before
the end of each of these months and that the CompleteDate would be null
or
on or after the first of that same month.

I could try to code and test this but, if my query is to be directly
usable
by you, I should use the exact table and column names you have. So, if
you
want that help, please do the following:

1. see if you agree with my first statement

2. provide the table and column names

3. Provide a bit of sample data and the results you expect it to produce

You will probably need a table that provides the months of each quarter
as
well. I'll cook up something for that to show you as well.

Tom Ellison


I have a table say table A with customer information. I need to count
each
pending record for each month where the ReceiveDate <= that month, but
the
CompleteDate is null or > that month, and have it broken down by for
each
month of a quarter.

In other words it should display something like:

Jan Feb Mar
12 5 8

Now I guess I need to use DateDiff from ReceiveDate and CompleteDate,
but
somehow provide the first day of the quarter. I have a form that will
provide the criteria for the query say Forms!frmRpts.Text1, which will
display the first day of the quarter.


I hope I am thinking through this process correctly.

Thanks for any response in this matter.

 

[Guest]

Tom, I hope you are still around.

Well I ended up doing 5 queries total. I hope there is an easier way.

One query for each month of the quarter, one Union query, then the crosstab.

Well this all works fine, but Like I said I hope there is an easier way.

Dear TT:

Help me see what's up with your sample data first, please:

CustomerID ReferralDateReceived OpenDate Months
Alternate
12 9/7/05
Oct, Nov, Dec Oct, Nov, Dec
13 9/7/05 9/8/05 -
none - - none -
14 9/8/05 10/1/05
- none -
15 9/8/05 10/15/05
- none -
16 9/8/05 11/3/05 Oct,
Nov Oct
17 9/8/05 10/15/05
- none -
18 10/2/05 12/03/05 Oct, Nov,
Dec Oct, Nov

I have added the months column above to show which months I would expect
each row to generate. But this would give Oct 6, Nov 3, and Dec 2, while
you show Oct 3, Nov 2, Dec 1. Is it the case that the range between
Referral and Open must span the last day of the month? Is that it? What
months would be included if the OpenDate were exactly on the last day of the
month?

I show this in the Alternate column, and it does give Oct 3, Nov 2, Dec1. I
guess this is it.

Now, for a new row:

19 9/9/05 10/31/05

Does this count as October or not. I'm testing the "boundary condition"
because that's often where misunderstandings occur.

I'm going to introduce a new table I expect to use:

MyQuarters:
Quarter MthEnd
"3/2005" 9/30/2005
"4/2005" 10/31/2005
"4/2005" 11/30/2005
"4/2005" 12/31/2005
"1/2006" 1/31/2006
etc.

I plan to use this in the next step. I'll respond when the questions are
answered so I have a chance to get it right the first time, and look good in
the process. : )

Tom Ellison

Thanks for your response Tom,

This is a part of a bigger picture. I have a report with 10 subreports
this
is one of those subreports.

My table is tblCustomer, the fields I will be messing with areCustomerID,
ReferralDateReceived, OpenDate, and DischargeDate.

On my form I have an option group that allows the user to select which
quarter they and also a combo box to select the year. Once they select
these
items it populates a hidden text box called Text1. When they press the
submit button it will run the report with all the subreports and build the
proper Titles for the months. I was able to get everything working fine
with
Allen's help this past weekend. However, they came back with a different
requirement. Now they want to display how many records were still pending
(no Opendate completed that month for that record) at the end of each
month.

I see some of your logic. I guess I will need another hidden text box to
have it populated with the last day of the month for the first month of
the
quarter. If and referrals were received before hand count those now look
at
the OpenDate count only those that have a null value or have a date after
this month.

However, do I need to build 3 different queries and have them dynamically
fill in an unbound subreport for each of the 3 months?


This is something I was thinking of for the first month:

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.OpenDate)<DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))>=1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate) Is Null));

the next month I would replace ("m",1...
with ("m",2....
But I don't think that will work either.


Sample Data

CustomerID ReferralDateReceived OpenDate
12 9/7/05
13 9/7/05 9/8/05
14 9/8/05 10/1/05
15 9/8/05 10/15/05
16 9/8/05 11/3/05
17 9/8/05 10/15/05
18 10/2/05 12/03/05

Should expect to see
Qtrs
Oct Nov Dec
3 2 1


Anyway any suggestion will be greatly apprecited. I would be nice to be
able to do this in a crosstab, but I don't see how. I would assume for
now
that they want to count the records that did not have an open date before
the
end of the month.

Dear TT:

I would think that the test would be that the ReceiveDate be on or before
the end of each of these months and that the CompleteDate would be null
or
on or after the first of that same month.

I could try to code and test this but, if my query is to be directly
usable
by you, I should use the exact table and column names you have. So, if
you
want that help, please do the following:

1. see if you agree with my first statement

2. provide the table and column names

3. Provide a bit of sample data and the results you expect it to produce

You will probably need a table that provides the months of each quarter
as
well. I'll cook up something for that to show you as well.

Tom Ellison


I have a table say table A with customer information. I need to count
each
pending record for each month where the ReceiveDate <= that month, but
the
CompleteDate is null or > that month, and have it broken down by for
each
month of a quarter.

In other words it should display something like:

Jan Feb Mar
12 5 8

Now I guess I need to use DateDiff from ReceiveDate and CompleteDate,
but
somehow provide the first day of the quarter. I have a form that will
provide the criteria for the query say Forms!frmRpts.Text1, which will
display the first day of the quarter.


I hope I am thinking through this process correctly.

Thanks for any response in this matter.

 

[Tom Ellison]

Dear TT:

So, the row with an OpenDate 12/03/05, which was Referred on 10/02/05,
doesn't count for October. If it were Referred 10/01/05, then would it? Or
would it have to be Referred before 10/01/05 to be counted?

This is a question about your definition of "before" some month.

I'll start work on a unified solution.

Tom Ellison

I am little confused of what you wrote or I guess the way it is displayed.
Either case let try to explain it a little better. Thye receive the
referral. However, sometimes they don't actually open the case until
sometimes later. So they need to show how many pending cases they have
during each month.

Let's count the ReferralReceiveDate on or before the end of the month and
any OpenDate that is null or after the last day of that month.

So by the example data that I provided if I was pulling the 2005 4th
quarter
data I would have the following:


Oct =3
1 for the null since it was received before Nov, but not opened
1 for 11/3/05 since it was received before Nov, but opened after Oct
12 for 12/03/05 since it was received before Nov, but opened after Oct

Nov=2
1 for null since it was received before Dec, but not opened
1 for 12/03/05 since it was received before Dec, but opened after Nov

Dec=1
1 for null since it was received before Jan, but not opened

I hope this makes better sense.

Dear TT:

Help me see what's up with your sample data first, please:

CustomerID ReferralDateReceived OpenDate Months
Alternate
12 9/7/05
Oct, Nov, Dec Oct, Nov, Dec
13 9/7/05 9/8/05 -
none - - none -
14 9/8/05 10/1/05
- none -
15 9/8/05 10/15/05
- none -
16 9/8/05 11/3/05 Oct,
Nov Oct
17 9/8/05 10/15/05
- none -
18 10/2/05 12/03/05 Oct,
Nov,
Dec Oct, Nov

I have added the months column above to show which months I would expect
each row to generate. But this would give Oct 6, Nov 3, and Dec 2, while
you show Oct 3, Nov 2, Dec 1. Is it the case that the range between
Referral and Open must span the last day of the month? Is that it? What
months would be included if the OpenDate were exactly on the last day of
the
month?

I show this in the Alternate column, and it does give Oct 3, Nov 2, Dec1.
I
guess this is it.

Now, for a new row:

19 9/9/05 10/31/05

Does this count as October or not. I'm testing the "boundary condition"
because that's often where misunderstandings occur.

I'm going to introduce a new table I expect to use:

MyQuarters:
Quarter MthEnd
"3/2005" 9/30/2005
"4/2005" 10/31/2005
"4/2005" 11/30/2005
"4/2005" 12/31/2005
"1/2006" 1/31/2006
etc.

I plan to use this in the next step. I'll respond when the questions are
answered so I have a chance to get it right the first time, and look good
in
the process. : )

Tom Ellison

Thanks for your response Tom,

This is a part of a bigger picture. I have a report with 10 subreports
this
is one of those subreports.

My table is tblCustomer, the fields I will be messing with
areCustomerID,
ReferralDateReceived, OpenDate, and DischargeDate.

On my form I have an option group that allows the user to select which
quarter they and also a combo box to select the year. Once they select
these
items it populates a hidden text box called Text1. When they press the
submit button it will run the report with all the subreports and build
the
proper Titles for the months. I was able to get everything working
fine
with
Allen's help this past weekend. However, they came back with a
different
requirement. Now they want to display how many records were still
pending
(no Opendate completed that month for that record) at the end of each
month.

I see some of your logic. I guess I will need another hidden text box
to
have it populated with the last day of the month for the first month of
the
quarter. If and referrals were received before hand count those now
look
at
the OpenDate count only those that have a null value or have a date
after
this month.

However, do I need to build 3 different queries and have them
dynamically
fill in an unbound subreport for each of the 3 months?


This is something I was thinking of for the first month:

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1]
And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.OpenDate)<DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))>=1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate) Is Null));

the next month I would replace ("m",1...
with ("m",2....
But I don't think that will work either.


Sample Data

CustomerID ReferralDateReceived OpenDate
12 9/7/05
13 9/7/05 9/8/05
14 9/8/05 10/1/05
15 9/8/05 10/15/05
16 9/8/05 11/3/05
17 9/8/05 10/15/05
18 10/2/05 12/03/05

Should expect to see
Qtrs
Oct Nov Dec
3 2 1


Anyway any suggestion will be greatly apprecited. I would be nice to
be
able to do this in a crosstab, but I don't see how. I would assume for
now
that they want to count the records that did not have an open date
before
the
end of the month.



:

Dear TT:

I would think that the test would be that the ReceiveDate be on or
before
the end of each of these months and that the CompleteDate would be
null
or
on or after the first of that same month.

I could try to code and test this but, if my query is to be directly
usable
by you, I should use the exact table and column names you have. So,
if
you
want that help, please do the following:

1. see if you agree with my first statement

2. provide the table and column names

3. Provide a bit of sample data and the results you expect it to
produce

You will probably need a table that provides the months of each
quarter
as
well. I'll cook up something for that to show you as well.

Tom Ellison


I have a table say table A with customer information. I need to
count
each
pending record for each month where the ReceiveDate <= that month,
but
the
CompleteDate is null or > that month, and have it broken down by for
each
month of a quarter.

In other words it should display something like:

Jan Feb Mar
12 5 8

Now I guess I need to use DateDiff from ReceiveDate and
CompleteDate,
but
somehow provide the first day of the quarter. I have a form that
will
provide the criteria for the query say Forms!frmRpts.Text1, which
will
display the first day of the quarter.


I hope I am thinking through this process correctly.

Thanks for any response in this matter.

 

[Guest]

No it would count for Oct and it would count for Nov as well since it did not
have an Opendate within or before that month.

It would have to be referred before 31 Oct and have and OpenDate after 31
Oct or still be null for it to count for Oct.


Thanks

Dear TT:

So, the row with an OpenDate 12/03/05, which was Referred on 10/02/05,
doesn't count for October. If it were Referred 10/01/05, then would it? Or
would it have to be Referred before 10/01/05 to be counted?

This is a question about your definition of "before" some month.

I'll start work on a unified solution.

Tom Ellison

I am little confused of what you wrote or I guess the way it is displayed.
Either case let try to explain it a little better. Thye receive the
referral. However, sometimes they don't actually open the case until
sometimes later. So they need to show how many pending cases they have
during each month.

Let's count the ReferralReceiveDate on or before the end of the month and
any OpenDate that is null or after the last day of that month.

So by the example data that I provided if I was pulling the 2005 4th
quarter
data I would have the following:


Oct =3
1 for the null since it was received before Nov, but not opened
1 for 11/3/05 since it was received before Nov, but opened after Oct
12 for 12/03/05 since it was received before Nov, but opened after Oct

Nov=2
1 for null since it was received before Dec, but not opened
1 for 12/03/05 since it was received before Dec, but opened after Nov

Dec=1
1 for null since it was received before Jan, but not opened

I hope this makes better sense.

Dear TT:

Help me see what's up with your sample data first, please:

CustomerID ReferralDateReceived OpenDate Months
Alternate
12 9/7/05
Oct, Nov, Dec Oct, Nov, Dec
13 9/7/05 9/8/05 -
none - - none -
14 9/8/05 10/1/05
- none -
15 9/8/05 10/15/05
- none -
16 9/8/05 11/3/05 Oct,
Nov Oct
17 9/8/05 10/15/05
- none -
18 10/2/05 12/03/05 Oct,
Nov,
Dec Oct, Nov

I have added the months column above to show which months I would expect
each row to generate. But this would give Oct 6, Nov 3, and Dec 2, while
you show Oct 3, Nov 2, Dec 1. Is it the case that the range between
Referral and Open must span the last day of the month? Is that it? What
months would be included if the OpenDate were exactly on the last day of
the
month?

I show this in the Alternate column, and it does give Oct 3, Nov 2, Dec1.
I
guess this is it.

Now, for a new row:

19 9/9/05 10/31/05

Does this count as October or not. I'm testing the "boundary condition"
because that's often where misunderstandings occur.

I'm going to introduce a new table I expect to use:

MyQuarters:
Quarter MthEnd
"3/2005" 9/30/2005
"4/2005" 10/31/2005
"4/2005" 11/30/2005
"4/2005" 12/31/2005
"1/2006" 1/31/2006
etc.

I plan to use this in the next step. I'll respond when the questions are
answered so I have a chance to get it right the first time, and look good
in
the process. : )

Tom Ellison


Thanks for your response Tom,

This is a part of a bigger picture. I have a report with 10 subreports
this
is one of those subreports.

My table is tblCustomer, the fields I will be messing with
areCustomerID,
ReferralDateReceived, OpenDate, and DischargeDate.

On my form I have an option group that allows the user to select which
quarter they and also a combo box to select the year. Once they select
these
items it populates a hidden text box called Text1. When they press the
submit button it will run the report with all the subreports and build
the
proper Titles for the months. I was able to get everything working
fine
with
Allen's help this past weekend. However, they came back with a
different
requirement. Now they want to display how many records were still
pending
(no Opendate completed that month for that record) at the end of each
month.

I see some of your logic. I guess I will need another hidden text box
to
have it populated with the last day of the month for the first month of
the
quarter. If and referrals were received before hand count those now
look
at
the OpenDate count only those that have a null value or have a date
after
this month.

However, do I need to build 3 different queries and have them
dynamically
fill in an unbound subreport for each of the 3 months?


This is something I was thinking of for the first month:

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1]
And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.OpenDate)<DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))>=1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate) Is Null));

the next month I would replace ("m",1...
with ("m",2....
But I don't think that will work either.


Sample Data

CustomerID ReferralDateReceived OpenDate
12 9/7/05
13 9/7/05 9/8/05
14 9/8/05 10/1/05
15 9/8/05 10/15/05
16 9/8/05 11/3/05
17 9/8/05 10/15/05
18 10/2/05 12/03/05

Should expect to see
Qtrs
Oct Nov Dec
3 2 1


Anyway any suggestion will be greatly apprecited. I would be nice to
be
able to do this in a crosstab, but I don't see how. I would assume for
now
that they want to count the records that did not have an open date
before
the
end of the month.



:

Dear TT:

I would think that the test would be that the ReceiveDate be on or
before
the end of each of these months and that the CompleteDate would be
null
or
on or after the first of that same month.

I could try to code and test this but, if my query is to be directly
usable
by you, I should use the exact table and column names you have. So,
if
you
want that help, please do the following:

1. see if you agree with my first statement

2. provide the table and column names

3. Provide a bit of sample data and the results you expect it to
produce

You will probably need a table that provides the months of each
quarter
as
well. I'll cook up something for that to show you as well.

Tom Ellison


I have a table say table A with customer information. I need to
count
each
pending record for each month where the ReceiveDate <= that month,
but
the
CompleteDate is null or > that month, and have it broken down by for
each
month of a quarter.

In other words it should display something like:

Jan Feb Mar
12 5 8

Now I guess I need to use DateDiff from ReceiveDate and
CompleteDate,
but
somehow provide the first day of the quarter. I have a form that
will
provide the criteria for the query say Forms!frmRpts.Text1, which
will
display the first day of the quarter.


I hope I am thinking through this process correctly.

Thanks for any response in this matter.

 

[Tom Ellison]

Dear TT:

My query is:

SELECT C.CustomerID, C.ReferralDateReceived, C.OpenDate,
Q.XYear, Q.XQtr, Q.XDate
FROM tblCustomer C, Qtr Q
WHERE C.ReferralDateReceived < DateSerial(Year(Q.XDate), Month(Q.XDate),
1)
AND Nz(C.OpenDate, #12/31/2999#) >= Q.XDate
AND Q.XYear = 2005 and Q.XQtr = 4
ORDER BY Q.XDate

I have created table Qtr:

Qtr
XYear XQtr XDate
2005 1 1/31/2005
2005 1 2/28/2005
2005 1 3/31/2005
2005 2 4/30/2005
2005 2 5/31/2005
2005 2 6/30/2005
2005 3 7/31/2005
2005 3 8/31/2005
2005 3 9/30/2005
2005 4 10/31/2005
2005 4 11/30/2005
2005 4 12/31/2005
2006 1 1/31/2006
2006 1 2/28/2006
2006 1 3/31/2006


The results are:

Q
CustomerID ReferralDateReceived OpenDate XYear XQtr XDate
16 9/8/2005 11/3/2005 2005 4 10/31/2005
12 9/7/2005
2005 4 10/31/2005
18 10/2/2005 12/3/2005 2005 4 11/30/2005
12 9/7/2005
2005 4 11/30/2005
12 9/7/2005
2005 4 12/31/2005


I did not have the October result for 18 as I believe you did. This started
on October 2, which should disqualify it according to what I have
understood.

The query could use some source for the year and quarter instead of the
filter I hard coded.

I believe this could be converted by a crosstab, or possibly even be
included right in a crosstab for a one step result.

Tom Ellison

I am little confused of what you wrote or I guess the way it is displayed.
Either case let try to explain it a little better. Thye receive the
referral. However, sometimes they don't actually open the case until
sometimes later. So they need to show how many pending cases they have
during each month.

Let's count the ReferralReceiveDate on or before the end of the month and
any OpenDate that is null or after the last day of that month.

So by the example data that I provided if I was pulling the 2005 4th
quarter
data I would have the following:


Oct =3
1 for the null since it was received before Nov, but not opened
1 for 11/3/05 since it was received before Nov, but opened after Oct
12 for 12/03/05 since it was received before Nov, but opened after Oct

Nov=2
1 for null since it was received before Dec, but not opened
1 for 12/03/05 since it was received before Dec, but opened after Nov

Dec=1
1 for null since it was received before Jan, but not opened

I hope this makes better sense.

Dear TT:

Help me see what's up with your sample data first, please:

CustomerID ReferralDateReceived OpenDate Months
Alternate
12 9/7/05
Oct, Nov, Dec Oct, Nov, Dec
13 9/7/05 9/8/05 -
none - - none -
14 9/8/05 10/1/05
- none -
15 9/8/05 10/15/05
- none -
16 9/8/05 11/3/05 Oct,
Nov Oct
17 9/8/05 10/15/05
- none -
18 10/2/05 12/03/05 Oct,
Nov,
Dec Oct, Nov

I have added the months column above to show which months I would expect
each row to generate. But this would give Oct 6, Nov 3, and Dec 2, while
you show Oct 3, Nov 2, Dec 1. Is it the case that the range between
Referral and Open must span the last day of the month? Is that it? What
months would be included if the OpenDate were exactly on the last day of
the
month?

I show this in the Alternate column, and it does give Oct 3, Nov 2, Dec1.
I
guess this is it.

Now, for a new row:

19 9/9/05 10/31/05

Does this count as October or not. I'm testing the "boundary condition"
because that's often where misunderstandings occur.

I'm going to introduce a new table I expect to use:

MyQuarters:
Quarter MthEnd
"3/2005" 9/30/2005
"4/2005" 10/31/2005
"4/2005" 11/30/2005
"4/2005" 12/31/2005
"1/2006" 1/31/2006
etc.

I plan to use this in the next step. I'll respond when the questions are
answered so I have a chance to get it right the first time, and look good
in
the process. : )

Tom Ellison

Thanks for your response Tom,

This is a part of a bigger picture. I have a report with 10 subreports
this
is one of those subreports.

My table is tblCustomer, the fields I will be messing with
areCustomerID,
ReferralDateReceived, OpenDate, and DischargeDate.

On my form I have an option group that allows the user to select which
quarter they and also a combo box to select the year. Once they select
these
items it populates a hidden text box called Text1. When they press the
submit button it will run the report with all the subreports and build
the
proper Titles for the months. I was able to get everything working
fine
with
Allen's help this past weekend. However, they came back with a
different
requirement. Now they want to display how many records were still
pending
(no Opendate completed that month for that record) at the end of each
month.

I see some of your logic. I guess I will need another hidden text box
to
have it populated with the last day of the month for the first month of
the
quarter. If and referrals were received before hand count those now
look
at
the OpenDate count only those that have a null value or have a date
after
this month.

However, do I need to build 3 different queries and have them
dynamically
fill in an unbound subreport for each of the 3 months?


This is something I was thinking of for the first month:

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1]
And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.OpenDate)<DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))>=1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate) Is Null));

the next month I would replace ("m",1...
with ("m",2....
But I don't think that will work either.


Sample Data

CustomerID ReferralDateReceived OpenDate
12 9/7/05
13 9/7/05 9/8/05
14 9/8/05 10/1/05
15 9/8/05 10/15/05
16 9/8/05 11/3/05
17 9/8/05 10/15/05
18 10/2/05 12/03/05

Should expect to see
Qtrs
Oct Nov Dec
3 2 1


Anyway any suggestion will be greatly apprecited. I would be nice to
be
able to do this in a crosstab, but I don't see how. I would assume for
now
that they want to count the records that did not have an open date
before
the
end of the month.



:

Dear TT:

I would think that the test would be that the ReceiveDate be on or
before
the end of each of these months and that the CompleteDate would be
null
or
on or after the first of that same month.

I could try to code and test this but, if my query is to be directly
usable
by you, I should use the exact table and column names you have. So,
if
you
want that help, please do the following:

1. see if you agree with my first statement

2. provide the table and column names

3. Provide a bit of sample data and the results you expect it to
produce

You will probably need a table that provides the months of each
quarter
as
well. I'll cook up something for that to show you as well.

Tom Ellison


I have a table say table A with customer information. I need to
count
each
pending record for each month where the ReceiveDate <= that month,
but
the
CompleteDate is null or > that month, and have it broken down by for
each
month of a quarter.

In other words it should display something like:

Jan Feb Mar
12 5 8

Now I guess I need to use DateDiff from ReceiveDate and
CompleteDate,
but
somehow provide the first day of the quarter. I have a form that
will
provide the criteria for the query say Forms!frmRpts.Text1, which
will
display the first day of the quarter.


I hope I am thinking through this process correctly.

Thanks for any response in this matter.

 

[Tom Ellison]

Dear TT:

I modified the query to:

SELECT C.CustomerID, C.ReferralDateReceived, C.OpenDate,
Q.XYear, Q.XQtr, Q.XDate
FROM tblCustomer C, Qtr Q
WHERE C.ReferralDateReceived <= Q.XDate
AND Nz(C.OpenDate, #12/31/2999#) >= Q.XDate
AND Q.XYear = 2005 and Q.XQtr = 4
ORDER BY Q.XDate

Based on more recent information you gave.

The results are now:

Q1
CustomerID ReferralDateReceived OpenDate XYear XQtr XDate
18 10/2/2005 12/3/2005 2005 4 10/31/2005
16 9/8/2005 11/3/2005 2005 4 10/31/2005
12 9/7/2005
2005 4 10/31/2005
18 10/2/2005 12/3/2005 2005 4 11/30/2005
12 9/7/2005
2005 4 11/30/2005
12 9/7/2005
2005 4 12/31/2005


Tom Ellison

Tom, I hope you are still around.

Well I ended up doing 5 queries total. I hope there is an easier way.

One query for each month of the quarter, one Union query, then the
crosstab.

Well this all works fine, but Like I said I hope there is an easier way.

Dear TT:

Help me see what's up with your sample data first, please:

CustomerID ReferralDateReceived OpenDate Months
Alternate
12 9/7/05
Oct, Nov, Dec Oct, Nov, Dec
13 9/7/05 9/8/05 -
none - - none -
14 9/8/05 10/1/05
- none -
15 9/8/05 10/15/05
- none -
16 9/8/05 11/3/05 Oct,
Nov Oct
17 9/8/05 10/15/05
- none -
18 10/2/05 12/03/05 Oct,
Nov,
Dec Oct, Nov

I have added the months column above to show which months I would expect
each row to generate. But this would give Oct 6, Nov 3, and Dec 2, while
you show Oct 3, Nov 2, Dec 1. Is it the case that the range between
Referral and Open must span the last day of the month? Is that it? What
months would be included if the OpenDate were exactly on the last day of
the
month?

I show this in the Alternate column, and it does give Oct 3, Nov 2, Dec1.
I
guess this is it.

Now, for a new row:

19 9/9/05 10/31/05

Does this count as October or not. I'm testing the "boundary condition"
because that's often where misunderstandings occur.

I'm going to introduce a new table I expect to use:

MyQuarters:
Quarter MthEnd
"3/2005" 9/30/2005
"4/2005" 10/31/2005
"4/2005" 11/30/2005
"4/2005" 12/31/2005
"1/2006" 1/31/2006
etc.

I plan to use this in the next step. I'll respond when the questions are
answered so I have a chance to get it right the first time, and look good
in
the process. : )

Tom Ellison

Thanks for your response Tom,

This is a part of a bigger picture. I have a report with 10 subreports
this
is one of those subreports.

My table is tblCustomer, the fields I will be messing with
areCustomerID,
ReferralDateReceived, OpenDate, and DischargeDate.

On my form I have an option group that allows the user to select which
quarter they and also a combo box to select the year. Once they select
these
items it populates a hidden text box called Text1. When they press the
submit button it will run the report with all the subreports and build
the
proper Titles for the months. I was able to get everything working
fine
with
Allen's help this past weekend. However, they came back with a
different
requirement. Now they want to display how many records were still
pending
(no Opendate completed that month for that record) at the end of each
month.

I see some of your logic. I guess I will need another hidden text box
to
have it populated with the last day of the month for the first month of
the
quarter. If and referrals were received before hand count those now
look
at
the OpenDate count only those that have a null value or have a date
after
this month.

However, do I need to build 3 different queries and have them
dynamically
fill in an unbound subreport for each of the 3 months?


This is something I was thinking of for the first month:

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1]
And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.OpenDate)<DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))>=1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate) Is Null));

the next month I would replace ("m",1...
with ("m",2....
But I don't think that will work either.


Sample Data

CustomerID ReferralDateReceived OpenDate
12 9/7/05
13 9/7/05 9/8/05
14 9/8/05 10/1/05
15 9/8/05 10/15/05
16 9/8/05 11/3/05
17 9/8/05 10/15/05
18 10/2/05 12/03/05

Should expect to see
Qtrs
Oct Nov Dec
3 2 1


Anyway any suggestion will be greatly apprecited. I would be nice to
be
able to do this in a crosstab, but I don't see how. I would assume for
now
that they want to count the records that did not have an open date
before
the
end of the month.



:

Dear TT:

I would think that the test would be that the ReceiveDate be on or
before
the end of each of these months and that the CompleteDate would be
null
or
on or after the first of that same month.

I could try to code and test this but, if my query is to be directly
usable
by you, I should use the exact table and column names you have. So,
if
you
want that help, please do the following:

1. see if you agree with my first statement

2. provide the table and column names

3. Provide a bit of sample data and the results you expect it to
produce

You will probably need a table that provides the months of each
quarter
as
well. I'll cook up something for that to show you as well.

Tom Ellison


I have a table say table A with customer information. I need to
count
each
pending record for each month where the ReceiveDate <= that month,
but
the
CompleteDate is null or > that month, and have it broken down by for
each
month of a quarter.

In other words it should display something like:

Jan Feb Mar
12 5 8

Now I guess I need to use DateDiff from ReceiveDate and
CompleteDate,
but
somehow provide the first day of the quarter. I have a form that
will
provide the criteria for the query say Forms!frmRpts.Text1, which
will
display the first day of the quarter.


I hope I am thinking through this process correctly.

Thanks for any response in this matter.

 

[Guest]

Sorry Tom, but I am lost what is Qtr a table, a query or something? If it's
another table why did we need to create it?

I am not following

Here are my 5 queries. It seems to give me what is needed, but hoped there
was an easier way.

Month1
SELECT tblCustomer.CustomerID, "0" AS MonthsOfQuarter,
tblCustomer.ReferralDateReceived, tblCustomer.OpenDate
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text2]) AND
((tblCustomer.OpenDate)>[Forms].[frmRpts].[Text2])) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text2]) AND
((tblCustomer.OpenDate) Is Null));

Month2
SELECT tblCustomer.CustomerID, "1" AS MonthsOfQuarter,
tblCustomer.ReferralDateReceived, tblCustomer.OpenDate
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text2] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",2,[Forms].[frmRpts].[Text2]))
AND ((tblCustomer.OpenDate)>[Forms].[frmRpts].[Text2] And
(tblCustomer.OpenDate)<DateAdd("m",2,[Forms].[frmRpts].[Text2]))) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text2] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",2,[Forms].[frmRpts].[Text2]))
AND ((tblCustomer.OpenDate) Is Null));

Month3
SELECT tblCustomer.CustomerID, "2" AS MonthOfQuarter,
tblCustomer.ReferralDateReceived, tblCustomer.OpenDate
FROM tblCustomer
WHERE
(((tblCustomer.ReferralDateReceived)<=DateAdd("m",3,[Forms].[frmRpts].[Text2]))
AND ((tblCustomer.OpenDate)>DateAdd("m",3,[Forms].[frmRpts].[Text2]))) OR
(((tblCustomer.ReferralDateReceived)<=DateAdd("m",3,[Forms].[frmRpts].[Text2])) AND ((tblCustomer.OpenDate) Is Null));

Union query
SELECT* from qryPendingMonth1
Union
Select * from qryPendingMonth2
UNION Select * from qryPendingMonth3;

Crosstab
TRANSFORM Count(qryPendingUnion.CustomerID) AS CountOfCustomerID
SELECT "Total Cases Pending in Month" AS Expr1 FROM qryPendingUnion
GROUP BY "Total Cases Pending in Month"
PIVOT qryPendingUnion.MonthsOfQuarter In (0,1,2);

Dear TT:

I modified the query to:

SELECT C.CustomerID, C.ReferralDateReceived, C.OpenDate,
Q.XYear, Q.XQtr, Q.XDate
FROM tblCustomer C, Qtr Q
WHERE C.ReferralDateReceived <= Q.XDate
AND Nz(C.OpenDate, #12/31/2999#) >= Q.XDate
AND Q.XYear = 2005 and Q.XQtr = 4
ORDER BY Q.XDate

Based on more recent information you gave.

The results are now:

Q1
CustomerID ReferralDateReceived OpenDate XYear XQtr XDate
18 10/2/2005 12/3/2005 2005 4 10/31/2005
16 9/8/2005 11/3/2005 2005 4 10/31/2005
12 9/7/2005
2005 4 10/31/2005
18 10/2/2005 12/3/2005 2005 4 11/30/2005
12 9/7/2005
2005 4 11/30/2005
12 9/7/2005
2005 4 12/31/2005


Tom Ellison

Tom, I hope you are still around.

Well I ended up doing 5 queries total. I hope there is an easier way.

One query for each month of the quarter, one Union query, then the
crosstab.

Well this all works fine, but Like I said I hope there is an easier way.

Dear TT:

Help me see what's up with your sample data first, please:

CustomerID ReferralDateReceived OpenDate Months
Alternate
12 9/7/05
Oct, Nov, Dec Oct, Nov, Dec
13 9/7/05 9/8/05 -
none - - none -
14 9/8/05 10/1/05
- none -
15 9/8/05 10/15/05
- none -
16 9/8/05 11/3/05 Oct,
Nov Oct
17 9/8/05 10/15/05
- none -
18 10/2/05 12/03/05 Oct,
Nov,
Dec Oct, Nov

I have added the months column above to show which months I would expect
each row to generate. But this would give Oct 6, Nov 3, and Dec 2, while
you show Oct 3, Nov 2, Dec 1. Is it the case that the range between
Referral and Open must span the last day of the month? Is that it? What
months would be included if the OpenDate were exactly on the last day of
the
month?

I show this in the Alternate column, and it does give Oct 3, Nov 2, Dec1.
I
guess this is it.

Now, for a new row:

19 9/9/05 10/31/05

Does this count as October or not. I'm testing the "boundary condition"
because that's often where misunderstandings occur.

I'm going to introduce a new table I expect to use:

MyQuarters:
Quarter MthEnd
"3/2005" 9/30/2005
"4/2005" 10/31/2005
"4/2005" 11/30/2005
"4/2005" 12/31/2005
"1/2006" 1/31/2006
etc.

I plan to use this in the next step. I'll respond when the questions are
answered so I have a chance to get it right the first time, and look good
in
the process. : )

Tom Ellison


Thanks for your response Tom,

This is a part of a bigger picture. I have a report with 10 subreports
this
is one of those subreports.

My table is tblCustomer, the fields I will be messing with
areCustomerID,
ReferralDateReceived, OpenDate, and DischargeDate.

On my form I have an option group that allows the user to select which
quarter they and also a combo box to select the year. Once they select
these
items it populates a hidden text box called Text1. When they press the
submit button it will run the report with all the subreports and build
the
proper Titles for the months. I was able to get everything working
fine
with
Allen's help this past weekend. However, they came back with a
different
requirement. Now they want to display how many records were still
pending
(no Opendate completed that month for that record) at the end of each
month.

I see some of your logic. I guess I will need another hidden text box
to
have it populated with the last day of the month for the first month of
the
quarter. If and referrals were received before hand count those now
look
at
the OpenDate count only those that have a null value or have a date
after
this month.

However, do I need to build 3 different queries and have them
dynamically
fill in an unbound subreport for each of the 3 months?


This is something I was thinking of for the first month:

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1]
And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.OpenDate)<DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))>=1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate) Is Null));

the next month I would replace ("m",1...
with ("m",2....
But I don't think that will work either.


Sample Data

CustomerID ReferralDateReceived OpenDate
12 9/7/05
13 9/7/05 9/8/05
14 9/8/05 10/1/05
15 9/8/05 10/15/05
16 9/8/05 11/3/05
17 9/8/05 10/15/05
18 10/2/05 12/03/05

Should expect to see
Qtrs
Oct Nov Dec
3 2 1


Anyway any suggestion will be greatly apprecited. I would be nice to
be
able to do this in a crosstab, but I don't see how. I would assume for
now
that they want to count the records that did not have an open date
before
the
end of the month.



:

Dear TT:

I would think that the test would be that the ReceiveDate be on or
before
the end of each of these months and that the CompleteDate would be
null
or
on or after the first of that same month.

I could try to code and test this but, if my query is to be directly
usable
by you, I should use the exact table and column names you have. So,
if
you
want that help, please do the following:

1. see if you agree with my first statement

2. provide the table and column names

3. Provide a bit of sample data and the results you expect it to
produce

You will probably need a table that provides the months of each
quarter
as
well. I'll cook up something for that to show you as well.

Tom Ellison


I have a table say table A with customer information. I need to
count
each
pending record for each month where the ReceiveDate <= that month,
but
the
CompleteDate is null or > that month, and have it broken down by for
each
month of a quarter.

In other words it should display something like:

Jan Feb Mar
12 5 8

Now I guess I need to use DateDiff from ReceiveDate and
CompleteDate,
but
somehow provide the first day of the quarter. I have a form that
will
provide the criteria for the query say Forms!frmRpts.Text1, which
will
display the first day of the quarter.


I hope I am thinking through this process correctly.

Thanks for any response in this matter.

 

[Guest]

Actually relooking at this I had to change my 3 queries. I used text1 on the
form. When ever I select a quarter and year it will update the text box with
the 1st day of that quarter.
So the queries will be as follows:

Month1
SELECT tblCustomer.CustomerID, "0" AS MonthsOfQuarter,
tblCustomer.ReferralDateReceived, tblCustomer.OpenDate
FROM tblCustomer
WHERE
(((tblCustomer.ReferralDateReceived)<=DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>DateAdd("m",1,[Forms].[frmRpts].[Text1]))) OR
(((tblCustomer.ReferralDateReceived)<=DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND ((tblCustomer.OpenDate) Is Null));

Month2
SELECT tblCustomer.CustomerID, "1" AS MonthsOfQuarter,
tblCustomer.ReferralDateReceived, tblCustomer.OpenDate
FROM tblCustomer
WHERE
(((tblCustomer.ReferralDateReceived)<=DateAdd("m",2,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>DateAdd("m",2,[Forms].[frmRpts].[Text1]))) OR
(((tblCustomer.ReferralDateReceived)<=DateAdd("m",2,[Forms].[frmRpts].[Text1])) AND ((tblCustomer.OpenDate) Is Null));

Month3
SELECT tblCustomer.CustomerID, "2" AS MonthsOfQuarter,
tblCustomer.ReferralDateReceived, tblCustomer.OpenDate
FROM tblCustomer
WHERE
(((tblCustomer.ReferralDateReceived)<=DateAdd("m",3,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>DateAdd("m",3,[Forms].[frmRpts].[Text1]))) OR
(((tblCustomer.ReferralDateReceived)<=DateAdd("m",3,[Forms].[frmRpts].[Text1])) AND ((tblCustomer.OpenDate) Is Null));

Dear TT:

I modified the query to:

SELECT C.CustomerID, C.ReferralDateReceived, C.OpenDate,
Q.XYear, Q.XQtr, Q.XDate
FROM tblCustomer C, Qtr Q
WHERE C.ReferralDateReceived <= Q.XDate
AND Nz(C.OpenDate, #12/31/2999#) >= Q.XDate
AND Q.XYear = 2005 and Q.XQtr = 4
ORDER BY Q.XDate

Based on more recent information you gave.

The results are now:

Q1
CustomerID ReferralDateReceived OpenDate XYear XQtr XDate
18 10/2/2005 12/3/2005 2005 4 10/31/2005
16 9/8/2005 11/3/2005 2005 4 10/31/2005
12 9/7/2005
2005 4 10/31/2005
18 10/2/2005 12/3/2005 2005 4 11/30/2005
12 9/7/2005
2005 4 11/30/2005
12 9/7/2005
2005 4 12/31/2005


Tom Ellison

Tom, I hope you are still around.

Well I ended up doing 5 queries total. I hope there is an easier way.

One query for each month of the quarter, one Union query, then the
crosstab.

Well this all works fine, but Like I said I hope there is an easier way.

Dear TT:

Help me see what's up with your sample data first, please:

CustomerID ReferralDateReceived OpenDate Months
Alternate
12 9/7/05
Oct, Nov, Dec Oct, Nov, Dec
13 9/7/05 9/8/05 -
none - - none -
14 9/8/05 10/1/05
- none -
15 9/8/05 10/15/05
- none -
16 9/8/05 11/3/05 Oct,
Nov Oct
17 9/8/05 10/15/05
- none -
18 10/2/05 12/03/05 Oct,
Nov,
Dec Oct, Nov

I have added the months column above to show which months I would expect
each row to generate. But this would give Oct 6, Nov 3, and Dec 2, while
you show Oct 3, Nov 2, Dec 1. Is it the case that the range between
Referral and Open must span the last day of the month? Is that it? What
months would be included if the OpenDate were exactly on the last day of
the
month?

I show this in the Alternate column, and it does give Oct 3, Nov 2, Dec1.
I
guess this is it.

Now, for a new row:

19 9/9/05 10/31/05

Does this count as October or not. I'm testing the "boundary condition"
because that's often where misunderstandings occur.

I'm going to introduce a new table I expect to use:

MyQuarters:
Quarter MthEnd
"3/2005" 9/30/2005
"4/2005" 10/31/2005
"4/2005" 11/30/2005
"4/2005" 12/31/2005
"1/2006" 1/31/2006
etc.

I plan to use this in the next step. I'll respond when the questions are
answered so I have a chance to get it right the first time, and look good
in
the process. : )

Tom Ellison


Thanks for your response Tom,

This is a part of a bigger picture. I have a report with 10 subreports
this
is one of those subreports.

My table is tblCustomer, the fields I will be messing with
areCustomerID,
ReferralDateReceived, OpenDate, and DischargeDate.

On my form I have an option group that allows the user to select which
quarter they and also a combo box to select the year. Once they select
these
items it populates a hidden text box called Text1. When they press the
submit button it will run the report with all the subreports and build
the
proper Titles for the months. I was able to get everything working
fine
with
Allen's help this past weekend. However, they came back with a
different
requirement. Now they want to display how many records were still
pending
(no Opendate completed that month for that record) at the end of each
month.

I see some of your logic. I guess I will need another hidden text box
to
have it populated with the last day of the month for the first month of
the
quarter. If and referrals were received before hand count those now
look
at
the OpenDate count only those that have a null value or have a date
after
this month.

However, do I need to build 3 different queries and have them
dynamically
fill in an unbound subreport for each of the 3 months?


This is something I was thinking of for the first month:

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1]
And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.OpenDate)<DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))>=1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate) Is Null));

the next month I would replace ("m",1...
with ("m",2....
But I don't think that will work either.


Sample Data

CustomerID ReferralDateReceived OpenDate
12 9/7/05
13 9/7/05 9/8/05
14 9/8/05 10/1/05
15 9/8/05 10/15/05
16 9/8/05 11/3/05
17 9/8/05 10/15/05
18 10/2/05 12/03/05

Should expect to see
Qtrs
Oct Nov Dec
3 2 1


Anyway any suggestion will be greatly apprecited. I would be nice to
be
able to do this in a crosstab, but I don't see how. I would assume for
now
that they want to count the records that did not have an open date
before
the
end of the month.



:

Dear TT:

I would think that the test would be that the ReceiveDate be on or
before
the end of each of these months and that the CompleteDate would be
null
or
on or after the first of that same month.

I could try to code and test this but, if my query is to be directly
usable
by you, I should use the exact table and column names you have. So,
if
you
want that help, please do the following:

1. see if you agree with my first statement

2. provide the table and column names

3. Provide a bit of sample data and the results you expect it to
produce

You will probably need a table that provides the months of each
quarter
as
well. I'll cook up something for that to show you as well.

Tom Ellison


I have a table say table A with customer information. I need to
count
each
pending record for each month where the ReceiveDate <= that month,
but
the
CompleteDate is null or > that month, and have it broken down by for
each
month of a quarter.

In other words it should display something like:

Jan Feb Mar
12 5 8

Now I guess I need to use DateDiff from ReceiveDate and
CompleteDate,
but
somehow provide the first day of the quarter. I have a form that
will
provide the criteria for the query say Forms!frmRpts.Text1, which
will
display the first day of the quarter.


I hope I am thinking through this process correctly.

Thanks for any response in this matter.

 

[Tom Ellison]

Dear TT:

It is a table. It is necessary for the solution I posted. I gave details
of it in an earlier post.

Tom Ellison

Sorry Tom, but I am lost what is Qtr a table, a query or something? If
it's
another table why did we need to create it?

I am not following

Here are my 5 queries. It seems to give me what is needed, but hoped
there
was an easier way.

Month1
SELECT tblCustomer.CustomerID, "0" AS MonthsOfQuarter,
tblCustomer.ReferralDateReceived, tblCustomer.OpenDate
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text2]) AND
((tblCustomer.OpenDate)>[Forms].[frmRpts].[Text2])) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text2]) AND
((tblCustomer.OpenDate) Is Null));

Month2
SELECT tblCustomer.CustomerID, "1" AS MonthsOfQuarter,
tblCustomer.ReferralDateReceived, tblCustomer.OpenDate
FROM tblCustomer
WHERE (((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text2] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",2,[Forms].[frmRpts].[Text2]))
AND ((tblCustomer.OpenDate)>[Forms].[frmRpts].[Text2] And
(tblCustomer.OpenDate)<DateAdd("m",2,[Forms].[frmRpts].[Text2]))) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text2] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",2,[Forms].[frmRpts].[Text2]))
AND ((tblCustomer.OpenDate) Is Null));

Month3
SELECT tblCustomer.CustomerID, "2" AS MonthOfQuarter,
tblCustomer.ReferralDateReceived, tblCustomer.OpenDate
FROM tblCustomer
WHERE
(((tblCustomer.ReferralDateReceived)<=DateAdd("m",3,[Forms].[frmRpts].[Text2]))
AND ((tblCustomer.OpenDate)>DateAdd("m",3,[Forms].[frmRpts].[Text2]))) OR
(((tblCustomer.ReferralDateReceived)<=DateAdd("m",3,[Forms].[frmRpts].[Text2]))
AND ((tblCustomer.OpenDate) Is Null));

Union query
SELECT* from qryPendingMonth1
Union
Select * from qryPendingMonth2
UNION Select * from qryPendingMonth3;

Crosstab
TRANSFORM Count(qryPendingUnion.CustomerID) AS CountOfCustomerID
SELECT "Total Cases Pending in Month" AS Expr1 FROM qryPendingUnion
GROUP BY "Total Cases Pending in Month"
PIVOT qryPendingUnion.MonthsOfQuarter In (0,1,2);

Dear TT:

I modified the query to:

SELECT C.CustomerID, C.ReferralDateReceived, C.OpenDate,
Q.XYear, Q.XQtr, Q.XDate
FROM tblCustomer C, Qtr Q
WHERE C.ReferralDateReceived <= Q.XDate
AND Nz(C.OpenDate, #12/31/2999#) >= Q.XDate
AND Q.XYear = 2005 and Q.XQtr = 4
ORDER BY Q.XDate

Based on more recent information you gave.

The results are now:

Q1
CustomerID ReferralDateReceived OpenDate XYear XQtr XDate
18 10/2/2005 12/3/2005 2005 4 10/31/2005
16 9/8/2005 11/3/2005 2005 4 10/31/2005
12 9/7/2005
2005 4 10/31/2005
18 10/2/2005 12/3/2005 2005 4 11/30/2005
12 9/7/2005
2005 4 11/30/2005
12 9/7/2005
2005 4 12/31/2005


Tom Ellison

Tom, I hope you are still around.

Well I ended up doing 5 queries total. I hope there is an easier way.

One query for each month of the quarter, one Union query, then the
crosstab.

Well this all works fine, but Like I said I hope there is an easier
way.


:

Dear TT:

Help me see what's up with your sample data first, please:

CustomerID ReferralDateReceived OpenDate Months
Alternate
12 9/7/05
Oct, Nov, Dec Oct, Nov, Dec
13 9/7/05
9/8/05 -
none - - none -
14 9/8/05 10/1/05
- none -
15 9/8/05 10/15/05
- none -
16 9/8/05 11/3/05
Oct,
Nov Oct
17 9/8/05 10/15/05
- none -
18 10/2/05 12/03/05
Oct,
Nov,
Dec Oct, Nov

I have added the months column above to show which months I would
expect
each row to generate. But this would give Oct 6, Nov 3, and Dec 2,
while
you show Oct 3, Nov 2, Dec 1. Is it the case that the range between
Referral and Open must span the last day of the month? Is that it?
What
months would be included if the OpenDate were exactly on the last day
of
the
month?

I show this in the Alternate column, and it does give Oct 3, Nov 2,
Dec1.
I
guess this is it.

Now, for a new row:

19 9/9/05 10/31/05

Does this count as October or not. I'm testing the "boundary
condition"
because that's often where misunderstandings occur.

I'm going to introduce a new table I expect to use:

MyQuarters:
Quarter MthEnd
"3/2005" 9/30/2005
"4/2005" 10/31/2005
"4/2005" 11/30/2005
"4/2005" 12/31/2005
"1/2006" 1/31/2006
etc.

I plan to use this in the next step. I'll respond when the questions
are
answered so I have a chance to get it right the first time, and look
good
in
the process. : )

Tom Ellison


Thanks for your response Tom,

This is a part of a bigger picture. I have a report with 10
subreports
this
is one of those subreports.

My table is tblCustomer, the fields I will be messing with
areCustomerID,
ReferralDateReceived, OpenDate, and DischargeDate.

On my form I have an option group that allows the user to select
which
quarter they and also a combo box to select the year. Once they
select
these
items it populates a hidden text box called Text1. When they press
the
submit button it will run the report with all the subreports and
build
the
proper Titles for the months. I was able to get everything working
fine
with
Allen's help this past weekend. However, they came back with a
different
requirement. Now they want to display how many records were still
pending
(no Opendate completed that month for that record) at the end of
each
month.

I see some of your logic. I guess I will need another hidden text
box
to
have it populated with the last day of the month for the first month
of
the
quarter. If and referrals were received before hand count those now
look
at
the OpenDate count only those that have a null value or have a date
after
this month.

However, do I need to build 3 different queries and have them
dynamically
fill in an unbound subreport for each of the 3 months?


This is something I was thinking of for the first month:

SELECT tblCustomer.ReferralDateReceived, tblCustomer.OpenDate,
DateDiff("m",[ReferralDateReceived],[OpenDate]) AS Months
FROM tblCustomer
WHERE
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1]
And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate)>=[Forms].[frmRpts].[Text1] And
(tblCustomer.OpenDate)<DateAdd("m",1,[Forms].[frmRpts].[Text1])) AND
((DateDiff("m",[ReferralDateReceived],[OpenDate]))>=1)) OR
(((tblCustomer.ReferralDateReceived)<=[Forms].[frmRpts].[Text1] And
(tblCustomer.ReferralDateReceived)<DateAdd("m",1,[Forms].[frmRpts].[Text1]))
AND ((tblCustomer.OpenDate) Is Null));

the next month I would replace ("m",1...
with ("m",2....
But I don't think that will work either.


Sample Data

CustomerID ReferralDateReceived OpenDate
12 9/7/05
13 9/7/05 9/8/05
14 9/8/05 10/1/05
15 9/8/05 10/15/05
16 9/8/05 11/3/05
17 9/8/05 10/15/05
18 10/2/05 12/03/05

Should expect to see
Qtrs
Oct Nov Dec
3 2 1


Anyway any suggestion will be greatly apprecited. I would be nice
to
be
able to do this in a crosstab, but I don't see how. I would assume
for
now
that they want to count the records that did not have an open date
before
the
end of the month.



:

Dear TT:

I would think that the test would be that the ReceiveDate be on or
before
the end of each of these months and that the CompleteDate would be
null
or
on or after the first of that same month.

I could try to code and test this but, if my query is to be
directly
usable
by you, I should use the exact table and column names you have.
So,
if
you
want that help, please do the following:

1. see if you agree with my first statement

2. provide the table and column names

3. Provide a bit of sample data and the results you expect it to
produce

You will probably need a table that provides the months of each
quarter
as
well. I'll cook up something for that to show you as well.

Tom Ellison


I have a table say table A with customer information. I need to
count
each
pending record for each month where the ReceiveDate <= that
month,
but
the
CompleteDate is null or > that month, and have it broken down by
for
each
month of a quarter.

In other words it should display something like:

Jan Feb Mar
12 5 8

Now I guess I need to use DateDiff from ReceiveDate and
CompleteDate,
but
somehow provide the first day of the quarter. I have a form
that
will
provide the criteria for the query say Forms!frmRpts.Text1, which
will
display the first day of the quarter.


I hope I am thinking through this process correctly.

Thanks for any response in this matter.