CrossTab Query

[Trey]

Hi,

I need to create a crosstab that produces cumulative percentages over
12 months. I have a database with several companies entered in it.

Each company is assigned one of two types: TypeA, TypeB

The companies are also assigned 3 dates: Estimation1, Estimation2,
Actual

Finally each company has a sales revenue %.


My query needs to figure out which date is higher (est1, est2, or
actual) and then use ONLY that date for the query. (this is where i am
stuck). It will then need to produce a cumulative sum of the revenue
figures.

|Jan | Feb |
|BusA |Revenue % | Revenue % |
|BusB |2% | 4% | 6

Does anyone have any ideas? Thanks in advance!

-Trey

 

[Duane Hookom]

It would help if you typed or copied a few records into your message. Are
you actually storing revenue percent or is this a calculation?

 

[Trey]

Duane,

Here is a sample record:

Business Name | Type | Est1 | Est2 | Actual | Revenue |
Joe's Hotdogs |FastFood |1/1/02 |1/3/04 |4/4/04 | 20% |
Mesa Grill |SitDown |2/5/05 |5/4/05 | | 2% |
Hans Sushi |SitDown |4/5/05 |6/4/05 |2/4/02 | 2% |
Toms Grill |FastFood |9/5/05 |11/4/05| | 13% |


So the cross tab will need to order by type, then select actual date
if present, if not use the larger of Est1 or Est2. I have entered all
of the revenues to add up to 100, thus why i would like it to do a
cumulative count (to show when revenue hits 100%).

Thanks in advance

 

[Duane Hookom]

Your first issue is normalization. If the dates each created a record in a
separate, related table then grabbing the max would be simple. However, I
would create a function that accepts the three date fields as arguements and
returns only the maximum. You could then use this in your crosstab.