Report with dates and dollars

  • Thread starter Thread starter Dennis M
  • Start date Start date
D

Dennis M

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
 
Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?
 
LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

S.Clark said:
Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

Dennis M said:
I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
 
Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


Dennis M said:
LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

S.Clark said:
Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

Dennis M said:
I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






KARL DEWEY said:
Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


Dennis M said:
LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

S.Clark said:
Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
Post sample data and example of your desired results.
--
Build a little, test a little.


Dennis M said:
Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






KARL DEWEY said:
Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


Dennis M said:
LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

:

Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
Karl,

how do I get you a sample of the data.

The report is to be three columns

Legal name monthly count year to date count

Dismissed 10 15
Filed 20 50
Revocation 4 6
Pending
Censure
Suspended
Probation
Civil Penalty $100 $250
Refunds $5000 $2500

That is the desired report look.

The table hendings are

ID
LicenseNum
LegalStatus
LegalRefund
Licensee
ExamDate
DateSent
DateofDelivery
ResponseDueDate
ResponseReceived
REFUNDS - not used for this report
NoticeofHearing - date (Date used for LegalDisp)
Status
Notes
Reminders
CivilPenalty - currency (for dollars)
Corrected
LegalDate
LegalDisp - text (the type)
DismissedDate - date (this is for refunds and civil penalty date)

Hope this is usable Karl

Dennis

KARL DEWEY said:
Post sample data and example of your desired results.
--
Build a little, test a little.


Dennis M said:
Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






KARL DEWEY said:
Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


:

LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

:

Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
Just run select query, a dozen or so records, with the following fields --

- NoticeofHearing - date (Date used for LegalDisp)
- CivilPenalty - currency (for dollars)
- LegalDisp - text (the type)
- DismissedDate - date (this is for refunds and civil penalty date)

--
Build a little, test a little.


Dennis M said:
Karl,

how do I get you a sample of the data.

The report is to be three columns

Legal name monthly count year to date count

Dismissed 10 15
Filed 20 50
Revocation 4 6
Pending
Censure
Suspended
Probation
Civil Penalty $100 $250
Refunds $5000 $2500

That is the desired report look.

The table hendings are

ID
LicenseNum
LegalStatus
LegalRefund
Licensee
ExamDate
DateSent
DateofDelivery
ResponseDueDate
ResponseReceived
REFUNDS - not used for this report
NoticeofHearing - date (Date used for LegalDisp)
Status
Notes
Reminders
CivilPenalty - currency (for dollars)
Corrected
LegalDate
LegalDisp - text (the type)
DismissedDate - date (this is for refunds and civil penalty date)

Hope this is usable Karl

Dennis

KARL DEWEY said:
Post sample data and example of your desired results.
--
Build a little, test a little.


Dennis M said:
Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






:

Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


:

LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

:

Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
NoticeofHearing CivilPenalty LegalDisp DismissedDate
2/19/2009 $100.00 Civil Penalties 6/24/2009
3/25/2009 $100.00 Civil Penalties 6/3/2009
5/14/2009 $100.00 Civil Penalties 6/24/2009
7/22/2009 $900.00 Civil Penalties 9/2/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $100.00 Civil Penalties 9/22/2009
8/24/2009 $0.00 Dismissed
9/11/2009

Hope this is what you meant.

KARL DEWEY said:
Just run select query, a dozen or so records, with the following fields --

- NoticeofHearing - date (Date used for LegalDisp)
- CivilPenalty - currency (for dollars)
- LegalDisp - text (the type)
- DismissedDate - date (this is for refunds and civil penalty date)

--
Build a little, test a little.


Dennis M said:
Karl,

how do I get you a sample of the data.

The report is to be three columns

Legal name monthly count year to date count

Dismissed 10 15
Filed 20 50
Revocation 4 6
Pending
Censure
Suspended
Probation
Civil Penalty $100 $250
Refunds $5000 $2500

That is the desired report look.

The table hendings are

ID
LicenseNum
LegalStatus
LegalRefund
Licensee
ExamDate
DateSent
DateofDelivery
ResponseDueDate
ResponseReceived
REFUNDS - not used for this report
NoticeofHearing - date (Date used for LegalDisp)
Status
Notes
Reminders
CivilPenalty - currency (for dollars)
Corrected
LegalDate
LegalDisp - text (the type)
DismissedDate - date (this is for refunds and civil penalty date)

Hope this is usable Karl

Dennis

KARL DEWEY said:
Post sample data and example of your desired results.
--
Build a little, test a little.


:

Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






:

Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


:

LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

:

Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
I will try what you have put together, sorry about the mixing of words.

Refunds and CivilPenalty are puklled on the dismisseddate in the table



KARL DEWEY said:
Create a table containing all of the LegalDisp to join with your data table.
I used names Dennis_M and Dennis_M_Legal.
You mixed up the names by having 'Civil Penalties', 'CivilPenalties' and
'Civil Penalty' so it took a lot of time finding why things would not work.

This gets you half way there --
SELECT Dennis_M_Legal.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
(SELECT Count([XX].LegalDisp) FROM Dennis_M AS [XX] WHERE
[Dennis_M_Legal].LegalDisp = [XX].LegalDisp AND ([XX].NoticeofHearing)
Between DateSerial(Year(Date()),1,1) and Date()-Day(Date()) ) AS [Year to
date]
FROM Dennis_M_Legal LEFT JOIN Dennis_M AS XX ON Dennis_M_Legal.LegalDisp =
XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY Dennis_M_Legal.LegalDisp;

