Multiple counts in report

[thart21]

Looking for the best way to accomplish this.

I have one table with "Requisitions" with the fields requisition, cca_number, assetnumber and supplier.

I need a report that will do the following

Group by CCA Number, then Supplier
Show a count of the requisitions
Show a count of the assetnumbers
Subtotal by CCA Number

One assetnumber can be used on more than one requisition so the counts will be different.

I think I need to make seperate queries to count the requisitions and the assetnumbers then group them by supplier and CCA Number, according to another post I found, but I can't figure out how to put it all together on the report.

If I use subreports from the Counts queries, how do I get it to group at the top level by CCA Number?

Thanks,

Toni

 

[andrew93]

Hi Toni

Your description is almost the exact design you need for your query. Create a new query, add your table, click View -> Totals, add the fields you want to see on your report (i.e. CCA Number, Supplier, requisition, assetnumber), under requisition and assetnumber change 'Group By' to 'Count'. Save the query.

Create a new report based on this query using the new report wizard. Group by CCA Number and sort by Supplier (within the wizard). View the sample report and you should be almost there. Don't worry about sub-reports, I don't think you need that.

To get the subtotals, copy the counts for the requisition and assetnumber from the detail section of the report into the 'CCA Number' footer section of the report, and change the control source from whatever it is (possibly CountOfRequisition?) to Sum([CountOfRequisition]). Be sure to use the correct field name where I assumed 'CountOfRequisition'. Repeat for the supplier count.

If you get stuck with your groups etc on your report, while in the report design screen click on View -> Sorting and Grouping.

HTH, Andrew

 

[Rich]

If you use the report wizard it'll do most of the work for you

 

[thart21]

Thanks for the reply. The problem I am running into is the wizard seems to be giving me a count of the "records" and not the unique values. For example, one supplier has been issued 64 requisitions and 44 asset numbers. When I use the wizard, it shows up as 64 requisitions and 64 asset numbers. I have tried to add "DISTINCT" to the SQL with no luck.

Thanks,

Toni

 

[thart21]

I think I've figured it out with a few different queries. I had to create two separate Select Distinct queries - one for Asset Number and one for Requisitions and then two separate Count queries for each of those. I then made one query with the 2nd group of 2 queries to put them together.

I ended up with:

SELECT CountofAsset.CountOfassetnumber, ReqCount.CountOfrequisition, ReqCount.supplier, CountofAsset.ccanumber, Sum(CountofAsset.SumOfSumOfcost) AS SumOfCost, CCA.AmountAuthorized
FROM (ReqCount INNER JOIN CountofAsset ON ReqCount.supplier = CountofAsset.supplier) INNER JOIN CCA ON ReqCount.ccanumber = CCA.CCANumber
GROUP BY CountofAsset.CountOfassetnumber, ReqCount.CountOfrequisition, ReqCount.supplier, CountofAsset.ccanumber, CCA.AmountAuthorized;

Now I have my report group by CCA, then Supplier and counts and $$ for each Asset #.

Thanks for the help and point in the right direction!