Yearly Planner

[ir26121973]

Hi,

Wonder is someone can help please.

I have a table containing data on specific e-forms that I perform certain tests on to make sure they work when down loaded by users.

There are many e-forms with many different tests but for ease of explanation I will use only a few.

The columns are labelled as below with the name of the form in the first column and then the dates under the relevant tests as can be seen in my example. These tests can be carried out on any day throughout the financial year i.e Apr-Mar.

e - Form Test 1 Test 2

Form 1 01/04/06 20/09/06
Form 2 18/10/06 05/12/06

What I would like, if possible is to show this data in a report with a 12 month timeline going across the top and then the forms down the left hand side and the test dates plotted under the relevant timeline date against them.

I'm not sure whether Access is the correct application to do this in but I feel it may be even more difficult in Excel.

Any suggestions or guidance would be greatly received.

Many thanks

Chris

 

[VicRauch]

I will assume that your timeline on your report will be:

Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec  Jan  Feb  Mar
(04) (05) (06) (07) (08) (09) (10) (11) (12) (01) (02) (03)

The (04)... line is just to see which month number, by date, each column is.
Now, all you have to do is get the date into the correct column. This could be done within a work table, query (would use VERY long formulas), or within the report. I personally would do it within a work table, as I feel that is usually much easier to debug and later change as necessary.
So, to do this into a work table, I would create a table that would look a lot like the report. e-form names in the first column, then a column for each month of the year, and I would name these columns "01", "02", etc. As long as the column name is the same as the month you are dealing with, then when you get to writting the report, all you need to do is put the "01" column as the tenth column over, because that is where the Jan column would be for your fiscal year.
The code, within a VBA function to put the data of a date of #01/22/07# into the "01" column (assuming "rs" as the recordset object name):

rs.fields(right("00" & month([e-formDate]), 2) = [e-formDate]

Hope this gets you started.

 

[ir26121973]

Hi Vic,

Thanks for taking the time to reply to my post, it is greatly appreciated.

There is one thing that I'm not too sure about. As you say the best way to show the forms is down the first column, but I thought my columns after that would need to be the actual tests with the dates underneath the headings as I showed in my original post. So I don't think I can label these as the actual months.

Do you understand what I'm trying to say.

Many thanks

Chris

 

[VicRauch]

My bad. I did not re-read your original post as I should have. (But I like my way, so please redesign your system to fit my way. OK? LOL) OK, lets get serious again.
What you want is the date of the test under the test name, which is it's own column name. Correct?
OK, I just re-read your post again. Now I need a little more clarification.

this data in a report with a 12 month timeline going across the top and then the forms down the left hand side

What does this mean? This is where I got the 12 months of columns. So how does this "12 month timeline going across the top" fit with the test names as the column names?
thanks,

 

[ir26121973]

Hi Vic,

Sorry I should have made it clearer.

In my form going from left to right my headings are:

e-Forms Test 1 Test 2


Under the heading of e-Forms I have the name of the form and then the date that the tests were carried out on under the headings Test 1 and Test 2.

Ok so that's the form.

I would then in a report like to show the forms down the left hand side and then the dates that the tests were carried out on, plotted under the 12 timeline, so a test carried out on 15/04/06 would be plotted under 15/04/06 on the timeline.

I know I could quite easily show this as text as a list but it would be a lot harder to read, so that's why I would like it graphically.

Does this make it a bit clearer.

Many thanks

Chris

 

[VicRauch]

So, the form has the Test Names across the top, but you want the report to have the Dates across the top, and then the test name at the intersection of the Date (Column) and e-form (Row)?

How many dates will be across the top of this report? Is there only one test date per month?

 

[ir26121973]

Hi Vic,

Yes you've got it about the form and report.

There should be only one test per month, but obviously that test may have been carried out on more than one form.

What I thought I could do would be that rather than to put the name of the test in the report to have it colour coded. So rather than text boxes, these would be blocks of colour with the actual shown in it.

Is this ok?

Many thanks and regards

Chris

 

[VicRauch]

OK, we need to put the TestName (now colored box) into the correct column, based on month of test. So really arn't we back to where we started? I think I gave you a method of plotting something (a date) into the correct month column. Will, you are still dealing with a date, the date of the test, except, you are putting the test name (colored box) into the column rather than the actual date. So, you still have the same method of determining the column, but now you would put a colored box there instead of a date.
Next question. How to show a colored box? Either use a number to represent the test name, or the test name itself, then in the report, use a Select/Case statement to determine what color to make the box. This code would be put into the OnFormat event of the report.
HTH,

 

[ir26121973]

Hi Vic,

Sorry to be a pain,

No what I would like to do is have the coloured box with the date in it. I think I have some coding I can use for the colour so I think that's ok.

The thing that confuses me a little still is the 04, 05, 06 etc in the report. I understand why it is there, but from what you said this number should relate to what I put in my table.

If my table headings are my tests with the relevant dates matched to the form underneath, where do I put the 04, 05, 06?

I'm really quite new to access, so I appreciate your patience.

Many thanks, once again

Chris

 

[ir26121973]

Hi Vic,

Sorry to be a pain,

No what I would like to do is have the coloured box with the date in it. I think I have some coding I can use for the colour so I think that's ok.

The thing that confuses me a little still is the 04, 05, 06 etc in the report. I understand why it is there, but from what you said this number should relate to what I put in my table.

If my table headings are my tests with the relevant dates matched to the form underneath, where do I put the 04, 05, 06?

I'm really quite new to access, so I appreciate your patience.

Many thanks, once again

Chris