Select Only 10 Columns Going Back

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.

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

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.

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....finishing 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 :
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.

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.

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

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

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

Thank you very much. I really appreciate it

Richard

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.

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:
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:
It is from this point that I created the crosstab queries.
I can also show you the crosstab queries too.

Thanks,
Richard

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.

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

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:
-- They just gave me this and I am just as clueless

Richard

Richard, I've had a horribly busy day and evening so I've been unable to catch up with all the technical posts that are outstanding.

I hope to catch up with all these posts tomorrow so please bear with me and I will respond to all you've posted.

Richard, I've had a horribly busy day and evening so I've been unable to catch up with all the technical posts that are outstanding.

I hope to catch up with all these posts tomorrow so please bear with me and I will respond to all you've posted.

No problem, That's fine - Thanks for letting me know,

Richard

Well, it seems that grouping by UserName would be entirely redundant as there is only one record per UserName anyway.

The spaces I was talking about were the spaces within the names of the months. Access provides ways to produce various standard forms of date text which we can use if we don't have to match the format in your earlier post.

Your source data looks interesting.
I presume that the initials link into another table with the UserNames in.

I don't think I know where MEQA comes into things though. As this is involved in the new layout perhaps you could explain.

Your source data looks interesting.

Expand|Select|Wrap|Line Numbers

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

I presume that the initials link into another table with the UserNames in.

I don't think I know where MEQA comes into things though. As this is involved in the new layout perhaps you could explain.

Actually what would happen is that the user would select a member then a date then click find. The data would appear like this: Lets say January 2008 was picked.


So as you can see, for every date picked, There must be a show of data that goes back 12 months. MEQA is simply a severe error commited by the user.

Hi NeoPa - Did you forget me?

No - Not forgotten.
Things have just been really busy recently on the site. As an Admin I sometimes get tied up with less visible issues.

I will get back to this as soon as I can. Easter weekend may enable me to catch up a bit.

Your data doesn't have a UserName included in it anywhere. Unless there is some correlation between Initials and UserName, the data cannot be selected by UserName as you suggest.

1. What is there that can be considered to be a unique identification in the data? Initials and Month perhaps.
2. What is the data type of your fields [Month] & [DateReceived]?
3. Lastly, how would the data be displayed? In a form? In a report? In a simple query window?

Clearly we need to have a query of some sort in the solution and the WHERE clause or filter (which is similar) must be designed to select only those items that you need. Without the information though, it's unclear how this can be achieved so I can't take this further just yet.

Your data doesn't have a UserName included in it anywhere. Unless there is some correlation between Initials and UserName, the data cannot be selected by UserName as you suggest.

What is there that can be considered to be a unique identification in the data? Initials and Month perhaps.

What is the data type of your fields [Month] & [DateReceived]?

Lastly, how would the data be displayed? In a form? In a report? In a simple query window?

Clearly we need to have a query of some sort in the solution and the WHERE clause or filter (which is similar) must be designed to select only those items that you need. Without the information though, it's unclear how this can be achieved so I can't take this further just yet.

The idea is that the data is selected by initials within a subreport. The initials link the child and parent form (the is how the data is given to me - username is also included but I don't think it work with the parent form.

So, the data would be displayed in a report - Month and date received should be date types. Date received is just a timestamp so it doesn't need to show on the final output - i don't know if it would be a good idea to make it part of the query - if you think it should be part then it shall be.

If you need me to clarify more information, please let me know and thanks for all your help.

Richard

PS I've reading up on PIVOT CHARTS (I have know experience on this area) do you think it be worth it to check it out?

I've edited post #23 to display my questions in a proper, numbered list.

It appears that you've answered question #2, but #1 & #3 are still awaiting answers.

Pop the answers to those in and we'll see if we can't knock up something a little more appropriate for your needs.

I've edited post #23 to display my questions in a proper, numbered list.

It appears that you've answered question #2, but #1 & #3 are still awaiting answers.

Pop the answers to those in and we'll see if we can't knock up something a little more appropriate for your needs.

For question 1: I've been trying to figure that out myself. By that, I mean the the first table is a collection of of other tables that contain all sorts of data that is useless to me. I wrote a series of select into queries and came up with a table that looks like this:
and I thought the PK would be Initials. but since te requirement is that is should be displauyed in report form in columnar form:
What I did is transform the table so that the date columns were shown horizontally and the initials were shown vertically:

I still don't know if this is a good idea or not - as far as the dates is concerned, I did not convert the full dates to months yet- I figured I can do that at the very end. In terms of a unique identifier, would the dates column be fit enough to work as a unique identifier? I am pressuming but I am not sure. What would you suggest?


3) The data would be displayed in a report.

