Select Only 10 Columns Going Back

**NeoPa** · Mar 13 '08, 02:24 PM

Are you creating (expecting to create) the SQL statement in code?
This can be done using code but might be fiddly. A clearer indication of exactly what you're dealing with is likely to be crucial.

**Rickster66** · Mar 13 '08, 03:51 PM

Originally posted by NeoPa

Are you creating (expecting to create) the SQL statement in code?
This can be done using code but might be fiddly. A clearer indication of exactly what you're dealing with is likely to be crucial.

Yes the SQL statement is to be created in code. 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
and measure performance. I thought of creating arrays for each month but something goes wrong......

**NeoPa** · Mar 13 '08, 05:59 PM

How many columns are there then in the data?
Is the number fixed?
How are they positioned relative to the current month (Month(Date())) ?
Where is the code to run from?
What is the SQL that you want as a result of a run done now?

With these answers we can look at how to produce the SQL relative to the data required.

**Rickster66** · Mar 13 '08, 07:41 PM

Originally posted by NeoPa

How many columns are there then in the data?
Is the number fixed?
How are they positioned relative to the current month (Month(Date())) ?
Where is the code to run from?
What is the SQL that you want as a result of a run done now?

With these answers we can look at how to produce the SQL relative to the data required.

Great! there are 12 columns in the table. They are positioned stating from the very first month and year. For example, If the current month is March 2008, then next month will be March 2007, then April 2007, May 2007, June 2008, etc....finishin g in March 2008.

The code can run from a form or by way of a query. This is what I have written so far ...based on fake data :

Code:

SELECT UserName,
       Count([January  2007]) AS [Jan  2007],
       Count([February  2007]) AS [Febuary  2007],
       Count([March  2007]) AS [Mrch  2007],
       Count([April  2007 ]) AS [Aprl  2007],
       Count([May  2007 ]) AS [My  2007],
       Count([June  2007 ]) AS [Jun  2007],
       Count([July  2007]) AS [Jul  2007],
       Count([August   2007]) AS [Aug   2007],
       Count([September  2007 ]) AS [Sept  2007],
       Count([November  2007 ]) AS [Nov  2007],
       Count([December  2007 ]) AS [Dec  2007],
       Count([January  2008]) AS [Jan  2008],
       Count([February  2008]) AS [Feb  2008]
FROM xtbMEQA_MONTH
GROUP BY UserName;

I created a crosstab query then added date functions from the original date column that received the date of the error. This was one column that had many rows of dates. The cross tab column took care of the rest -- this is all I could think of.

**NeoPa** · Mar 13 '08, 11:50 PM

Sorry, had a busy Admin evening so may need to get to this tomorrow.
If you don't see a further reply tomorrow please * Bump * the thread as a reminder.

**Rickster66** · Mar 14 '08, 04:49 PM

I'm not sure If I did this the way to BUMP a message but here it goes :
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:

Code: ( text )
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

This what I wrote at the beginning tof the thread

**Rickster66** · Mar 14 '08, 07:07 PM

Someone suggested using getrows with ubound or maybe the fields.count preoperty...... ..any thoughts?

**NeoPa** · Mar 14 '08, 07:49 PM

Bumping is simply adding a new post (contents irrelevant) so that the thread moves up to the top (most recently added to) in the list of threads.

Don't worry, I will catch up with this today (or tomorrow latest) and answer the original question first. I always try to work in order. Extra questions can wait until I've covered the outstanding ones at least.

I haven't forgotten you ;)

**Rickster66** · Mar 14 '08, 08:38 PM

Thank you very much. I really appreciate it

Richard

**NeoPa** · Mar 14 '08, 11:41 PM

Right, I went back to post #5 and I had to reformat your SQL to be able to read it. The effect is unchanged btw.
Unfortunately, I suspect there's something not quite right with processing the results of a crosstab the way you have proposed. It seems to me that the crosstab would produce the sort of figures you are after without needing to GROUP by the [UserName].

Can you post in some sample data for the crosstab query so I can get a feel for the sort of data we're dealing with? Are there more than one lines for each [UserName]?

BTW, what is going on with the names of the fields in there? There are various with extra spaces, both within and at the end of field names, as well as some very unusual abbreviations that are supposed to represent months. This may not seem important now, but it will soon when we try to get the code to produce your SQL for you.

**Rickster66** · Mar 17 '08, 05:29 PM

Originally posted by NeoPa

Right, I went back to post #5 and I had to reformat your SQL to be able to read it. The effect is unchanged btw.
Unfortunately, I suspect there's something not quite right with processing the results of a crosstab the way you have proposed. It seems to me that the crosstab would produce the sort of figures you are after without needing to GROUP by the [UserName].

Can you post in some sample data for the crosstab query so I can get a feel for the sort of data we're dealing with? Are there more than one lines for each [UserName]?

