Math updates

[jkwok]

Hi,

I have an attribute in a table called 'work' that records time in hours. I want to be able to run a query, or a macro or whatever else is needed so that the 'work' table can be updated.

I want to update only those records in 'work' with hours that are greater than 24. With those records, I want them to be reduced by 24 until the final result is under 24 hours total. So if hours was 61, it would have 24 subtracted from it until it's under 24 total. i.e. 61-24-24=13

Example

BEFORE:

Hours

id hours
1 19
2 36
3 51
4 42

AFTER:

Hours

id hours
1 19
2 12 (36-24)
3 3 (51-24-24)
4 18 (42-24)

I'm certain I'll have to use a loop somewhere, but I'm really unsure as to how to get this done in Access.

Any help would be greatly appreciated!

Thanks,
Jason

[ADezii]

Hi,

I have an attribute in a table called 'work' that records time in hours. I want to be able to run a query, or a macro or whatever else is needed so that the 'work' table can be updated.

I want to update only those records in 'work' with hours that are greater than 24. With those records, I want them to be reduced by 24 until the final result is under 24 hours total. So if hours was 61, it would have 24 subtracted from it until it's under 24 total. i.e. 61-24-24=13

Example

BEFORE:

Hours

id hours
1 19
2 36
3 51
4 42

AFTER:

Hours

id hours
1 19
2 12 (36-24)
3 3 (51-24-24)
4 18 (42-24)

I'm certain I'll have to use a loop somewhere, but I'm really unsure as to how to get this done in Access.

Any help would be greatly appreciated!

Thanks,
Jason

[CODE=sql]UPDATE Work SET Work.Hours = [Hours] Mod 24
WHERE Work.Hours > 24;[/CODE]

[jkwok]

Wow, that is so simple. Thank you very much!

[ADezii]

Wow, that is so simple. Thank you very much!

You are quite welcome.

[jkwok]

Regarding using mod, is there a way to control the decimal places?

For example if I mod 26.5, it gives me 2. How can I get 2.5?

My 'hours' attribute is currently a number data type, field size double with decimal places set to 2.

[ADezii]

Regarding using mod, is there a way to control the decimal places?

For example if I mod 26.5, it gives me 2. How can I get 2.5?

My 'hours' attribute is currently a number data type, field size double with decimal places set to 2.

You cannot control the Decimal Places with MOD since it Truncates Decimal Places, but there is a workable alternative:
[CODE=sql]UPDATE [Work] SET [Work].Hours = [Hours]-(Int([Hours]/24)*24);[/CODE]

[jkwok]

This is great, thank you so much ADezii!

[ADezii]

This is great, thank you so much ADezii!

You are quite welcome.