As always, thank you so much - this is beginning to make sense for me

Richard

Sorry Richard. I'm getting confused here a little (Question 1). Q2 & 3 are fine, but I was talking about the data laid out in my post #19 that you posted originally in post #12.

I suppose what I really need to know is "Is it possible for the initials, matched with any particular month, appear more than once?"

EG. Is the following possible (where EYL / Jan 2008 appears more than once)?
This was the data source I was thinking of working with but your focus seems to be elsewhere so I'm hoping this is still valid.

If that is possible data then the query needs to have a GROUP BY clause and is complicated by aggregate functions etc. As a proponent of KISS I'm trying not to overcomplicate things.

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

Right, I've just skimmed through the thread to remind myself what the basic request is (we've spent a while nailing down some details) so let me see if I can outline what I think we need and you can confirm this as I look at knocking something up. If, in the mean time, you can provide any real-life names for items that I'm using that would also help.

You have a form (name) where the operator will select a user (initials) and a month.
When the command button is clicked, a query (or report) should be run to produce lines, in descending order of month, to show thirteen months of MEQA data for the user where the first record (latest month) matches the month on the form.

Names required :
Form
Control where initials entered/selected
Control where month entered/selected
Command button that triggers the query/report
Table
Fields assumed to be [Initials]; [MEQA]; [Month]

One last question is :
What is the field type of [Month]?
This is treated differently depending on whether it's a Date/Time field or a Text field.

PS. Just to clarify - the first two and the last paragraphs are the most important as far as responses are concerned. I can get by without the names (I'll invent my own) but I do need confirmation I'm on the right lines and I need to know how to deal with the [Month] data.

In terms of names for real life items I have:

Names required :
Form ....frmstats

Control where initials entered/selected.....cbInitials - the rowsource property will by a table that will fill out the combobox with initials
Control where month entered/selected - PVDate where when clicked, a calendar appears and the user selects a desired date. next to it is a non visible text box called EndDate - this textbox is populated automatically by ways of a function that picks up the date chosen from PVDate. the sql statement grabs these 2 dates as parameters and runs it.

Command button that triggers the query/report : cmdFind - when clicked, it it call the sql statement which will grab the parameters and run the query.

Table: tblHistory


Fields assumed to be [Initials]; [MEQA]; [Month] - this is correct

Month -I am presuming ought to be a date/Time type - I wonder how could the parameters work if it was to be a text field. I figure a format function for would change date date from say 3/5/2007 to March 2007 right at the textbox on the report - I hope this makes sense

In the absense of confirmation relating to the first two paragraphs, I will continue as if you have read them and agree that they are on the button.

You talk about [PVDate] & [EndDate] controls and [EndDate] being populated from [PVDate]. If these controls have different values then this only helps if I know how [EndDate] is determined. I can only ignore [EndDate] for now and assume that [PVDate] has the value I require.
The code may be considerably simpler if we know that the dates stored (as [Month]) are always stored as the first day of the month. As I don't know that I have to allow for the worst case scenario (hence the WHERE clause is so complex).

WOW!!!!!!! It works!!!

What do you think of saving the query as a report to then use it as a sub report?

by the way - A million thanks - the query works well!!!

Richard

Firstly, you're welcome.

As to your question, I'm afraid there seems to be some confusion. As asked it doesn't make any sense. You cannot save queries as reports.

I suspect you have something more straightforward in mind, but I'm afraid I can't guess what that may be. If you can ask the question more clearly I'll happily give my thoughts on the matter.

WOW!!!!!!! It works!!!

I will talk with the other admins about making it a sackable offense to express any surprise when my solutions work in future.

It's as if no-one has any confidence in me :(

(LOL)

I will talk with the other admins about making it a sackable offense to express any surprise when my solutions work in future.

It's as if no-one has any confidence in me :(

(LOL)

I always had total and complete faith in you -I was very excited that It is working. I am trying to figure out a way to put the results of the query as a subreport. :)

Of course.

And I'm sure you know I was just kidding around :)

Of course.

And I'm sure you know I was just kidding around :)

I presumed that

I have another question, if i need to list every month that does not have a value or is not on the data but must include a value of 0 for that month, how would I go about it?

