Access Report, calculated field

[Gweener]

Have an access report from an access query. Report gives a count of values in a field (count of result codes entered into CRM from calling activity).

I want to put a calculated field on the report which gives a count where the result codes = x or y or z.

If I were to write it in T-SQL, it'd be

select count(resultcode) from conthist
where resultcode in ('apy', 'nno', 'rfc')

Query already extracts the count of results and displays on report together with a sum of the count. I just want to put a condition on the 'sum of the count'.

Simple, but i seem to be getting nowhere.

 

[johnnygeo]

If I understand correctly, your final query (call this queryA1) prior to the report contains one record?

I would recommend creating a parallel queryA2, with SQL like you wrote above. Then a queryB which simply combines the "sum" from queryA1 with the "conditional sum" from queryA2.

queryA1:

select count(resultcode) as mySum from conthist

queryA2:

select count(resultcode) as myCondSum from conthist where resultcode in ('apy', 'nno', 'rfc')

queryB:

select queryA1.mySum, queryA2.myCondSum from queryA1, queryA2

Then use queryB as the source for your report.

 

[Gweener]

Not quite. Access query outputs many rows.

Main access query (not in SQL but expressed as such here) is

select userid, resultcode, count(resultcode)
from conthist
group by userid, resultcode
order by userid

This is output in a report, showing the number of instances of each resultcode by userid.

There is a USERID footer (i.e. the footer of the 'group by' band) on on the report which has a total of the count (a sum of count i.e. a total of the results logged). What i want to do is put a condition on this sum so that it gives a total for 'success' contacts i.e. only certain resultcodes therefore

where resultcode in ('XXX', 'XXX', 'XXX') etc.

 

[johnnygeo]

You could try using a domain aggregate function, although every time I use one of these I pull my hair out trying to get the syntax right:

in your report calculated field:
=DSum("[countofresultcode]", "MainAccessQueryName", "[resultcode] in (""XXX"",""XXX"","XXX"")")

Note that the name of the query is not in brackets (one of the syntax things that always trips me up).

Otherwise, I would still create a second query just to calculate the sum you want, then embed a subreport containing just this field into the main report footer.

 

[Gweener]

Will give it a go. Thanks Gents!

 

[Gweener]

You're right about this syntax - doing my head in!

=DSum("[countofresultcode]","qryCallsbyAgent","[ResultCode] in ('apy','rfc','sal')"

Can you see the syntax error?

CountOfResultCode = the calculated field in query

qryCallsByAgent = the query itself

ResultCode = result code field in query

apy, rfc, sal = result codes i want to count instances of.