Select only 12 columns going back - Dashboard_PV

[Rickster66]

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) :

Basically what this is about is measuring the performance of employess in terms of how many errors they made this month and compare the current figure with the ones made before. For example, for employee A the number of errors for the Month of March 2008 is 324, for february 2008 is 420, for January 2008 is 200, etc....going all the back to the month of March 2007 and see how the errors have increased or decreased over time. The users of this application want to be able to do this by picking from any given month and look back up to a year in monthly increments

This time, the difference is in how the data from the table is presented. I constructed the table from a series of data dumps that are issued monthly. I select the column with the data that I want, called PV and rename the column for the month in which it came. For example, if I took a column corresponding to the month of January, the metadata would look like this:

Table Name=[1/31/2007]
 Field;    Type;    IndexInfo
ID;       String;  
Name;     String;    PK
Approve;  Numeric

The Sample data looks like this:

ID    Name       Approve
1     LE_DH       4518
2     DONG_PA     3182 
3     DANG_HT     3175

and so on

I rename the fields Name to UserName and Approve to PV.
I then do a SQL join with tblMembers table, which has this structure:

Table Name=[tblMembers]
 Field;    Type;    IndexInfo
MemberID; String;   PK
Name;     String      
UserName; String
Initials; String

Here is some sample data:

MemberID   Name         UserName   Initials
1          Dang,Hong    dang_ht    htd
2          Dong,Patrick dong_pa    pdd

and so on

I then write sql for a right join between tblMembers and the built tables.
I add every table representing a month and I end up with a table which i called Dashboard_PV. Its structure is:

Table Name=[Dashboard_PV]
 Field; Type; IndexInfo
Initials; String; PK
UserName; String
February2008; Numeric
January2008; Numeric
December2007; Numeric

here is some sample data:

Dashboard_PV
Initials   UserName  February2008   January2008   December2007
HTD        dang_ht      3165           4523          4043
KKD        diep_kk      2528           2662          1409

and so on


I hope this is clear. I am not sure if I went about it the right way - your help is appreciated,


Richard

[NeoPa]

This post is to register.
Tonight I will just do some tidying up on the post adding links and adjusting the format somewhat. This is likely to be the main reference point so quite important.

At first look it seems as if you've made a decent attempt from which to start. Getting late now so I won't try to find any solutions tonight.

[NeoPa]

Richard, before I head off for the night, if you have made up Dashboard_PV from other data sources, I suggest you reconsider the layout. This will be a real head-ache to work with and should be avoided if at all possible.

Let me know.

[Rickster66]

This post is to register.
Tonight I will just do some tidying up on the post adding links and adjusting the format somewhat. This is likely to be the main reference point so quite important.

At first look it seems as if you've made a decent attempt from which to start. Getting late now so I won't try to find any solutions tonight.

That's fine, thanks so much

[Rickster66]

That's fine, thanks so much

I just got note from my bosses about a new requirement regarding the last post. Should I post it here or there. I've been trying to work with it but it's not working.

Richard

[NeoPa]

I suspect you meant to say "the last thread" rather than "the last post". If so, then that thread is where it needs to be posted.

While I'm here, can I direct your attention back to post #3 and request a response. Does your new table need to be in this un-database like format?

[Rickster66]

I suspect you meant to say "the last thread" rather than "the last post". If so, then that thread is where it needs to be posted.

While I'm here, can I direct your attention back to post #3 and request a response. Does your new table need to be in this un-database like format?

The new table does not need to be in the un-database-like format. If you think that it should be in database-like format or not is perfectly fine.

[Rickster66]

Richard, before I head off for the night, if you have made up Dashboard_PV from other data sources, I suggest you reconsider the layout. This will be a real head-ache to work with and should be avoided if at all possible.

Let me know.

I absolutely have no problem reconsidering the layout. At this moment, I can't think of another type of redesign.

[NeoPa]

How about :

Table Name=[Dashboard_PV]
 Field; Type; IndexInfo
Initials; String; PK
MonthInd; String; PK
PV; Numeric

NB. [MonthInd] is a STRING field and NOT a date/time one. The format for this data must be EXACTLY "yyyymm". If this is not possible then we can extract the data with the month as previously displayed then update it with an UPDATE query.
Also, there is no need for a [UserName] field if this is already available in [tblMembers].
Here is some sample data:

