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=[[U]1/31/2007[/U]]
[I]Field;    Type;    IndexInfo[/I]
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=[[U]tblMembers[/U]]
[I]Field;    Type;    IndexInfo[/I]
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=[[U]Dashboard_PV[/U]]
[I]Field; Type; IndexInfo[/I]
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=[[U]Dashboard_PV[/U]]
[I]Field; Type; IndexInfo[/I]
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:

[[B][U]Dashboard_PV[/U][/B]]
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=[[U]Dashboard_PV[/U]]
[I]Field; Type; IndexInfo[/I]
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:

[[B][U]Dashboard_PV[/U][/B]]
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.txtcurren tdate and Stats.cbInitial s:

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