Calculated Field Help Needed

[Jay]

I have a query which groups by the field 'Manufacturer' and by the
'SoldMonth' chosen on a form [Forms]![frmSwitchboard]![cboMonth].

The query has a calculated Expression field which counts the grouping per
Manufacturer -
Field: ManufacturerCount: Count(*)
Total: Expression

I have another calculated expression field which calculates the
ManufacturerCount as a percentage:
Field: Percentage: (Count(*)/(SELECT Count(*) FROM
[tblValidationMileages]))*100
Total: Expression

However, the calculated Percentage field calculates the ManufacturerCount as
a percentage of ALL the records in the source table and I want it to be a
percentage of records where the 'SoldMonth' is the same as
Forms]![frmSwitchboard]![cboMonth].

Can anyone advise how I change my query. I'm not very good at writing in
SQL but the code is:

SELECT tblValidationMileages.Manufacturer, Count(*) AS ManufacturerCount,
tblValidationMileages.SoldMonth, (Count(*)/(SELECT Count(*) FROM
[tblValidationMileages]))*100 AS Percentage
FROM tblValidationMileages
GROUP BY tblValidationMileages.Manufacturer, tblValidationMileages.SoldMonth
HAVING
(((tblValidationMileages.SoldMonth)=[Forms]![frmSwitchboard]![cboMonth]));

Many Thanks,

Jason

 

[Michel Walsh]

Field: Percentage: (Count(*)/(SELECT Count(*) FROM
[tblValidationMileages] WHERE SoldMonth
=Forms![frmSwitchboard]![cboMonth])*100


which is adding the where clause in the initial sub-query for the percentage
calculated value.



Hoping it may help,
Vanderghast, Access MVP

 

[Jason]

That worked perfectly. Thanks a lot Michel, I wasted nearly an hour
earlier today trying to figure it out. I was failing because for some
reason I thought it was the first Count(*) in the expression that gave
the overall total, so I left the Count(*) after SELECT unchanged.

One more question if I may. How would I include another WHERE
condition? as I want the percentage to be of the Count of all records
SoldMonth=Forms![frmSwitchboard]![cboMonth] AND where the field
'TradeValue' does not equal 0.

MANY thanks for your help........Jason

Field: Percentage: (Count(*)/(SELECT Count(*) FROM
[tblValidationMileages] WHERE SoldMonth
=Forms![frmSwitchboard]![cboMonth])*100


which is adding the where clause in the initial sub-query for the percentage
calculated value.



Hoping it may help,
Vanderghast, Access MVP

I have a query which groups by the field 'Manufacturer' and by the
'SoldMonth' chosen on a form [Forms]![frmSwitchboard]![cboMonth].

The query has a calculated Expression field which counts the grouping per
Manufacturer -
Field: ManufacturerCount: Count(*)
Total: Expression

I have another calculated expression field which calculates the
ManufacturerCount as a percentage:
Field: Percentage: (Count(*)/(SELECT Count(*) FROM
[tblValidationMileages]))*100
Total: Expression

However, the calculated Percentage field calculates the ManufacturerCount
as a percentage of ALL the records in the source table and I want it to be
a percentage of records where the 'SoldMonth' is the same as
Forms]![frmSwitchboard]![cboMonth].

Can anyone advise how I change my query. I'm not very good at writing in
SQL but the code is:

SELECT tblValidationMileages.Manufacturer, Count(*) AS ManufacturerCount,
tblValidationMileages.SoldMonth, (Count(*)/(SELECT Count(*) FROM
[tblValidationMileages]))*100 AS Percentage
FROM tblValidationMileages
GROUP BY tblValidationMileages.Manufacturer,
tblValidationMileages.SoldMonth
HAVING
(((tblValidationMileages.SoldMonth)=[Forms]![frmSwitchboard]![cboMonth]));

Many Thanks,

Jason

 

[Michel Walsh]

use the AND conjonction:


(Count(*)/(SELECT Count(*) FROM
tblValidationMileages WHERE SoldMonth
=Forms![frmSwitchboard]![cboMonth]
AND TradeValue=0 )*100




Hoping it may help,
Vanderghast, Access MVP

That worked perfectly. Thanks a lot Michel, I wasted nearly an hour
earlier today trying to figure it out. I was failing because for some
reason I thought it was the first Count(*) in the expression that gave the
overall total, so I left the Count(*) after SELECT unchanged.

One more question if I may. How would I include another WHERE condition?
as I want the percentage to be of the Count of all records
SoldMonth=Forms![frmSwitchboard]![cboMonth] AND where the field
'TradeValue' does not equal 0.

MANY thanks for your help........Jason

Field: Percentage: (Count(*)/(SELECT Count(*) FROM
[tblValidationMileages] WHERE SoldMonth
=Forms![frmSwitchboard]![cboMonth])*100


which is adding the where clause in the initial sub-query for the
percentage calculated value.



Hoping it may help,
Vanderghast, Access MVP

I have a query which groups by the field 'Manufacturer' and by the
'SoldMonth' chosen on a form [Forms]![frmSwitchboard]![cboMonth].

The query has a calculated Expression field which counts the grouping
per Manufacturer -
Field: ManufacturerCount: Count(*)
Total: Expression

I have another calculated expression field which calculates the
ManufacturerCount as a percentage:
Field: Percentage: (Count(*)/(SELECT Count(*) FROM
[tblValidationMileages]))*100
Total: Expression

However, the calculated Percentage field calculates the
ManufacturerCount as a percentage of ALL the records in the source table
and I want it to be a percentage of records where the 'SoldMonth' is the
same as Forms]![frmSwitchboard]![cboMonth].

Can anyone advise how I change my query. I'm not very good at writing
in SQL but the code is:

SELECT tblValidationMileages.Manufacturer, Count(*) AS
ManufacturerCount, tblValidationMileages.SoldMonth, (Count(*)/(SELECT
Count(*) FROM [tblValidationMileages]))*100 AS Percentage
FROM tblValidationMileages
GROUP BY tblValidationMileages.Manufacturer,
tblValidationMileages.SoldMonth
HAVING
(((tblValidationMileages.SoldMonth)=[Forms]![frmSwitchboard]![cboMonth]));

Many Thanks,

Jason