BTW, what is going on with the names of the fields in there? There are various with extra spaces, both within and at the end of field names, as well as some very unusual abbreviations that are supposed to represent months. This may not seem important now, but it will soon when we try to get the code to produce your SQL for you.

Here's some sample data from the query:

Code:

 UserName	1/31/2007	2/28/2007	3/31/2007	4/30/2007
dang_ht	3175	2647	3440	3966
desai_ra	1477	916	1365	2243
diep_kk	1828	2911	3490	2482
diep_tk	1952	1617	1105	1808
djauhar_aj	1828	1913	1215	165
fazeli_np	1727	3276	2003	1785
guirgui_es	2065	2407	2561	2402
hoang_cq	1082	506	1255	1146
hoang_hn		1324	1836	1863
huynh_cn	1460	863	497	444
juson_kn		1495	2961	1815
lam_at	1483	1439	2216	2741
lam_mu	1732	1628	2207	1965
lam_td		2082	4291	1668
lau_sc	2350	2572	1473	4022
le_am	1627	1393	1789	2173
lee_ee	1673	2643	2456	2347
lee_jy		1266	3509	1600
lee_ke	1919	2421	1511	1179
lui_ey	2214	360	1506	1578
luong_dn	1509	1068	1005	1242
mackey_yr	2056	1392	1432	1073
mai_jm		87	1791	1134
martino_gc	2668	1791	1104	1191
nemeh_ga				1
nghiem_tn	1684	2895	3335	3030
nguyen_ad		965	2416	1084
nguyen_al	1308	1438	1861	2360
nguyen_ct		876	1883	1456
nguyen_ea		1132	2587	1248
nguyen_ja				1

I have another query that needs to be handled in the same manner (rolling dynamically as described below. This other one is a type of errors data that each user has generated. The data I just posted is the more broad type of errors that the users have generated ( I hope I'm not confusing you too much)the other data loos like this:

Code:

 
Initials	MEQA	Month	               DateReceived
EYL	1	January  2008	1/15/2008
EJW	1	January  2008	1/15/2008
EAN	4	June  2007 	6/13/2007
AMN	1	June  2008	                6/12/2008
EAN	1	June  2008 	6/12/2008
AMN	3	March  2007	3/4/2007
AMN	1	March  2007	3/6/2007
EYL	1	March  2008	3/10/2008
EJW	1	March  2008	3/7/2008

It is from this point that I created the crosstab queries.
I can also show you the crosstab queries too.

Thanks,
Richard

**Rickster66** · Mar 17 '08, 05:32 PM

Originally posted by NeoPa

Right, I went back to post #5 and I had to reformat your SQL to be able to read it. The effect is unchanged btw.
Unfortunately, I suspect there's something not quite right with processing the results of a crosstab the way you have proposed. It seems to me that the crosstab would produce the sort of figures you are after without needing to GROUP by the [UserName].

Can you post in some sample data for the crosstab query so I can get a feel for the sort of data we're dealing with? Are there more than one lines for each [UserName]?

BTW, what is going on with the names of the fields in there? There are various with extra spaces, both within and at the end of field names, as well as some very unusual abbreviations that are supposed to represent months. This may not seem important now, but it will soon when we try to get the code to produce your SQL for you.

As far as the abbreviations is concerned, I had no specific reason behind it - I will write them out if you think that is a better idea.

**Rickster66** · Mar 17 '08, 05:37 PM

Originally posted by Rickster66

As far as the abbreviations is concerned, I had no specific reason behind it - I will write them out if you think that is a better idea.

One more thing - the spaces indicate that no data was generated for that person for that month. Most likely because they were hired later in the year or perhaps they were on leave or some other similar thing.

Once again thank you so much!
Richard

**Rickster66** · Mar 17 '08, 06:19 PM

Originally posted by NeoPa

Right, I went back to post #5 and I had to reformat your SQL to be able to read it. The effect is unchanged btw.
Unfortunately, I suspect there's something not quite right with processing the results of a crosstab the way you have proposed. It seems to me that the crosstab would produce the sort of figures you are after without needing to GROUP by the [UserName].

Can you post in some sample data for the crosstab query so I can get a feel for the sort of data we're dealing with? Are there more than one lines for each [UserName]?

BTW, what is going on with the names of the fields in there? There are various with extra spaces, both within and at the end of field names, as well as some very unusual abbreviations that are supposed to represent months. This may not seem important now, but it will soon when we try to get the code to produce your SQL for you.

Please forgive for the confusion but the department has changed its miond. They now want the data the be able to be pulled dynamically as before using the same rules as before but they are to be display in the following manner:
for a given person:

Code:

MEQA	Month
1	January  2008
1	December  2007
4	November  2007
1	October  2007
1	September  2007
3               August  2007
1	July  2007
.               .
1	January 2007

-- They just gave me this and I am just as clueless

Richard

Select Only 10 Columns Going Back

Select Only 10 Columns Going Back

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment