Select Only 10 Columns Going Back

[Rickster66]

I need to create a sql statement in MS Access from only the last 12 columns a table that has has dates (the last days of the month) as the names of the columns. In actuality, each column is part of table that is sent to my office monthly. I only use one particular column and discard the rest. The columns in the table then looks like this:

2_29_2008 || 1_31_2007 || 12_31_2007  11_30_2007 10_31_2007 ...etc.....1_31_2007

I need to be able to select any month dynamically and select the last 12 months only.

Another idea is to use the whole tables but I the requirement is that the last 12 months must be gathered. What it basically shows is the number of errors made by a person, so the previous months represent previous performance.

I am very desperate, any help is greatly appreciated!

Richard

[NeoPa]

tblHistory is already linked in, in the SQL provided.

What we need to do is to discover and fix the problem rather than try different things aimlessly. This is not as hard as it seems.

Firstly, do you have VBA code that runs to test this or are you simply invoking it (the SQL in a QueryDef) from the database window?

When you have answered this first question please proceed to setting up the SQL slightly differently as specified here :

SELECT tH.MEQA,
       Format(subD.Month,'d mmmm yyyy') AS [Month]
FROM (SELECT DateAdd('m',-[DateOffset],Forms!frmStats.PVDate) AS Month
      FROM tblDate) AS subD LEFT JOIN
     tblHistory AS tH
  ON subD.Month=tH.Month
ORDER BY subD.Month DESC

This should show the actual dates that are being created as an offset from PVDate and confirms the first step.

When you have done this then post the results in here so that we can progress further.

[NeoPa]

As an afterthought, what happened to the initials in all this?
Was there not a [cbInitials] control on the form that was required to select the data for each individual?

[Rickster66]

tblHistory is already linked in, in the SQL provided.

What we need to do is to discover and fix the problem rather than try different things aimlessly. This is not as hard as it seems.

Firstly, do you have VBA code that runs to test this or are you simply invoking it (the SQL in a QueryDef) from the database window?

When you have answered this first question please proceed to setting up the SQL slightly differently as specified here :

SELECT tH.MEQA,
       Format(subD.Month,'d mmmm yyyy') AS [Month]
FROM (SELECT DateAdd('m',-[DateOffset],Forms!frmStats.PVDate) AS Month
      FROM tblDate) AS subD LEFT JOIN
     tblHistory AS tH
  ON subD.Month=tH.Month
ORDER BY subD.Month DESC

This should show the actual dates that are being created as an offset from PVDate and confirms the first step.

When you have done this then post the results in here so that we can progress further.

What I do is run it on a query on access on the database then for the parameters, the form that selects the initials and the date is where the parameter comes from. that is the extent of it . Show it test it with vba code?

[Rickster66]

As an afterthought, what happened to the initials in all this?
Was there not a [cbInitials] control on the form that was required to select the data for each individual?

What I've been doing is adding the field initials from tbl history then adding the criteria on the design view while unckecking the visible button.

[NeoPa]

What I do is run it on a query on access on the database then for the parameters, the form that selects the initials and the date is where the parameter comes from. that is the extent of it . Show it test it with vba code?

I'm not sure I understand what you mean by this :S

I assume you are still planning on responding to the questions in my post (#51).

[NeoPa]

What I've been doing is adding the field initials from tbl history then adding the criteria on the design view while unckecking the visible button.

This will not produce correct results in all circumstances.

This also means of course that you are testing a different query from what I supplied (and think you are reporting on).

As far as I can tell it would not explain having empty MEQA data though.

Let me provide some replacement SQL that you won't feel you need to change. For this I need you to confirm that the control from your form is named (fully) Forms!frmStats.cbInitials?

[Rickster66]

This will not produce correct results in all circumstances.

This also means of course that you are testing a different query from what I supplied (and think you are reporting on).

As far as I can tell it would not explain having empty MEQA data though.

Let me provide some replacement SQL that you won't feel you need to change. For this I need you to confirm that the control from your form is named (fully) Forms!frmStats.cbInitials?

The control form is Forms!Stats.cbInitials

I am going to test it using vba like you said and let you know

[Rickster66]

I'm not sure I understand what you mean by this :S

I assume you are still planning on responding to the questions in my post (#51).

what i meant is that I've created a query with the code that you wrote and then I tested it on the window. itself on access.

exactly, I got confused I am copying the sql codeso it can be tested on vba and see what happens.

[NeoPa]

The control form is Forms!Stats.cbInitials

I am going to test it using vba like you said and let you know

That sounds fine and I will await your results.

I'm a bit curious about the "Forms!Stats.cbInitials" bit though. In your post (#30) you gave the name of the form as frmStats. Would I be right in thinking this (post #57) was simply a typo?

[Rickster66]

That sounds fine and I will await your results.

I'm a bit curious about the "Forms!Stats.cbInitials" bit though. In your post (#30) you gave the name of the form as frmStats. Would I be right in thinking this (post #57) was simply a typo?

I called status -- a typo

[Rickster66]

That sounds fine and I will await your results.

I'm a bit curious about the "Forms!Stats.cbInitials" bit though. In your post (#30) you gave the name of the form as frmStats. Would I be right in thinking this (post #57) was simply a typo?

I ran the test on vba and I am having the following problem:

Private Sub cmdOK_Click()
  Dim db As DAO.Database
   Dim rs As New ADODB.Recordset
  Dim sSQL As String
  Set db = CurrentDb
 
  sSQL = "SELECT tH.MEQA, Format(subD.Month,'d mmmm yyyy') AS [Month]" & _
         " FROM (SELECT DateAdd('m',[DateOffset], Forms!Stats.txtEndDate) AS Month" & _
      " FROM tblDate) AS subD LEFT JOIN " & _
     " tblHistory As tH" & _
  " ON subD.Month=tH.Month" & _
" ORDER BY subD.Month DESC"
Debug.Print sSQL
 rs.Open sSQL, CurrentProject.Connection
  MsgBox rs!MEQA, vbInformation, rs!FirstName
  MsgBox sSQL
  rs.Close
  Set rs = Nothing
End Sub
 

And the error that I get is: "The select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

[NeoPa]

I called status -- a typo

Now I'm really confused. Your answer isn't an answer but gives a THIRD possible name "Status" for the form.

Please just give me the actual name of the form - no typos - with care.

PS. I will work on the SQL assuming it's called [frmStats] and redo it when you have a clear answer indicating the true name of the form you're using.

[NeoPa]

As far as the VBA code is concerned, I'm not sure why we're looking at this just yet.

I'm not even sure it's the right way to go later, but we'll deal with that then.

For the moment, we want to see what's happening with the SQL. When that's working correctly, we can consider how to proceed.

[NeoPa]

New SQL to include the selection of Initials from the form :

SELECT subH.MEQA,
       Format(subD.Month,'d mmmm yyyy') AS [Month]
FROM (SELECT DateAdd('m',-[DateOffset],Forms!frmStats.PVDate) AS Month
      FROM tblDate) AS subD LEFT JOIN
     (SELECT [MEQA],
             [Month]
      FROM tblHistory
      WHERE [Initials]=Forms!frmStats.cbInitials) AS subH
  ON subD.Month=subH.Month
ORDER BY subD.Month DESC

When you have this please run it and post the results in this thread.

[Rickster66]

New SQL to include the selection of Initials from the form :

SELECT subH.MEQA,
       Format(subD.Month,'d mmmm yyyy') AS [Month]
FROM (SELECT DateAdd('m',-[DateOffset],Forms!frmStats.PVDate) AS Month
      FROM tblDate) AS subD LEFT JOIN
     (SELECT [MEQA],
             [Month]
      FROM tblHistory
      WHERE [Initials]=Forms!frmStats.cbInitials) AS subH
  ON subD.Month=subH.Month
ORDER BY subD.Month DESC

When you have this please run it and post the results in this thread.

It WORKS!!!!
This is what I have: this the numerical value for the MEQA - also a rrequirement. Ithe latest datadump had descriptions of the MEQA - I am working on numbers MEQA -- this is fantastic !

MEQA                                                          Month
                                                             1 April 2008
                                                              1 March 2008
"""Wrong Patient, Same Name    """             1 February 2008
"""Right Drug, Wrong Substitution"""             1 February 2008
"""Right Drug, Wrong Formulation"""             1 January 2008
"""Right Drug, Wrong Patient ( Family Member )"""    1 January 2008
                                                              1 December 2007
"""Right Drug, Wrong Strength"""                              1 November 2007
"""Right Drug, Wrong Strength"""                              1 November 2007
                                                              1 October 2007
"""Right Drug, Wrong Strength"""                              1 September 2007
"""Right Drug, Wrong Sig"""                              1 September 2007
"""Right Drug, Wrong Formulation"""             1 September 2007
                                                             1 August 2007
                                                              1 July 2007
"""Right Drug, Wrong Quantity"""                              1 June 2007
Inappropriate Dose/Sig                             1 June 2007
"""Right Drug, Wrong Patient ( Family Member )""" 1 June 2007
"""Wrong Drug, Right Patient"""                              1 May 2007
"""Right Drug, Wrong Patient ( Family Member )""" 1 April 2007
 

I will paste the results of the numerical MEQA values soon

[NeoPa]

Richard, I'm very pleased that this seems to be working for you, but I'm worried as this data that you're showing me now is not what we've talked about and worked towards over 65 posts.

Look back at post #28 where you say that :

The initials matched to a particular month cannot appear more than once. Sorry for not making it clear.

The data shown in your last post indicates that clearly there can be more than one record per month.

Post #48 indicates the data in the tblHistory.MEQA field is numeric. This data indicates it's textual. I could go on...

I hope there is a further post coming to clarify the situation and to get us back on track.

[NeoPa]

By the way, you can now remove the "d " from line #2 in the SQL. This will leave it as :

SELECT subH.MEQA,
       Format(subD.Month,'mmmm yyyy') AS [Month]
FROM (SELECT DateAdd('m',-[DateOffset],Forms!frmStats.PVDate) AS Month
      FROM tblDate) AS subD LEFT JOIN
     (SELECT [MEQA],
             [Month]
      FROM tblHistory
      WHERE [Initials]=Forms!frmStats.cbInitials) AS subH
  ON subD.Month=subH.Month
ORDER BY subD.Month DESC

The "d " (day) was to confirm that we were dealing exclusively with the first day of the month (in [PVDate]) which is confirmed by your last post.

[Rickster66]

It WORKS!!!!
This is what I have: this the numerical value for the MEQA - also a rrequirement. Ithe latest datadump had descriptions of the MEQA - I am working on numbers MEQA -- this is fantastic !

MEQA                                                          Month
                                                             1 April 2008
                                                              1 March 2008
"""Wrong Patient, Same Name    """             1 February 2008
"""Right Drug, Wrong Substitution"""             1 February 2008
"""Right Drug, Wrong Formulation"""             1 January 2008
"""Right Drug, Wrong Patient ( Family Member )"""    1 January 2008
                                                              1 December 2007
"""Right Drug, Wrong Strength"""                              1 November 2007
"""Right Drug, Wrong Strength"""                              1 November 2007
                                                              1 October 2007
"""Right Drug, Wrong Strength"""                              1 September 2007
"""Right Drug, Wrong Sig"""                              1 September 2007
"""Right Drug, Wrong Formulation"""             1 September 2007
                                                             1 August 2007
                                                              1 July 2007
"""Right Drug, Wrong Quantity"""                              1 June 2007
Inappropriate Dose/Sig                             1 June 2007
"""Right Drug, Wrong Patient ( Family Member )""" 1 June 2007
"""Wrong Drug, Right Patient"""                              1 May 2007
"""Right Drug, Wrong Patient ( Family Member )""" 1 April 2007
 

Here are the numerical values of the MEQA

1. Name Initials February2008 January2008
   
   Mary J HTD 3165 4523 Diep, K KKD 2528 2662
   Diep, T TKD 2204 2388
   

Thre month column decreases. I've been writing sql code to look like the results above - can you help me?

Thank you for all your help -- I am in sincere awe of your mastery of access.

Richard

[NeoPa]

Richard, if I had the first idea what you're asking about I would at least try.

But I would expect some feed back on the last question first. I'm still not clear on where we got to on that. Loose ends can be very confusing and hard to follow you understand.

[Rickster66]

Richard, if I had the first idea what you're asking about I would at least try.

But I would expect some feed back on the last question first. I'm still not clear on where we got to on that. Loose ends can be very confusing and hard to follow you understand.

I complete understand, I will explain more clearly:

I received a datadump from which i need to create report that is based on the sql that you created above. This is what the table looks like :

Dashboard_PV
Initials    UserName    February2008    January2008     December2007
HTD    dang_ht    3165                   4523            4043
KKD    diep_kk    2528                   2662            1409
TKD    diep_tk    2204                   2388            1788
NPF    fazeli_np    3625                   5395            3615
ESG    guirgui_es    3714                   4600            2487
CQH    hoang_cq    2089                   2919            1503
HNH    hoang_hn    1895                   1907             1331
CNH    huynh_cn    39                   246             239
KNJ    juson_kn    1848                   2817            1658
 

each column title goes back one month - the result should look like:
for example:

Month                   PV
1 April 2008    0
1 March 2008    0
1 February 2008    2
1 January 2008    2
1 December 2007    0
1 November 2007    2
1 October 2007    0
1 September 2007    3
1 August 2007    0
1 July 2007    0
1 June 2007    3
1 May 2007    1
1 April 2007    1
 

With same the dynamic requirements as before
I've been working at it - I first transposed the table so that the column titles are shown horizontally and the names. Initials, and UserName Columns are shown vertically (I got this from http://support.microsoft.com/kb/202176) - i am not sure if this is the best way to go about it.

Richard

[Rickster66]

Richard, I'm very pleased that this seems to be working for you, but I'm worried as this data that you're showing me now is not what we've talked about and worked towards over 65 posts.

Look back at post #28 where you say that :

The data shown in your last post indicates that clearly there can be more than one record per month.

Post #48 indicates the data in the tblHistory.MEQA field is numeric. This data indicates it's textual. I could go on...

I hope there is a further post coming to clarify the situation and to get us back on track.

The verbal result display of the MEQA is a newer requirement that allows for repetitions during each month - each lime represents the exact nature of the error commited by the individual. When the MEQA is in numbers, the non repetition rule applies as before. I found this new requirement recently.

[NeoPa]

Richard, if I had the first idea what you're asking about I would at least try.

But I would expect some feed back on the last question first. I'm still not clear on where we got to on that. Loose ends can be very confusing and hard to follow you understand.

At the risk of repeating myself I expect some feedback on where we are with the previous problem before I intend to continue.

I have to admit to more than a little frustration that you seem to ignore anything I post that isn't immediately convenient to you.

YOU're supposed to be doing the work and you don't seem to be making any effort even to communicate clearly.

I will revisit after the weekend is over and see if things have changed any - otherwise I'm afraid my patience dealing with you is running perilously thin.

The problems are interesting enough but dealing with someone who pays so little attention to what they are saying (and particularly what I've spent time and effort saying) is frankly frustrating.

[Rickster66]

At the risk of repeating myself I expect some feedback on where we are with the previous problem before I intend to continue.

I have to admit to more than a little frustration that you seem to ignore anything I post that isn't immediately convenient to you.

YOU're supposed to be doing the work and you don't seem to be making any effort even to communicate clearly.

I will revisit after the weekend is over and see if things have changed any - otherwise I'm afraid my patience dealing with you is running perilously thin.

The problems are interesting enough but dealing with someone who pays so little attention to what they are saying (and particularly what I've spent time and effort saying) is frankly frustrating.

It was never my intention to ignore your request for feedback - In all honesty, I am am trying to make sense of the requirements that have been made and how they have changed and I am trying my best to communicate this to you in the best way that i can . I am sincerely very sorry for not adequately communicating with this you. Your help has been invaluable and hope that you can continue to help me - In order to prevent confusion I will let you know in as clear and detailed manner as I can. If I ommited something, it was never intentional Once again, I am very sorry.

The table from which the sql statement was written contains numerous columns - 2 of them date and MeQA in numerical value. From these 2 columns came the sql statement that you created. On this table, there is another column that has textual value for MEQA. I ignored this table at that moment because the requirement was to use numerical values. That is why I did not let you know about it. The sudden inception of the requirement using textual MEQA had different rules that I regretfully failed to inform you.

Once you created the SQL statement successlly, my supervisors decided suddenly to add a requirement to display the textual MEQA data in a similar fashion to the one using numerical MEQA value. In this requirement, the data could be repeated each month This request was new and I did not tell you about it, partly because I had no clear understanding of it myself. Instead, I used the working SQL statement and was able to create the desired results successfully. I failed to let you know about this because I was very happy about the success of the query that you created. The results for both numerical and textual MEQA have been successful in terms of producing the data in the manner that it was requested. I also changed the names of some of the controls, sorry for the confusion. It had to do with my own understanding of the functional requirements of the project.

Now there is a new requirement which entails presenting the data exactly as before and in similar a fashion to how you crafted the SQL statement except the columns consist of name, username, initials, February 2008, January 2008, December 2007, etc......These columns exclusively contain numerical data. They are called PV's - I pasted some sample data and hope it's clear for you. I will gladly paste it again if you want me too.

I hope this clarifies things for you. I can't think of any more feedback or information to report. Once again, the SQL was brilliantly successful in terms of the MEQA solution. My new dilemma is dealing with the PV solution with its rather different table structure. I have been very carefully culling through every fact and detail and can't find anything else to let you know. If you find that I have, I apologize beforehand. My most sincere thanks and gratitute,

Richard

[NeoPa]

Richard, I appreciate that you had no intention of being disrespectful (clear from your latest reply), but just let me explain a few things, including why it's so important for me to get all the feedback that I request. I am not simply trying to throw my weight about or even treat you without respect.

Here's something I've used before :

Please understand that working remotely like this on other people's problems is a lot harder to get right than simply working on your own database.
We have two main problems :
1. We have to build up a picture of the whole problem in our heads without reference to any testing except at second hand via the requestor.
2. We have to rely on the accuracy and completeness of information passed to us via posts.
It doesn't take a great deal of imagination to realise, then, how crucial is the information passed to us.

You can see I need all the feedback I can get to even have a clue as to what's happening on your side of things. Without this information, or worse with factually incorrect information, I can start to lead you down a cul-de-sac. When we get there and you say things are not working, where do I look for the problem? It's almost impossible to recover from such a situation without starting the whole process again from scratch. Apart from the enormous amount of work this would involve, it's also very depressing. This is a situation I'm trying very hard to avoid for us.

I hope this helps you to understand the particular complications involved with working together via such a medium and we can look forward to a smoother ride in future.

PS. I will look at the latest problem in more detail when I next visit the site. Your latest post seems to clarify the current situation adequately so I think I'll be able to proceed. If I find I need more information when I look at it in more detail you will be the first to know ;)

[NeoPa]

Right, after a closer look at the new requirements and the information I have available to me in the thread, I suggest that we start again from scratch so that the information available is clear and not cluttered with lots of wrong or out-of-date information.

I have looked back over a number of posts and it's clear to me that it's going to be extra difficult for me, in these circumstances, to separate the current information from the stuff I should be ignoring.

If you start a new thread then add a post in here that links to it (just tell me it's there - I can find it and add the linking if necessary). This way I will have the necessary information easily to hand. I don't suggest this lightly, I know it will involve you in a bit of work too, but I think you will also benefit from this as it will force you to think more clearly of the exact problem.

The data you've posted that needs to be at the heart of the new query may seem to you to be similar to the earlier data but trust me - it's not remotely similar as far as extracting the information is concerned. When we have the accurate layout posted I'll be in a better position to see what, if any, approach can work.

Please post the layout of the tables (metadata) as similarly as possible to the example I include below. When you click on Reply to my post you will have access to the codes I use. Copy and paste my example and simply modify the table and field details found within :

Table Name=[Dashboard_PV]
 Field; Type; IndexInfo
Initials; String; PK
UserName; String
????; Numeric
????; Numeric
????; Numeric

I need the metadata for all relevant tables. Also, example data may help.

Remember, if your first post contains all the information required laid out clearly, this will make working with it immeasurably easier (and therefore quicker). Clearly this will be difficult to get right first time, but the nearer we come to it the better.

[Rickster66]

Hi NeoPa, I just started a new post titled: select only 12 columns going back - Dashboard_PV. I hope you can find it , thanks,

[Rickster66]

Hi NeoPa, I just started a new post titled: select only 12 columns going back - Dashboard_PV. I hope you can find it , thanks,

As mentioned in "Select only 12 columns going back - Dashboard_PV" my bosses presented me a new requirement. It has to do with a column which was part of the table from which the MEQA sql that you wrote came from (post 73)

SELECT subH.MEQA,
       Format(subD.Month,'mmmm yyyy') AS [Month]
FROM (SELECT DateAdd('m',-[DateOffset],Forms!frmStats.PVDate) AS Month
      FROM tblDate) AS subD LEFT JOIN
     (SELECT [MEQA],
             [Month]
      FROM tblHistory
      WHERE [Initials]=Forms!frmStats.cbInitials) AS subH
  ON subD.Month=subH.Month
ORDER BY subD.Month DESC
 

I mentioned earlier that there were many columns in that original table and ignored most columns except MEQA and date(month) and initials. One of the once ignored columns is one named PV1. It has 2 possible values: Mail Services and PSC. They would like the results of the sql as you wrote it but with a parameter that behaves like the initials parameter. In other words the parameter can be Mail Services or PSC. I've tried to add the parameter but I get this error message: "Invalid bracketing of name <[SELECT DateAdd('m',[DateOffset>. I'm not sure how to proceed.

[NeoPa]

I'll have a look later and see what I can come up with.

In the mean time, post what you did. Remember, we're here to help you learn rather than simply to do it all for you.

Maybe you are picking some stuff up but if you post what you have we can see how close you came for next time.

I can maybe even illustrate where and why you went wrong.

[Rickster66]

I'll have a look later and see what I can come up with.

In the mean time, post what you did. Remember, we're here to help you learn rather than simply to do it all for you.

Maybe you are picking some stuff up but if you post what you have we can see how close you came for next time.

I can maybe even illustrate where and why you went wrong.

Sounds good, Iwould really like to have a better understanding with this project and learn from the errors. Here's what I did:

SELECT subH.Category AS MEQA, 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],
             [Month]
      FROM tblprotoHistory
      WHERE [Initials]=Forms!Stats.cbInitials]. and  subH.PV1="Mail Service"   AS subH ON subD.Month = subH.Month
ORDER BY subD.Month DESC;
 

The PV1 parameter is not passed in the form. The error happens even before I add subH.PV1="Mail Service" They happen even if i move any part of the code and bring it back

[NeoPa]

Firstly, I need any and all assertions I include to be confirmed or denied. To make this easier I will letter them so that you know how many and which need responding to.

1. tblHistory is now called tblProtoHistory.
2. [MEQA] is now called [Category] but we would still like it to show up as [MEQA] in the results.
3. You are trying to filter on the literal text string ("Mail service") but eventually you'd like this to work from a control on your form where you would enter or select the value required.
4. The form you use is actually called Stats and not frmStats.

Consider the SQL as the following, then we will look at the SubQuery parts :

SELECT subH.MEQA,
       Format(subD.Month,'mmmm yyyy') AS [Month]
FROM subD LEFT JOIN subH
  ON subD.Month=subH.Month
ORDER BY subD.Month DESC

This is fairly straightforward (We still need to look at subD & subH in more detail) and shows the [MEQA] value and the [Month] value. subH gets the required data out of the history table. The FROM clause specifies that we use both subqueries and how they link together (Get ALL records from subD, but only matching records from subH).

subD is not changed from before and is there to provide a dataset of exactly the months required relative to that selected in Forms!Stats.txtCurrentDate.

(SELECT DateAdd('m',-[DateOffset],Forms!Stats.txtCurrentDate) AS Month
 FROM tblDate) AS subD

subH is a little different and is where we need to add the extra selection criteria (WHERE clause).

(SELECT [Category] AS [MEQA],
        [Month]
 FROM tblProtoHistory
 WHERE (([Initials]=Forms!Stats.cbInitials)
   AND  ([PV1]='Mail Service'))) AS subH

So, putting it all together we get :

SELECT subH.MEQA,
       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
ORDER BY subD.Month DESC

Right, try that and see what you get.

[Rickster66]

...
A - tblHistory is now called tblProtoHistory.

That is correct, I'm still not sure if I went about it the right way. tbl History had fields that did not match the data from the incoming dumps so it was easier for me to create a make table query and it was here where I creatted an additional "month column" - the datadump had only general dates.

B - [MEQA] is now called [Category] but we would still like it to show up as [MEQA] in the results.

That is correct.
I think that is more of a mixup on my part that based on the success of the latest query which seems to have worked. My bosses first called the attribute "Category," the "MEQA" and that confused me -correct name shouold be MEQA.

C - You are trying to filter on the literal text string ("Mail service") but eventually you'd like this to work from a control on your form where you would enter or select the value required.

In actuality, both "Mail Service" and "PSC will be 2 distinct sets of results. As the current sql has "mail service" as the parameter, an additional query with the will replace "mail service" with the parameter "psc." Thus, there will be 2 queries using the same parameter.

D - The form you use is actually called Stats and not frmStats.

Yes that is correct.

I ran the code and this is what I got:

MEQA                                                Month
                                                    April 2008
                                                    March 2008
"""Wrong Patient, Same Name    """                  February 2008
"""Right Drug, Wrong Substitution"""                February 2008
"""Right Drug, Wrong Formulation"""                 January 2008
"""Right Drug, Wrong Patient ( Family Member )"""   January 2008
                                                    December 2007
"""Right Drug, Wrong Strength"""                    November 2007
"""Right Drug, Wrong Strength"""                    November 2007
                                                    October 2007
"""Right Drug, Wrong Strength"""                    September 2007
"""Right Drug, Wrong Sig"""                         September 2007
"""Right Drug, Wrong Formulation"""                 September 2007
                                                    August 2007
                                                    July 2007
"""Right Drug, Wrong Quantity"""                    June 2007
Inappropriate Dose/Sig                              June 2007
"""Right Drug, Wrong Patient ( Family Member )"""   June 2007
"""Wrong Drug, Right Patient"""                        May 2007
"""Right Drug, Wrong Patient ( Family Member )"""   April 2007

This means that these results are successful. I tried to make the data as straight as possible.

[NeoPa]

Sounds good, but I need to rush before I have a chance to look through it all properly.

Give this a bump if nothing heard by tomorrow.

[Rickster66]

Sounds good, but I need to rush before I have a chance to look through it all properly.

Give this a bump if nothing heard by tomorrow.

thanks for your help - I still have some questions when you get a chance

[NeoPa]

I'll see if I can pick this up again from home tonight. It's a bit late to start anything now at work. My family will wonder where I've got to ;)

If not - feel free to bump tomorrow.

[NeoPa]

thanks for your help - I still have some questions when you get a chance

I thought you were saying you had unanswered questions already posted in the thread. In my rush I was unable to check then :D

Anyway, I couldn't find any so now I'm thinking you have some to post. Go ahead.

If they are not really the same question then do as before and post them in their own threads. Again, I'm happy for you to post links between this and the other threads.

PS. Just a tip. When pasting tables of info (or anything tabular) use spaces rather than TABs. Although TABs work, they're much harder to predict and often cause untidiness.

PPS. Look back to your post #81 for an update of the layout. On that subject, having the date appear first would possibly work better. Let me know if you would like to do this and can't manage it yourself. I expect you can but just in case.

[Rickster66]

I thought you were saying you had unanswered questions already posted in the thread. In my rush I was unable to check then :D

Anyway, I couldn't find any so now I'm thinking you have some to post. Go ahead.

If they are not really the same question then do as before and post them in their own threads. Again, I'm happy for you to post links between this and the other threads.

PS. Just a tip. When pasting tables of info (or anything tabular) use spaces rather than TABs. Although TABs work, they're much harder to predict and often cause untidiness.

PPS. Look back to your post #81 for an update of the layout. On that subject, having the date appear first would possibly work better. Let me know if you would like to do this and can't manage it yourself. I expect you can but just in case.

Thanks for the advice and for taking the time to explain the sql statement to me. I wanted to know why is it important that the 1st day of the month be used
as a date parameter. even though, the sql works beautifully, I am not very clear as to why does it have to be the 1ist day.

I have several other questions that entail the queries on this thread and the other one as well.

A new requirement is to have calculations that involve MEQA values divided by PV values. The MEQA values come from this queue. Even though I did mention it at the begining, the MEQA's have a textual as well as a numerical value. What I did on the sql query is add a count aggregate:

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;
 
 

The result is accurate. Now what they want is to calculate the MEQA's and divide then by the PV's and then calculate the percentage. The resulting data from the PV's should look similarly like the one this thread. These calculations are to be done going 3, 6, 9 and 12 months back. I realize the data type for the queties is text so how the data type issue be resolved here. I was wondering if it is possible to change data types dynamically. I can't find anything so far.

[NeoPa]

I'll try to answer one question at a time. That way I can mange it better (maybe do one today and continue with others tomorrow when I have some more time).

Thanks for the advice and for taking the time to explain the sql statement to me. I wanted to know why is it important that the 1st day of the month be used
as a date parameter. even though, the sql works beautifully, I am not very clear as to why does it have to be the 1ist day.

That is because we are creating some dates artificially in our subD subquery. When you create a date from "January 2008" (for instance) it returns a date of the 1st January 2008. As we are creating a dataset that we want to link to our table [tblProtoHistory], it is important that the dates in that table are also 1st of the month otherwise they would not match and it wouldn't link.

Using any other day of the month would still display as "January 2008" but the actual date BEHIND it would not match the date we're trying to link to.

Format(#6 July 2007, 'mmmm yyyy') = Format(#1 July 2007, 'mmmm yyyy')
("July 2007") = ("July 2007") BUT
6 July 2007 =/= 1 July 2007.

[NeoPa]

...
A new requirement is to have calculations that involve MEQA values divided by PV values. The MEQA values come from this queue. Even though I did mention it at the begining, the MEQA's have a textual as well as a numerical value. What I did on the sql query is add a count aggregate:

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;

The result is accurate. Now what they want is to calculate the MEQA's and divide then by the PV's and then calculate the percentage. The resulting data from the PV's should look similarly like the one this thread. These calculations are to be done going 3, 6, 9 and 12 months back. I realize the data type for the queties is text so how the data type issue be resolved here. I was wondering if it is possible to change data types dynamically. I can't find anything so far.

This doesn't make any sense to me at the moment. Possibly because it's too late and I need to go to bed.

I'll look again tomorrow and see what I can make of it.

[NeoPa]

...
In actuality, both "Mail Service" and "PSC will be 2 distinct sets of results. As the current sql has "mail service" as the parameter, an additional query with the will replace "mail service" with the parameter "psc." Thus, there will be 2 queries using the same parameter.
...

I just remembered I forgot to comment on this earlier.

It seems to me that we can link this into a control on your form rather than redo everything in another query.

You could have a ComboBox control filled with all the valid options for this field, then the SQL could be changed slightly to select using the selected value rather than a hard-coded string.

[Rickster66]

This doesn't make any sense to me at the moment. Possibly because it's too late and I need to go to bed.

I'll look again tomorrow and see what I can make of it.

It sounds awfully convoluted - I will be happy to explain it again if you want.

[Rickster66]

I just remembered I forgot to comment on this earlier.

It seems to me that we can link this into a control on your form rather than redo everything in another query.

You could have a ComboBox control filled with all the valid options for this field, then the SQL could be changed slightly to select using the selected value rather than a hard-coded string.

That would be a good idea except that what they want is a control that selects date and initials then they want a report that displays the MEQA textual results for both Mail service and PSC as well as the MEQA numeric results for both the PSC and Mail Service. In addition, they want performance rations of MEQA/PV x 100 going back 3,6,9 and 12 months back -- all results showing at the same time.

[NeoPa]

That would be a good idea except that what they want is a control that selects date and initials then they want a report that displays the MEQA textual results for both Mail service and PSC as well as the MEQA numeric results for both the PSC and Mail Service. In addition, they want performance rations of MEQA/PV x 100 going back 3,6,9 and 12 months back -- all results showing at the same time.

Forgive me for asking, but with a requirement as complex as this, why are they entrusting it to someone with your limited experience?

Sometimes people want to take advantage and get something for nothing. It's rarely a good idea to encourage them in this approach. This is the sort of stuff I may expect to be requested of me at work (actually it's more complex - but I would expect myself to handle it if I had full hands-on control) and I expect I'm probably at a considerably more advanced position than you.

I don't say this to make you look small, or even to pump myself up. This is a reflection on the fairness of asking for so much from someone with relatively little experience.

Furthermore, I feel it likely that we will struggle to accomplish something of this complexity remotely. It's not impossible, but it requires communication to work at a clear and precise level. A level in fact, that most experts struggle to work at. We'll have to see how it goes, but be prepared for this process not to provide all the answers.

I will have to revisit this over the weekend possibly, when I can let you know whether or not the requirement needs further explanation.

[Rickster66]

Forgive me for asking, but with a requirement as complex as this, why are they entrusting it to someone with your limited experience?

Sometimes people want to take advantage and get something for nothing. It's rarely a good idea to encourage them in this approach. This is the sort of stuff I may expect to be requested of me at work (actually it's more complex - but I would expect myself to handle it if I had full hands-on control) and I expect I'm probably at a considerably more advanced position than you.

I don't say this to make you look small, or even to pump myself up. This is a reflection on the fairness of asking for so much from someone with relatively little experience.

Furthermore, I feel it likely that we will struggle to accomplish something of this complexity remotely. It's not impossible, but it requires communication to work at a clear and precise level. A level in fact, that most experts struggle to work at. We'll have to see how it goes, but be prepared for this process not to provide all the answers.

I will have to revisit this over the weekend possibly, when I can let you know whether or not the requirement needs further explanation.

The answer to your question is in the second paragraph-

[NeoPa]

The answer to your question is in the second paragraph-

...and early on at that I bet ;)

[Rickster66]

...and early on at that I bet ;)

