sum of field, broken down by month.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total for each sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub station in Jan,
Feb, March etc...I do have a field in the query that would allow for this it
is called Date Assigned. I just cannot seem to get the code right. Should I
use Between or IIF. Any Suggestions would be wonderful...
 
It seems that you could create a totals query that groups by AssignedTo and
Format([Date Assigned],"yyyymm"). Then use this query as the record source
for a subreport.
 
When I am creating this totals query I have Field as Assigned to, table as
Registrants, Total as Group By, and in the Criteria I have put ([assigned
to]="Headquarters"). I then repeat this for the next sub. When I view the
Datasheet there is no information pulled. Should I be using Expression in
the Totals section?

Duane Hookom said:
It seems that you could create a totals query that groups by AssignedTo and
Format([Date Assigned],"yyyymm"). Then use this query as the record source
for a subreport.

--
Duane Hookom
MS Access MVP
--

atwater said:
I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total for each sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub station in
Jan,
Feb, March etc...I do have a field in the query that would allow for this
it
is called Date Assigned. I just cannot seem to get the code right.
Should I
use Between or IIF. Any Suggestions would be wonderful...
Click to expand...
Click to expand...
 
Maybe you need to provide some sample records and desired display in your
report...

--
Duane Hookom
MS Access MVP
--

atwater said:
When I am creating this totals query I have Field as Assigned to, table as
Registrants, Total as Group By, and in the Criteria I have put ([assigned
to]="Headquarters"). I then repeat this for the next sub. When I view
the
Datasheet there is no information pulled. Should I be using Expression in
the Totals section?

Duane Hookom said:
It seems that you could create a totals query that groups by AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as the record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

atwater said:
I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total for each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub station in
Jan,
Feb, March etc...I do have a field in the query that would allow for
this
it
is called Date Assigned. I just cannot seem to get the code right.
Should I
use Between or IIF. Any Suggestions would be wonderful...
Click to expand...
Click to expand...
Click to expand...
 
I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person doing the data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with totals for each month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this report.

Thanks for all of your help. I really appreciate it.






Duane Hookom said:
Maybe you need to provide some sample records and desired display in your
report...

--
Duane Hookom
MS Access MVP
--

atwater said:
When I am creating this totals query I have Field as Assigned to, table as
Registrants, Total as Group By, and in the Criteria I have put ([assigned
to]="Headquarters"). I then repeat this for the next sub. When I view
the
Datasheet there is no information pulled. Should I be using Expression in
the Totals section?

Duane Hookom said:
It seems that you could create a totals query that groups by AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as the record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total for each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub station in
Jan,
Feb, March etc...I do have a field in the query that would allow for
this
it
is called Date Assigned. I just cannot seem to get the code right.
Should I
use Between or IIF. Any Suggestions would be wonderful...
Click to expand...
Click to expand...
Click to expand...
 
I tried this:
=Abs(Sum([assigned to]="Name of Sub" And [Date Assigned]="01/ddyyyy"))

In the report. I grabbed the Date Assigned from the field list and dropped
it into the report. I then typed the above into the control source box. I
was able to start to run the report by beginning date and ending date then I
got "this expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numberic expression my contain too many
complicated elements. Try simplifying the expression by assigning parts of
the expression to variables." this is something new to learn for me. Thanks
for being patient.


atwater said:
I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person doing the data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with totals for each month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this report.

Thanks for all of your help. I really appreciate it.






Duane Hookom said:
Maybe you need to provide some sample records and desired display in your
report...

--
Duane Hookom
MS Access MVP
--

atwater said:
When I am creating this totals query I have Field as Assigned to, table as
Registrants, Total as Group By, and in the Criteria I have put ([assigned
to]="Headquarters"). I then repeat this for the next sub. When I view
the
Datasheet there is no information pulled. Should I be using Expression in
the Totals section?

:

It seems that you could create a totals query that groups by AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as the record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total for each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub station in
Jan,
Feb, March etc...I do have a field in the query that would allow for
this
it
is called Date Assigned. I just cannot seem to get the code right.
Should I
use Between or IIF. Any Suggestions would be wonderful...
Click to expand...
Click to expand...
Click to expand...
 
