Counting Values in a Field if they are different.

[david.isaacks]

I have a field for Username and I want to run a report that displays
each user name with the number of times is apprears in the field. I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300 different
usernames and I don't want to have to code each txt box. Is thier a way
I can run a report which lists the usersnames and the number of times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[Duane Hookom]

Create a totals query and use it as the record source of your report or a
subreport.

 

[david.isaacks]

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And ([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to work. Is this
what you meant by a totals query or am I doing something else?

I would like to put this in a time series down to 15 minute gaps from
7AM to 7PM. Possible?

Many Thanks - David

Create a totals query and use it as the record source of your report or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report that displays
each user name with the number of times is apprears in the field. I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300 different
usernames and I don't want to have to code each txt box. Is thier a way
I can run a report which lists the usersnames and the number of times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[Duane Hookom]

You may need to start over. I don't have a clue what you have, what you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And ([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to work. Is this
what you meant by a totals query or am I doing something else?

I would like to put this in a time series down to 15 minute gaps from
7AM to 7PM. Possible?

Many Thanks - David

Create a totals query and use it as the record source of your report or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report that displays
each user name with the number of times is apprears in the field. I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300 different
usernames and I don't want to have to code each txt box. Is thier a way
I can run a report which lists the usersnames and the number of times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[david.isaacks]

Ok, thanks for working with me.

I have a table called [tbl_HRC CRM Output FY06] with the following
fields.

Owner, Opened

The owner is a text field with a username in it. Example "vahrchallp"

Opened is a date and time field.

I want to be able to run a query where I have listed for a month the
different days

Owners Day1 Day2 Day3
vahrchallp 13(Cout of how many times this user appreas
during that date) is the owner field)

I also want to be able to do it for a specific day in 15 minute
incremements.

David

You may need to start over. I don't have a clue what you have, what you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And ([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to work. Is this
what you meant by a totals query or am I doing something else?

I would like to put this in a time series down to 15 minute gaps from
7AM to 7PM. Possible?

Many Thanks - David

Create a totals query and use it as the record source of your report or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report that displays
each user name with the number of times is apprears in the field. I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300 different
usernames and I don't want to have to code each txt box. Is thier a way
I can run a report which lists the usersnames and the number of times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[david.isaacks]

I have been playing around with the crosstab query, but I am not sure I
am in the right direction.

Dave

You may need to start over. I don't have a clue what you have, what you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And ([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to work. Is this
what you meant by a totals query or am I doing something else?

I would like to put this in a time series down to 15 minute gaps from
7AM to 7PM. Possible?

Many Thanks - David

Create a totals query and use it as the record source of your report or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report that displays
each user name with the number of times is apprears in the field. I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300 different
usernames and I don't want to have to code each txt box. Is thier a way
I can run a report which lists the usersnames and the number of times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[Duane Hookom]

A crosstab should work. Set the column heading field to
"D" & Day([Opened])
and set the Column Headings property to: "D1","D2","D3","D4",..."D31"
Make sure you only return a month of data or March 1st will be totaled with
April 1st etc.

--
Duane Hookom
MS Access MVP

I have been playing around with the crosstab query, but I am not sure I
am in the right direction.

Dave

You may need to start over. I don't have a clue what you have, what you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And ([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to work. Is this
what you meant by a totals query or am I doing something else?

I would like to put this in a time series down to 15 minute gaps from
7AM to 7PM. Possible?

Many Thanks - David


Duane Hookom wrote:
Create a totals query and use it as the record source of your report
or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report that displays
each user name with the number of times is apprears in the field. I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300 different
usernames and I don't want to have to code each txt box. Is thier a
way
I can run a report which lists the usersnames and the number of
times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[david.isaacks]

Do you know how I could get it down to 15 minute intervals. Starting at
5:30 and ending at 7PM?

I got it down to the a specific date, but have not made it any further.


Dave

A crosstab should work. Set the column heading field to
"D" & Day([Opened])
and set the Column Headings property to: "D1","D2","D3","D4",..."D31"
Make sure you only return a month of data or March 1st will be totaled with
April 1st etc.

--
Duane Hookom
MS Access MVP

I have been playing around with the crosstab query, but I am not sure I
am in the right direction.

Dave

You may need to start over. I don't have a clue what you have, what you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And ([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to work. Is this
what you meant by a totals query or am I doing something else?

I would like to put this in a time series down to 15 minute gaps from
7AM to 7PM. Possible?

Many Thanks - David


Duane Hookom wrote:
Create a totals query and use it as the record source of your report
or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report that displays
each user name with the number of times is apprears in the field. I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300 different
usernames and I don't want to have to code each txt box. Is thier a
way
I can run a report which lists the usersnames and the number of
times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[Duane Hookom]

You can get times rounded to the nearest 15 minute interval with an
expression like:
CDate(CInt([TimeFieldValue] * 96)/96)
If this is column heading in a crosstab query I would use and expression
like
"T" & CInt([TimeFieldValue] * 96)
Then set your Column Headings property to:
"T22","T23","T24","T25"...,"T76"

--
Duane Hookom
MS Access MVP

Do you know how I could get it down to 15 minute intervals. Starting at
5:30 and ending at 7PM?

I got it down to the a specific date, but have not made it any further.


Dave

A crosstab should work. Set the column heading field to
"D" & Day([Opened])
and set the Column Headings property to: "D1","D2","D3","D4",..."D31"
Make sure you only return a month of data or March 1st will be totaled
with
April 1st etc.

--
Duane Hookom
MS Access MVP

I have been playing around with the crosstab query, but I am not sure I
am in the right direction.

Dave

Duane Hookom wrote:
You may need to start over. I don't have a clue what you have, what
you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And ([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to work. Is
this
what you meant by a totals query or am I doing something else?

I would like to put this in a time series down to 15 minute gaps
from
7AM to 7PM. Possible?

Many Thanks - David


Duane Hookom wrote:
Create a totals query and use it as the record source of your
report
or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report that
displays
each user name with the number of times is apprears in the field.
I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300 different
usernames and I don't want to have to code each txt box. Is thier
a
way
I can run a report which lists the usersnames and the number of
times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[david.isaacks]

Duane thanks for working with me.

I keep getting a "Overflow" error, so something I must be doing is
wrong.

THe Field name with the dates and times is called [Opened] & the format
is this "5/26/2006 6:04:00 AM".

If I use the first expression: CDate(CInt([TimeFieldValue] * 96)/96) do
I use that in a corss tab query or where you just telling me that this
was a way to round to the nearest 15 minute interval?

I guess I want to group all the times by a 15 miute interval (for a
day) so that I can count them. Maybe I am putting the expression in the
worng place. I am putting it under the opened field.

The second expression you gave me gives me the same error. However I am
having trouble understanding where I set the column heading properties.
When I pull up the properties to the column header is only gives me on
box.

This is my SQL for 5/26/2006 with the Opened field as a Row Heading.
TRANSFORM Count([qryTotals_5/26/06_HB].Type) AS CountOfType
SELECT [qryTotals_5/26/06_HB].Opened,
Count([qryTotals_5/26/06_HB].Type) AS [Total Of Type]
FROM [qryTotals_5/26/06_HB]
GROUP BY [qryTotals_5/26/06_HB].Opened
PIVOT [qryTotals_5/26/06_HB].Owner;

If I put Opened as the column heading it give me an error like, to
complicated or cannot.

thanks,
David

You can get times rounded to the nearest 15 minute interval with an
expression like:
CDate(CInt([TimeFieldValue] * 96)/96)
If this is column heading in a crosstab query I would use and expression
like
"T" & CInt([TimeFieldValue] * 96)
Then set your Column Headings property to:
"T22","T23","T24","T25"...,"T76"

--
Duane Hookom
MS Access MVP

Do you know how I could get it down to 15 minute intervals. Starting at
5:30 and ending at 7PM?

I got it down to the a specific date, but have not made it any further.


Dave

A crosstab should work. Set the column heading field to
"D" & Day([Opened])
and set the Column Headings property to: "D1","D2","D3","D4",..."D31"
Make sure you only return a month of data or March 1st will be totaled
with
April 1st etc.

--
Duane Hookom
MS Access MVP

I have been playing around with the crosstab query, but I am not sure I
am in the right direction.

Dave

Duane Hookom wrote:
You may need to start over. I don't have a clue what you have, what
you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And ([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to work. Is
this
what you meant by a totals query or am I doing something else?

I would like to put this in a time series down to 15 minute gaps
from
7AM to 7PM. Possible?

Many Thanks - David


Duane Hookom wrote:
Create a totals query and use it as the record source of your
report
or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report that
displays
each user name with the number of times is apprears in the field.
I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300 different
usernames and I don't want to have to code each txt box. Is thier
a
way
I can run a report which lists the usersnames and the number of
times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[Duane Hookom]

I would use
"T" & CInt(TimeValue([TimeFieldValue]) * 96)
As the column heading of a crosstab query.
or
CDate(CInt([TimeFieldValue] * 96)/96)
as a Row Heading

--
Duane Hookom
MS Access MVP

Duane thanks for working with me.

I keep getting a "Overflow" error, so something I must be doing is
wrong.

THe Field name with the dates and times is called [Opened] & the format
is this "5/26/2006 6:04:00 AM".

If I use the first expression: CDate(CInt([TimeFieldValue] * 96)/96) do
I use that in a corss tab query or where you just telling me that this
was a way to round to the nearest 15 minute interval?

I guess I want to group all the times by a 15 miute interval (for a
day) so that I can count them. Maybe I am putting the expression in the
worng place. I am putting it under the opened field.

The second expression you gave me gives me the same error. However I am
having trouble understanding where I set the column heading properties.
When I pull up the properties to the column header is only gives me on
box.

This is my SQL for 5/26/2006 with the Opened field as a Row Heading.
TRANSFORM Count([qryTotals_5/26/06_HB].Type) AS CountOfType
SELECT [qryTotals_5/26/06_HB].Opened,
Count([qryTotals_5/26/06_HB].Type) AS [Total Of Type]
FROM [qryTotals_5/26/06_HB]
GROUP BY [qryTotals_5/26/06_HB].Opened
PIVOT [qryTotals_5/26/06_HB].Owner;

If I put Opened as the column heading it give me an error like, to
complicated or cannot.

thanks,
David

You can get times rounded to the nearest 15 minute interval with an
expression like:
CDate(CInt([TimeFieldValue] * 96)/96)
If this is column heading in a crosstab query I would use and expression
like
"T" & CInt([TimeFieldValue] * 96)
Then set your Column Headings property to:
"T22","T23","T24","T25"...,"T76"

--
Duane Hookom
MS Access MVP

Do you know how I could get it down to 15 minute intervals. Starting at
5:30 and ending at 7PM?

I got it down to the a specific date, but have not made it any further.


Dave


Duane Hookom wrote:
A crosstab should work. Set the column heading field to
"D" & Day([Opened])
and set the Column Headings property to: "D1","D2","D3","D4",..."D31"
Make sure you only return a month of data or March 1st will be totaled
with
April 1st etc.

--
Duane Hookom
MS Access MVP

I have been playing around with the crosstab query, but I am not sure
I
am in the right direction.

Dave

Duane Hookom wrote:
You may need to start over. I don't have a clue what you have, what
you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And
([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to work.
Is
this
what you meant by a totals query or am I doing something else?

I would like to put this in a time series down to 15 minute gaps
from
7AM to 7PM. Possible?

Many Thanks - David


Duane Hookom wrote:
Create a totals query and use it as the record source of your
report
or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report that
displays
each user name with the number of times is apprears in the
field.
I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300
different
usernames and I don't want to have to code each txt box. Is
thier
a
way
I can run a report which lists the usersnames and the number
of
times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[david.isaacks]

I just cannot seem to get either way to work. :-(

thanks for your time. David

I would use
"T" & CInt(TimeValue([TimeFieldValue]) * 96)
As the column heading of a crosstab query.
or
CDate(CInt([TimeFieldValue] * 96)/96)
as a Row Heading

--
Duane Hookom
MS Access MVP

Duane thanks for working with me.

I keep getting a "Overflow" error, so something I must be doing is
wrong.

THe Field name with the dates and times is called [Opened] & the format
is this "5/26/2006 6:04:00 AM".

If I use the first expression: CDate(CInt([TimeFieldValue] * 96)/96) do
I use that in a corss tab query or where you just telling me that this
was a way to round to the nearest 15 minute interval?

I guess I want to group all the times by a 15 miute interval (for a
day) so that I can count them. Maybe I am putting the expression in the
worng place. I am putting it under the opened field.

The second expression you gave me gives me the same error. However I am
having trouble understanding where I set the column heading properties.
When I pull up the properties to the column header is only gives me on
box.

This is my SQL for 5/26/2006 with the Opened field as a Row Heading.
TRANSFORM Count([qryTotals_5/26/06_HB].Type) AS CountOfType
SELECT [qryTotals_5/26/06_HB].Opened,
Count([qryTotals_5/26/06_HB].Type) AS [Total Of Type]
FROM [qryTotals_5/26/06_HB]
GROUP BY [qryTotals_5/26/06_HB].Opened
PIVOT [qryTotals_5/26/06_HB].Owner;

If I put Opened as the column heading it give me an error like, to
complicated or cannot.

thanks,
David

You can get times rounded to the nearest 15 minute interval with an
expression like:
CDate(CInt([TimeFieldValue] * 96)/96)
If this is column heading in a crosstab query I would use and expression
like
"T" & CInt([TimeFieldValue] * 96)
Then set your Column Headings property to:
"T22","T23","T24","T25"...,"T76"

--
Duane Hookom
MS Access MVP

Do you know how I could get it down to 15 minute intervals. Starting at
5:30 and ending at 7PM?

I got it down to the a specific date, but have not made it any further.


Dave


Duane Hookom wrote:
A crosstab should work. Set the column heading field to
"D" & Day([Opened])
and set the Column Headings property to: "D1","D2","D3","D4",..."D31"
Make sure you only return a month of data or March 1st will be totaled
with
April 1st etc.

--
Duane Hookom
MS Access MVP

I have been playing around with the crosstab query, but I am not sure
I
am in the right direction.

Dave

Duane Hookom wrote:
You may need to start over. I don't have a clue what you have, what
you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And
([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to work.
Is
this
what you meant by a totals query or am I doing something else?

I would like to put this in a time series down to 15 minute gaps
from
7AM to 7PM. Possible?

Many Thanks - David


Duane Hookom wrote:
Create a totals query and use it as the record source of your
report
or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report that
displays
each user name with the number of times is apprears in the
field.
I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300
different
usernames and I don't want to have to code each txt box. Is
thier
a
way
I can run a report which lists the usersnames and the number
of
times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[david.isaacks]

Just to confirm, where do I put these expressions?

I would use
"T" & CInt(TimeValue([TimeFieldValue]) * 96)
As the column heading of a crosstab query.
or
CDate(CInt([TimeFieldValue] * 96)/96)
as a Row Heading

--
Duane Hookom
MS Access MVP

Duane thanks for working with me.

I keep getting a "Overflow" error, so something I must be doing is
wrong.

THe Field name with the dates and times is called [Opened] & the format
is this "5/26/2006 6:04:00 AM".

If I use the first expression: CDate(CInt([TimeFieldValue] * 96)/96) do
I use that in a corss tab query or where you just telling me that this
was a way to round to the nearest 15 minute interval?

I guess I want to group all the times by a 15 miute interval (for a
day) so that I can count them. Maybe I am putting the expression in the
worng place. I am putting it under the opened field.

The second expression you gave me gives me the same error. However I am
having trouble understanding where I set the column heading properties.
When I pull up the properties to the column header is only gives me on
box.

This is my SQL for 5/26/2006 with the Opened field as a Row Heading.
TRANSFORM Count([qryTotals_5/26/06_HB].Type) AS CountOfType
SELECT [qryTotals_5/26/06_HB].Opened,
Count([qryTotals_5/26/06_HB].Type) AS [Total Of Type]
FROM [qryTotals_5/26/06_HB]
GROUP BY [qryTotals_5/26/06_HB].Opened
PIVOT [qryTotals_5/26/06_HB].Owner;

If I put Opened as the column heading it give me an error like, to
complicated or cannot.

thanks,
David

You can get times rounded to the nearest 15 minute interval with an
expression like:
CDate(CInt([TimeFieldValue] * 96)/96)
If this is column heading in a crosstab query I would use and expression
like
"T" & CInt([TimeFieldValue] * 96)
Then set your Column Headings property to:
"T22","T23","T24","T25"...,"T76"

--
Duane Hookom
MS Access MVP

Do you know how I could get it down to 15 minute intervals. Starting at
5:30 and ending at 7PM?

I got it down to the a specific date, but have not made it any further.


Dave


Duane Hookom wrote:
A crosstab should work. Set the column heading field to
"D" & Day([Opened])
and set the Column Headings property to: "D1","D2","D3","D4",..."D31"
Make sure you only return a month of data or March 1st will be totaled
with
April 1st etc.

--
Duane Hookom
MS Access MVP

I have been playing around with the crosstab query, but I am not sure
I
am in the right direction.

Dave

Duane Hookom wrote:
You may need to start over. I don't have a clue what you have, what
you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And
([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to work.
Is
this
what you meant by a totals query or am I doing something else?

I would like to put this in a time series down to 15 minute gaps
from
7AM to 7PM. Possible?

Many Thanks - David


Duane Hookom wrote:
Create a totals query and use it as the record source of your
report
or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report that
displays
each user name with the number of times is apprears in the
field.
I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300
different
usernames and I don't want to have to code each txt box. Is
thier
a
way
I can run a report which lists the usersnames and the number
of
times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[Duane Hookom]

Where do you want them? Are they column headings or row headings?
--
Duane Hookom
MS Access MVP

Just to confirm, where do I put these expressions?

I would use
"T" & CInt(TimeValue([TimeFieldValue]) * 96)
As the column heading of a crosstab query.
or
CDate(CInt([TimeFieldValue] * 96)/96)
as a Row Heading

--
Duane Hookom
MS Access MVP

Duane thanks for working with me.

I keep getting a "Overflow" error, so something I must be doing is
wrong.

THe Field name with the dates and times is called [Opened] & the format
is this "5/26/2006 6:04:00 AM".

If I use the first expression: CDate(CInt([TimeFieldValue] * 96)/96) do
I use that in a corss tab query or where you just telling me that this
was a way to round to the nearest 15 minute interval?

I guess I want to group all the times by a 15 miute interval (for a
day) so that I can count them. Maybe I am putting the expression in the
worng place. I am putting it under the opened field.

The second expression you gave me gives me the same error. However I am
having trouble understanding where I set the column heading properties.
When I pull up the properties to the column header is only gives me on
box.

This is my SQL for 5/26/2006 with the Opened field as a Row Heading.
TRANSFORM Count([qryTotals_5/26/06_HB].Type) AS CountOfType
SELECT [qryTotals_5/26/06_HB].Opened,
Count([qryTotals_5/26/06_HB].Type) AS [Total Of Type]
FROM [qryTotals_5/26/06_HB]
GROUP BY [qryTotals_5/26/06_HB].Opened
PIVOT [qryTotals_5/26/06_HB].Owner;

If I put Opened as the column heading it give me an error like, to
complicated or cannot.

thanks,
David

Duane Hookom wrote:
You can get times rounded to the nearest 15 minute interval with an
expression like:
CDate(CInt([TimeFieldValue] * 96)/96)
If this is column heading in a crosstab query I would use and
expression
like
"T" & CInt([TimeFieldValue] * 96)
Then set your Column Headings property to:
"T22","T23","T24","T25"...,"T76"

--
Duane Hookom
MS Access MVP

Do you know how I could get it down to 15 minute intervals. Starting
at
5:30 and ending at 7PM?

I got it down to the a specific date, but have not made it any
further.


Dave


Duane Hookom wrote:
A crosstab should work. Set the column heading field to
"D" & Day([Opened])
and set the Column Headings property to:
"D1","D2","D3","D4",..."D31"
Make sure you only return a month of data or March 1st will be
totaled
with
April 1st etc.

--
Duane Hookom
MS Access MVP

I have been playing around with the crosstab query, but I am not
sure
I
am in the right direction.

Dave

Duane Hookom wrote:
You may need to start over. I don't have a clue what you have,
what
you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM
Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And
([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to
work.
Is
this
what you meant by a totals query or am I doing something else?

I would like to put this in a time series down to 15 minute
gaps
from
7AM to 7PM. Possible?

Many Thanks - David


Duane Hookom wrote:
Create a totals query and use it as the record source of your
report
or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report that
displays
each user name with the number of times is apprears in the
field.
I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300
different
usernames and I don't want to have to code each txt box. Is
thier
a
way
I can run a report which lists the usersnames and the
number
of
times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[david.isaacks]

Duane,

I want the column headings to be the 15 minute increments and the first
row to be the count of how many time a date&time fall within those 15
minute increments to include Null returns.

Dave

Where do you want them? Are they column headings or row headings?
--
Duane Hookom
MS Access MVP

Just to confirm, where do I put these expressions?

I would use
"T" & CInt(TimeValue([TimeFieldValue]) * 96)
As the column heading of a crosstab query.
or
CDate(CInt([TimeFieldValue] * 96)/96)
as a Row Heading

--
Duane Hookom
MS Access MVP



Duane thanks for working with me.

I keep getting a "Overflow" error, so something I must be doing is
wrong.

THe Field name with the dates and times is called [Opened] & the format
is this "5/26/2006 6:04:00 AM".

If I use the first expression: CDate(CInt([TimeFieldValue] * 96)/96) do
I use that in a corss tab query or where you just telling me that this
was a way to round to the nearest 15 minute interval?

I guess I want to group all the times by a 15 miute interval (for a
day) so that I can count them. Maybe I am putting the expression in the
worng place. I am putting it under the opened field.

The second expression you gave me gives me the same error. However I am
having trouble understanding where I set the column heading properties.
When I pull up the properties to the column header is only gives me on
box.

This is my SQL for 5/26/2006 with the Opened field as a Row Heading.
TRANSFORM Count([qryTotals_5/26/06_HB].Type) AS CountOfType
SELECT [qryTotals_5/26/06_HB].Opened,
Count([qryTotals_5/26/06_HB].Type) AS [Total Of Type]
FROM [qryTotals_5/26/06_HB]
GROUP BY [qryTotals_5/26/06_HB].Opened
PIVOT [qryTotals_5/26/06_HB].Owner;

If I put Opened as the column heading it give me an error like, to
complicated or cannot.

thanks,
David

Duane Hookom wrote:
You can get times rounded to the nearest 15 minute interval with an
expression like:
CDate(CInt([TimeFieldValue] * 96)/96)
If this is column heading in a crosstab query I would use and
expression
like
"T" & CInt([TimeFieldValue] * 96)
Then set your Column Headings property to:
"T22","T23","T24","T25"...,"T76"

--
Duane Hookom
MS Access MVP

Do you know how I could get it down to 15 minute intervals. Starting
at
5:30 and ending at 7PM?

I got it down to the a specific date, but have not made it any
further.


Dave


Duane Hookom wrote:
A crosstab should work. Set the column heading field to
"D" & Day([Opened])
and set the Column Headings property to:
"D1","D2","D3","D4",..."D31"
Make sure you only return a month of data or March 1st will be
totaled
with
April 1st etc.

--
Duane Hookom
MS Access MVP

I have been playing around with the crosstab query, but I am not
sure
I
am in the right direction.

Dave

Duane Hookom wrote:
You may need to start over. I don't have a clue what you have,
what
you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM
Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And
([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to
work.
Is
this
what you meant by a totals query or am I doing something else?

I would like to put this in a time series down to 15 minute
gaps
from
7AM to 7PM. Possible?

Many Thanks - David


Duane Hookom wrote:
Create a totals query and use it as the record source of your
report
or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report that
displays
each user name with the number of times is apprears in the
field.
I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300
different
usernames and I don't want to have to code each txt box. Is
thier
a
way
I can run a report which lists the usersnames and the
number
of
times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[Duane Hookom]

It isn't clear what you want for Row Headings. If you want Owner as the Row
Heading, try this SQL and type in more "T" values in the PIVOT section:

TRANSFORM Count([qryTotals_5/26/06_HB].Type) AS CountOfType
SELECT [qryTotals_5/26/06_HB].Owner,
Count([qryTotals_5/26/06_HB].Type) AS [Total Of Type]
FROM [qryTotals_5/26/06_HB]
GROUP BY [qryTotals_5/26/06_HB].Owner
PIVOT "T" & CInt(TimeValue([TimeFieldValue]) * 96)
IN ( "T22","T23","T24","T25"...,"T76");

--
Duane Hookom
MS Access MVP

Duane,

I want the column headings to be the 15 minute increments and the first
row to be the count of how many time a date&time fall within those 15
minute increments to include Null returns.

Dave

Where do you want them? Are they column headings or row headings?
--
Duane Hookom
MS Access MVP

Just to confirm, where do I put these expressions?



Duane Hookom wrote:
I would use
"T" & CInt(TimeValue([TimeFieldValue]) * 96)
As the column heading of a crosstab query.
or
CDate(CInt([TimeFieldValue] * 96)/96)
as a Row Heading

--
Duane Hookom
MS Access MVP



Duane thanks for working with me.

I keep getting a "Overflow" error, so something I must be doing is
wrong.

THe Field name with the dates and times is called [Opened] & the
format
is this "5/26/2006 6:04:00 AM".

If I use the first expression: CDate(CInt([TimeFieldValue] * 96)/96)
do
I use that in a corss tab query or where you just telling me that
this
was a way to round to the nearest 15 minute interval?

I guess I want to group all the times by a 15 miute interval (for a
day) so that I can count them. Maybe I am putting the expression in
the
worng place. I am putting it under the opened field.

The second expression you gave me gives me the same error. However I
am
having trouble understanding where I set the column heading
properties.
When I pull up the properties to the column header is only gives me
on
box.

This is my SQL for 5/26/2006 with the Opened field as a Row Heading.
TRANSFORM Count([qryTotals_5/26/06_HB].Type) AS CountOfType
SELECT [qryTotals_5/26/06_HB].Opened,
Count([qryTotals_5/26/06_HB].Type) AS [Total Of Type]
FROM [qryTotals_5/26/06_HB]
GROUP BY [qryTotals_5/26/06_HB].Opened
PIVOT [qryTotals_5/26/06_HB].Owner;

If I put Opened as the column heading it give me an error like, to
complicated or cannot.

thanks,
David

Duane Hookom wrote:
You can get times rounded to the nearest 15 minute interval with an
expression like:
CDate(CInt([TimeFieldValue] * 96)/96)
If this is column heading in a crosstab query I would use and
expression
like
"T" & CInt([TimeFieldValue] * 96)
Then set your Column Headings property to:
"T22","T23","T24","T25"...,"T76"

--
Duane Hookom
MS Access MVP

Do you know how I could get it down to 15 minute intervals.
Starting
at
5:30 and ending at 7PM?

I got it down to the a specific date, but have not made it any
further.


Dave


Duane Hookom wrote:
A crosstab should work. Set the column heading field to
"D" & Day([Opened])
and set the Column Headings property to:
"D1","D2","D3","D4",..."D31"
Make sure you only return a month of data or March 1st will be
totaled
with
April 1st etc.

--
Duane Hookom
MS Access MVP

I have been playing around with the crosstab query, but I am
not
sure
I
am in the right direction.

Dave

Duane Hookom wrote:
You may need to start over. I don't have a clue what you
have,
what
you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM
Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And
([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to
work.
Is
this
what you meant by a totals query or am I doing something
else?

I would like to put this in a time series down to 15 minute
gaps
from
7AM to 7PM. Possible?

Many Thanks - David


Duane Hookom wrote:
Create a totals query and use it as the record source of
your
report
or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report
that
displays
each user name with the number of times is apprears in
the
field.
I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300
different
usernames and I don't want to have to code each txt box.
Is
thier
a
way
I can run a report which lists the usersnames and the
number
of
times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David

 

[david.isaacks]

Duane,

ahahahahah, I was not typing in the "T" values.. It works perfect now..
I appreciate all your help.

David

It isn't clear what you want for Row Headings. If you want Owner as the Row
Heading, try this SQL and type in more "T" values in the PIVOT section:

TRANSFORM Count([qryTotals_5/26/06_HB].Type) AS CountOfType
SELECT [qryTotals_5/26/06_HB].Owner,
Count([qryTotals_5/26/06_HB].Type) AS [Total Of Type]
FROM [qryTotals_5/26/06_HB]
GROUP BY [qryTotals_5/26/06_HB].Owner
PIVOT "T" & CInt(TimeValue([TimeFieldValue]) * 96)
IN ( "T22","T23","T24","T25"...,"T76");

--
Duane Hookom
MS Access MVP

Duane,

I want the column headings to be the 15 minute increments and the first
row to be the count of how many time a date&time fall within those 15
minute increments to include Null returns.

Dave

Where do you want them? Are they column headings or row headings?
--
Duane Hookom
MS Access MVP

Just to confirm, where do I put these expressions?



Duane Hookom wrote:
I would use
"T" & CInt(TimeValue([TimeFieldValue]) * 96)
As the column heading of a crosstab query.
or
CDate(CInt([TimeFieldValue] * 96)/96)
as a Row Heading

--
Duane Hookom
MS Access MVP



Duane thanks for working with me.

I keep getting a "Overflow" error, so something I must be doing is
wrong.

THe Field name with the dates and times is called [Opened] & the
format
is this "5/26/2006 6:04:00 AM".

If I use the first expression: CDate(CInt([TimeFieldValue] * 96)/96)
do
I use that in a corss tab query or where you just telling me that
this
was a way to round to the nearest 15 minute interval?

I guess I want to group all the times by a 15 miute interval (for a
day) so that I can count them. Maybe I am putting the expression in
the
worng place. I am putting it under the opened field.

The second expression you gave me gives me the same error. However I
am
having trouble understanding where I set the column heading
properties.
When I pull up the properties to the column header is only gives me
on
box.

This is my SQL for 5/26/2006 with the Opened field as a Row Heading.
TRANSFORM Count([qryTotals_5/26/06_HB].Type) AS CountOfType
SELECT [qryTotals_5/26/06_HB].Opened,
Count([qryTotals_5/26/06_HB].Type) AS [Total Of Type]
FROM [qryTotals_5/26/06_HB]
GROUP BY [qryTotals_5/26/06_HB].Opened
PIVOT [qryTotals_5/26/06_HB].Owner;

If I put Opened as the column heading it give me an error like, to
complicated or cannot.

thanks,
David

Duane Hookom wrote:
You can get times rounded to the nearest 15 minute interval with an
expression like:
CDate(CInt([TimeFieldValue] * 96)/96)
If this is column heading in a crosstab query I would use and
expression
like
"T" & CInt([TimeFieldValue] * 96)
Then set your Column Headings property to:
"T22","T23","T24","T25"...,"T76"

--
Duane Hookom
MS Access MVP

Do you know how I could get it down to 15 minute intervals.
Starting
at
5:30 and ending at 7PM?

I got it down to the a specific date, but have not made it any
further.


Dave


Duane Hookom wrote:
A crosstab should work. Set the column heading field to
"D" & Day([Opened])
and set the Column Headings property to:
"D1","D2","D3","D4",..."D31"
Make sure you only return a month of data or March 1st will be
totaled
with
April 1st etc.

--
Duane Hookom
MS Access MVP

I have been playing around with the crosstab query, but I am
not
sure
I
am in the right direction.

Dave

Duane Hookom wrote:
You may need to start over. I don't have a clue what you
have,
what
you
want, or where any time values are coming from.
--
Duane Hookom
MS Access MVP

SELECT [tbl_HRC CRM Output FY06].Owner, Count([tbl_HRC CRM
Output
FY06].Owner) AS CountOfOwner
FROM [tbl_HRC CRM Output FY06]
WHERE ((([tbl_HRC CRM Output FY06].Opened)>=#5/1/2006# And
([tbl_HRC
CRM Output FY06].Opened)<#6/1/2006#))
GROUP BY [tbl_HRC CRM Output FY06].Owner
ORDER BY [tbl_HRC CRM Output FY06].Owner;

The owner is the username I am looking for. This seems to
work.
Is
this
what you meant by a totals query or am I doing something
else?

I would like to put this in a time series down to 15 minute
gaps
from
7AM to 7PM. Possible?

Many Thanks - David


Duane Hookom wrote:
Create a totals query and use it as the record source of
your
report
or a
subreport.
--
Duane Hookom
MS Access MVP

I have a field for Username and I want to run a report
that
displays
each user name with the number of times is apprears in
the
field.
I
know I can use differnet count or sum functions like
"=Sum(Abs([Facility VISN]="10"))" but I have like 300
different
usernames and I don't want to have to code each txt box.
Is
thier
a
way
I can run a report which lists the usersnames and the
number
of
times
it apprears within a field?

Somthing like this:
dayb 31
coxm 15

David