This is not very straightforward in SQL :(

Essentially what you need to do is have a record source (Let's call it tblDates for this illustration) which would be treated as the master table and must contain all the dates you need to show, then link it to your other data with a LEFT JOIN.

There are two main difficulties with this approach :

1. The linked fields must be identical, so the [Month] field in your [tblHistory] table MUST be consistent and predictable (easiest is to ensure always the first day of the month).
2. Providing a record source that has all the dates you need for whatever frmStata.PVDate is, is a very complicated issue.

Let's start with confirming that the [Month] value and the [PVDate] value ALWAYS reflect the first day of the month.

I'm going one step at a time as there is no danger of skipping important steps or questions that way.

Let's start with confirming that the [Month] value and the [PVDate] value ALWAYS reflect the first day of the month.

This is no problem. I receive the data dumps in excel and they leave it to me to prepare the data as I see fit so the Month value and the PVDate will ALWAYS be the same. I pick the columns that are needed for this project and incorporate them into the table. What they care about is months so I will make sure both values are the same.

This is not very straightforward in SQL :(

Essentially what you need to do is have a record source (Let's call it tblDates for this illustration) which would be treated as the master table and must contain all the dates you need to show, then link it to your other data with a LEFT JOIN.

There are two main difficulties with this approach :

1. The linked fields must be identical, so the [Month] field in your [tblHistory] table MUST be consistent and predictable (easiest is to ensure always the first day of the month).
2. Providing a record source that has all the dates you need for whatever frmStata.PVDate is, is a very complicated issue.

I kinda figured that it would be no easy task. The tblDates table would have a PK, and a field with Dates in month format, correct? does that mean that every row of individual dates need to be converted to the 1st dat of the Month - that is VERY POSSIBLE- my bosses only care about Months not specific dates...

This is no problem. I receive the data dumps in excel and they leave it to me to prepare the data as I see fit so the Month value and the PVDate will ALWAYS be the same. I pick the columns that are needed for this project and incorporate them into the table. What they care about is months so I will make sure both values are the same.

As long as you can ensure that all dates are stored as the first of the month (As Access stores month dates anyway) then we're good.

Next we need to create a table called [tblDate] as follows :
This table needs to be populated with [DateOffset] values of 0 through -12 (13 records).

This may seem a little weird, but it will become clearer when we use it.

...The tblDates table would have a PK, and a field with Dates in month format, correct?...

No. As per my previous post (#43) the data is simply a DateOffset value which is itself the PK.

...Does that mean that every row of individual dates need to be converted to the 1st dat of the Month - that is VERY POSSIBLE- my bosses only care about Months not specific dates...

If you have any date values stored in your [Month] field that are NOT the first of the month then yes.

FYI: If you run this code you will get the result shown :
This is the default date Access assumes if you leave out the day number and just use the month.

The last step is to change the SQL of your query to :

The last step is to change the SQL of your query to :

Expand|Select|Wrap|Line Numbers

1. SELECT tH.MEQA,
2.        Format(subD.Month,'mmmm yyyy') AS [Month]
3. FROM (SELECT DateAdd('m',[DateOffset],Forms!frmStats.PVDate) AS Month
4.       FROM tblDate) AS subD LEFT JOIN
5.      tblHistory AS tH
6.   ON subD.Month=tH.Month
7. ORDER BY subD.Month DESC

I am not getting any values on the MEQA field

Post in what all your settings are (the value of the PVDate control; The actual results of the query etc) and we'll see what might be missing.

PS. Include the data of the relevant records in tblHistory too, showing all dates completely.

Post in what all your settings are (the value of the PVDate control; The actual results of the query etc) and we'll see what might be missing.

PS. Include the data of the relevant records in tblHistory too, showing all dates completely.

for PVDate I have entered 2/28/2008,2/1/2008, and february 2008 with nothing showing in the MEQA column - these are results:

Here are the records from tbl history:

i hope this information is clear

thanks again,

Richard

Richard, this indicates to me that you haven't followed the instructions in post #43 quite correctly.

The numbers should be 0 through (minus)12.

To fix this without redoing the table data, just add a - (minus) before the [DateOffset] bit in line #3 of the latest SQL code.
Let me know how it goes after that.

Richard, this indicates to me that you haven't followed the instructions in post #43 quite correctly.

The numbers should be 0 through (minus)12.

To fix this without redoing the table data, just add a - (minus) before the [DateOffset] bit in line #3 of the latest SQL code.

Expand|Select|Wrap|Line Numbers

1. FROM (SELECT DateAdd('m',-[DateOffset],Forms!frmStats.PVDate) AS Month

Let me know how it goes after that.

Good Morning, Here's what I get when I enter 2/1/2008:

The MEQA field still shows nothing. Would it be feasible to perhaps use this newly created query and join it with tbl History again? I'll give it a shot--