[Dashboard_PV]
Initials   MonthEnd      PV
HTD        200802     3,165
HTD        200801     4,523
HTD        200712     4,043
KKD        200802     2,528
KKD        200801     2,662
KKD        200712     1,409
...

[Rickster66]

How about :

Table Name=[Dashboard_PV]
 Field; Type; IndexInfo
Initials; String; PK
MonthInd; String; PK
PV; Numeric

NB. [MonthInd] is a STRING field and NOT a date/time one. The format for this data must be EXACTLY "yyyymm". If this is not possible then we can extract the data with the month as previously displayed then update it with an UPDATE query.
Also, there is no need for a [UserName] field if this is already available in [tblMembers].
Here is some sample data:

[Dashboard_PV]
Initials   MonthEnd      PV
HTD        200802     3,165
HTD        200801     4,523
HTD        200712     4,043
KKD        200802     2,528
KKD        200801     2,662
KKD        200712     1,409
...

I finished constructing the table as you specified

[NeoPa]

Right, if you could post clearly what you need it to do now, then we can look at the problem.

I couldn't get this from your first post. It seemed to stop after saying that it was different from the other thread.

[Rickster66]

Right, if you could post clearly what you need it to do now, then we can look at the problem.

I couldn't get this from your first post. It seemed to stop after saying that it was different from the other thread.

Very well, what I need is to be able to pull a list of data from the newly done Dashboard_PV table using date parameters and initials. The data should be displayed month by month going backwards 12 months. The column MonthEnd in the table Dashboard shows (with a text datatype) monthly information as it is. This is what result should look like:
Asssuming the query was run with parameters for Stats.txtcurrentdate and Stats.cbInitials:

PV                     Month
1458                  January  2008
1299                  December  2007
1499                  November  2007
1320                  October  2007
1277                  September  2007
1322                  August  2007
1889                  July  2007
.                         .
2998                  January 2007

I can see Dashboard_PV's structure is very similar to this sample. The issue is that must go back 12 months. So this is what I need to get done. the next step would be to calculate the percentage of the MEQA/PV going back 3, 6, 9 and 12 months back.

[Rickster66]

hello I hope you haven't forgotten my situation

[NeoPa]

This one I didn't even catch, so haven't yet had an opportunity to forget ;)

I was away over the weekend so maybe that explains why. I will try to look again soon. All pretty busy at the moment I'm afraid.

[NeoPa]

Try the following :

SELECT Format(subD.Month,'mmmm yyyy') AS [Month],
       subH.PV
FROM (SELECT DateAdd('m',-[DateOffset],Forms!Stats.txtCurrentDate) AS Month,
             Format([Month],'yyyymm') AS YM
      FROM tblDate
      WHERE [DateOffset]<n) AS subD LEFT JOIN
     (SELECT [PV],
             [MonthEnd]
      FROM Dashboard_PV
      WHERE (([Initials]=Forms!Stats.cbInitials)
        AND  ([PV1]='Mail Service'))) AS subH
  ON subD.YM=subH.MonthEnd
ORDER BY subD.Month DESC

