L
lj
Small database tracking the activity in sales territories... I have a table
with AreaID and details about the area. It is related to another table
detailing the activity, that is when the SalesRep started working it and
when he finished (DateOut, DateIn)
I'm trying to generate a report that tells what percentage of the total
areas have been covered in a given time. I have an expression on the
report:
=Round(Count([AreaID])/[CountOfAreaID]*100,2) & "%"
The Count([AreaID]) refers to those areas included in my activity query as
having been covered during the specified time.
The CountOfAreaID is a query on the Area table to give me the total areas we
have.
This all works fine unless an area has been covered more than once during
the time period. I need to show the record, but I don't want to count it.
What I want is a Count of how many different areas were worked:
AreaID DateOut DateIn
1 Mar 3 June 4
4 Apr 6 May 9
4 July 7 Aug 10
7 Feb 14 Mar 20
I want it to give a Count of 3, since three different areas were covered,
not four because that messes up the percentage. ( i.e., If there were 10
areas, the difference is 40% vs. 30% -- that is a big difference...)
I hope this wasn't too long-winded and confusing??? I would really
appreciate some help. Thanks in advance.
lj
with AreaID and details about the area. It is related to another table
detailing the activity, that is when the SalesRep started working it and
when he finished (DateOut, DateIn)
I'm trying to generate a report that tells what percentage of the total
areas have been covered in a given time. I have an expression on the
report:
=Round(Count([AreaID])/[CountOfAreaID]*100,2) & "%"
The Count([AreaID]) refers to those areas included in my activity query as
having been covered during the specified time.
The CountOfAreaID is a query on the Area table to give me the total areas we
have.
This all works fine unless an area has been covered more than once during
the time period. I need to show the record, but I don't want to count it.
What I want is a Count of how many different areas were worked:
AreaID DateOut DateIn
1 Mar 3 June 4
4 Apr 6 May 9
4 July 7 Aug 10
7 Feb 14 Mar 20
I want it to give a Count of 3, since three different areas were covered,
not four because that messes up the percentage. ( i.e., If there were 10
areas, the difference is 40% vs. 30% -- that is a big difference...)
I hope this wasn't too long-winded and confusing??? I would really
appreciate some help. Thanks in advance.
lj