I need some rules on Refunds, Civil Penalties, and the dates that would move
the displayed data to another location.


--
Build a little, test a little.


Dennis M said:
NoticeofHearing CivilPenalty LegalDisp DismissedDate
2/19/2009 $100.00 Civil Penalties 6/24/2009
3/25/2009 $100.00 Civil Penalties 6/3/2009
5/14/2009 $100.00 Civil Penalties 6/24/2009
7/22/2009 $900.00 Civil Penalties 9/2/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $100.00 Civil Penalties 9/22/2009
8/24/2009 $0.00 Dismissed
9/11/2009

Hope this is what you meant.

KARL DEWEY said:
Just run select query, a dozen or so records, with the following fields --

- NoticeofHearing - date (Date used for LegalDisp)
- CivilPenalty - currency (for dollars)
- LegalDisp - text (the type)
- DismissedDate - date (this is for refunds and civil penalty date)

--
Build a little, test a little.


:


Karl,

how do I get you a sample of the data.

The report is to be three columns

Legal name monthly count year to date count

Dismissed 10 15
Filed 20 50
Revocation 4 6
Pending
Censure
Suspended
Probation
Civil Penalty $100 $250
Refunds $5000 $2500

That is the desired report look.

The table hendings are

ID
LicenseNum
LegalStatus
LegalRefund
Licensee
ExamDate
DateSent
DateofDelivery
ResponseDueDate
ResponseReceived
REFUNDS - not used for this report
NoticeofHearing - date (Date used for LegalDisp)
Status
Notes
Reminders
CivilPenalty - currency (for dollars)
Corrected
LegalDate
LegalDisp - text (the type)
DismissedDate - date (this is for refunds and civil penalty date)

Hope this is usable Karl

Dennis

:

Post sample data and example of your desired results.
--
Build a little, test a little.


:

Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






:

Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


:

LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

:

Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
Karl,

I must make sure (XX) is reperesenting the table I made of all the
legalDisp, correct?



Dennis M said:
I will try what you have put together, sorry about the mixing of words.

Refunds and CivilPenalty are puklled on the dismisseddate in the table



KARL DEWEY said:
Create a table containing all of the LegalDisp to join with your data table.
I used names Dennis_M and Dennis_M_Legal.
You mixed up the names by having 'Civil Penalties', 'CivilPenalties' and
'Civil Penalty' so it took a lot of time finding why things would not work.

This gets you half way there --
SELECT Dennis_M_Legal.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
(SELECT Count([XX].LegalDisp) FROM Dennis_M AS [XX] WHERE
[Dennis_M_Legal].LegalDisp = [XX].LegalDisp AND ([XX].NoticeofHearing)
Between DateSerial(Year(Date()),1,1) and Date()-Day(Date()) ) AS [Year to
date]
FROM Dennis_M_Legal LEFT JOIN Dennis_M AS XX ON Dennis_M_Legal.LegalDisp =
XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY Dennis_M_Legal.LegalDisp;

I need some rules on Refunds, Civil Penalties, and the dates that would move
the displayed data to another location.


--
Build a little, test a little.


Dennis M said:
NoticeofHearing CivilPenalty LegalDisp DismissedDate
2/19/2009 $100.00 Civil Penalties 6/24/2009
3/25/2009 $100.00 Civil Penalties 6/3/2009
5/14/2009 $100.00 Civil Penalties 6/24/2009
7/22/2009 $900.00 Civil Penalties 9/2/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $100.00 Civil Penalties 9/22/2009
8/24/2009 $0.00 Dismissed
9/11/2009

Hope this is what you meant.

:

Just run select query, a dozen or so records, with the following fields --

- NoticeofHearing - date (Date used for LegalDisp)
- CivilPenalty - currency (for dollars)
- LegalDisp - text (the type)
- DismissedDate - date (this is for refunds and civil penalty date)

--
Build a little, test a little.


:


Karl,

how do I get you a sample of the data.

The report is to be three columns

Legal name monthly count year to date count

Dismissed 10 15
Filed 20 50
Revocation 4 6
Pending
Censure
Suspended
Probation
Civil Penalty $100 $250
Refunds $5000 $2500

That is the desired report look.

The table hendings are

ID
LicenseNum
LegalStatus
LegalRefund
Licensee
ExamDate
DateSent
DateofDelivery
ResponseDueDate
ResponseReceived
REFUNDS - not used for this report
NoticeofHearing - date (Date used for LegalDisp)
Status
Notes
Reminders
CivilPenalty - currency (for dollars)
Corrected
LegalDate
LegalDisp - text (the type)
DismissedDate - date (this is for refunds and civil penalty date)

Hope this is usable Karl

Dennis

:

Post sample data and example of your desired results.
--
Build a little, test a little.


:

Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






:

Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


:

LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

:

Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
Create a table containing all of the LegalDisp to join with your data table.
I used names Dennis_M and Dennis_M_Legal.
You mixed up the names by having 'Civil Penalties', 'CivilPenalties' and
'Civil Penalty' so it took a lot of time finding why things would not work.

