Subform Totals

[Brian]

May be what I am trying to do isn't possible with Access, but if there is a
solution then I'd be grateful for a few pointers.

My database keeps track of the hours worked by employees on a particular job.
The main form [ORDERS] has a subform [Labour Costings] (based on a query) I
enter the employee's "NAME" (from a combo) and the "HOURS" worked on the sub.

In the subform footer I can Sum(Hours) and get a total hours figure for that
job and in the main form but what I have been trying to do (and failed) is to
dispaly in the subform a separate or breakdown total for each "NAME".

I thought I'd cracked it with DSum but the result was a total for each
"NAME" for the entire DB not a particular job.

So far I have only tried writing IIF expressions in the Control Source of a
Text Box with only #Error or #Name? responses.

 

[Keith Wilby]

I thought I'd cracked it with DSum but the result was a total for each
"NAME" for the entire DB not a particular job.

I think DSum should do it with a bit of extra criteria. Care to post the
code you tried?

Keith.
www.keithwilby.co.uk

 

[Brian]

Hi Keith
Thanks for trying to Help.
As we only have 4 employees [Names], I created a TextBox for each in the
form footer with the following Control Source Expression

=DSum("[Hours]","[qyLabourCharges]","[Name] = 'Chris'")

Initialy I thought the reference should be to [qyLabourCharges subform] but
that failed to work so it seems to require the query itself not the subform.
Then I realised that is was totalling "Chris" for all jobs in the DB.

Perhaps it needs something in the query?

If we had more employees I would probably use a different approach entirely
but I can get this to work it will save a lot or re-writing.

Thanks again.

 

[Keith Wilby]

Hi Keith
Thanks for trying to Help.
As we only have 4 employees [Names], I created a TextBox for each in the
form footer with the following Control Source Expression

=DSum("[Hours]","[qyLabourCharges]","[Name] = 'Chris'")

Initialy I thought the reference should be to [qyLabourCharges subform]
but
that failed to work so it seems to require the query itself not the
subform.
Then I realised that is was totalling "Chris" for all jobs in the DB.

Perhaps it needs something in the query?

Hi Brian.

I think you just need another clause in your filter criteria to refer to the
job ID number. I assume there's a text box on your sub-form bound to your
Job ID number, so try something like this:

=DSum("[Hours]","[qyLabourCharges]","[Name] = 'Chris' And [JobID] = " &
Me.txtJobID)

where txtJobID is the name of your text box.

Keith.
www.keithwilby.co.uk

 

[Brian]

Well done! Keith, initially I couldn't get the expression to work but I took
out the [Me.] clause and Hey Presto I go t the results I required.

So problem solved thanks to you.