Select Only 10 Columns Going Back (Calculations)

[Rickster66]

As Instructed this is a new thread regarding my original post: "Select Only 10 Columns Going Back"
I'm sorry for the late response. I've been gathering up information and carefully with as much detail as possible, making clear and straiforward for you.

I need to create 3 new queries based on the queries that you wrote. Each query has a numerical value and a textual value. The new queries are based on the queries with a numerical value. I will show these queries with data to illustrate. As each query displays numerical values going back one month a time for the last 12 months, The numerical values should display cumulative figures going back 3 months back, 6, 9 and 12.

For Mail Service:

Dates	       MEQA_Mail_Service
April 2008      0
March 2008      0
February 2008   0
January 2008    0
December 2007   1
November 2007   1
October 2007    0
September 2007  1
August 2007     2
July 2007       0
June 2007       0
May 2007        0

[CODE=SQL]SELECT Count(subH.MEQA ) AS [Count],
Format(subD.Mon th,'mmmm yyyy') AS [Month]
FROM (SELECT DateAdd('m',[DateOffset],Forms!Stats.tx tCurrentDate) AS Month
FROM tblDate) AS subD LEFT JOIN
(SELECT [Category] AS [MEQA],
[Month]
FROM tblProtoHistory
WHERE (([Initials]=Forms!Stats.cb Initials)
AND ([PV1]='Mail Service'))) AS subH
ON subD.Month = subH.Month
GROUP BY Format(subD.Mon th,'mmmm yyyy'),
subD.Month
ORDER BY subD.Month DESC;[/CODE]
For PSC:

Dates	    MEQA_PSC
April 2008      0
March 2008      0
February 2008   0
January 2008    0
December 2007   0
November 2007   4
October 2007    0
September 2007  0
August 2007     2
July 2007       0
June 2007       0
May 2007        9

[CODE=SQL]SELECT Count(subH.MEQA ) AS MEQA_PSC, Format(subD.Mon th,'mmmm yyyy') AS [Month]
FROM [SELECT DateAdd('m',[DateOffset],Forms!Stats.tx tCurrentDate) AS Month
FROM tblDate]. AS subD LEFT JOIN [SELECT [Category] AS [MEQA],
[Month]
FROM tblProtoHistory
WHERE (([Initials]=Forms!Stats.cb Initials)
AND ([PV1]='PSC'))]. AS subH ON subD.Month = subH.Month
GROUP BY Format(subD.Mon th,'mmmm yyyy'), subD.Month
ORDER BY subD.Month DESC;[/CODE]
both these queries comes from this table

Table Name=[[U]tblProtoHistory[/U]]
[I]Field;        Type;        IndexInfo[/I]
memberID;     AutoNumber;  PK
DateReceived; Date/Time
Month;        Date/Time
Initials;     Text
Name;         Text
PV1;          Text
MEQA;         Numeric

The titles for columns PV1 and DateReceived came from the numerous data dumps from which I assembled this query. The value for PV1 here: "Mail Service or "PSC." This is not to be confused with the PV1 column in the Dashboard_PV query below. Sorry for the ambiguity.


For Dashboard_PV:

Month            PV1
April 2008         0.1
March 2008         0.1
February 2008   4560
January 2008      76
December 2007   3171
November 2007   2816
October 2007    2126
September 2007  3093
August 2007     3022
July 2007       3070
June 2007       2246
May 2007        2311

[CODE=SQL]SELECT Format(subD.Mon th,'mmmm yyyy') AS [Month], nz([pv],0.1) AS PV1
FROM (SELECT DateAdd('m',[DateOffset],Forms!Stats.tx tCurrentDate) AS Month,
Format([Month],'yyyymm') AS YM
FROM tblDate
WHERE [DateOffset]>-12) AS subD LEFT JOIN [SELECT [PV],
[MonthEnd]
FROM Dashboard_PV
WHERE [Initials]=Forms!Stats.cb Initials) AS subH
ON subD.YM = subH.MonthEnd
ORDER BY subD.Month DESC;[/CODE]

Table Name=[[U]Dashboard_PV[/U]]
[I]Field;    Type;        IndexInfo[/I]
Initials; AutoNumber;  PK
MonthEnd; String
PV;       Number
Username; String

This means that, there is a MEQA query for Mail Service, a MEQA query for PSC and a Dashboard query which stands on its own.

Now I need a query that calculates performance percentages going back 3 months, 6 months, 9 months and 12 months. For example, we are looking at data from 3 months back, the current month the last month and the month before, that is, the 3rd month back is summed up and divided. The basic formula is:

For the current month:

Dashboard_PV - MEQA/Dashboard_PV * 100

For 3 months back:

Sum(last 3 months Dashboard_PV) - Sum(Last 3 months MEQA)/SUM(Last 3 months Dashboard_PV) * 100 =
For 6 months back:

Sum(last 6 months Dashboard_PV) - Sum(Last 6 months MEQA)/Sum(Last 6 months Dashboard_PV) * 100 =

For 9 months back:

Sum(last 9 months Dashboard_PV) - Sum(Last 9 months MEQA)/Sum (Last 9 months Dashboard_PV )* 100 =

For 12 months back:

Sum(last 12 months Dashboard_PV) - Sum(Last 12 months MEQA)/SUM(Last 12 months Dashboard_PV * 100) =

This would compose the queries, One for mail service and one for PsC. The last query would the summation of Dashboard_PV + MEQA and then also apply the formulas above.

I wrote this out as clearly as possible, Please let me know if you want me to clarify something-

I was researching about EXCEL as a possibility for these calculations. I wanted to know how you felt about this. Could it work? Could it be viable?

Thanks so much
Richard

[Rickster66]

Hi NeoPa, I inadvertedly duplicated this thread - I don't know know how to remove the duplicate. My apologies.

[NeoPa]

Duplicate removed and link to original thread (Select Only 10 Columns Going Back) added.

[Rickster66]

Duplicate removed and link to original thread (Select Only 10 Columns Going Back) added.

Great, very good. This way it's less confusing

[NeoPa]

I'm still working on tidying up the original post so that it displays correctly and is as correct as I can make it. Bear with me a while. There's a lot of it to work on.

[NeoPa]

Tidying of original post completed (at least for now).

Now I need to give this a good hard look and see if I can think of a way that Access can handle these extreme requirements.

I'll keep you posted.

[Rickster66]

Tidying of original post completed (at least for now).

Now I need to give this a good hard look and see if I can think of a way that Access can handle these extreme requirements.

I'll keep you posted.

Sounds good,

thanks again

[NeoPa]

Richard, please see related PM.

I'm afraid I just can't follow the question clearly enough to be able to help.

Using Excel may be a good idea for you :
On the plus side, that sort of displaying of disparate figures it handles quite well.
On the other hand, programming it (especially from Access) is not too trivial.

If you have any more precise questions (on how to do a particular thing) then I'd be happy to see if I can help. I have fairly extensive experince in Excel as well as in Access.

Good luck with this.

[Rickster66]

Richard, please see related PM.

I'm afraid I just can't follow the question clearly enough to be able to help.

Using Excel may be a good idea for you :
On the plus side, that sort of displaying of disparate figures it handles quite well.
On the other hand, programming it (especially from Access) is not too trivial.

If you have any more precise questions (on how to do a particular thing) then I'd be happy to see if I can help. I have fairly extensive experince in Excel as well as in Access.

Good luck with this.

I need your help

I need to update the date column to for example to 1/1/2008 or to 2/1/2008 as you specified before - Tjhis what I've been using

Month: Format([Date Reported],'mmmm yyyy')

but I'm getting
"January 2008, etc- do you have a formulas for converting to for example 6/1/2008 format instead which is what you specified?
thanks

Richard

[NeoPa]

Richard, it's not clear from your examples (2/1/2008) whether you're referring to January 2nd or February 1st.

The Format() function can handle either of course.
If you want 2/1/2008 as February 1st it would be :
[CODE=SQL]Format([Date Reported],'m/d/yyyy')[/CODE]
If you want 2/1/2008 as January 2nd it would be :
[CODE=SQL]Format([Date Reported],'d/m/yyyy')[/CODE]
If you're in the USA it's likely you'll be after the first approach. In Britain (or Europe I think too) you'll be after the second.

[Rickster66]

Richard, it's not clear from your examples (2/1/2008) whether you're referring to January 2nd or February 1st.

The Format() function can handle either of course.
If you want 2/1/2008 as February 1st it would be :
[CODE=SQL]Format([Date Reported],'m/d/yyyy')[/CODE]
If you want 2/1/2008 as January 2nd it would be :
[CODE=SQL]Format([Date Reported],'d/m/yyyy')[/CODE]
If you're in the USA it's likely you'll be after the first approach. In Britain (or Europe I think too) you'll be after the second.

I meant to use both dates, what I need is to be able to for the day to always display 1 no matter what. So if I have 10/16/2007 I need it to update to 10/1/2007; If I have 3/23/2008, I need to update to 3/1/2008 and so on. The update should be made for the day and so far it's not taking place.

[NeoPa]

RIC.

No, not Ric - Ah, I See ;)

In that case simply replace the d with a 1.
[CODE=SQL]Format([Date Reported],'m/1/yyyy')[/CODE]
Does that help?

PS. Congratulations on reaching 100 posts :)

[Rickster66]

RIC.

No, not Ric - Ah, I See ;)

In that case simply replace the d with a 1.
[CODE=SQL]Format([Date Reported],'m/1/yyyy')[/CODE]
Does that help?

PS. Congratulations on reaching 100 posts :)

Yes it works!!!! I only ever wish my solutions were as uncomplicated and elegant as yours.

Thank you! Do I get a 100 post prize? --lol

You guys have helped me out a great deal - thank you so much

I've also learned 100 times more than I ever did while I was in College taking this same subject

-----I have a related question on the same subject

I ran the queries that you wrote and turned then into tables using a make-table statement. Then, I created a form where the new tables are displayed as subforms. The form is working very well. How would you recommend going about passing this form with 4 subqueries to an excel spreadsheet. I even though of passing the data to WORD using Automation. I did it before and it's always worked very well. The idea is to be able to send via email this form as a document informing the user how he or she has performed. I've read up on many ways to transfer data to excel but this not my forte, what do you suggest?

Richard

[NeoPa]

Right, a straightforward if not entirely simple question.

I'll look into this sometime over the weekend. I think I did something on this recently I can probably dig up and link to with a bit of explanation thrown in for luck if it's needed.

As usual, if I forget, feel free to bump. This one shouldn't be too much of an issue though.

[Rickster66]

Right, a straightforward if not entirely simple question.

I'll look into this sometime over the weekend. I think I did something on this recently I can probably dig up and link to with a bit of explanation thrown in for luck if it's needed.

As usual, if I forget, feel free to bump. This one shouldn't be too much of an issue though.

That's great -thanks

Richard