Use a subtotal and %total

[finlain]

Hi
I have a query and I am trying to subtotal by "Provider" for each sales associate. Then take the %to total of the provider by name for each associate.

I can do it in Excel using a Pivot Table very easily but I would like to do it in Access saving me some steps.

Any help would be much appreciated.

Thanks


Elaine

[rpeare]

If you're doing this on a report and you want to subtotal on the report that's one thing, if you're looking to do it in a query, don't. The methods you'd have to use would really bog down your query. I'm assuming the image on the left is what you're trying to get to.

If that's the case on your report set your sorting and grouping in layers:

DBA_ArcTable.Name is your first sort
tblAgentNameID.Name is your secondary sort
Provider is your tertiary sort

Create a HEADER and FOOTER section for your provider, shrink the DETAIL section of the report to a height of 0

Put in the provider label and put in a text box with the formula = count(provider)
in the tblAgentNameID.Name footer, put the same formula and name it something like 'AgentSubtot'
create another text box in your PROVIDER footer with the formula = count(provider)/[AgentSubTot] that should provide you the percentage of that provider for that agent

from there it's just a matter of formatting section footers and adding formulas where appropriate.