By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,932 Members | 1,547 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,932 IT Pros & Developers. It's quick & easy.

Select Only 10 Columns Going Back (Calculations)

100+
P: 106
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:

Expand|Select|Wrap|Line Numbers
  1. Dates           MEQA_Mail_Service
  2. April 2008      0
  3. March 2008      0
  4. February 2008   0
  5. January 2008    0
  6. December 2007   1
  7. November 2007   1
  8. October 2007    0
  9. September 2007  1
  10. August 2007     2
  11. July 2007       0
  12. June 2007       0
  13. May 2007        0
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(subH.MEQA) AS [Count],
  2.        Format(subD.Month,'mmmm yyyy') AS [Month]
  3. FROM (SELECT DateAdd('m',[DateOffset],Forms!Stats.txtCurrentDate) AS Month
  4.       FROM tblDate) AS subD LEFT JOIN 
  5.            (SELECT [Category] AS [MEQA],
  6.                    [Month]
  7.             FROM tblProtoHistory
  8.             WHERE (([Initials]=Forms!Stats.cbInitials)
  9.               AND  ([PV1]='Mail Service'))) AS subH
  10.         ON subD.Month = subH.Month
  11. GROUP BY Format(subD.Month,'mmmm yyyy'),
  12.          subD.Month
  13. ORDER BY subD.Month DESC;
For PSC:
Expand|Select|Wrap|Line Numbers
  1. Dates        MEQA_PSC
  2. April 2008      0
  3. March 2008      0
  4. February 2008   0
  5. January 2008    0
  6. December 2007   0
  7. November 2007   4
  8. October 2007    0
  9. September 2007  0
  10. August 2007     2
  11. July 2007       0
  12. June 2007       0
  13. May 2007        9
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(subH.MEQA) AS MEQA_PSC, Format(subD.Month,'mmmm yyyy') AS [Month]
  2. FROM [SELECT DateAdd('m',[DateOffset],Forms!Stats.txtCurrentDate) AS Month
  3.       FROM tblDate]. AS subD LEFT JOIN [SELECT [Category] AS [MEQA],
  4.               [Month]
  5.        FROM tblProtoHistory
  6.        WHERE (([Initials]=Forms!Stats.cbInitials)
  7.          AND  ([PV1]='PSC'))]. AS subH ON subD.Month = subH.Month
  8. GROUP BY Format(subD.Month,'mmmm yyyy'), subD.Month
  9. ORDER BY subD.Month DESC;
both these queries comes from this table
Expand|Select|Wrap|Line Numbers
  1. Table Name=[tblProtoHistory]
  2. Field;        Type;        IndexInfo
  3. memberID;     AutoNumber;  PK
  4. DateReceived; Date/Time
  5. Month;        Date/Time
  6. Initials;     Text
  7. Name;         Text
  8. PV1;          Text
  9. 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:
Expand|Select|Wrap|Line Numbers
  1. Month            PV1
  2. April 2008         0.1
  3. March 2008         0.1
  4. February 2008   4560
  5. January 2008      76
  6. December 2007   3171
  7. November 2007   2816
  8. October 2007    2126
  9. September 2007  3093
  10. August 2007     3022
  11. July 2007       3070
  12. June 2007       2246
  13. May 2007        2311
Expand|Select|Wrap|Line Numbers
  1. SELECT Format(subD.Month,'mmmm yyyy') AS [Month], nz([pv],0.1) AS PV1
  2. FROM (SELECT DateAdd('m',[DateOffset],Forms!Stats.txtCurrentDate) AS Month,
  3.              Format([Month],'yyyymm') AS YM
  4.       FROM tblDate
  5.       WHERE [DateOffset]>-12) AS subD LEFT JOIN [SELECT [PV],
  6.             [MonthEnd]
  7.       FROM Dashboard_PV
  8.       WHERE [Initials]=Forms!Stats.cbInitials) AS subH
  9.   ON subD.YM = subH.MonthEnd
  10. ORDER BY subD.Month DESC;
Expand|Select|Wrap|Line Numbers
  1. Table Name=[Dashboard_PV]
  2. Field;    Type;        IndexInfo
  3. Initials; AutoNumber;  PK
  4. MonthEnd; String
  5. PV;       Number
  6. 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:
Expand|Select|Wrap|Line Numbers
  1. Dashboard_PV - MEQA/Dashboard_PV * 100
  2.  
  3. For 3 months back:
  4.  
  5. Sum(last 3 months Dashboard_PV) - Sum(Last 3 months MEQA)/SUM(Last 3 months Dashboard_PV) * 100 =
  6. For 6 months back:
  7.  
  8. Sum(last 6 months Dashboard_PV) - Sum(Last 6 months MEQA)/Sum(Last 6 months Dashboard_PV) * 100 =
  9.  
  10. For 9 months back:
  11.  
  12. Sum(last 9 months Dashboard_PV) - Sum(Last 9 months MEQA)/Sum (Last 9 months Dashboard_PV )* 100 =
  13.  
  14. For 12 months back:
  15.  
  16. 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
Apr 28 '08 #1
Share this Question
Share on Google+
22 Replies