Select [Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases
FROM Registration
GROUP BY [Assigned To], Month([Date Assigned]),
Format([Date Assigned],"mmmm");

Use the above query as the Record Source for your report.

--
Duane Hookom
MS Access MVP
--

atwater said:
I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person doing the data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with totals for each
month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this report.

Thanks for all of your help. I really appreciate it.






Duane Hookom said:
Maybe you need to provide some sample records and desired display in your
report...

--
Duane Hookom
MS Access MVP
--

atwater said:
When I am creating this totals query I have Field as Assigned to, table
as
Registrants, Total as Group By, and in the Criteria I have put
([assigned
to]="Headquarters"). I then repeat this for the next sub. When I view
the
Datasheet there is no information pulled. Should I be using Expression
in
the Totals section?

:

It seems that you could create a totals query that groups by
AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as the record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total for
each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub station
in
Jan,
Feb, March etc...I do have a field in the query that would allow for
this
it
is called Date Assigned. I just cannot seem to get the code right.
Should I
use Between or IIF. Any Suggestions would be wonderful...
Click to expand...
Click to expand...
Click to expand...
 
When trying to enter Select[Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases I get an
error message that states The wyntax of the subquery in this expression is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses. When I click ok it takes me to the word Select. Did I miss
something?

Duane Hookom said:
Select [Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases
FROM Registration
GROUP BY [Assigned To], Month([Date Assigned]),
Format([Date Assigned],"mmmm");

Use the above query as the Record Source for your report.

--
Duane Hookom
MS Access MVP
--

atwater said:
I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person doing the data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with totals for each
month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this report.

Thanks for all of your help. I really appreciate it.






Duane Hookom said:
Maybe you need to provide some sample records and desired display in your
report...

--
Duane Hookom
MS Access MVP
--

When I am creating this totals query I have Field as Assigned to, table
as
Registrants, Total as Group By, and in the Criteria I have put
([assigned
to]="Headquarters"). I then repeat this for the next sub. When I view
the
Datasheet there is no information pulled. Should I be using Expression
in
the Totals section?

:

It seems that you could create a totals query that groups by
AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as the record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total for
each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub station
in
Jan,
Feb, March etc...I do have a field in the query that would allow for
this
it
is called Date Assigned. I just cannot seem to get the code right.
Should I
use Between or IIF. Any Suggestions would be wonderful...
Click to expand...
Click to expand...
Click to expand...
 
There was no subquery in the sql I posted. Did you copy and paste anything?

--
Duane Hookom
MS Access MVP
--

atwater said:
When trying to enter Select[Assigned To], Month([Date Assigned]) as
MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases I get an
error message that states The wyntax of the subquery in this expression is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses. When I click ok it takes me to the word Select. Did I miss
something?

Duane Hookom said:
Select [Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases
FROM Registration
GROUP BY [Assigned To], Month([Date Assigned]),
Format([Date Assigned],"mmmm");

Use the above query as the Record Source for your report.

--
Duane Hookom
MS Access MVP
--

atwater said:
I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person doing the
data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with totals for each
month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this report.

Thanks for all of your help. I really appreciate it.






:

Maybe you need to provide some sample records and desired display in
your
report...

--
Duane Hookom
MS Access MVP
--

When I am creating this totals query I have Field as Assigned to,
table
as
Registrants, Total as Group By, and in the Criteria I have put
([assigned
to]="Headquarters"). I then repeat this for the next sub. When I
view
the
Datasheet there is no information pulled. Should I be using
Expression
in
the Totals section?

:

It seems that you could create a totals query that groups by
AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as the record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total for
each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub
station
in
Jan,
Feb, March etc...I do have a field in the query that would allow
for
this
it
is called Date Assigned. I just cannot seem to get the code
right.
Should I
use Between or IIF. Any Suggestions would be wonderful...
Click to expand...
Click to expand...
Click to expand...
 
I entered the information in the SQL view. It creates the query for me but
when trying to view the info I get this.
Expr1 MthNum MthName Cases DATE ASSIGNED
HEADQUARTERS 3 March 1 03/26/2005
HEADQUARTERS 3 March 29 03/29/2005
HEADQUARTERS 4 April 1 04/12/2005
HEADQUARTERS 4 April 1 04/14/2005
HEADQUARTERS 4 April 3 04/19/2005
HEADQUATERS 3 March 1 03/29/2005
sub 1 1 January 14 01/19/2005
sub 1 1 January 1 01/20/2005
sub 1 3 March 1 03/24/2005
sub 1 3 March 1 03/29/2005
sub 1 4 April 1 04/12/2005
sub 2 2 February 14 02/07/2005
sub 2 4 April 1 04/01/2005
sub 2 4 April 1 04/12/2005
sub 3 1 January 1 01/20/2005
sub 3 3 March 20 03/24/2005
sub 3 4 April 1 04/01/2005
sub 3 4 April 3 04/12/2005
sub 3 4 April 2 04/19/2005
sub 3 3 March 1 03/24/2005

I entered a Field to ask me for a date range when viewing the information
and it works great. Now I just need ot have the Totals for the month all in
one entry. How do I change the SQL?

Thanks








When trying to enter Select[Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases I get an
error message that states The wyntax of the subquery in this expression is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses. When I click ok it takes me to the word Select. Did I miss
something?

Duane Hookom said:
Select [Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases
FROM Registration
GROUP BY [Assigned To], Month([Date Assigned]),
Format([Date Assigned],"mmmm");

Use the above query as the Record Source for your report.

--
Duane Hookom
MS Access MVP
--

atwater said:
I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person doing the data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with totals for each
month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this report.

Thanks for all of your help. I really appreciate it.






:

Maybe you need to provide some sample records and desired display in your
report...

--
Duane Hookom
MS Access MVP
--

When I am creating this totals query I have Field as Assigned to, table
as
Registrants, Total as Group By, and in the Criteria I have put
([assigned
to]="Headquarters"). I then repeat this for the next sub. When I view
the
Datasheet there is no information pulled. Should I be using Expression
in
the Totals section?

:

It seems that you could create a totals query that groups by
AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as the record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total for
each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub station
in
Jan,
Feb, March etc...I do have a field in the query that would allow for
this
it
is called Date Assigned. I just cannot seem to get the code right.
Should I
use Between or IIF. Any Suggestions would be wonderful...
Click to expand...
Click to expand...
Click to expand...
 
That is not the result of my SQL. Why do you have [Date Assigned]
displaying?

--
Duane Hookom
MS Access MVP
--

atwater said:
I entered the information in the SQL view. It creates the query for me
but
when trying to view the info I get this.
Expr1 MthNum MthName Cases DATE ASSIGNED
HEADQUARTERS 3 March 1 03/26/2005
HEADQUARTERS 3 March 29 03/29/2005
HEADQUARTERS 4 April 1 04/12/2005
HEADQUARTERS 4 April 1 04/14/2005
HEADQUARTERS 4 April 3 04/19/2005
HEADQUATERS 3 March 1 03/29/2005
sub 1 1 January 14 01/19/2005
sub 1 1 January 1 01/20/2005
sub 1 3 March 1 03/24/2005
sub 1 3 March 1 03/29/2005
sub 1 4 April 1 04/12/2005
sub 2 2 February 14 02/07/2005
sub 2 4 April 1 04/01/2005
sub 2 4 April 1 04/12/2005
sub 3 1 January 1 01/20/2005
sub 3 3 March 20 03/24/2005
sub 3 4 April 1 04/01/2005
sub 3 4 April 3 04/12/2005
sub 3 4 April 2 04/19/2005
sub 3 3 March 1 03/24/2005

I entered a Field to ask me for a date range when viewing the information
and it works great. Now I just need ot have the Totals for the month all
in
one entry. How do I change the SQL?

Thanks








When trying to enter Select[Assigned To], Month([Date Assigned]) as
MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases I get an
error message that states The wyntax of the subquery in this expression
is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses. When I click ok it takes me to the word Select. Did I miss
something?

Duane Hookom said:
Select [Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases
FROM Registration
GROUP BY [Assigned To], Month([Date Assigned]),
Format([Date Assigned],"mmmm");

Use the above query as the Record Source for your report.

--
Duane Hookom
MS Access MVP
--

I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person doing the
data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with totals for
each
month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this report.

Thanks for all of your help. I really appreciate it.






:

Maybe you need to provide some sample records and desired display in
your
report...

--
Duane Hookom
MS Access MVP
--

When I am creating this totals query I have Field as Assigned to,
table
as
Registrants, Total as Group By, and in the Criteria I have put
([assigned
to]="Headquarters"). I then repeat this for the next sub. When I
view
the
Datasheet there is no information pulled. Should I be using
Expression
in
the Totals section?

:

It seems that you could create a totals query that groups by
AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as the
record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total
for
each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub
station
in
Jan,
Feb, March etc...I do have a field in the query that would
allow for
this
it
is called Date Assigned. I just cannot seem to get the code
right.
Should I
use Between or IIF. Any Suggestions would be wonderful...
Click to expand...
Click to expand...
Click to expand...
 
No, I did not copy and paste anything. everything is working great expect
that I do not get all of the monthly totals in one entry. The totals for the
month are broken down by day. Is there a way to get them in just one entry?
Thanks for all of your help.

Duane Hookom said:
There was no subquery in the sql I posted. Did you copy and paste anything?

--
Duane Hookom
MS Access MVP
--

atwater said:
When trying to enter Select[Assigned To], Month([Date Assigned]) as
MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases I get an
error message that states The wyntax of the subquery in this expression is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses. When I click ok it takes me to the word Select. Did I miss
something?

Duane Hookom said:
Select [Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases
FROM Registration
GROUP BY [Assigned To], Month([Date Assigned]),
Format([Date Assigned],"mmmm");

Use the above query as the Record Source for your report.

--
Duane Hookom
MS Access MVP
--

I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person doing the
data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with totals for each
month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this report.

Thanks for all of your help. I really appreciate it.






:

Maybe you need to provide some sample records and desired display in
your
report...

--
Duane Hookom
MS Access MVP
--

When I am creating this totals query I have Field as Assigned to,
table
as
Registrants, Total as Group By, and in the Criteria I have put
([assigned
to]="Headquarters"). I then repeat this for the next sub. When I
view
the
Datasheet there is no information pulled. Should I be using
Expression
in
the Totals section?

:

It seems that you could create a totals query that groups by
AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as the record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total for
each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub
station
in
Jan,
Feb, March etc...I do have a field in the query that would allow
for
this
it
is called Date Assigned. I just cannot seem to get the code
right.
Should I
use Between or IIF. Any Suggestions would be wonderful...
Click to expand...
Click to expand...
Click to expand...
 
I removed the x in the show box for the date. I still have several months
that have two totals instead of one though. Should there be something about
when the month is the same that they should be added together to get one
total. Does that require an IIF in the SQL?

Duane Hookom said:
That is not the result of my SQL. Why do you have [Date Assigned]
displaying?

--
Duane Hookom
MS Access MVP
--

atwater said:
I entered the information in the SQL view. It creates the query for me
but
when trying to view the info I get this.
Expr1 MthNum MthName Cases DATE ASSIGNED
HEADQUARTERS 3 March 1 03/26/2005
HEADQUARTERS 3 March 29 03/29/2005
HEADQUARTERS 4 April 1 04/12/2005
HEADQUARTERS 4 April 1 04/14/2005
HEADQUARTERS 4 April 3 04/19/2005
HEADQUATERS 3 March 1 03/29/2005
sub 1 1 January 14 01/19/2005
sub 1 1 January 1 01/20/2005
sub 1 3 March 1 03/24/2005
sub 1 3 March 1 03/29/2005
sub 1 4 April 1 04/12/2005
sub 2 2 February 14 02/07/2005
sub 2 4 April 1 04/01/2005
sub 2 4 April 1 04/12/2005
sub 3 1 January 1 01/20/2005
sub 3 3 March 20 03/24/2005
sub 3 4 April 1 04/01/2005
sub 3 4 April 3 04/12/2005
sub 3 4 April 2 04/19/2005
sub 3 3 March 1 03/24/2005

I entered a Field to ask me for a date range when viewing the information
and it works great. Now I just need ot have the Totals for the month all
in
one entry. How do I change the SQL?

Thanks








When trying to enter Select[Assigned To], Month([Date Assigned]) as
MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases I get an
error message that states The wyntax of the subquery in this expression
is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses. When I click ok it takes me to the word Select. Did I miss
something?

:

Select [Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases
FROM Registration
GROUP BY [Assigned To], Month([Date Assigned]),
Format([Date Assigned],"mmmm");

Use the above query as the Record Source for your report.

--
Duane Hookom
MS Access MVP
--

I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person doing the
data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with totals for
each
month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this report.

Thanks for all of your help. I really appreciate it.






:

Maybe you need to provide some sample records and desired display in
your
report...

--
Duane Hookom
MS Access MVP
--

When I am creating this totals query I have Field as Assigned to,
table
as
Registrants, Total as Group By, and in the Criteria I have put
([assigned
to]="Headquarters"). I then repeat this for the next sub. When I
view
the
Datasheet there is no information pulled. Should I be using
Expression
in
the Totals section?

:

It seems that you could create a totals query that groups by
AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as the
record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total
for
each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub
station
in
Jan,
Feb, March etc...I do have a field in the query that would
allow for
this
it
is called Date Assigned. I just cannot seem to get the code
right.
Should I
use Between or IIF. Any Suggestions would be wonderful...
Click to expand...
Click to expand...
Click to expand...
 
Why don't you just use the sql that I provided?
What is the current SQL view that you are using?

--
Duane Hookom
MS Access MVP


atwater said:
I removed the x in the show box for the date. I still have several months
that have two totals instead of one though. Should there be something
about
when the month is the same that they should be added together to get one
total. Does that require an IIF in the SQL?

Duane Hookom said:
That is not the result of my SQL. Why do you have [Date Assigned]
displaying?

--
Duane Hookom
MS Access MVP
--

atwater said:
I entered the information in the SQL view. It creates the query for me
but
when trying to view the info I get this.
Expr1 MthNum MthName Cases DATE ASSIGNED
HEADQUARTERS 3 March 1 03/26/2005
HEADQUARTERS 3 March 29 03/29/2005
HEADQUARTERS 4 April 1 04/12/2005
HEADQUARTERS 4 April 1 04/14/2005
HEADQUARTERS 4 April 3 04/19/2005
HEADQUATERS 3 March 1 03/29/2005
sub 1 1 January 14 01/19/2005
sub 1 1 January 1 01/20/2005
sub 1 3 March 1 03/24/2005
sub 1 3 March 1 03/29/2005
sub 1 4 April 1 04/12/2005
sub 2 2 February 14 02/07/2005
sub 2 4 April 1 04/01/2005
sub 2 4 April 1 04/12/2005
sub 3 1 January 1 01/20/2005
sub 3 3 March 20 03/24/2005
sub 3 4 April 1 04/01/2005
sub 3 4 April 3 04/12/2005
sub 3 4 April 2 04/19/2005
sub 3 3 March 1 03/24/2005

I entered a Field to ask me for a date range when viewing the
information
and it works great. Now I just need ot have the Totals for the month
all
in
one entry. How do I change the SQL?

Thanks









When trying to enter Select[Assigned To], Month([Date Assigned]) as
MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases I get
an
error message that states The wyntax of the subquery in this
expression
is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses. When I click ok it takes me to the word Select. Did I
miss
something?

:

Select [Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases
FROM Registration
GROUP BY [Assigned To], Month([Date Assigned]),
Format([Date Assigned],"mmmm");

Use the above query as the Record Source for your report.

--
Duane Hookom
MS Access MVP
--

I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person doing
the
data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with totals for
each
month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this report.

Thanks for all of your help. I really appreciate it.






:

Maybe you need to provide some sample records and desired display
in
your
report...

--
Duane Hookom
MS Access MVP
--

When I am creating this totals query I have Field as Assigned
to,
table
as
Registrants, Total as Group By, and in the Criteria I have put
([assigned
to]="Headquarters"). I then repeat this for the next sub.
When I
view
the
Datasheet there is no information pulled. Should I be using
Expression
in
the Totals section?

:

It seems that you could create a totals query that groups by
AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as the
record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total
for
each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub
station
in
Jan,
Feb, March etc...I do have a field in the query that would
allow for
this
it
is called Date Assigned. I just cannot seem to get the code
right.
Should I
use Between or IIF. Any Suggestions would be wonderful...
Click to expand...
Click to expand...
Click to expand...
 
I typed in the SQL exactly as you gave it to me. When I access the sql field
this morining this is what it looks like:


SELECT [SEX REGISTRANTS].[ASSIGNED TO] AS Expr1, Month([Date assigned]) AS
MthNum, Format([Date Assigned],"mmmm") AS MthName, Count(*) AS Cases, [SEX
REGISTRANTS].[DATE ASSIGNED]
FROM [SEX REGISTRANTS]
GROUP BY [SEX REGISTRANTS].[ASSIGNED TO], Month([Date assigned]),
Format([Date Assigned],"mmmm"), [SEX REGISTRANTS].[DATE ASSIGNED]
HAVING ((([SEX REGISTRANTS].[DATE ASSIGNED]) Between [start date] And [end
date]));


This runs wonderful. I get all of the correct information. I just need to
have January, Feb etc all listed one time instead of having it listed like
this:


Expr1 MthNum MthName Cases
HEADQUARTERS 3 March 1
HEADQUARTERS 3 March 29
HEADQUARTERS 4 April 1
HEADQUARTERS 4 April 1
HEADQUARTERS 4 April 3
HEADQUATERS 3 March 1
sub1 1 January 14
sub1 1 January 1
sub1 3 March 1
sub1 3 March 1
sub1 4 April 1
sub2 2 February 14
sub2 4 April 1
sub2 4 April 1
sub3 1 January 1
sub3 3 March 20
sub3 4 April 1
sub3 4 April 3
sub3 4 April 2
sub3 3 March 1

Thanks for your great and patient help.



Duane Hookom said:
Why don't you just use the sql that I provided?
What is the current SQL view that you are using?

--
Duane Hookom
MS Access MVP


atwater said:
I removed the x in the show box for the date. I still have several months
that have two totals instead of one though. Should there be something
about
when the month is the same that they should be added together to get one
total. Does that require an IIF in the SQL?

Duane Hookom said:
That is not the result of my SQL. Why do you have [Date Assigned]
displaying?

--
Duane Hookom
MS Access MVP
--


I entered the information in the SQL view. It creates the query for me
but
when trying to view the info I get this.
Expr1 MthNum MthName Cases DATE ASSIGNED
HEADQUARTERS 3 March 1 03/26/2005
HEADQUARTERS 3 March 29 03/29/2005
HEADQUARTERS 4 April 1 04/12/2005
HEADQUARTERS 4 April 1 04/14/2005
HEADQUARTERS 4 April 3 04/19/2005
HEADQUATERS 3 March 1 03/29/2005
sub 1 1 January 14 01/19/2005
sub 1 1 January 1 01/20/2005
sub 1 3 March 1 03/24/2005
sub 1 3 March 1 03/29/2005
sub 1 4 April 1 04/12/2005
sub 2 2 February 14 02/07/2005
sub 2 4 April 1 04/01/2005
sub 2 4 April 1 04/12/2005
sub 3 1 January 1 01/20/2005
sub 3 3 March 20 03/24/2005
sub 3 4 April 1 04/01/2005
sub 3 4 April 3 04/12/2005
sub 3 4 April 2 04/19/2005
sub 3 3 March 1 03/24/2005

I entered a Field to ask me for a date range when viewing the
information
and it works great. Now I just need ot have the Totals for the month
all
in
one entry. How do I change the SQL?

Thanks









When trying to enter Select[Assigned To], Month([Date Assigned]) as
MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases I get
an
error message that states The wyntax of the subquery in this
expression
is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses. When I click ok it takes me to the word Select. Did I
miss
something?

:

Select [Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases
FROM Registration
GROUP BY [Assigned To], Month([Date Assigned]),
Format([Date Assigned],"mmmm");

Use the above query as the Record Source for your report.

--
Duane Hookom
MS Access MVP
--

I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person doing
the
data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with totals for
each
month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this report.

Thanks for all of your help. I really appreciate it.






:

Maybe you need to provide some sample records and desired display
in
your
report...

--
Duane Hookom
MS Access MVP
--

When I am creating this totals query I have Field as Assigned
to,
table
as
Registrants, Total as Group By, and in the Criteria I have put
([assigned
to]="Headquarters"). I then repeat this for the next sub.
When I
view
the
Datasheet there is no information pulled. Should I be using
Expression
in
the Totals section?

:

It seems that you could create a totals query that groups by
AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as the
record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

I have a report that runs from a query. .

I have used this to sort by sub stations and give me a total
for
each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each sub
station
in
Jan,
Feb, March etc...I do have a field in the query that would
allow for
this
it
is called Date Assigned. I just cannot seem to get the code
right.
Should I
use Between or IIF. Any Suggestions would be wonderful...
Click to expand...
Click to expand...
Click to expand...
 
I don't see the [SEX REGISTRANTS].[DATE ASSIGNED] in my SELECT or GROUP BY
parts of the query.
Try this SQL:

SELECT [ASSIGNED TO],
Month([Date assigned]) AS MthNum,
Format([Date Assigned],"mmmm") AS MthName,
Count(*) AS Cases
FROM [SEX REGISTRANTS]
WHERE [DATE ASSIGNED] Between [start date] And [end date]
GROUP BY [ASSIGNED TO],
Month([Date assigned]),
Format([Date Assigned],"mmmm");

--
Duane Hookom
MS Access MVP
--

atwater said:
I typed in the SQL exactly as you gave it to me. When I access the sql
field
this morining this is what it looks like:


SELECT [SEX REGISTRANTS].[ASSIGNED TO] AS Expr1, Month([Date assigned]) AS
MthNum, Format([Date Assigned],"mmmm") AS MthName, Count(*) AS Cases, [SEX
REGISTRANTS].[DATE ASSIGNED]
FROM [SEX REGISTRANTS]
GROUP BY [SEX REGISTRANTS].[ASSIGNED TO], Month([Date assigned]),
Format([Date Assigned],"mmmm"), [SEX REGISTRANTS].[DATE ASSIGNED]
HAVING ((([SEX REGISTRANTS].[DATE ASSIGNED]) Between [start date] And [end
date]));


This runs wonderful. I get all of the correct information. I just need
to
have January, Feb etc all listed one time instead of having it listed like
this:


Expr1 MthNum MthName Cases
HEADQUARTERS 3 March 1
HEADQUARTERS 3 March 29
HEADQUARTERS 4 April 1
HEADQUARTERS 4 April 1
HEADQUARTERS 4 April 3
HEADQUATERS 3 March 1
sub1 1 January 14
sub1 1 January 1
sub1 3 March 1
sub1 3 March 1
sub1 4 April 1
sub2 2 February 14
sub2 4 April 1
sub2 4 April 1
sub3 1 January 1
sub3 3 March 20
sub3 4 April 1
sub3 4 April 3
sub3 4 April 2
sub3 3 March 1

Thanks for your great and patient help.



Duane Hookom said:
Why don't you just use the sql that I provided?
What is the current SQL view that you are using?

--
Duane Hookom
MS Access MVP


atwater said:
I removed the x in the show box for the date. I still have several
months
that have two totals instead of one though. Should there be something
about
when the month is the same that they should be added together to get
one
total. Does that require an IIF in the SQL?

:

That is not the result of my SQL. Why do you have [Date Assigned]
displaying?

--
Duane Hookom
MS Access MVP
--


I entered the information in the SQL view. It creates the query for
me
but
when trying to view the info I get this.
Expr1 MthNum MthName Cases DATE ASSIGNED
HEADQUARTERS 3 March 1 03/26/2005
HEADQUARTERS 3 March 29 03/29/2005
HEADQUARTERS 4 April 1 04/12/2005
HEADQUARTERS 4 April 1 04/14/2005
HEADQUARTERS 4 April 3 04/19/2005
HEADQUATERS 3 March 1 03/29/2005
sub 1 1 January 14 01/19/2005
sub 1 1 January 1 01/20/2005
sub 1 3 March 1 03/24/2005
sub 1 3 March 1 03/29/2005
sub 1 4 April 1 04/12/2005
sub 2 2 February 14 02/07/2005
sub 2 4 April 1 04/01/2005
sub 2 4 April 1 04/12/2005
sub 3 1 January 1 01/20/2005
sub 3 3 March 20 03/24/2005
sub 3 4 April 1 04/01/2005
sub 3 4 April 3 04/12/2005
sub 3 4 April 2 04/19/2005
sub 3 3 March 1 03/24/2005

I entered a Field to ask me for a date range when viewing the
information
and it works great. Now I just need ot have the Totals for the
month
all
in
one entry. How do I change the SQL?

Thanks









When trying to enter Select[Assigned To], Month([Date Assigned]) as
MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases I
get
an
error message that states The wyntax of the subquery in this
expression
is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses. When I click ok it takes me to the word Select. Did
I
miss
something?

:

Select [Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases
FROM Registration
GROUP BY [Assigned To], Month([Date Assigned]),
Format([Date Assigned],"mmmm");

Use the above query as the Record Source for your report.

--
Duane Hookom
MS Access MVP
--

I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person doing
the
data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY
ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with totals
for
each
month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this report.

Thanks for all of your help. I really appreciate it.






:

Maybe you need to provide some sample records and desired
display
in
your
report...

--
Duane Hookom
MS Access MVP
--

When I am creating this totals query I have Field as
Assigned
to,
table
as
Registrants, Total as Group By, and in the Criteria I have
put
([assigned
to]="Headquarters"). I then repeat this for the next sub.
When I
view
the
Datasheet there is no information pulled. Should I be using
Expression
in
the Totals section?

:

It seems that you could create a totals query that groups
by
AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as
the
record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

message
I have a report that runs from a query. .

I have used this to sort by sub stations and give me a
total
for
each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each
sub
station
in
Jan,
Feb, March etc...I do have a field in the query that
would
allow for
this
it
is called Date Assigned. I just cannot seem to get the
code
right.
Should I
use Between or IIF. Any Suggestions would be
wonderful...
Click to expand...
Click to expand...
Click to expand...
 
I cannot say THANK YOU enough. My headache is gone. It works absolutly
perfect!!

Duane Hookom said:
I don't see the [SEX REGISTRANTS].[DATE ASSIGNED] in my SELECT or GROUP BY
parts of the query.
Try this SQL:

SELECT [ASSIGNED TO],
Month([Date assigned]) AS MthNum,
Format([Date Assigned],"mmmm") AS MthName,
Count(*) AS Cases
FROM [SEX REGISTRANTS]
WHERE [DATE ASSIGNED] Between [start date] And [end date]
GROUP BY [ASSIGNED TO],
Month([Date assigned]),
Format([Date Assigned],"mmmm");

--
Duane Hookom
MS Access MVP
--

atwater said:
I typed in the SQL exactly as you gave it to me. When I access the sql
field
this morining this is what it looks like:


SELECT [SEX REGISTRANTS].[ASSIGNED TO] AS Expr1, Month([Date assigned]) AS
MthNum, Format([Date Assigned],"mmmm") AS MthName, Count(*) AS Cases, [SEX
REGISTRANTS].[DATE ASSIGNED]
FROM [SEX REGISTRANTS]
GROUP BY [SEX REGISTRANTS].[ASSIGNED TO], Month([Date assigned]),
Format([Date Assigned],"mmmm"), [SEX REGISTRANTS].[DATE ASSIGNED]
HAVING ((([SEX REGISTRANTS].[DATE ASSIGNED]) Between [start date] And [end
date]));


This runs wonderful. I get all of the correct information. I just need
to
have January, Feb etc all listed one time instead of having it listed like
this:


Expr1 MthNum MthName Cases
HEADQUARTERS 3 March 1
HEADQUARTERS 3 March 29
HEADQUARTERS 4 April 1
HEADQUARTERS 4 April 1
HEADQUARTERS 4 April 3
HEADQUATERS 3 March 1
sub1 1 January 14
sub1 1 January 1
sub1 3 March 1
sub1 3 March 1
sub1 4 April 1
sub2 2 February 14
sub2 4 April 1
sub2 4 April 1
sub3 1 January 1
sub3 3 March 20
sub3 4 April 1
sub3 4 April 3
sub3 4 April 2
sub3 3 March 1

Thanks for your great and patient help.



Duane Hookom said:
Why don't you just use the sql that I provided?
What is the current SQL view that you are using?

--
Duane Hookom
MS Access MVP


I removed the x in the show box for the date. I still have several
months
that have two totals instead of one though. Should there be something
about
when the month is the same that they should be added together to get
one
total. Does that require an IIF in the SQL?

:

That is not the result of my SQL. Why do you have [Date Assigned]
displaying?

--
Duane Hookom
MS Access MVP
--


I entered the information in the SQL view. It creates the query for
me
but
when trying to view the info I get this.
Expr1 MthNum MthName Cases DATE ASSIGNED
HEADQUARTERS 3 March 1 03/26/2005
HEADQUARTERS 3 March 29 03/29/2005
HEADQUARTERS 4 April 1 04/12/2005
HEADQUARTERS 4 April 1 04/14/2005
HEADQUARTERS 4 April 3 04/19/2005
HEADQUATERS 3 March 1 03/29/2005
sub 1 1 January 14 01/19/2005
sub 1 1 January 1 01/20/2005
sub 1 3 March 1 03/24/2005
sub 1 3 March 1 03/29/2005
sub 1 4 April 1 04/12/2005
sub 2 2 February 14 02/07/2005
sub 2 4 April 1 04/01/2005
sub 2 4 April 1 04/12/2005
sub 3 1 January 1 01/20/2005
sub 3 3 March 20 03/24/2005
sub 3 4 April 1 04/01/2005
sub 3 4 April 3 04/12/2005
sub 3 4 April 2 04/19/2005
sub 3 3 March 1 03/24/2005

I entered a Field to ask me for a date range when viewing the
information
and it works great. Now I just need ot have the Totals for the
month
all
in
one entry. How do I change the SQL?

Thanks









When trying to enter Select[Assigned To], Month([Date Assigned]) as
MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases I
get
an
error message that states The wyntax of the subquery in this
expression
is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses. When I click ok it takes me to the word Select. Did
I
miss
something?

:

Select [Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases
FROM Registration
GROUP BY [Assigned To], Month([Date Assigned]),
Format([Date Assigned],"mmmm");

Use the above query as the Record Source for your report.

--
Duane Hookom
MS Access MVP
--

I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person doing
the
data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY
ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with totals
for
each
month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this report.

Thanks for all of your help. I really appreciate it.






:

Maybe you need to provide some sample records and desired
display
in
your
report...

--
Duane Hookom
MS Access MVP
--

When I am creating this totals query I have Field as
Assigned
to,
table
as
Registrants, Total as Group By, and in the Criteria I have
put
([assigned
to]="Headquarters"). I then repeat this for the next sub.
When I
view
the
Datasheet there is no information pulled. Should I be using
Expression
in
the Totals section?

:

It seems that you could create a totals query that groups
by
AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as
the
record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

message
I have a report that runs from a query. .

I have used this to sort by sub stations and give me a
total
for
each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to each
sub
station
in
Click to expand...
Click to expand...
Click to expand...
 
What kind of classes are you offering? What do you have to do to get a
"date" assigned?
BTW: I don't go for the "GROUP BY" or anything like that :-)

--
Duane Hookom
MS Access MVP


atwater said:
I cannot say THANK YOU enough. My headache is gone. It works absolutly
perfect!!

Duane Hookom said:
I don't see the [SEX REGISTRANTS].[DATE ASSIGNED] in my SELECT or GROUP
BY
parts of the query.
Try this SQL:

SELECT [ASSIGNED TO],
Month([Date assigned]) AS MthNum,
Format([Date Assigned],"mmmm") AS MthName,
Count(*) AS Cases
FROM [SEX REGISTRANTS]
WHERE [DATE ASSIGNED] Between [start date] And [end date]
GROUP BY [ASSIGNED TO],
Month([Date assigned]),
Format([Date Assigned],"mmmm");

--
Duane Hookom
MS Access MVP
--

atwater said:
I typed in the SQL exactly as you gave it to me. When I access the sql
field
this morining this is what it looks like:


SELECT [SEX REGISTRANTS].[ASSIGNED TO] AS Expr1, Month([Date assigned])
AS
MthNum, Format([Date Assigned],"mmmm") AS MthName, Count(*) AS Cases,
[SEX
REGISTRANTS].[DATE ASSIGNED]
FROM [SEX REGISTRANTS]
GROUP BY [SEX REGISTRANTS].[ASSIGNED TO], Month([Date assigned]),
Format([Date Assigned],"mmmm"), [SEX REGISTRANTS].[DATE ASSIGNED]
HAVING ((([SEX REGISTRANTS].[DATE ASSIGNED]) Between [start date] And
[end
date]));


This runs wonderful. I get all of the correct information. I just
need
to
have January, Feb etc all listed one time instead of having it listed
like
this:


Expr1 MthNum MthName Cases
HEADQUARTERS 3 March 1
HEADQUARTERS 3 March 29
HEADQUARTERS 4 April 1
HEADQUARTERS 4 April 1
HEADQUARTERS 4 April 3
HEADQUATERS 3 March 1
sub1 1 January 14
sub1 1 January 1
sub1 3 March 1
sub1 3 March 1
sub1 4 April 1
sub2 2 February 14
sub2 4 April 1
sub2 4 April 1
sub3 1 January 1
sub3 3 March 20
sub3 4 April 1
sub3 4 April 3
sub3 4 April 2
sub3 3 March 1

Thanks for your great and patient help.



:

Why don't you just use the sql that I provided?
What is the current SQL view that you are using?

--
Duane Hookom
MS Access MVP


I removed the x in the show box for the date. I still have several
months
that have two totals instead of one though. Should there be
something
about
when the month is the same that they should be added together to get
one
total. Does that require an IIF in the SQL?

:

That is not the result of my SQL. Why do you have [Date Assigned]
displaying?

--
Duane Hookom
MS Access MVP
--


I entered the information in the SQL view. It creates the query
for
me
but
when trying to view the info I get this.
Expr1 MthNum MthName Cases DATE ASSIGNED
HEADQUARTERS 3 March 1 03/26/2005
HEADQUARTERS 3 March 29 03/29/2005
HEADQUARTERS 4 April 1 04/12/2005
HEADQUARTERS 4 April 1 04/14/2005
HEADQUARTERS 4 April 3 04/19/2005
HEADQUATERS 3 March 1 03/29/2005
sub 1 1 January 14 01/19/2005
sub 1 1 January 1 01/20/2005
sub 1 3 March 1 03/24/2005
sub 1 3 March 1 03/29/2005
sub 1 4 April 1 04/12/2005
sub 2 2 February 14 02/07/2005
sub 2 4 April 1 04/01/2005
sub 2 4 April 1 04/12/2005
sub 3 1 January 1 01/20/2005
sub 3 3 March 20 03/24/2005
sub 3 4 April 1 04/01/2005
sub 3 4 April 3 04/12/2005
sub 3 4 April 2 04/19/2005
sub 3 3 March 1 03/24/2005

I entered a Field to ask me for a date range when viewing the
information
and it works great. Now I just need ot have the Totals for the
month
all
in
one entry. How do I change the SQL?

Thanks









When trying to enter Select[Assigned To], Month([Date Assigned])
as
MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases
I
get
an
error message that states The wyntax of the subquery in this
expression
is
incorrect. Check the subquery's syntax and enclose the subquery
in
parentheses. When I click ok it takes me to the word Select.
Did
I
miss
something?

:

Select [Assigned To], Month([Date Assigned]) as MthNum,
Format([Date Assigned],"mmmm") as MthName, Count(*) as Cases
FROM Registration
GROUP BY [Assigned To], Month([Date Assigned]),
Format([Date Assigned],"mmmm");

Use the above query as the Record Source for your report.

--
Duane Hookom
MS Access MVP
--

I am looking for the following look in the report

Total Cases 10

assigned to HQ 3
January 1
Feb 1
March 1

Assigned to Other Station 4
January 2
April 2


Assigned to Third Station 18

January 15
March 3


In the query I that the report is based on:

Field: Assigned to
Table: Registration
Show is marked.

the Field: Assigned to is a drop down box that the person
doing
the
data
entry can choose the location that the case is assigned to.

Field: Date Assigned
Table: Registration
Show is marked.

the Field Date Assigned is a short date format.

The query that the report is based on Pulls the following:
LAST NAME FIRST NAME MIDDLE NAME
ADDRESS CITY COUNTY
ZIP
ORI ASSIGNED TO DATE ASSIGNED REASSIGNED TO
DATE REASSIGNED TO DET CASE STATUS

I only need to have the assigned to (sorted by sub with
totals
for
each
month)
Reassigned to(sorted by name with totals for each month).

The rest of the information is not necessary for this
report.

Thanks for all of your help. I really appreciate it.






:

Maybe you need to provide some sample records and desired
display
in
your
report...

--
Duane Hookom
MS Access MVP
--

message
When I am creating this totals query I have Field as
Assigned
to,
table
as
Registrants, Total as Group By, and in the Criteria I
have
put
([assigned
to]="Headquarters"). I then repeat this for the next
sub.
When I
view
the
Datasheet there is no information pulled. Should I be
using
Expression
in
the Totals section?

:

It seems that you could create a totals query that
groups
by
AssignedTo
and
Format([Date Assigned],"yyyymm"). Then use this query as
the
record
source
for a subreport.

--
Duane Hookom
MS Access MVP
--

message
I have a report that runs from a query. .

I have used this to sort by sub stations and give me a
total
for
each
sub
and it works great.
=Abs(Sum([assigned to]="Headquarters"))
Now I need to have how many cases were assigned to
each
sub
station
in
Click to expand...
Click to expand...
Click to expand...
 
Back
Top