Code to divide results by units based on Site Code.

[jereece]

I have a report (IRs_Generated) based on a query. On the report I have fields [site_cd] and [=Count(*)] in the header. This way I get a total count of the records returned from the query for each site. However, I want to report the results based on the number of units at each site. Some sites may have 2 units and some 3. Is there a code I can use in a field on the report that says if the [site_cd] is X or Y or Z then divide by 2 but if the [site_cd] is A, B or C then divide by 3? What is the best way to handle this?

Thanks
Jim

 

[vbaInet]

jereece all that's needed is:

1. Group By [site_cd]
2. Put =Count(*) in the Group's Header or Footer section.

The following site should give you some pointers:

http://office.microsoft.com/en-us/access-help/create-a-grouped-or-summary-report-HA010006894.aspx

 

[jereece]

vbaInet - I don't think you fully read my question. I already did what you replied. My questions is how to multiply the count results by a number depending on how many units the particular site has. For example if [site_cd] is A, then I would want the [=Count(*)] to be multiplied by 2. But if the [site_cd] is B, then I would want the [=Count(*)] to be multiplied by 3.

Thanks,
Jim

 

[vbaInet]

=Count([ID]) * Switch([site_cd] = 'A', 2, [site_cd] = 'B', 3)

where ID is the name of your ID field.

 

[jereece]

The name of the field that I count all of them is [SiteCount]. When I use this in the expression, I get prompted to enter a value for this field. In reality I want it to use the [SiteCount] number times the the number of units the site has.

 

[vbaInet]

SiteCount is not a field. It is the name of the control. You can't Count using a control name. Use the calculation in the SiteCount control.