This gets you half way there --
SELECT Dennis_M_Legal.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
(SELECT Count([XX].LegalDisp) FROM Dennis_M AS [XX] WHERE
[Dennis_M_Legal].LegalDisp = [XX].LegalDisp AND ([XX].NoticeofHearing)
Between DateSerial(Year(Date()),1,1) and Date()-Day(Date()) ) AS [Year to
date]
FROM Dennis_M_Legal LEFT JOIN Dennis_M AS XX ON Dennis_M_Legal.LegalDisp =
XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY Dennis_M_Legal.LegalDisp;

I need some rules on Refunds, Civil Penalties, and the dates that would move
the displayed data to another location.


--
Build a little, test a little.


Dennis M said:
NoticeofHearing CivilPenalty LegalDisp DismissedDate
2/19/2009 $100.00 Civil Penalties 6/24/2009
3/25/2009 $100.00 Civil Penalties 6/3/2009
5/14/2009 $100.00 Civil Penalties 6/24/2009
7/22/2009 $900.00 Civil Penalties 9/2/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $100.00 Civil Penalties 9/22/2009
8/24/2009 $0.00 Dismissed
9/11/2009

Hope this is what you meant.

KARL DEWEY said:
Just run select query, a dozen or so records, with the following fields --

- NoticeofHearing - date (Date used for LegalDisp)
- CivilPenalty - currency (for dollars)
- LegalDisp - text (the type)
- DismissedDate - date (this is for refunds and civil penalty date)

--
Build a little, test a little.


Dennis M said:
Karl,

how do I get you a sample of the data.

The report is to be three columns

Legal name monthly count year to date count

Dismissed 10 15
Filed 20 50
Revocation 4 6
Pending
Censure
Suspended
Probation
Civil Penalty $100 $250
Refunds $5000 $2500

That is the desired report look.

The table hendings are

ID
LicenseNum
LegalStatus
LegalRefund
Licensee
ExamDate
DateSent
DateofDelivery
ResponseDueDate
ResponseReceived
REFUNDS - not used for this report
NoticeofHearing - date (Date used for LegalDisp)
Status
Notes
Reminders
CivilPenalty - currency (for dollars)
Corrected
LegalDate
LegalDisp - text (the type)
DismissedDate - date (this is for refunds and civil penalty date)

Hope this is usable Karl

Dennis

:

Post sample data and example of your desired results.
--
Build a little, test a little.


:

Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






:

Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


:

LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

:

Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
Karl

First off the table with the data is ExamErrorTracking, the one I made for
legal is LegalStatus

I figured it out, and I wish I could think of ways like this to do it. In
time I hope.

It didn't populate any data to the table. I have two tables XX and
LegalStatus. with 4 fields in the query. It didnt ask me for a date start or
end and not familiar with the way you have done it.


Dennis M said:
Karl,

I must make sure (XX) is reperesenting the table I made of all the
legalDisp, correct?



Dennis M said:
I will try what you have put together, sorry about the mixing of words.

Refunds and CivilPenalty are puklled on the dismisseddate in the table



KARL DEWEY said:
Create a table containing all of the LegalDisp to join with your data table.
I used names Dennis_M and Dennis_M_Legal.
You mixed up the names by having 'Civil Penalties', 'CivilPenalties' and
'Civil Penalty' so it took a lot of time finding why things would not work.

This gets you half way there --
SELECT Dennis_M_Legal.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
(SELECT Count([XX].LegalDisp) FROM Dennis_M AS [XX] WHERE
[Dennis_M_Legal].LegalDisp = [XX].LegalDisp AND ([XX].NoticeofHearing)
Between DateSerial(Year(Date()),1,1) and Date()-Day(Date()) ) AS [Year to
date]
FROM Dennis_M_Legal LEFT JOIN Dennis_M AS XX ON Dennis_M_Legal.LegalDisp =
XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY Dennis_M_Legal.LegalDisp;

I need some rules on Refunds, Civil Penalties, and the dates that would move
the displayed data to another location.


--
Build a little, test a little.


:

NoticeofHearing CivilPenalty LegalDisp DismissedDate
2/19/2009 $100.00 Civil Penalties 6/24/2009
3/25/2009 $100.00 Civil Penalties 6/3/2009
5/14/2009 $100.00 Civil Penalties 6/24/2009
7/22/2009 $900.00 Civil Penalties 9/2/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $100.00 Civil Penalties 9/22/2009
8/24/2009 $0.00 Dismissed
9/11/2009

Hope this is what you meant.

:

Just run select query, a dozen or so records, with the following fields --

- NoticeofHearing - date (Date used for LegalDisp)
- CivilPenalty - currency (for dollars)
- LegalDisp - text (the type)
- DismissedDate - date (this is for refunds and civil penalty date)

--
Build a little, test a little.


:


Karl,

how do I get you a sample of the data.

The report is to be three columns

Legal name monthly count year to date count

Dismissed 10 15
Filed 20 50
Revocation 4 6
Pending
Censure
Suspended
Probation
Civil Penalty $100 $250
Refunds $5000 $2500

That is the desired report look.

The table hendings are

