DSum & Sum (Access 97 SR2)

Topic

New Reply

1. Using an unbound form, I’m wanting to 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.

Any assistance is appreciated !!

Reply | Quote

Replies

You will need to tell us a bit more about the structure of your table. What are the field names and types? You only mention type_hr_cd.

Reply | Quote

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.

Reply | Quote

Let’s say your data are in a table tblHours. Note: the expressions in the following may be too long to be displayed on one line.

Place a text box on an unbound form and set its Control Source property to

=DSum(“[Civ Hrs Mtd]”,”tblHours”,”Left([Type Hr Cd],1)=’O'”)/DSum(“[Civ Hrs Mtd]”,”tblHours”)

and set its Format property to Percentage. This will calculate the overall overtime percentage. If you have all divisions in one table, you might put a combo box cboDivision on the form from which the user can select the dicision. To return results for a single division, set the Control Source property to

=DSum(“[Civ Hrs Mtd]”,”tblHours”,”Left([Type Hr Cd],1)=’O’ And Division = ‘” & Me.cboDivision & “‘”)/DSum(“[Civ Hrs Mtd]”,”tblHours”, “Division = ‘” & Me.cboDivision & “‘)

Since you have supplied no information on how the date comes into this, I can’t tell you how month-to-date or year-to-date values should be calculated.

Reply | Quote

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.

Reply | Quote

Hi Bob,

It’s always better to post files in the Lounge; that way other Loungers can have a stab at your problem too. If your spreadsheet contains sensitive information, you can replace actual names etc. by dummy values. If the .xls file is too large (there is a 100 KB limit on attachments), you can WinZip it.

Reply | Quote

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.

Reply | Quote

Bob,

To get a report displaying a summary like that in your spreadsheet., I came up with a series of queries: separate queries to sum civilian hours and civilian overtime hours, and separate queries for month-to-date and year-to-date. They are all bound together with a query returning all divisions (orgs).

Posting the SQL for each probably isn’t very enlightening; I have attached a zipped database with the queries and a report. I have added dummy data for other months, and to keep the size down, I have kept only a few divisions. If you need explanation (or if it doesn’t do what you want) post back.

Reply | Quote

Hi Hans

Great stuff! You sovled a problem I was going to have next month.

If you want the query to ask for starting year, would simpley add Year: Year([Period]) to qryOrgs with group by then set criteria to ask for year?

John

Reply | Quote

Hello John,

It’s a bit more complicated than that. The query qryOrgs in the database I posted simply returns all divisions (orgs). The four queries with CivHrs in their names all have criteria for restricting to the current year and month. If you want to prompt the user for a year and month, you must make these four into parameter queries. The parameters must be declared explicitly in Query/Parameters… for them to work correctly.

In the (zipped Access 97) database attached to this reply, I have modified the four queries to ask for month and year; qryOrgs and qryPercOTPerOrg are unchanged. I also changed the text in the report header to reflect the parameters.

Reply | Quote

Hans,

Unbelievable !! I don’t think I would have though of designing the queries as you did. Sorry I have not responded before now, but I was placed onto another project. I’ve studied both attachments, and will certainly be able to use them for the intended data….and other applications.

Again, thank you!!

Bob in Indy

Reply | Quote

What happened to the attachment ?!?!?!?!?

The file on my PC has data in the table, but nothing in the queries nor report. I thought I could redownload the attachment from here, but there is no data in any of the queries nor report.

Anyone have any ideas ??

Reply | Quote

That’s because the sample data in the attached db (originally posted last year) all had dates in 2002. The current year is now 2003. As a result the queries displayed no data. You have to update the dates in “Period” field to reflect 2003. You can do this with some update queries. Example:

UPDATE tblTime SET tblTime.Period = DateSerial(2003,1,Day([Period]))
WHERE (((Month([Period]))=10));

This updates the OCT 2002 dates to JAN 2003.

I attached a copy of the same attachment with updated dates as zip file, converted to ACC 97 format (I’m using A2K & AXP).

HTH

Reply | Quote

(Pointing finger at self while quoting Forrest Gump…)

“Stupid is as stupid does…”

Bob (feeling stupid) in Indy

Reply | Quote

Thanks, Mark, for putting that straight!

Reply | Quote

Not to be butting in, but the question made me curious, as it would be highly uncharacteristic for you to post a sample db with bogus data – perhaps as a little “joke”? – so I took liberty of posting copy of original db with updated dates to clarify any confusion….

Reply | Quote

Hi Mark,

You’re welcome! Because of your reply, IndyAries didn’t have to wait for me to come online again. Although the time difference can be a problem sometimes, I like the idea that Loungers span the globe (more or less), so that questions get replied to 24 hours a day.

Reply | Quote

Another way is to build a query (named qryTotal) for the total sum like:
SELECT Sum([Civ Hrs Mtd]) as TotalHrs FROM tblHours

Then build another query like:
SELECT Sum([Civ Hrs Mtd])/qryTotal .TotalHrs as AvgHours FROM tblHours, qryTotal WHERE Left([Type Hr Cd],1)=’O’

I don’t know if this is more or less efficient than Hans solution, but if the tables are small it should not matter. Perhaps Hans will respond to this.
HTH
Pat

Reply | Quote