Do I need a table column or "on the fly" calculation

[johncassell]

Hi There,

sorry if this is in the wrong forum but I thought it may cover more than one subject.

I have a table with "FitterName", "WorkedHours" and "ChargedHours" columns.

I need to display (in both reports and in forms) the difference between the charged and the worked. Should I have a 4th column with "Difference" or make my forms and reports calculate it when requested.

Was hoping someone had experience of both ways and could advise.

Thanks

John

 

[Arg81]

In your SQL select, create the 4th column.

I.E. don't have it as a physical field in your DB, but let your query work it out;

SELECT FitterName, WorkedHours, ChargedHours, (WorkedHours - ChargedHours) AS DiffHours
FROM Table


Not sure how you want the difference to be - with that I set to worked minus charged. You could have it the other way round if needbe.

 

[cjard]

If your form will edit either the Worked or the CHarged hours, and you require an instant update of the data, then the column must be calculated by an Expression:

Your datatable will have an extra readonly column, not present in the db, and not present in any queries. It will have its .Expression column set to:

[ChargedHours] - [WorkedHours]


This is a string. If set in code, it needs "quotes". If set in the designer, just write it.

 

[johncassell]

Hi,

I do require an instant update of the data so thanks for that cjard.

I will also look into your SQL select method Arg as this may be useful for me in future.

Thanks very much

John

 

[johncassell]

Cjard,

Thanks again for that. Got it working.

By any chance is there a way to make the rows containing negative figures for my new column to be set in red font?

John