ID
LicenseNum
LegalStatus
LegalRefund
Licensee
ExamDate
DateSent
DateofDelivery
ResponseDueDate
ResponseReceived
REFUNDS - not used for this report
NoticeofHearing - date (Date used for LegalDisp)
Status
Notes
Reminders
CivilPenalty - currency (for dollars)
Corrected
LegalDate
LegalDisp - text (the type)
DismissedDate - date (this is for refunds and civil penalty date)

Hope this is usable Karl

Dennis

:

Post sample data and example of your desired results.
--
Build a little, test a little.


:

Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






:

Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


:

LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

:

Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
It didnt ask me for a date start or end and not familiar with the way you
have done it.
It automatically pulls data for last month and for year to end of last month.
'XX' is an alias for your ExamErrorTracking -- FROM Dennis_M_Legal LEFT
JOIN Dennis_M AS XX

Here I put your table names --
SELECT LegalStatus.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
Sum(XX.CivilPenalty) AS SumOfCivilPenalty, (SELECT Count([XX].LegalDisp) FROM
ExamErrorTracking AS [XX] WHERE [LegalStatus].LegalDisp = [XX].LegalDisp
AND ([XX].NoticeofHearing) Between DateSerial(Year(Date()),1,1) and
Date()-Day(Date()) ) AS [Year to date]
FROM LegalStatus LEFT JOIN ExamErrorTracking AS XX ON LegalStatus.LegalDisp
= XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY LegalStatus.LegalDisp;


--
Build a little, test a little.


Dennis M said:
Karl

First off the table with the data is ExamErrorTracking, the one I made for
legal is LegalStatus

I figured it out, and I wish I could think of ways like this to do it. In
time I hope.

It didn't populate any data to the table. I have two tables XX and
LegalStatus. with 4 fields in the query. It didnt ask me for a date start or
end and not familiar with the way you have done it.


Dennis M said:
Karl,

I must make sure (XX) is reperesenting the table I made of all the
legalDisp, correct?



Dennis M said:
I will try what you have put together, sorry about the mixing of words.

Refunds and CivilPenalty are puklled on the dismisseddate in the table



:

Create a table containing all of the LegalDisp to join with your data table.
I used names Dennis_M and Dennis_M_Legal.
You mixed up the names by having 'Civil Penalties', 'CivilPenalties' and
'Civil Penalty' so it took a lot of time finding why things would not work.

This gets you half way there --
SELECT Dennis_M_Legal.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
(SELECT Count([XX].LegalDisp) FROM Dennis_M AS [XX] WHERE
[Dennis_M_Legal].LegalDisp = [XX].LegalDisp AND ([XX].NoticeofHearing)
Between DateSerial(Year(Date()),1,1) and Date()-Day(Date()) ) AS [Year to
date]
FROM Dennis_M_Legal LEFT JOIN Dennis_M AS XX ON Dennis_M_Legal.LegalDisp =
XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY Dennis_M_Legal.LegalDisp;

I need some rules on Refunds, Civil Penalties, and the dates that would move
the displayed data to another location.


--
Build a little, test a little.


:

NoticeofHearing CivilPenalty LegalDisp DismissedDate
2/19/2009 $100.00 Civil Penalties 6/24/2009
3/25/2009 $100.00 Civil Penalties 6/3/2009
5/14/2009 $100.00 Civil Penalties 6/24/2009
7/22/2009 $900.00 Civil Penalties 9/2/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $100.00 Civil Penalties 9/22/2009
8/24/2009 $0.00 Dismissed
9/11/2009

Hope this is what you meant.

:

Just run select query, a dozen or so records, with the following fields --

- NoticeofHearing - date (Date used for LegalDisp)
- CivilPenalty - currency (for dollars)
- LegalDisp - text (the type)
- DismissedDate - date (this is for refunds and civil penalty date)

--
Build a little, test a little.


:


Karl,

how do I get you a sample of the data.

The report is to be three columns

Legal name monthly count year to date count

Dismissed 10 15
Filed 20 50
Revocation 4 6
Pending
Censure
Suspended
Probation
Civil Penalty $100 $250
Refunds $5000 $2500

That is the desired report look.

The table hendings are

ID
LicenseNum
LegalStatus
LegalRefund
Licensee
ExamDate
DateSent
DateofDelivery
ResponseDueDate
ResponseReceived
REFUNDS - not used for this report
NoticeofHearing - date (Date used for LegalDisp)
Status
Notes
Reminders
CivilPenalty - currency (for dollars)
Corrected
LegalDate
LegalDisp - text (the type)
DismissedDate - date (this is for refunds and civil penalty date)

Hope this is usable Karl

Dennis

:

Post sample data and example of your desired results.
--
Build a little, test a little.


:

Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






:

Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


:

LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

:

Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
Karl

Made a query and ran it, still no data.

It is a left join from LegalStatus.LegalDisp to XX.LegalDisp

KARL DEWEY said:
have done it.
It automatically pulls data for last month and for year to end of last month.
'XX' is an alias for your ExamErrorTracking -- FROM Dennis_M_Legal LEFT
JOIN Dennis_M AS XX