You have no idea how happy I am that you understand my situation - nonetheless, I'm not afraid of a challenge and I have learned so much from your help on this project...

[Rickster66]

You have no idea how happy I am that you understand my situation - nonetheless, I'm not afraid of a challenge and I have learned so much from your help on this project...

Hello, I hope you haven't forgotten me

[NeoPa]

This one I did yes :(

I'm afraid that, as your threads are so much more involved than most of the others I deal with, I have to treat them with a lower priority.

Recently, they have been taking a good deal more than their fair share of my time so, to be fair to the other members I cannot always spend all my time on your threads.

That said, I will endeavour to get some progress going in here as soon as I'm able, which should be in the next couple of days at least.

[NeoPa]

Time is still tight at the moment I'm afraid.

I will have to concentrate on one of your threads at a time unless / until things free up for me a little. Sorry.

[Rickster66]

Time is still tight at the moment I'm afraid.

I will have to concentrate on one of your threads at a time unless / until things free up for me a little. Sorry.

That's fine, I appreciate the effort.

[NeoPa]

I'm sorry Richard. I put aside some time today to have a look at this and I'm quite unclear on :

1. Exactly what you want (exact is important as vague is nowhere in these situations).
2. Exactly what data I have at my disposal to start with.

I'm sure (I expect anyway) that this information is hidden in here somewhere but with 100 posts now in this thread it's a big struggle trying to dig it all up.

What I suggest you do is start a new thread and post in here when done. I will add in any links to enable switching between the two when I get the trigger of your posting in here.

Your new thread must include, as clearly (and especially accurately) as possible, the data that I will be working with, as well as a clear indication of what is required as output.

Meta-data (info about the layout / structure) for the recordsets (tables) is a requirement and must be clear and accurate. This will work much better if you can post it in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used.

Table Name=[tblStudent]
 Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Remember too that getting this right first post will save all sorts of complications with the work and avoid having to search around every time I have a question I need answered (from the existing posts). All reference material in a single place makes life a great deal easier.