is this possible in Access????

  • Thread starter Thread starter Tim S
  • Start date Start date
T

Tim S

Currently I have two queries in Access that provide info
on field offices.

query1 below tells me the number of stores rated 1,2,3,or4
for the "stores" managed by a field office. for example
Field Office Champ has ten 1 rated stores, thirty 2 rated
stores, three 3 rated stores and two 4 rated stores.

query2 below tells me the number of stores managed by each
field office. Champ manages 55 stores in total.

currently I export both of these queries to excel and then
calculate for each office the percent that are rated
1,2,3, or 4. Champ would have 18.18% of its stores rated 1

What I would like to do is have access do the percentage
calculation. how would I do this?????

Query1 is a cross tab query
query1

Field Office 1 2 3 4
Champ 10 30 3 2
Cinn 17 23 3
Clev 5 15 3 2
C-North 11 20 9 1
C-South 13 17 4

query2 is a select query
query2
Field Office CountOfCHARTER
Champ 55
Cinn 45
Clev 29
C-North 42
C-South 40
 
Hi,



From the first crosstab:

TRANSFORM COUNT(Something) As CountOfSomething SELECT ... FROM
tableName ... GROUP BY thisField .... PIVOT thatField ...

change it to

TRANSFORM COUNT(Something) / DCount( "Something", "tableName",
"thisField=" & thisField ) As PercentOfSomething SELECT ...




I assumed thisField is numerical (not text, not date_time) and that is has
no NULL value. If you want the percentage over the column, use thatField (
the field in the PIVTO clause) instead of thisField (the field in the GROUP
BY), in the third argument of the DCount:

... / DCount( "Something", "tableName", "thatField=" & thatField )



or use


... / DCount( "Something", "tableName")

without criteria, for a global percentage.



If thisField has NULL values, you have to change the criteria (third
argument of DCount) from

"thisField=" & thisField

into

iif( IsNull( thisField), True, "thisField=" & thisField )



If the initial TRANSFORM is using a SUM, rather than a COUNT, use DSum
rather than DCount.




Hoping it may help
Vanderghast, Access MVP
 
This is what I have currently. I tried to do what you
said, but couldn't get it right

TRANSFORM Count(Results.[Capital Rating]) AS
[CountOfCapital rating]
SELECT [field office].[Field Office]
FROM [field office] INNER JOIN Results ON [field office].
[budget code] = Results.[SUPERVISORY OFFICE]
WHERE (((Results.[Capital Rating]) Is Not Null))
GROUP BY [field office].[Field Office]
PIVOT Results.[Capital Rating];
-----Original Message-----
Hi,



From the first crosstab:

TRANSFORM COUNT(Something) As CountOfSomething SELECT ... FROM
tableName ... GROUP BY thisField .... PIVOT thatField ...

change it to

TRANSFORM COUNT(Something) / DCount ( "Something", "tableName",
"thisField=" & thisField ) As PercentOfSomething SELECT ...




I assumed thisField is numerical (not text, not date_time) and that is has
no NULL value. If you want the percentage over the column, use thatField (
the field in the PIVTO clause) instead of thisField (the field in the GROUP
BY), in the third argument of the DCount:

... / DCount
Click to expand...
( "Something", "tableName", "thatField=" & thatField )
 
Hi,



TRANSFORM Count(Results.[Capital Rating]) / DCount("*","field
office","[Field Office]=""" & [Field Office] & """" ) As [CountOfCapital
Rating] SELECT ...



since I assume that [Field Office] is a field with datatype text (not
numerical), so you need " as delimiter.



Hoping it may help,
Vanderghast, Access MVP



Tim S said:
This is what I have currently. I tried to do what you
said, but couldn't get it right

TRANSFORM Count(Results.[Capital Rating]) AS
[CountOfCapital rating]
SELECT [field office].[Field Office]
FROM [field office] INNER JOIN Results ON [field office].
[budget code] = Results.[SUPERVISORY OFFICE]
WHERE (((Results.[Capital Rating]) Is Not Null))
GROUP BY [field office].[Field Office]
PIVOT Results.[Capital Rating];
-----Original Message-----
Hi,



From the first crosstab:

TRANSFORM COUNT(Something) As CountOfSomething SELECT ... FROM
tableName ... GROUP BY thisField .... PIVOT thatField ...

change it to