Here I put your table names --
SELECT LegalStatus.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
Sum(XX.CivilPenalty) AS SumOfCivilPenalty, (SELECT Count([XX].LegalDisp) FROM
ExamErrorTracking AS [XX] WHERE [LegalStatus].LegalDisp = [XX].LegalDisp
AND ([XX].NoticeofHearing) Between DateSerial(Year(Date()),1,1) and
Date()-Day(Date()) ) AS [Year to date]
FROM LegalStatus LEFT JOIN ExamErrorTracking AS XX ON LegalStatus.LegalDisp
= XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY LegalStatus.LegalDisp;


--
Build a little, test a little.


Dennis M said:
Karl

First off the table with the data is ExamErrorTracking, the one I made for
legal is LegalStatus

I figured it out, and I wish I could think of ways like this to do it. In
time I hope.

It didn't populate any data to the table. I have two tables XX and
LegalStatus. with 4 fields in the query. It didnt ask me for a date start or
end and not familiar with the way you have done it.


Dennis M said:
Karl,

I must make sure (XX) is reperesenting the table I made of all the
legalDisp, correct?



:

I will try what you have put together, sorry about the mixing of words.

Refunds and CivilPenalty are puklled on the dismisseddate in the table



:

Create a table containing all of the LegalDisp to join with your data table.
I used names Dennis_M and Dennis_M_Legal.
You mixed up the names by having 'Civil Penalties', 'CivilPenalties' and
'Civil Penalty' so it took a lot of time finding why things would not work.

This gets you half way there --
SELECT Dennis_M_Legal.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
(SELECT Count([XX].LegalDisp) FROM Dennis_M AS [XX] WHERE
[Dennis_M_Legal].LegalDisp = [XX].LegalDisp AND ([XX].NoticeofHearing)
Between DateSerial(Year(Date()),1,1) and Date()-Day(Date()) ) AS [Year to
date]
FROM Dennis_M_Legal LEFT JOIN Dennis_M AS XX ON Dennis_M_Legal.LegalDisp =
XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY Dennis_M_Legal.LegalDisp;

I need some rules on Refunds, Civil Penalties, and the dates that would move
the displayed data to another location.


--
Build a little, test a little.


:

NoticeofHearing CivilPenalty LegalDisp DismissedDate
2/19/2009 $100.00 Civil Penalties 6/24/2009
3/25/2009 $100.00 Civil Penalties 6/3/2009
5/14/2009 $100.00 Civil Penalties 6/24/2009
7/22/2009 $900.00 Civil Penalties 9/2/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $100.00 Civil Penalties 9/22/2009
8/24/2009 $0.00 Dismissed
9/11/2009

Hope this is what you meant.

:

Just run select query, a dozen or so records, with the following fields --

- NoticeofHearing - date (Date used for LegalDisp)
- CivilPenalty - currency (for dollars)
- LegalDisp - text (the type)
- DismissedDate - date (this is for refunds and civil penalty date)

--
Build a little, test a little.


:


Karl,

how do I get you a sample of the data.

The report is to be three columns

Legal name monthly count year to date count

Dismissed 10 15
Filed 20 50
Revocation 4 6
Pending
Censure
Suspended
Probation
Civil Penalty $100 $250
Refunds $5000 $2500

That is the desired report look.

The table hendings are

ID
LicenseNum
LegalStatus
LegalRefund
Licensee
ExamDate
DateSent
DateofDelivery
ResponseDueDate
ResponseReceived
REFUNDS - not used for this report
NoticeofHearing - date (Date used for LegalDisp)
Status
Notes
Reminders
CivilPenalty - currency (for dollars)
Corrected
LegalDate
LegalDisp - text (the type)
DismissedDate - date (this is for refunds and civil penalty date)

Hope this is usable Karl

Dennis

:

Post sample data and example of your desired results.
--
Build a little, test a little.


:

Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






:

Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


:

LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

:

Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
Try dropping the --
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
to see what you get.
Keep droping stuff until it work to find why.

--
Build a little, test a little.


Dennis M said:
Karl

Made a query and ran it, still no data.

It is a left join from LegalStatus.LegalDisp to XX.LegalDisp

KARL DEWEY said:
It didnt ask me for a date start or end and not familiar with the way you
Click to expand...
have done it.
It automatically pulls data for last month and for year to end of last month.
I have two tables XX and LegalStatus
Click to expand...
'XX' is an alias for your ExamErrorTracking -- FROM Dennis_M_Legal LEFT
JOIN Dennis_M AS XX

Here I put your table names --
SELECT LegalStatus.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
Sum(XX.CivilPenalty) AS SumOfCivilPenalty, (SELECT Count([XX].LegalDisp) FROM
ExamErrorTracking AS [XX] WHERE [LegalStatus].LegalDisp = [XX].LegalDisp
AND ([XX].NoticeofHearing) Between DateSerial(Year(Date()),1,1) and
Date()-Day(Date()) ) AS [Year to date]
FROM LegalStatus LEFT JOIN ExamErrorTracking AS XX ON LegalStatus.LegalDisp
= XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY LegalStatus.LegalDisp;


--
Build a little, test a little.


Dennis M said:
Karl

First off the table with the data is ExamErrorTracking, the one I made for
legal is LegalStatus

I figured it out, and I wish I could think of ways like this to do it. In
time I hope.

It didn't populate any data to the table. I have two tables XX and
LegalStatus. with 4 fields in the query. It didnt ask me for a date start or
end and not familiar with the way you have done it.


