Show calculated data along with original data in form

[RECrerar]

Hi, I am trying to create a form to display some of our costing data.

I have a table that includes the following fields:

Project ID
Total Cost
Total Hours
1
2
3
.
.
.
51
(where the last field represent weeks)

Each of the week fields contian a percetage of the total cost as it's expected to be distributed.

For each week I want to calculate the equivalent hours using the calculation [Week] / ([Total Cost]/[Total Hours])

I would then ideally like to display this on a form as if for each project there were two sets of records one with the raw data and one with the data converted into hours.

What would be the best way to do this?

if you need more information let me know and I will try and provide as much as possible

 

[statsman]

There are two ways to do this.

The first would be to call your form from a query and do the calculations in the query:
CalculatedCost: [Week] / ([Total Cost]/[Total Hours])
Then place the CalculatedCost field onto the form.

The second would be to place a blank text box onto the form and place your calculation directly into the text box:
=[Week] / ([Total Cost]/[Total Hours])

 

[RECrerar]

Thanks for getting back to me, this is just going to be a quick acnologement as I have to dash to get the train (will look more seriously in the morning).

Firth thing that comes to mind is this.

I need to display all 51 weeks on the form, making the form very cluttered. I was solving this my having the main form with the general project data and a subform in datasheet view for the weekly data.

I have already got the values calulcated in a query (some oddness happening there but that may well form a different thread) and I can get both the calculated and the non-calculate value to display on the same form. I am currently doing this via two datasheet view sub-forms but that looks messy and I will need some code behing the form to make sure they both scroll at the same rate I think.

Not sure if that makes sense, but I guess the question is more of finding an asthetic way of displaying the information rather than simply how to get it all to disp[lay on the same from.

Thanks and I'll look at it some more in the morning

 

[statsman]

I generally avoid datasheet forms unless I am the only one who is going to use it. It is cluttered and difficult to read.

Have you considered using a tab or columnar form instead? At least you can change the design of these types of forms to assist other users.

 

[RECrerar]

I haven't really considered other types for this yet, as I think for my data this is the nicest way to display it. I'll put up an screen print of it in the morning to see if you agree or may try and put together a sample db as I don't think I'm be very popular if I posted company finances on the web.

I'm at home now and really should stop thinking about work, so until tomorrow..

Robyn

 

[DCrake]

One thing not discussed is the fact that you have 51 fields in one table that represents the weeks. It would be far more easier and normalised to have a different table with the projectId and a week no field and the other fields you need for your calculations. It is far easier to manage rows than columns.

By using rows you can display the weekly activity in a list box, of which the contents will grow as the weeks progress. That way you do not need to accommodate 51 fields on a form.

Your listbox could contain brief header data, but if the user clicks on a specific week no you could get it to bring up a popup form with a more detailed view of that weeks performance. This means the end user does not have to scroll through loads of data to find the small piece they are looking for.

CodeMaster:

 

[RECrerar]

Hi, Yeah I am just finding out that fields are much harder to work with. The problem is that it's just a projected forcast, so there will be no more details to break down on a weekly basis, just the one value for each project for each week, and the users need to be able to view all the weeks simultaneously and when entering the data they will enter data for the whole year in one go.

Additional considerations are that everything in the company shows weeks progessing horizontally rather than vertically and in presenting the data I would not want to modify this convention, and we will also be intersted in summing the weekly spends on multiple projects to know the toal weekly spend. Therefore I will need to create sums accross both fields and records regardless of which way round the data is presented, unless I have a seperate table for each week, which will rapidly see me ending up with literally hundreds of tables.

Hope this sin't too waffly, it is more of a brain dump than anything else.

Would it be best to keep this in Excel? Will it be easy to connect to this via access in order to create reports? Oh dear lord why have they given me this job?

 

[neileg]

The CodeMaster is right, you need 51 records, not 51 fields. You could then use a continuous from to provide a scrollable list of the 51 values.

 

[RECrerar]

Okay but I also need to be able to sum the values accross projects and therefore if I had seperate records for each week I would either need a seperate field for each project or a seperate table for each project, neither of which strike me as being very workable.

I also need to be able to view all the weeks together.

Therefore the questions are:

Is there any way to work with data that needs to be summed accross rows and columns in Access?

If I was to store this data in Excel, can I easily read in values into Access, specifically for use in charts?

I know logically this is work that would be much easier to achieve in Excel, however the whole point of this project is to collate all our data into one place, namely an Access database and therefore if it is possible to work with the data directly in Access, even if it is not eay, that would be preferable.

 

[DCrake]

At the moment you have

Project A; wk1;wk2;wk3;wk4;etc;wk51
Project B; wk1;wk2;wk3;wk4;etc;wk51
Project C; wk1;wk2;wk3;wk4;etc;wk51

You would then have to add all the wks together to get a running total
You would have to do this for all fields irrespective if they had data in them or not.
You would have to ensure that the user did not skip any weeks


What you need is

project A
wkNo

Data would appear
Project A
---Wk1
-------Value
---Wk2
-------Value
---Wk3
-------Value
---Etc
-------Value
---Wk51
-------Value
Project B
---Wk1
-------Value
---Wk2
-------Value
---Wk3
-------Value
---Etc
-------Value
---Wk51
-------Value
Project C
---Wk1
-------Value
---Wk2
-------Value
---Wk3
-------Value
---Etc
-------Value
---Wk51
-------Value

This way you simply run a crosstab query that uses project names as row headings, Week numbers as column heading and the value as the figure you want to sum. If you include totals in your query it will give you year to date activity.

Columns are expensive, rows are free

 

[RECrerar]

Okay,

So I have been looking at this more and the Crosstab query does display the data in a nice a doable format.

So I now have a table with the following fields

Project ID
Week Number
Amount

This does seem the best format, sorry for being a bit slow, I'm pretty new to Access (as you may have noticed). There is a lot more I need to be able to do with the data but will have a look at this myself first before asking more, but just thought I would let you know that I have abandoned the fields idea.

Will no doubt get back with more questions soon.