Report summing up contracts by agents

[cattironheart]

I just started a new job as an office manager and am trying to use my self-taught access skills to use since they don’t want to hire a computer person.

I have a table that tracks contracts made and by what AgentID completes them. What I require is a report that breaks the amount of contracts per AgentID where it shows total and also the different types. Because of the report I have to download that contains all sales to add to the file I am limited to having for the different types of contracts a YES or a NO. I used the ABS SUM to get the sum for each AgentID but can’t figure out how to show for the AgentID and showing the different contracts.

Also each AgentID has breakdown like:

1A to 1F and this goes through to 8A to 8F. These represent teams.

I would need to also have a report that shows the totals for each team all on one quick report that shows the numbers. Currently being used are excel sheets that all have to be hand jammed and are just cumbersome.

Team Report:
1 / total contracts / total contract a / total contract b etc
2 / total contracts / total contract a / total contract b etc
3 / total contracts / total contract a / total contract b etc
All the way to 8. (and then a breakdown per agent)

I was thinking maybe I need to make a table for each team and then have them link with the agents? Or is there another route?

PS due to the computer network at the business our internet is disabled; we only use our intranet so I am at home trying to remember what I need. Plus popping migraine pills as I couldn’t wrap my head around this type of coding.

Thanks for any assistance
Chris

 

[dhookom]

Chris,
Welcome to Tek-Tips.
It would really help if you typed in some sample records including the significant fields. Then show us how these records should display in the report.

Apparently you already understand how to use Abs(Sum( )) which is great. I'm not sure if you just substitute in each AgentID into separate expression or what. I would consider using a subreport that groups by AgentID. A better description of your table structure and data would help.

Duane
Hook'D on Access
MS Access MVP

 

[cattironheart]

Duane, thank you for the welcome!

Since I am not at work where the database resides I am going off of memory so I cant pound out the code from my head .. currently have a migraine which is not helping with that either!

The table has these fields which I pull from a report in excel and then copy into the table:

Contractdate
AgentID (1A to 1F and up to 8F)
Type1 (Yes or No but typed out not a switch)
Type2 (same as Type1, and then there are more types)
Plus other crap that I am told is important, but I don't seem to need lol. Oh and right now there is no key since each contract is linked to the customer SSN, but they can have multiple contracts per SSN so I can't use the SSN as a key and there is no contract code.

Need to report all the 1's together and then all of them separately as well.

I was thinking of subreports but was hoping there would be an easier way. Something where in the textbox I can enter the Abs(sum) field but work it to add all of the 1A-1F and then show the amount of Type1 with Yes. Now I did figure out how to sum up all agentsIDs beginning with their first numeral, but couldn't get it to then show the amount of distinct contracts for that grouping.

The report should show as:

CONTRACTS
TEAM TOTAL TYPE1 TYPE2 TYPE3 TYPE4
TEAM1
TEAM2
TEAM3
TEAM4
TEAM5
TEAM6
TEAM7
TEAM8


TEAM 1 CONTRACTS
AGENT TOTAL TYPE1 TYPE2 TYPE3 TYPE4
1A
1B
1C
1D
1E
1F
TOTAL

The reports are year to date numbers, quarterly, weekly and daily (all on separate reports which is doable with the contractdate.)

I hope that helped clarify some, or did it create more questions!

Thanks again for any assistance.

Chris

 

[dhookom]

What determines a "team"? It looks like your first grouping is by team and then another section that is grouped by Team and Agent. This suggests a subreport for one or the other.

Duane
Hook'D on Access
MS Access MVP

 

[cattironheart]

Team 1 = 1A,1B,1C,1D,1E,1F
Team 2 .. same as above and keeps going for the teams.
The agents are the 1A,1B ...

 

[dhookom]

If the team is determined by the first character of the AgentID then create a new column in your report's recordsource with and expression like:

Team: Left(AgentID,1)

This allows you to group by and sum or count aggregates for each team.




Duane
Hook'D on Access
MS Access MVP

 

[cattironheart]

Yes that is what I am using to sort total team numbers:

=Abs(Sum(Left([AgentID],1)="1")) This shows team total numbers.

But how can I have it also add up all the different contracts for the team and individual agentIDs? Is the only way to show that with subreports? Do I need to have them all just query for each different contract type in subreports? It makes it a bit difficult for formattting a single report that shows these numbers.

 

[dhookom]

As I stated, create a new column in your report's recordsource. I would not hardcode in values in expressions.
Group in your report by the new team column and use text boxes to show the results you desire. I you really need to show contracts as summaries by team first together and then details by agent later, you will need to use a subreport for one or the other. Just one subreport.

Duane
Hook'D on Access
MS Access MVP