:

Karl,

I must make sure (XX) is reperesenting the table I made of all the
legalDisp, correct?



:

I will try what you have put together, sorry about the mixing of words.

Refunds and CivilPenalty are puklled on the dismisseddate in the table



:

Create a table containing all of the LegalDisp to join with your data table.
I used names Dennis_M and Dennis_M_Legal.
You mixed up the names by having 'Civil Penalties', 'CivilPenalties' and
'Civil Penalty' so it took a lot of time finding why things would not work.

This gets you half way there --
SELECT Dennis_M_Legal.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
(SELECT Count([XX].LegalDisp) FROM Dennis_M AS [XX] WHERE
[Dennis_M_Legal].LegalDisp = [XX].LegalDisp AND ([XX].NoticeofHearing)
Between DateSerial(Year(Date()),1,1) and Date()-Day(Date()) ) AS [Year to
date]
FROM Dennis_M_Legal LEFT JOIN Dennis_M AS XX ON Dennis_M_Legal.LegalDisp =
XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY Dennis_M_Legal.LegalDisp;

I need some rules on Refunds, Civil Penalties, and the dates that would move
the displayed data to another location.


--
Build a little, test a little.


:

NoticeofHearing CivilPenalty LegalDisp DismissedDate
2/19/2009 $100.00 Civil Penalties 6/24/2009
3/25/2009 $100.00 Civil Penalties 6/3/2009
5/14/2009 $100.00 Civil Penalties 6/24/2009
7/22/2009 $900.00 Civil Penalties 9/2/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $100.00 Civil Penalties 9/22/2009
8/24/2009 $0.00 Dismissed
9/11/2009

Hope this is what you meant.

:

Just run select query, a dozen or so records, with the following fields --

- NoticeofHearing - date (Date used for LegalDisp)
- CivilPenalty - currency (for dollars)
- LegalDisp - text (the type)
- DismissedDate - date (this is for refunds and civil penalty date)

--
Build a little, test a little.


:


Karl,

how do I get you a sample of the data.

The report is to be three columns

Legal name monthly count year to date count

Dismissed 10 15
Filed 20 50
Revocation 4 6
Pending
Censure
Suspended
Probation
Civil Penalty $100 $250
Refunds $5000 $2500

That is the desired report look.

The table hendings are

ID
LicenseNum
LegalStatus
LegalRefund
Licensee
ExamDate
DateSent
DateofDelivery
ResponseDueDate
ResponseReceived
REFUNDS - not used for this report
NoticeofHearing - date (Date used for LegalDisp)
Status
Notes
Reminders
CivilPenalty - currency (for dollars)
Corrected
LegalDate
LegalDisp - text (the type)
DismissedDate - date (this is for refunds and civil penalty date)

Hope this is usable Karl

Dennis

:

Post sample data and example of your desired results.
--
Build a little, test a little.


:

Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






:

Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


:

LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

:

Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
When I removed the date expression I got this:
Civil Penalty should be dollars only not count, civil penalties is showing
correct totla dollars for YTD and there are 5, but there were none in August
and 2 in September.
Dismissed is the YTD total in Montly Count, August was 0, and 9 in September.
Filed should be using NoticeofHearing as date and should be 7 for August 24
in Septemeber and 52 ytd.
Pending is 25 ytd. 21 in September 2 in August, 2 before that.

I know it wont count right without the date function in, so I gave you this
to help I hope narrow down.

LegalDisp Monthly Count SumOfCivilPenalty Year to date
Cease & Desist 0
Censure 0 0
Civil Penalties 5 $1,300.00 5
Dismissed 15 $0.00 12
Filed 0 0
Pending 25 $0.00 4
Probation 0 0
Refunds 1 1
Revocation 1 1
Suspension 0 0

KARL DEWEY said:
have done it.
It automatically pulls data for last month and for year to end of last month.
'XX' is an alias for your ExamErrorTracking -- FROM Dennis_M_Legal LEFT
JOIN Dennis_M AS XX

Here I put your table names --
SELECT LegalStatus.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
Sum(XX.CivilPenalty) AS SumOfCivilPenalty, (SELECT Count([XX].LegalDisp) FROM
ExamErrorTracking AS [XX] WHERE [LegalStatus].LegalDisp = [XX].LegalDisp
AND ([XX].NoticeofHearing) Between DateSerial(Year(Date()),1,1) and
Date()-Day(Date()) ) AS [Year to date]
FROM LegalStatus LEFT JOIN ExamErrorTracking AS XX ON LegalStatus.LegalDisp
= XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY LegalStatus.LegalDisp;


--
Build a little, test a little.


Dennis M said:
Karl

First off the table with the data is ExamErrorTracking, the one I made for
legal is LegalStatus

I figured it out, and I wish I could think of ways like this to do it. In
time I hope.

It didn't populate any data to the table. I have two tables XX and
LegalStatus. with 4 fields in the query. It didnt ask me for a date start or
end and not familiar with the way you have done it.


Dennis M said:
Karl,

I must make sure (XX) is reperesenting the table I made of all the
legalDisp, correct?



:

I will try what you have put together, sorry about the mixing of words.

Refunds and CivilPenalty are puklled on the dismisseddate in the table



