Any Statisticians??? I need help w/ 3 Month Rates 1

[air0jmb]

I have a table that captures monthly data totals for aircraft departures and exceptions.

tblData has the following fields: CalMonth, Tail, Exc, SRD

CalMonth is Date/Time with each entry entered as first day of month: 1/1/05, 2/1/02.... 12/1/05. (I'm using only 2005 records to help simplify.)

Tail is Text and contains aircraft numbers: 101, 102, 103... 132

Exc contains number of Exceptions for the month

SRD contains number of Departures for the month

So, tblData has 384 records (12 months x 32 Tails)

I want the 3 month rates of Exceptions [Exc] per 100 Departures [SRD] for each Tail for each month.

If I qry tblData to filter for only one tail, the result is 12 records...

If I use this query I get the 3 month rates...

SELECT qryA.CalMonth, Sum(qryA_1!Exc)/Sum(qryA_1!SRD)*100 AS 3mRate
FROM qryA, qryA AS qryA_1
WHERE (((qryA_1.CalMonth)>DateAdd("m",-3,[qryA]![CalMonth]) And (qryA_1.CalMonth)<=[qryA]![CalMonth]))
GROUP BY qryA.CalMonth
HAVING (((Count(qryA_1.CalMonth))>2));

...The result is 10 records (3/1/05 - 12/1/05) since the first 3 month rate doesn't occur until 3/1/05. I can now use this query in a line chart to show the 3 month rates over time, establish performance standards, etc.

But what I'm really after is the 3 month rate for each Tail in tblData (not just one tail). If I use the above query but replace qryA with tblData, everything goes screwy. This is regardless of whether I bring the Tail field into the query or not...

SELECT tblData.CalMonth, tblData.Tail, Sum([tblData_1].[Exc])/Sum([tblData_1].[SRD])*100 AS Ex_3mRate
FROM tblData, tblData AS tblData_1
WHERE (((tblData_1.CalMonth)>DateAdd("m",-3,[tblData].[CalMonth]) And (tblData_1.CalMonth)<=[tblData].[CalMonth]))
GROUP BY tblData.CalMonth, tblData.Tail
HAVING (((Count(tblData_1.CalMonth))>2));

I'm hoping to end up with 332 records - 10 months(3/1/05-12/1/05) x 32 Tails, with 3 month rate for each displayed.

Can I do this in one query?? Any help would be much appreciated!!

Thanks,

Mike

 

[AlexCuse]

I haven't seen your data, but I think you need to handle for divide by zero errors. Something like this:

iif(sum([tblData_1].[SRD])=0, 0,
Sum([tblData_1].[Exc])/Sum([tblData_1].[SRD])*100 AS Ex_3mRate

Hope this helps,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[AlexCuse]

* note that I forgot last parentheses. Should read:

iif(sum([tblData_1].[SRD])=0, 0,
(Sum([tblData_1].[Exc])/Sum([tblData_1].[SRD])*100)) AS Ex_3mRate

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[rac2]

. . . everything goes screwy . . .

I cant count the number of times I have run into that very same problem myself.


Your reasoning seems correct to me. I dont see why the GROUP BY tail doesnt do the trick. Maybe a slightly different approach will work, using a self-join of the previous two months instead of all previous months.

SELECT M1.CalMonth,
       M1.Tail, 
       Sum( M1.Exc + M2.Exc + M3.Exc ) 
        / Sum( M1.SRD + M2.SRD + M3.SRD )*100 AS Ex_3mRate

FROM tblData M1
JOIN tblData M2 ON M2.CalMonth = DateAdd("m",-1,M1.CalMonth)
               AND M2.Tail = M1.Tail
JOIN tblData M3 ON M3.CalMonth = DateAdd("m",-2,M1.CalMonth)
               AND M3.Tail = M1.Tail

GROUP BY M1.CalMonth, M1.Tail

 

[air0jmb]

Thanks for your replies!

I do account for the zero's in my actual query, but I was trying to keep things as simple as possible while trying to solve this issue.

The screwy part seems to surface when the table or query has more than one record for each month. For example, if I use the query that contains only 1 tail (12 records, 1 for each month for that 1 tail), everything works. But if I perform the same same query where there's 2 or more records for 1 month (I.e, 2 tails), then it goes haywire. Note I haven't even brought the tail field into the query.

Also, when using the example with only 1 record per month, the result in the field >>>HAVING (((Count(tblData_1.CalMonth))>2))>>> = 3 for every record. When I run the query with the data containing 32 records per month, the first record returns "32", the second "64", and then the rest show "96". Additionally, the result includes records for 1/1/05 and 2/1/05... so the 3 month calculation is definitely not working.

I have tried multiple scenarios using the left joins with no luck. But I will definitely give it another spin using your suggestion and post back with results.

Any other suggestions??

Mike

 

[AlexCuse]

rac2's example might do the trick, but I am really stumped as to why yours is not working. Screwy indeed...

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[lameid]

Rac2 nailed it, you need to join on Tail. I noticed a correct solution after coming up with one. I modified your query so it my be easier for you to catch what is wrong. If you group by something in a self join, chances are you want to join on that field too.

Note the addition of "tblData_1.Tail = [tblData].Tail AND" to your where clause below.

SELECT tblData.CalMonth, tblData.Tail, Sum([tblData_1].[Exc])/Sum([tblData_1].[SRD])*100 AS Ex_3mRate
FROM tblData, tblData AS tblData_1
WHERE tblData_1.Tail = [tblData].Tail AND (((tblData_1.CalMonth)>DateAdd("m",-3,[tblData].[CalMonth]) And (tblData_1.CalMonth)<=[tblData].[CalMonth]))
GROUP BY tblData.CalMonth, tblData.Tail
HAVING (((Count(tblData_1.CalMonth))>2));

 

[air0jmb]

lameid,

I copied and pasted your SQL, and it works like a charm.

One question... do I need to create any joins in the design window? you mentioned "you want to join on that field too" but no join lines show up in design window.

I really, really wish I could comprehend what's going on with this query. Is there any way this could be broken down where I might be able to understand it?? Regardless, it works!!!

Many thanks!

Mike

 

[air0jmb]

To each of you that's responded,

Being relatively new at this, I'm kinda curious about what types of applications people using this type of query? I've read hundreds of posts here and haven't come across too much like this. It's boggles me that you can just glance through a problem like mine and simply whip out a response off the top of your head. Obviously, you've done similar for your own applications. Could you help me understand where else one might use this type of query?

Or, where I might go to learn more about using Access for statistical data mining, analysis, trending, reporting, etc. I've yet to see anybody else's databases that are set up for those purposes. Is there any sample databases available? And, if anybody has one in use, would it possible to check out a copy?

Thanks again!

Mike

 

[PHV]

Could you help me understand where else one might use this type of query?
Ranking
Running Total
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lameid]

air0jmb,

There are two ways to join in the a select statement.

SQL ANSI-92 the way Access does it typically. This is done in the From clause.
I.E.

From TableA <Jointype> TableB ON <Criteria>

My example used SQL ANSI-89 syntax or putting the join in the where clause (criteria only).

When you have a non-equijoin (Join on fields/columns that are not equal), I find it is simpler to go with the old ANSI-89 Syntax. I do this because all the join shows up in the same clause. Otherwise you will end up with Tails in your From clause (and get a line) and have your other comparison in your where clause. To see what I mean, start with your query as you posted originally, and in the QBE draw the join line between tails and then switch back to SQL. Both should work, it is more a matter of style and readability and how you think through the problem conceptually. This one is really all preference. There are people who probably would say do as much as ANSI 92 as you can. That may even prove faster.

Hmmm as far as how I knew to do it... I have been using access for almost 10 years and started with version 2.0. I have also read a book on SQL which is why I talked about ANSI standards. The only real difference between standard support and Jet SQL is that the standards do not use a semicolon to end a statement. Also Jet can execute some SQL that is intuitive but beyond the scope of ANSI. Beyond that, I also have been active in these forums for a long time and read a couple Access books (I like Sybex and O'reilly as publishers, again all preference).