TRANSFORM COUNT(Something) / DCount ( "Something", "tableName",
"thisField=" & thisField ) As PercentOfSomething SELECT ...




I assumed thisField is numerical (not text, not date_time) and that is has
no NULL value. If you want the percentage over the column, use thatField (
the field in the PIVTO clause) instead of thisField (the field in the GROUP
BY), in the third argument of the DCount:

... / DCount
Click to expand...
( "Something", "tableName", "thatField=" & thatField )
or use


... / DCount( "Something", "tableName")

without criteria, for a global percentage.



If thisField has NULL values, you have to change the criteria (third
argument of DCount) from

"thisField=" & thisField

into

iif( IsNull( thisField), True, "thisField=" & thisField )



If the initial TRANSFORM is using a SUM, rather than a COUNT, use DSum
rather than DCount.




Hoping it may help
Vanderghast, Access MVP






.
Click to expand...
Click to expand...
 
still only displays the actual count, not the percentage
of offices rated 1,2,3,4, or 5

TRANSFORM Count(Results.[Capital Rating]) / DCount
("*","field office","[Field Office]=""" & [Field Office]
& """" ) AS CountOfCapitalRating
SELECT [field office].[Field Office]
FROM [field office] INNER JOIN Results ON [field office].
[budget code] = Results.[SUPERVISORY OFFICE]
WHERE (((Results.[Capital Rating]) Is Not Null))
GROUP BY [field office].[Field Office]
PIVOT Results.[Capital Rating];
-----Original Message-----
Hi,



TRANSFORM Count(Results.[Capital Rating]) / DCount ("*","field
office","[Field Office]=""" & [Field Office] & """" ) As [CountOfCapital
Rating] SELECT ...



since I assume that [Field Office] is a field with datatype text (not
numerical), so you need " as delimiter.



Hoping it may help,
Vanderghast, Access MVP



This is what I have currently. I tried to do what you
said, but couldn't get it right

TRANSFORM Count(Results.[Capital Rating]) AS
[CountOfCapital rating]
SELECT [field office].[Field Office]
FROM [field office] INNER JOIN Results ON [field office].
[budget code] = Results.[SUPERVISORY OFFICE]
WHERE (((Results.[Capital Rating]) Is Not Null))
GROUP BY [field office].[Field Office]
PIVOT Results.[Capital Rating];
-----Original Message-----
Hi,



From the first crosstab:

TRANSFORM COUNT(Something) As CountOfSomething SELECT ... FROM
tableName ... GROUP BY thisField .... PIVOT thatField ...

change it to

TRANSFORM COUNT(Something) / DCount ( "Something", "tableName",
"thisField=" & thisField ) As PercentOfSomething SELECT ...




I assumed thisField is numerical (not text, not date_time) and that is has
no NULL value. If you want the percentage over the column, use thatField (
the field in the PIVTO clause) instead of thisField
Click to expand...
(the
field in the GROUP
BY), in the third argument of the DCount:

... / DCount
Click to expand...
( "Something", "tableName", "thatField=" & thatField )
or use


... / DCount( "Something", "tableName")

without criteria, for a global percentage.



If thisField has NULL values, you have to change
Click to expand...
the
criteria (third
argument of DCount) from

"thisField=" & thisField

into

iif( IsNull( thisField), True, "thisField=" & thisField )



If the initial TRANSFORM is using a SUM, rather
Click to expand...
than
a COUNT, use DSum
rather than DCount.




Hoping it may help
Vanderghast, Access MVP



Currently I have two queries in Access that provide info
on field offices.

query1 below tells me the number of stores rated 1,2,3,or4
for the "stores" managed by a field office. for example
Field Office Champ has ten 1 rated stores, thirty 2 rated
stores, three 3 rated stores and two 4 rated stores.

query2 below tells me the number of stores managed by each
field office. Champ manages 55 stores in total.

currently I export both of these queries to excel and then
calculate for each office the percent that are rated
1,2,3, or 4. Champ would have 18.18% of its stores rated 1

What I would like to do is have access do the percentage
calculation. how would I do this?????

Query1 is a cross tab query
query1

Field Office 1 2 3 4
Champ 10 30 3 2
Cinn 17 23 3
Clev 5 15 3 2
C-North 11 20 9 1
C-South 13 17 4

query2 is a select query
query2
Field Office CountOfCHARTER
Champ 55
Cinn 45
Clev 29
C-North 42
C-South 40



.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi,


At least, there is no error.

Since DCount requires a table or a saved query, and since you probably
need the inner join to get the Count right, I suppose you have to make a
query from the actual join:

SELECT [field office].[Field Office],
Results.[Capital Rating]
FROM [field office] INNER JOIN Results
ON [field office].[budget code] = Results.[SUPERVISORY OFFICE]


as a saved query, say Q1, then,

TRANSFORM Count([Capital Rating]) /
DCount("*","q1","[Field Office]=""" & [Field Office] & """" )
AS CountOfCapitalRating
SELECT [Field Office]
FROM q1
WHERE [Capital Rating] Is Not Null
GROUP BY [Field Office]
PIVOT [Capital Rating];


would work, as long as DCount return the right count of office for a given
[Field Office] (thing you can check in the debug window). The problem is
not with the crosstab, anymore, it is with the denominator of the division,
DCount( ... ).



Hoping it may help,
Vanderghast, Access MVP



timS said:
still only displays the actual count, not the percentage
of offices rated 1,2,3,4, or 5

TRANSFORM Count(Results.[Capital Rating]) / DCount
("*","field office","[Field Office]=""" & [Field Office]
& """" ) AS CountOfCapitalRating
SELECT [field office].[Field Office]
FROM [field office] INNER JOIN Results ON [field office].
[budget code] = Results.[SUPERVISORY OFFICE]
WHERE (((Results.[Capital Rating]) Is Not Null))
GROUP BY [field office].[Field Office]
PIVOT Results.[Capital Rating];
-----Original Message-----
Hi,



TRANSFORM Count(Results.[Capital Rating]) / DCount ("*","field
office","[Field Office]=""" & [Field Office] & """" ) As [CountOfCapital
Rating] SELECT ...



since I assume that [Field Office] is a field with datatype text (not
numerical), so you need " as delimiter.



Hoping it may help,
Vanderghast, Access MVP



This is what I have currently. I tried to do what you
said, but couldn't get it right

TRANSFORM Count(Results.[Capital Rating]) AS
[CountOfCapital rating]
SELECT [field office].[Field Office]
FROM [field office] INNER JOIN Results ON [field office].
[budget code] = Results.[SUPERVISORY OFFICE]
WHERE (((Results.[Capital Rating]) Is Not Null))
GROUP BY [field office].[Field Office]
PIVOT Results.[Capital Rating];

-----Original Message-----
Hi,



From the first crosstab:

TRANSFORM COUNT(Something) As CountOfSomething
SELECT ... FROM
tableName ... GROUP BY thisField .... PIVOT thatField ...

change it to

TRANSFORM COUNT(Something) / DCount
( "Something", "tableName",
"thisField=" & thisField ) As PercentOfSomething
SELECT ...




I assumed thisField is numerical (not text, not
date_time) and that is has
no NULL value. If you want the percentage over the
column, use thatField (
the field in the PIVTO clause) instead of thisField (the
field in the GROUP
BY), in the third argument of the DCount:

... / DCount
( "Something", "tableName", "thatField=" & thatField )



or use


... / DCount( "Something", "tableName")

without criteria, for a global percentage.



If thisField has NULL values, you have to change the
criteria (third
argument of DCount) from

"thisField=" & thisField

into

iif( IsNull( thisField), True, "thisField=" &
thisField )



If the initial TRANSFORM is using a SUM, rather than
a COUNT, use DSum
rather than DCount.




Hoping it may help
Vanderghast, Access MVP



message
Currently I have two queries in Access that provide info
on field offices.

query1 below tells me the number of stores rated
1,2,3,or4
for the "stores" managed by a field office. for example
Field Office Champ has ten 1 rated stores, thirty 2
rated
stores, three 3 rated stores and two 4 rated stores.

query2 below tells me the number of stores managed by
each
field office. Champ manages 55 stores in total.

currently I export both of these queries to excel and
then
calculate for each office the percent that are rated
1,2,3, or 4. Champ would have 18.18% of its stores
rated 1

What I would like to do is have access do the percentage
calculation. how would I do this?????

Query1 is a cross tab query
query1

Field Office 1 2 3 4
Champ 10 30 3 2
Cinn 17 23 3
Clev 5 15 3 2
C-North 11 20 9 1
C-South 13 17 4

query2 is a select query
query2
Field Office CountOfCHARTER
Champ 55
Cinn 45
Clev 29
C-North 42
C-South 40



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top