:

Create a table containing all of the LegalDisp to join with your data table.
I used names Dennis_M and Dennis_M_Legal.
You mixed up the names by having 'Civil Penalties', 'CivilPenalties' and
'Civil Penalty' so it took a lot of time finding why things would not work.

This gets you half way there --
SELECT Dennis_M_Legal.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
(SELECT Count([XX].LegalDisp) FROM Dennis_M AS [XX] WHERE
[Dennis_M_Legal].LegalDisp = [XX].LegalDisp AND ([XX].NoticeofHearing)
Between DateSerial(Year(Date()),1,1) and Date()-Day(Date()) ) AS [Year to
date]
FROM Dennis_M_Legal LEFT JOIN Dennis_M AS XX ON Dennis_M_Legal.LegalDisp =
XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY Dennis_M_Legal.LegalDisp;

I need some rules on Refunds, Civil Penalties, and the dates that would move
the displayed data to another location.


--
Build a little, test a little.


:

NoticeofHearing CivilPenalty LegalDisp DismissedDate
2/19/2009 $100.00 Civil Penalties 6/24/2009
3/25/2009 $100.00 Civil Penalties 6/3/2009
5/14/2009 $100.00 Civil Penalties 6/24/2009
7/22/2009 $900.00 Civil Penalties 9/2/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $100.00 Civil Penalties 9/22/2009
8/24/2009 $0.00 Dismissed
9/11/2009

Hope this is what you meant.

:

Just run select query, a dozen or so records, with the following fields --

- NoticeofHearing - date (Date used for LegalDisp)
- CivilPenalty - currency (for dollars)
- LegalDisp - text (the type)
- DismissedDate - date (this is for refunds and civil penalty date)

--
Build a little, test a little.


:


Karl,

how do I get you a sample of the data.

The report is to be three columns

Legal name monthly count year to date count

Dismissed 10 15
Filed 20 50
Revocation 4 6
Pending
Censure
Suspended
Probation
Civil Penalty $100 $250
Refunds $5000 $2500

That is the desired report look.

The table hendings are

ID
LicenseNum
LegalStatus
LegalRefund
Licensee
ExamDate
DateSent
DateofDelivery
ResponseDueDate
ResponseReceived
REFUNDS - not used for this report
NoticeofHearing - date (Date used for LegalDisp)
Status
Notes
Reminders
CivilPenalty - currency (for dollars)
Corrected
LegalDate
LegalDisp - text (the type)
DismissedDate - date (this is for refunds and civil penalty date)

Hope this is usable Karl

Dennis

:

Post sample data and example of your desired results.
--
Build a little, test a little.


:

Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






:

Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


:

LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

:

Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
You got the data and query that is close now.
I am going to have to let you work it out to fix the remaining problems.

--
Build a little, test a little.


Dennis M said:
When I removed the date expression I got this:
Civil Penalty should be dollars only not count, civil penalties is showing
correct totla dollars for YTD and there are 5, but there were none in August
and 2 in September.
Dismissed is the YTD total in Montly Count, August was 0, and 9 in September.
Filed should be using NoticeofHearing as date and should be 7 for August 24
in Septemeber and 52 ytd.
Pending is 25 ytd. 21 in September 2 in August, 2 before that.

I know it wont count right without the date function in, so I gave you this
to help I hope narrow down.

LegalDisp Monthly Count SumOfCivilPenalty Year to date
Cease & Desist 0
Censure 0 0
Civil Penalties 5 $1,300.00 5
Dismissed 15 $0.00 12
Filed 0 0
Pending 25 $0.00 4
Probation 0 0
Refunds 1 1
Revocation 1 1
Suspension 0 0

KARL DEWEY said:
It didnt ask me for a date start or end and not familiar with the way you
Click to expand...
have done it.
It automatically pulls data for last month and for year to end of last month.
I have two tables XX and LegalStatus
Click to expand...
'XX' is an alias for your ExamErrorTracking -- FROM Dennis_M_Legal LEFT
JOIN Dennis_M AS XX

Here I put your table names --
SELECT LegalStatus.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
Sum(XX.CivilPenalty) AS SumOfCivilPenalty, (SELECT Count([XX].LegalDisp) FROM
ExamErrorTracking AS [XX] WHERE [LegalStatus].LegalDisp = [XX].LegalDisp
AND ([XX].NoticeofHearing) Between DateSerial(Year(Date()),1,1) and
Date()-Day(Date()) ) AS [Year to date]
FROM LegalStatus LEFT JOIN ExamErrorTracking AS XX ON LegalStatus.LegalDisp
= XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY LegalStatus.LegalDisp;


--
Build a little, test a little.


Dennis M said:
Karl

First off the table with the data is ExamErrorTracking, the one I made for
legal is LegalStatus

I figured it out, and I wish I could think of ways like this to do it. In
time I hope.

It didn't populate any data to the table. I have two tables XX and
LegalStatus. with 4 fields in the query. It didnt ask me for a date start or
end and not familiar with the way you have done it.


:

Karl,

I must make sure (XX) is reperesenting the table I made of all the
legalDisp, correct?



:

I will try what you have put together, sorry about the mixing of words.

Refunds and CivilPenalty are puklled on the dismisseddate in the table



:

