Report Summary Count

[stvlai]

Hi

I am trying to create a Report that will do some Counting of record. I
been trying to use Count and DCount but always came back with the
wrong count. Any help would be much appreciated. It's a simple report
but I am stuck. Thank you!
E.g

Data :
ID Tech JobID
1 Joe AA
1 Joe AA
1 Joe AB
1 Sam AA
1 Sam AB
1 Sam AB
1 Sam AC

Report:

Job Count Report:

Tech Job AA Job AB Job AC
Joe 2
1 0
Sam 1
2 1

Steven

 

[Ken Sheridan]

Steven:

Either base the report on a crosstab query:

TRANSFORM NZ(COUNT(*),0)
SELECT Tech
FROM YourTable
GROUP BY Tech
PIVOT JobID IN("AA","AB","AC");

Or on a query which sums the return value of expressions returning 1 or 0
depending on the value of JobID:

SELECT Tech,
SUM(IIF(JobID = "AA",1,0)) AS [Job AA],
SUM(IIF(JobID = "AB",1,0)) AS [Job AB],
SUM(IIF(JobID = "AC",1,0)) AS [Job AC]
GROUP BY Tech;

Ken Sheridan
Stafford, England

 

[Dale Fye]

Steven,

The challenge of using a cross-tab as the column headers for a report is
that you cannot be sure what values will show up, unless you know exactly
what values are in that column, and force the query to return those values.
In your example data, you would have columns AA, AB, and AC, but what if the
last record for Sam wasn't there, then you would only have columns (AA and
AB). The way to force the Crosstab query to list specific columns, and to
put them in the order you want is with a IN clause, as Ken did in his
example. Access will normally alphabetized the column headers and will only
return columns for those values that are in the column identified as the
column header. However, the In clause allows you to:

1. Force the return of columns, even if there is no matching data in the
recordset, and
2. Put them in whatever order you want them in.

So, if you had sales data, and you wanted the column headers to be the month
number, then you might have a query that looks like:

TRANSFORM NZ(Sum(Sales), 0)
SELECT RegionID, OfficeID
FROM tbl_Sales
WHERE YEAR(SalesDate) = 2007
GROUP BY RegionID, OfficeID
PIVOT Month(SalesDate)

But if you wanted the month headers to be the three character abbreviation
for the month, you might use:

TRANSFORM NZ(Sum(Sales), 0)
SELECT RegionID, OfficeID
FROM tbl_Sales
WHERE YEAR(SalesDate) = 2007
GROUP BY RegionID, OfficeID
PIVOT Format(SalesDate, "mmm")
IN ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Oct", "Nov",
"Dec")

Note, the values in the IN clause must match exactly what is in the Pivot
column (although they are not case sensitive). If you look closely, I have
"accidentally" forgetten to put "Sep" in the list, and if you make this kind
of error, it would be obvious when the columns are months, but it may not be
obvious that you have left something out.

The other challenge with using Cross-tab queries as the source for a report,
is that depending on the query, you might not have the same column headers
from one month to another. In this case, you will have to create a dynamic
report that has some code behind it to set the names of the column headers
and set the control source of the various columns. If you need to do this,
do a Google search on:

Access +dynamic +crosstab +reports

HTH
Dale

 

[stvlai]

Hi

Thanks a lot for your advise & help. Yes, I forgot about Cross Tab and
it seems to help what I want to acheive.

However, how would you have Parameters for a Date Range for the
CrossTab query. I have been checking out the forum. I specify the
'StartDate' & 'EndDate' for the Date Range in the Query Parameters. It
works fine in the CrossTab Query. However, when I try to create a
Report using the Report Wizard based on the CrossTab Query, it does
not list any of the Query Fields or Column at all. Just blank! If I
remove the Date Range from the CrossTab Query, then it show all the
Query's fields. Strange!

Do you have any idea why it is doing this? Any suggestions or ideas is
very much appreciated. Thanks again.

Steven

 

[Dale Fye]

You will actually need to declare the parameters. To do this, right click in
the grey area at the top of the query design grid (next to one of the
tables). Select the Parameters option. This will open a dialog box with
column headers Parameter, and Data Type. Enter your parameters into the
parameter column and select the appropriate data type in that column. The
best way to enter the parameters is to actually copy them from the criteria
of your query, so that you know they are spelled exactly the same as in the
query. Finally, close the Query Parameters dialog. What this does, is add a
PARAMETERS clause at the beginning of the SQL for your query.

Since you don't show any date fields in the data you provided, I'll use the
example I used in my previous post. The new SQL might look like:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
TRANSFORM NZ(Sum(Sales), 0)
SELECT RegionID, OfficeID
FROM tbl_Sales
WHERE [SalesDate] BETWEEN [StartDate] AND [EndDate]
GROUP BY RegionID, OfficeID
PIVOT Format(SalesDate, "mmm")

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.