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
- SELECT Count(subH.MEQA) AS [Count],
-
Format(subD.Month,'mmmm yyyy') AS [Month]
-
FROM (SELECT DateAdd('m',[DateOffset],Forms!Stats.txtCurrentDate) AS Month
-
FROM tblDate) AS subD LEFT JOIN
-
(SELECT [Category] AS [MEQA],
-
[Month]
-
FROM tblProtoHistory
-
WHERE (([Initials]=Forms!Stats.cbInitials)
-
AND ([PV1]='Mail Service'))) AS subH
-
ON subD.Month = subH.Month
-
GROUP BY Format(subD.Month,'mmmm yyyy'),
-
subD.Month
-
ORDER BY subD.Month DESC;
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
- SELECT Count(subH.MEQA) AS MEQA_PSC, Format(subD.Month,'mmmm yyyy') AS [Month]
-
FROM [SELECT DateAdd('m',[DateOffset],Forms!Stats.txtCurrentDate) AS Month
-
FROM tblDate]. AS subD LEFT JOIN [SELECT [Category] AS [MEQA],
-
[Month]
-
FROM tblProtoHistory
-
WHERE (([Initials]=Forms!Stats.cbInitials)
-
AND ([PV1]='PSC'))]. AS subH ON subD.Month = subH.Month
-
GROUP BY Format(subD.Month,'mmmm yyyy'), subD.Month
-
ORDER BY subD.Month DESC;
both these queries comes from this table - Table Name=[tblProtoHistory]
- Field; Type; IndexInfo
-
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
- SELECT Format(subD.Month,'mmmm yyyy') AS [Month], nz([pv],0.1) AS PV1
-
FROM (SELECT DateAdd('m',[DateOffset],Forms!Stats.txtCurrentDate) 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.cbInitials) AS subH
-
ON subD.YM = subH.MonthEnd
-
ORDER BY subD.Month DESC;
- Table Name=[Dashboard_PV]
- Field; Type; IndexInfo
-
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
22  2773 
Hi NeoPa, I inadvertedly duplicated this thread - I don't know know how to remove the duplicate. My apologies.
NeoPa 32,561
Recognized Expert Moderator MVP
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 32,561
Recognized Expert Moderator MVP
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.
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 32,561
Recognized Expert Moderator MVP
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.
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 32,561
Recognized Expert Moderator MVP
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 : - Format([Date Reported],'m/d/yyyy')
If you want 2/1/2008 as January 2nd it would be : - 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.
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 :
- Format([Date Reported],'m/d/yyyy')
If you want 2/1/2008 as January 2nd it would be :
- 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.
NeoPa 32,561
Recognized Expert Moderator MVP
RIC.
No, not Ric - Ah, I See ;)
In that case simply replace the d with a 1. - Format([Date Reported],'m/1/yyyy')
Does that help?
PS. Congratulations on reaching 100 posts :)
RIC.
No, not Ric - Ah, I See ;)
In that case simply replace the d with a 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
NeoPa 32,561
Recognized Expert Moderator MVP
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.
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
NeoPa 32,561
Recognized Expert Moderator MVP
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 (... - Call DoCmd.TransferSpreadsheet(TransferType, _
-
SpreadsheetType, _
-
TableName, _
-
FileName, _
-
HasFieldNames, _
-
Range, _
-
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.
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 (...
- Call DoCmd.TransferSpreadsheet(TransferType, _
-
SpreadsheetType, _
-
TableName, _
-
FileName, _
-
HasFieldNames, _
-
Range, _
-
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?
NeoPa 32,561
Recognized Expert Moderator MVP
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.
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.
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
NeoPa 32,561
Recognized Expert Moderator MVP
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.
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.
NeoPa 32,561
Recognized Expert Moderator MVP
Good for you Rick.
Let me know if you have any specific issues you want assistance with :)
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I COULD be wrong... :) I've tried the
access group...twice...and all I get is "Access doesn't like ".", which I know,
or that my query names are too...
|
by: jamminc |
last post by:
Hi All,
I am currently developing a module where the user will input multiple items
(as many as 20-600) and I am suppose to retrieve it from a sql database all
the information and update it with some calculation which is quite intense.
Currently, I am using a loop to select the information from the table
For i = 0 to SelectedParts.count...
|
by: jjturon |
last post by:
Can anyone help me??
I am trying to pass a Select Query variable to a table using Dlookup
and return the value to same select query but to another field.
Ex.
SalesManID SalesManName
AT Alan Time
|
by: rAinDeEr |
last post by:
Hi,
I have a web application with a table to store terms and
conditions of a Company.
This may some times run into many pages and some times it may be just a
few sentences. It is a character text field. I want to know which Data
type I need to use so that it doesnt waste memory.
thanks in advance,
rAinDeEr
|
by: Ian Richardson |
last post by:
Hi,
The function I've put together below is a rough idea to extend a SELECT
list, starting from:
<body>
<form name="bambam">
<select id="fred">
<option value="1">1</option>
<option value="2">2</option>
| | |
by: Jimmy |
last post by:
thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far:
<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source=" & msDbFilename
moConn.Properties("Persist Security Info") = False
moConn.ConnectionString = msConnString
moConn.CursorLocation = adUseClient...
|
by: Rickster66 |
last post by:
This thread is similar to the thread (Select only 10 columns going back) that I posted regarding the dynamic selection of data going back one month at a time for 12 months. Here is the qiote fron the other thread (#3) :
This time, the difference is in how the data from the table is presented. I constructed the table from a series of data...
|
by: trose178 |
last post by:
Good day all, I am working on a multi-select list box for a standard
question checklist database and I am running into a syntax error in
the code that I cannot seem to correct. I will also note that I am
using Allen Browne's multi-select list box for a report as a guide. I
should also note that my access skills are not the best so I may need...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
