Replies

Greetings,

Here is the format in Excel. I want to accomplish the same in Access. I’m using Access 97 SR2.

The “All Lines” sheet contains all of the applicable information. The “Summary” sheet is the summary for all Organizations.

The data here is for one month. As more months are added, the Year-To-Date will change accordingly. I will be tracking Month-To-Date and Year-To-Date numbers.

Would it be easier to try and show these based on a query or queries rather than an unbound form/report?

Thanking one and all for their assistance.

Bob in Indy.

Hans, would it be better if I sent the Excel 97 spreadsheet showing what I’m trying to accomplish in Access? If so, I would want to send it directly to you, rather than post it here on the internet.

Hi. Sorry for not replying before now. Was placed on another project.

The information comes from an Excel worksheet. I’m taking the total hours worked month-to-date and year-to-date, and calculating the percentage of overtime worked by each department.

Table layout is flat-file.

Below is an example of the hours I’m tracking for 1 of 26 divisions. The ALPHA column contains the Type-Hour codes. Any code beginning with the letter O denotes overtime.

Type Civ Hrs Mil Hrs Tot Civ Tot OT %
Hr Cd MTD MTD Hrs OT Hrs

CD 9.00 0.00
CN 4.50 0.00
CT 27.50 0.00
LH 16.00 0.00
LS 3.50 0.00
RG 311.25 0.00
CD 35.75 0.00
CN 64.00 0.00
CT 9.00 0.00
LA 108.00 0.00
LH 80.00 0.00
LS 92.00 0.00
OS 2.00 0.00
RG 1,524.25 0.00 2,286.75 2.00 0.09%

I would like to;

1. Using an unbound form, perform a calculation. I need to calculate the percentage of overtime used in a particular work area. There are 26 different work areas. The only distinct item of data that indicates overtime is in a field called Type Hr Cd. All overtime begins with the letter O.

2. I would also like to perform this calculation within a query and also in a bound report.

Can I use a DSUM expression to perform calculations within an unbound form? Not only will I be tracking month-to-date, but also year-to-date.

Thanks in advance !!

Bob in Indy

PS: Hmmm…..the columns of info didn’t post correctly.

Thomas, right after I posted my reply to your answer, I began playing with your SQL. I made it happen. Here it is, and it works like a charm!! Thanks again!!

UPDATE t_DIX_Bak SET t_DIX_Bak.MANHRS = Left([MANHRS],3) & “”, t_DIX_Bak.BK4 = Left([BK4],0) & “”
WHERE (((t_DIX_Bak.BK4) Is Not Null));

Thomas, your SQL worked like a charm !!!!! Thank you !!

Do you have any ideas pertaining to my second question? Wondering if I can somehow modify your SQL to delete or clear whole fields or parts of fields?