100+
P: 106
Hi NeoPa, I inadvertedly duplicated this thread - I don't know know how to remove the duplicate. My apologies.
Apr 29 '08 #2

NeoPa
Expert Mod 15k+
P: 31,758
Duplicate removed and link to original thread (Select Only 10 Columns Going Back) added.
May 1 '08 #3

100+
P: 106
Duplicate removed and link to original thread (Select Only 10 Columns Going Back) added.
Great, very good. This way it's less confusing
May 1 '08 #4

NeoPa
Expert Mod 15k+
P: 31,758
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.
May 1 '08 #5

NeoPa
Expert Mod 15k+
P: 31,758
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.
May 2 '08 #6

100+
P: 106
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
May 2 '08 #7

NeoPa
Expert Mod 15k+
P: 31,758
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.
May 6 '08 #8

100+
P: 106
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
Expand|Select|Wrap|Line Numbers
  1. 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
May 7 '08 #9

NeoPa
Expert Mod 15k+
P: 31,758
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 :
Expand|Select|Wrap|Line Numbers
  1. Format([Date Reported],'m/d/yyyy')
If you want 2/1/2008 as January 2nd it would be :
Expand|Select|Wrap|Line Numbers
  1. Format([Date Reported],'d/m/yyyy')
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.
May 8 '08 #10

100+
P: 106
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 :
Expand|Select|Wrap|Line Numbers
  1. Format([Date Reported],'m/d/yyyy')
If you want 2/1/2008 as January 2nd it would be :
Expand|Select|Wrap|Line Numbers
  1. Format([Date Reported],'d/m/yyyy')
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.
May 8 '08 #11

NeoPa
Expert Mod 15k+
P: 31,758
RIC.

No, not Ric - Ah, I See ;)

In that case simply replace the d with a 1.
Expand|Select|Wrap|Line Numbers
  1. Format([Date Reported],'m/1/yyyy')
Does that help?

PS. Congratulations on reaching 100 posts :)
May 8 '08 #12

100+
P: 106
RIC.

No, not Ric - Ah, I See ;)

In that case simply replace the d with a 1.
Expand|Select|Wrap|Line Numbers
  1. Format([Date Reported],'m/1/yyyy')
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
May 9 '08 #13

NeoPa
Expert Mod 15k+
P: 31,758
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.
May 9 '08 #14

100+
P: 106
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
May 9 '08 #15

NeoPa
Expert Mod 15k+
P: 31,758
I've had a dig and can't find anything remotely connected I'm afraid :(

Basically, a worksheet in an Access export to Excel is named after the TableName parameter (...
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.TransferSpreadsheet(TransferType, _
  2.                                SpreadsheetType, _
  3.                                TableName, _
  4.                                FileName, _
  5.                                HasFieldNames, _
  6.                                Range, _
  7.                                UseOA)
...)
For multiple worksheets to be exported it will take multiple queries (or some clever renaming). Otherwise the names can be allowed to be handled automatically by Access or even put into separate workbooks (files).

Let me know if this answers your current needs.
May 13 '08 #16

100+
P: 106
I've had a dig and can't find anything remotely connected I'm afraid :(

Basically, a worksheet in an Access export to Excel is named after the TableName parameter (...
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.TransferSpreadsheet(TransferType, _
  2.                                SpreadsheetType, _
  3.                                TableName, _
  4.                                FileName, _
  5.                                HasFieldNames, _
  6.                                Range, _
  7.                                UseOA)
...)
For multiple worksheets to be exported it will take multiple queries (or some clever renaming). Otherwise the names can be allowed to be handled automatically by Access or even put into separate workbooks (files).

Let me know if this answers your current needs.
This could work for me. Also, I was thinking of creating a report with several subreports to accomodate the queries. Could it make sense?
May 13 '08 #17

NeoPa
Expert Mod 15k+
P: 31,758
I think it could.

I can't promise it'll be easy though. I've rarely used subreports myself. They need to be handled carefully if my memory serves me correctly.
May 13 '08 #18

100+
P: 106
I think it could.

I can't promise it'll be easy though. I've rarely used subreports myself. They need to be handled carefully if my memory serves me correctly.
That's fine.Your help is always appreciated.
May 15 '08 #19

100+
P: 106
That's fine.Your help is always appreciated.
Hey NeoPa, I've been uploading datadumps so I've been busy - the idea of subreports seems so far a feasibible alternative - how do you suggest we start?Regards,

Richard
May 20 '08 #20

NeoPa
Expert Mod 15k+
P: 31,758
I suggest you start by seeing what you can do on your own. You can't learn by being walked through everything.

When you struggle, that's when you should think about calling in help.

Even if you fall over a couple of times, it's still worth your trying on your own first.
May 20 '08 #21

100+
P: 106
I suggest you start by seeing what you can do on your own. You can't learn by being walked through everything.

When you struggle, that's when you should think about calling in help.

Even if you fall over a couple of times, it's still worth your trying on your own first.
Absolutely, I've been working on them right now.
May 22 '08 #22

NeoPa
Expert Mod 15k+
P: 31,758
Good for you Rick.

Let me know if you have any specific issues you want assistance with :)
May 22 '08 #23

Post your reply

Sign in to post your reply or Sign up for a free account.