It assumes the same values (0 to 12) in the [tblDate] table ([DateOffset]).
n (line #5) is the value of the number of months you need (3; 6; 9; 12 etc).

[Rickster66]

Try the following :

SELECT Count(subH.MEQA) AS [Count], Format(subD.Month,'mmmm yyyy') AS [Month]
FROM (SELECT DateAdd('m',-[DateOffset],Forms!Stats.txtCurrentDate) AS Month,
             Format([Month],'yyyymm') AS YM
      FROM tblDate
      WHERE [DateOffset]<n) AS subD LEFT JOIN
     (SELECT [PV],
             [MonthEnd]
      FROM Dashboard_PV
      WHERE (([Initials]=Forms!Stats.cbInitials)
        AND  ([PV1]='Mail Service'))) AS subH
  ON subD.YM=subH.MonthEnd
ORDER BY subD.Month DESC

It assumes the same values (0 to 12) in the [tblDate] table ([DateOffset]).
n (line #5) is the value of the number of months you need (3; 6; 9; 12 etc).

I ran the code:

SELECT Count(([subH].[MEQA])) AS [Count], Format(subD.Month,'mmmm yyyy') AS [Month]
FROM [SELECT DateAdd('m',[DateOffset],Forms!Stats.txtCurrentDate) AS Month,
             Format([Month],'yyyymm') AS YM
      FROM tblDate
      WHERE [DateOffset]<n]. AS subD LEFT JOIN [SELECT [PV],
             [MonthEnd]
      FROM Dashboard_PV
      WHERE (([Initials]=Forms!Stats.cbInitials)
        AND  ([PV1]='Mail Service'))]. AS subH ON subD.YM = subH.MonthEnd
GROUP BY Format(subD.Month,'mmmm yyyy'), subD.Month
ORDER BY subD.Month DESC;

after removing the "-" because it already exists on the tblDate table. It asked for several parameters:
I entered

...txtcurrentdate: 4/1/2008
...n :3 or 6 or 9 or 12
...PV1 Mail Service
...subH.MEQA 1? 3? (not sure what to enter here because MEQA information should come from tblProtohistory, which does not appear in this query - Dashboard_PV is PV data with Month without Mail Service parameters - my apologies if I failed to mention this before) what I get then is:

  Count    Month
1    April 2008
1    March 2008
1    February 2008
1    January 2008
1    December 2007
1    November 2007
1    October 2007
1    September 2007
1    August 2007
1    July 2007
1    June 2007
1    May 2007
1    April 2007
 

Not sure what to make of this
My gratitude as always,

Richard

[NeoPa]

I've literally just had a second to look at this and the code I posted was sloppy. I was a little pushed for time yesterday so didn't check it as carefully as usual.

You will notice though, that there is an explanation for the "n" in the SQL later in the post.

I do need to ask - where in the table is the data to enable us to filter by the "Mail service" value?

[Rickster66]

I've literally just had a second to look at this and the code I posted was sloppy. I was a little pushed for time yesterday so didn't check it as carefully as usual.

You will notice though, that there is an explanation for the "n" in the SQL later in the post.

I do need to ask - where in the table is the data to enable us to filter by the "Mail service" value?

I understand the functiion n plays in the query.
"Mail Service" and "PSC" is part of the MEQA data and its table is tblprotohistory. Both Mail service and psc data are under a column named: "PV1." The table Dashboard_PV data does not have "Mail Service" or "PSC" data. I hope this answers your question, if it does not, I would appreciate it if you rephrase it for me.

Many thanks,


Richard

[Rickster66]

There's something else I wanted to add. I noticed that everytime I change anything in the sql code, even if I change it back, I'll get an error: "Invalid bracketing of name 'SELECT DateAdd('m', [DateOffset'. " Is there something that I can do to avoid this?

[NeoPa]

Here we get into Access specific territory. This has come up a few times so I've knocked up a little article on it that you can refer to (Access QueryDefs Mis-save Subquery SQL).

[NeoPa]

Try the following :

SELECT Format(subD.Month,'mmmm yyyy') AS [Month],
       subH.PV
FROM (SELECT DateAdd('m',[DateOffset],Forms!Stats.txtCurrentDate) AS Month,
             Format([Month],'yyyymm') AS YM
      FROM tblDate
      WHERE [DateOffset]>-n) 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

[Rickster66]

Here we get into Access specific territory. This has come up a few times so I've knocked up a little article on it that you can refer to (Access QueryDefs Mis-save Subquery SQL).

I am reading it now you explanationa are helping me make sense of this complex sql process.

[Rickster66]

Try the following :

SELECT Format(subD.Month,'mmmm yyyy') AS [Month],
       subH.PV
FROM (SELECT DateAdd('m',[DateOffset],Forms!Stats.txtCurrentDate) AS Month,
             Format([Month],'yyyymm') AS YM
      FROM tblDate
      WHERE [DateOffset]>-n) 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

It works!! I get the desired results. Thank you

[NeoPa]

Excellent :)

I've been tied up over the weekend so I've only just caught this.

Just to confirm, this one can be put to bed and we can concentrate on the other for a while yes?

[Rickster66]

Excellent :)

I've been tied up over the weekend so I've only just caught this.

Just to confirm, this one can be put to bed and we can concentrate on the other for a while yes?

Yes sir, that is correct - thank you again

[Rickster66]

Yes sir, that is correct - thank you again

My boosses are pleased.

Richard

[NeoPa]

I'm very pleased to hear it Richard :)

I haven't forgotten the other one still - I just need a bit longer time to deal with such a complex question ;)

[Rickster66]

I'm very pleased to hear it Richard :)

I haven't forgotten the other one still - I just need a bit longer time to deal with such a complex question ;)

That's good - as long as you don't for me please......

Richard

[NeoPa]

Not forgotten. I will try to find enough time tomorrow.