If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Subform Totals
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. |
#2
|
|||
|
|||
Subform Totals
"Brian" wrote in message
... 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 |
#3
|
|||
|
|||
Subform Totals
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" wrote: "Brian" wrote in message ... 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 . |
#4
|
|||
|
|||
Subform Totals
"Brian" wrote in message
... 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 |
#5
|
|||
|
|||
Subform Totals
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. "Keith Wilby" wrote: "Brian" wrote in message ... 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 . |
Thread Tools | |
Display Modes | |
|
|