Is this DSum correct, or would something else work better?

[Luna Saisho]

Hi,

I'm trying to do something with my database I'm having no luck with, and I'm
pretty desperate for help *offers cookies*

Basically, I've made a database to track our warehouse staff's pallet's per
hour in four different catagories. This report runs off a query, and the
Detail part of the report has the date of this record, their hours worked and
various other bits, including the important Area they worked.

What I want is to have the Report Footer show the averages for all dates and
all employees printed. It works great, but I realized I'm averaging all
pallets to the entire pool of hours worked, when my managers (understandably)
want hours worked in shipping to add to a pool of shipping's hours,
palletizer to palletizer hours, etc.

I'm getting a completely wrong figure from this:
DSum("[EFF-Performance].[Shift_ID1_Hours]+[Shift_ID2_Hours]","[EFF-Performance]","[Area]='Shipping'")

EFF-Performance is my table to look through, the Shift IDs are normal and
overtime, and Area is where they worked.

Can someone tell me what I'm doing wrong, and maybe point me in the right
direction?

Thank you!
Steph

 

[Luna Saisho]

While I still need help, I figured out something with what I was doing with
DSum. It is finding ALL the records with (in this case) Shipping, and giving
me a total. I verified with DCount, and it's counting 46 records and adding
them together ... the problem is that I need it to do only the displayed
records, and putting a query name in (The same query that helps create the
report) instead creates an error.

Any help is appreciated, thank you so much!!
Steph

 

[Steve Schapel]

Steph,

I admit I haven't quite got a grasp on your project here. But it seems
to me that you could just put like this in your report footer...
=Sum([Shift_ID1_Hours]+[Shift_ID2_Hours])

 

[Luna Saisho]

Hi Steve,

Thanks for responding. ^_^

That basically is what I already have there, but the problem is that sums
all the hours. What I need from Access is for it to keep that running sum of
hours, but to allow me to find out how many hours worked in each area. For
instance, if someone works at the palletizer, it will show how many hours he
worked there total. Because I'm using a total sum of hours displayed, over
however many employees are displayed, the results for each area are
inaccurate since the hours include Shipping, Receiving and Depaletizer hours
as well.

If you have any other ideas, please let me know. Thank you so much!
Steph

Steph,

I admit I haven't quite got a grasp on your project here. But it seems
to me that you could just put like this in your report footer...
=Sum([Shift_ID1_Hours]+[Shift_ID2_Hours])

--
Steve Schapel, Microsoft Access MVP

While I still need help, I figured out something with what I was doing with
DSum. It is finding ALL the records with (in this case) Shipping, and giving
me a total. I verified with DCount, and it's counting 46 records and adding
them together ... the problem is that I need it to do only the displayed
records, and putting a query name in (The same query that helps create the
report) instead creates an error.

Any help is appreciated, thank you so much!!
Steph

 

[Steve Schapel]

Steph,

In that case (as far as I understand it!), two possible approaches would be:

- use an Area Footer section in the report, and put your totals
textbox in there, this will give the totals for each Area.

- use an expression like this in the report footer...
=Sum(IIf([Area]="palletizer",[Shift_ID1_Hours]+[Shift_ID2_Hours],0))

If this doesn't solve it, you are going to need to provide a lot more
detail, with examples, of your data and your report.

 

[Luna Saisho]

Hi Steve,

When I tried your sum example, unfortunately it didn't work. It came up
with prompts asking me what the total hours 1 and 2 should be.

I also replied with this image:
http://img.photobucket.com/albums/v648/lunasaisho/misc/reportexample.jpg

and more info, but it doesn't seem to have posted, so lemme give you the
rundown again, hoping you see it.

Our example is named Steve... I scribbled out his name, yes. Anyway, he
was on the last page of the report that I had give us the 24th and 25th.

Each record is displayed as you see, with the date and area they worked on
the left, followed by the hours for the day, total palletes handled, and the
average per hour. To the right of that is each of the 4 sections, Shipping,
Palletizer, Depal, and Receiving. Each little bit has the total and average,
and below the various stats for each.

Below that, starting with "Hrs: 16" is the summary for all of that
employee's displayed records.

The last three lines (Well, two for the rest) is the total and averages for
each area. The problem is that it's all based on total hours worked, which I
think is the part that you knew. So, I basically would like to add up
all the displayed records hours into each of the four catagories.

If this didn't make sense, I apologize in advance. If it does make sense,
then ... woo hoo?

Thanks!
Steph

Steph,

In that case (as far as I understand it!), two possible approaches would be:

- use an Area Footer section in the report, and put your totals
textbox in there, this will give the totals for each Area.

- use an expression like this in the report footer...
=Sum(IIf([Area]="palletizer",[Shift_ID1_Hours]+[Shift_ID2_Hours],0))

If this doesn't solve it, you are going to need to provide a lot more
detail, with examples, of your data and your report.

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

Thanks for responding. ^_^

That basically is what I already have there, but the problem is that sums
all the hours. What I need from Access is for it to keep that running sum of
hours, but to allow me to find out how many hours worked in each area. For
instance, if someone works at the palletizer, it will show how many hours he
worked there total. Because I'm using a total sum of hours displayed, over
however many employees are displayed, the results for each area are
inaccurate since the hours include Shipping, Receiving and Depaletizer hours
as well.

 

[Steve Schapel]

Steph,

Thanks for the additional information.

As regards the screen shot, which figures specifically are not correct?
And what should they be instead? And how do you calculate them? So
far I can't see what's wrong... sorry!

In any case, a screen shot of the report design would be more
informative than the preview.

Is this report based on a crosstab query? Can you also show the SQL
view of the query?

What does "prompts asking me what the total hours 1 and 2 should be"?
What were the exact parameters it was asking for? Where did these come
from?

No further questions, your honour. At this stage, anyway .

 

[Luna Saisho]

I feel like dancing! Although my foot is a bit messed up at the moment, so
I'll twirl around in my chair a bit, cheer, and smile a lot!

Your suggestion was so close to working that I cannot believe I figured out
what the issue was myself.

Here's what I ended up using:
=Sum(Abs([Area]="Shipping")*[TotalHours])

[TotalHours] is what my query is using for
[Shift_ID1_Hours]+[Shift_ID2_Hours] so I thought, ok... The source is my
query, it has no idea what the Hours fields were, so let's try this... and
.... And... AND... ! it worked!

A small victory for most, but the final victory that means THIS DATABASE IS
DONE! ^_^

Thank you, Steve! I appreciate your help so much! **Big Hug** Thank you!
Steph

Steph,

In that case (as far as I understand it!), two possible approaches would be:

- use an Area Footer section in the report, and put your totals
textbox in there, this will give the totals for each Area.

- use an expression like this in the report footer...
=Sum(IIf([Area]="palletizer",[Shift_ID1_Hours]+[Shift_ID2_Hours],0))

If this doesn't solve it, you are going to need to provide a lot more
detail, with examples, of your data and your report.

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

Thanks for responding. ^_^

That basically is what I already have there, but the problem is that sums
all the hours. What I need from Access is for it to keep that running sum of
hours, but to allow me to find out how many hours worked in each area. For
instance, if someone works at the palletizer, it will show how many hours he
worked there total. Because I'm using a total sum of hours displayed, over
however many employees are displayed, the results for each area are
inaccurate since the hours include Shipping, Receiving and Depaletizer hours
as well.

 

[Steve Schapel]

Congratulations, Steph. I am very happy to hear the good news.