Create a table containing all of the LegalDisp to join with your data table.
I used names Dennis_M and Dennis_M_Legal.
You mixed up the names by having 'Civil Penalties', 'CivilPenalties' and
'Civil Penalty' so it took a lot of time finding why things would not work.

This gets you half way there --
SELECT Dennis_M_Legal.LegalDisp, Count(XX.LegalDisp) AS [Monthly Count],
(SELECT Count([XX].LegalDisp) FROM Dennis_M AS [XX] WHERE
[Dennis_M_Legal].LegalDisp = [XX].LegalDisp AND ([XX].NoticeofHearing)
Between DateSerial(Year(Date()),1,1) and Date()-Day(Date()) ) AS [Year to
date]
FROM Dennis_M_Legal LEFT JOIN Dennis_M AS XX ON Dennis_M_Legal.LegalDisp =
XX.LegalDisp
WHERE (((XX.NoticeofHearing) Between Date()-Day(Date()) And
DateAdd("m",-1,(Date()-Day(Date()))+1)))
GROUP BY Dennis_M_Legal.LegalDisp;

I need some rules on Refunds, Civil Penalties, and the dates that would move
the displayed data to another location.


--
Build a little, test a little.


:

NoticeofHearing CivilPenalty LegalDisp DismissedDate
2/19/2009 $100.00 Civil Penalties 6/24/2009
3/25/2009 $100.00 Civil Penalties 6/3/2009
5/14/2009 $100.00 Civil Penalties 6/24/2009
7/22/2009 $900.00 Civil Penalties 9/2/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $0.00 Dismissed
9/11/2009
8/24/2009 $100.00 Civil Penalties 9/22/2009
8/24/2009 $0.00 Dismissed
9/11/2009

Hope this is what you meant.

:

Just run select query, a dozen or so records, with the following fields --

- NoticeofHearing - date (Date used for LegalDisp)
- CivilPenalty - currency (for dollars)
- LegalDisp - text (the type)
- DismissedDate - date (this is for refunds and civil penalty date)

--
Build a little, test a little.


:


Karl,

how do I get you a sample of the data.

The report is to be three columns

Legal name monthly count year to date count

Dismissed 10 15
Filed 20 50
Revocation 4 6
Pending
Censure
Suspended
Probation
Civil Penalty $100 $250
Refunds $5000 $2500

That is the desired report look.

The table hendings are

ID
LicenseNum
LegalStatus
LegalRefund
Licensee
ExamDate
DateSent
DateofDelivery
ResponseDueDate
ResponseReceived
REFUNDS - not used for this report
NoticeofHearing - date (Date used for LegalDisp)
Status
Notes
Reminders
CivilPenalty - currency (for dollars)
Corrected
LegalDate
LegalDisp - text (the type)
DismissedDate - date (this is for refunds and civil penalty date)

Hope this is usable Karl

Dennis

:

Post sample data and example of your desired results.
--
Build a little, test a little.


:

Karl,

I assume you intend this to be a query attached to the report.

Let me explain a little more:
The report has to columns: one is the text in LegaDisp, 8 different types,
and the other is a counting column for how many times in the month it has
been done, that column is deterimend by a date that appears in noticeof
hearing. I have accomplished the above with the text box with this in the
control source, =Count(IIf([LegalDisp]="Censure",0)) replacing censure with
what ever I am looking for to count. I have 8 of those.

The odd balls are the ones I need to sum tied to a different column for
date, that is what is throwing me. I am sorry I didn't give you more in the
beginning.

Of course the boss would love to see another column with year to date for
all of it also.
Thanks for help.






:

Try this --
SELECT Sum(iif([LegaDisp]="Dismissed", 1,0)) AS CountOfDismissed,
Sum(Nz([Refunds])
FROM YourTable
WHERE [notice of hearing] Between [start] AND [end];

Or this --
SELECT Sum(iif([LegaDisp]="Dismissed" AND [notice of hearing] Between
[start] AND [end], 1,0)) AS CountOfDismissed, Sum(IIF([closed] Between
[start] AND [end],Nz([Refunds], 0), 0) AS Refunded
FROM YourTable;

--
Build a little, test a little.


:

LegalDisp containes text ,"Dismissed", "Pending" etc... Refunds will be
dollar amounts.
I need to be able to count the dismissed and total the refunds by different
dates.

The dates for the items in the LegalDisp field are located i na column
called noticeof hearing and that is where I use the between [start] and
[end].

But the refunds come from closed dates that appear in a column called closed.
I am not a VB person, this is 2003 Access.

Thanks for your help

That may still be unclear hope you gtet the jest of it.

:

Your desire is not clear, but I'll toss out some random guidance. Sometimes,
it is helpful to create fields in the query, then use that field on the
report.

In your current expressions, you reference the fields [LegaDisp] and
[Refunds]. Are these the fields you want to reference? If not, then what
are the columns that you want to reference?

:

I have a monthly report that I count occurances between dates and I need to
sum columns refrencing a different column. The ones icount I use one column
the the one Isum will be a different column for dates.
In the rpeort I am justusing text boxes with Count
(iif(l[LegaDisp])="Dismissed",0)).
Sum(Nz([Refunds]) but I cant figue out how to reference the correct column
in the table.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top