Calculation to Obtain Compliance % Using Count of Partial Field V

[ctfrigg]

Hi!

I need to create a query that seems a little more complex than what I have
done before. I would greatly appreciate any advice on how I can accomplish
the following.

I have airline invoice detail that I need to compute the compliance % of
travelers booking air travel at least 14 days in advance. This field contains
a number. There is also a field that states whether the transaction is
"Over14" or "Less14".

I have previously done this calculation in Excel . I used a pivot table and
created a calculated item using the Count of "Over14"/Total Count of the
Over14 & Less14 for the value; Invoice Quarter as the column heading & a
manager's name (or country) as the row heading. I now need to do this in
Access as there is too much data and I need to be able to provide trending
information by traveler and manager.

Q1 2008 Q2 2008 Q3 2008
Manager1 75% 72% 73%
Manager2 65% 69% 70%


Table fields: Manager, Country, Days Advance Purchase (contains "Over14" or
"Less14"), Net Ticket Amt, Invoice Qtr ("Q1 2008", "Q2 2008", "Q3 2008", "Q4
2008") and some others.

What is the best way to do this??

Thank you so much for the help!

 

[Dale Fye]

I'm not sure if I fully understand your data structure, but I'll give it a
shot. Because you want to column that reflect the values in row of your
table, you are going to need to create a crosstab query.

First, add the Manager, [Invoice Qtr], and [Days Advance Purchase] fields to
the query grid.

Then, change the query to a Crosstab query, and adjust the values in the
grid so that it looks something like:

Field: Manager Invoice Qtr [Days Advance Purchase]
Table:
Total: Group By Group By Expression
Total: Row Head Col Head Value

Then replace [Days Advance Purchase] with:

Sum(iif([Days Advance Purchase] = "Over14", 1, 0))/Count([Days Advance
Purchase])

What this is telling Access to do is to group by manager and invoice
quarter, and then count the number of "Over14" values and divide that by the
total number of values for each manager/Invoice Quarter combination. It may
seem strange to use the Sum(iif([Days Advance Purchase] = "Over14", 1, 0) to
get the count of records but if you use Count( ) instead, you will count the
1's and 0's. By using Sum, you are actually only "counting" the 1's

When you look at the final query in the SQL view, it should look something
like:


TRANSFORM
Sum(IIf([Days_Advance_Purchase]="Over14",1,0))/Count([Days_Advance_Purchase])
AS Expr1
SELECT tbl_Tickets.Manager
FROM tbl_Tickets
GROUP BY tbl_Tickets.Manager
PIVOT tbl_Tickets.[Invoice Qtr];

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.