analyze table

[austenr]

I need to do an analysis on a table showing trends. This is an obvious choice for excel but the problem is that the amount of rows (145,000) precludes me from using that tool. What I need to do is show a 12 month trend on a particular field showing increase and decrease in it, preferably a scatter plot or line graph or something. Also, the ability to pick out a particular account for a that same time period and do the same. I am at a loss as to how to go about completing this task. Any help is appreciated. Thanks

[OBP]

Hello austenr, this sounds interesting.
Do you want to plot a trend of an average for each month, a total for each month?
What is it that you are trying to plot, a single value, multiple values, combined values?
This should be quite easy to achieve using a Cross Tab query but it really depends upon your data structure.
If you could post a small sample I will have a look for you.

[austenr]

Hi OBP,

What I need is to plot the trend in a certain charge for the months given. So lets sat that acct #1 has a charge of 10.00 for month one, 25.00, for month two, 3.00 for month three, 98.00 for month four. Each month has about 10,000 accounts in is so you would have to find the specified account in each month and then present it in a plot or graph of some sort. If you would want to make up a small DB with four months with each month have a few records as an example that would be great. Unfortunatly, I cannot sanitize and post a sample but hopefully from my example, you get the idea. If this is not clear please post back or send me an IM. Thanks

[OBP]

Something like this?
The first query shows all accounts and the second restricts it to account 1.

[OBP]

austenr, here is the new version, go to the Form called "Control Form", slect one of your accounts (1, 22 or 30) and hit the View Account Data button.
It usses the Version 2 query.

[stanl]

Assuming the use of the word trend is to plot the relative difference(s) in Charges between dates you might consider something like this (paste in as a new query into OPB's DB, but note I changed the field name from "account number" to acct because I'm old, and I also think the HAVING clause needs work to eliminate non-sequential monthly trends).



SELECT a.acct, a.date, b.date, (b.Charge-a.Charge) AS trend
FROM Charges AS a LEFT JOIN Charges AS b ON (a.date<b.date) AND (a.acct=b.acct)
GROUP BY a.acct, a.Date, b.Date, b.Charge-a.Charge
HAVING COUNT(*)=1;

Stan

[austenr]

Thanks Stan

[austenr]

Thanks OBP. Both your and Stan's work have helped me out considerably. I think I have a good handle on it now thanks to you both. Great job!!

[austenr]

OK I have hit a bump in the road. Now I need to do another crosstab query that will give the revenue up to three months before the account closed. I have the table and the data is there but when I run the crosstab query, i get no amounts for revenue.

Say the account closed in 20060701. I have the table with the charges from the previous three months up until 20060701 but when i do the query I dont get anything. As the table contains propriteritary data I cannot post it. The fields are as follows:

Statement date, Bank #, Acct #, revenue amt, closed date. Any ideas? Thanks

[OBP]

austenr, it sounds as if your date criteria is not correctly set, can you use my database, modified to show what you are trying to do?

[austenr]

well i got it to work but there is an extra row under the data I need with all zeroes. So now I have to find a way to get rid of the extra row. Can send you an example tomorrow.

[austenr]

Tony,

Here is my output from my crosstab query. As you can see, I get two lines for each closed account. Suggestions?

[stanl]

OK I have hit a bump in the road. Now I need to do another crosstab query that will give the revenue up to three months before the account closed.

Maybe a stretch here, but if you go back to the LEFT JOIN code I posted you could alter the HAVING clause to include (b.Date<( MAX(a.Date-93) ), which assumes all accounts have at least 3 months of activity. This, of couse, begs the question: do you want to solve the problem in Access, or just query the data into Excel and build any charts there.:dunno Stan

[austenr]

Definatly solve in Access. I believe that my CTQ is incorrect but not sure.

[OBP]

austenr, I think this crosstab basically does what you want, I have just used from today's date - 90 days to today's date.
I think you are getting the extra line because you haven't set the Column that you are using to restrict the dates to have a Group function of "Where", i.e. it is criteria, it has probably defaulted to "Group".

[austenr]

Thanks Tony that helped me out. Actually I did the same exact CTQ on another simalar table and it worked fine. Like you said must have been in the set up. Thanks for the time you spent on it for me. Cheers.

[OBP]

austenr, tricky little SOBs aren't they:beerchug:

[CFDM]

that works better in excel and i never say that