Yearly Planner

ir26121973

Board Regular
Joined
Mar 7, 2006
Messages
79
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
I will assume that your timeline on your report will be:
Code:
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):
Code:
rs.fields(right("00" & month([e-formDate]), 2) = [e-formDate]
Hope this gets you started.
 

ir26121973

Board Regular
Joined
Mar 7, 2006
Messages
79
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

Board Regular
Joined
Mar 7, 2006
Messages
79

ADVERTISEMENT

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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

Board Regular
Joined
Mar 7, 2006
Messages
79

ADVERTISEMENT

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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

Board Regular
Joined
Mar 7, 2006
Messages
79
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

Board Regular
Joined
Mar 7, 2006
Messages
79
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
 

Forum statistics

Threads
1,141,204
Messages
5,704,944
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top