Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Driving Me Crazy (A2000 SR1)

    Dave

    Try creating two queries, one that pulls out the "f"s and another that pulls out the "b"s, and then do a dsum on each of them, using a single where condition
    where EstimateNo&Supp Match the EstimateNo&Supp from this form.
    Regards
    John



  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving Me Crazy (A2000 SR1)

    Hmmmmmmm

    I see what you say but really wanted to use the 1 query I already have.
    All the info I need is there already.

    Its the syntax I can't work out.
    e.g;
    =DSum ("New","&","R-R","qryAddBodyAndFit","Where ID= 'F'" "and EstimateNo & Supp" = Forms!frmEstimateDetails!EstimateNo&Supp" ??????????????????????????????????????

    Thanks
    Dave

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving Me Crazy (A2000 SR1)

    you could try 2 Dsums, one for "New" and the other for "R-R" in hidden fields then add them together to show the total.

    As an alternative to Dsums you could use totals in the footer of the subform and link to them

    =Sum(IIf([ID]="F", <img src=/S/new.gif border=0 alt=new width=35 height=15>,0))

    HTH

    Peter

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving Me Crazy (A2000 SR1)


  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Driving Me Crazy (A2000 SR1)

    HINT: To display "New" in brackets without causing the annoying New! icon to appear in place of text use the "TAG" tag from the Markup Tag panel. To do this type:

    <!t>[t]<!/t>New<!t>[/t]<!/t>

    When displayed, this will place brackets around text:

    <!t>[New]<!/t>

    Also "New" is a VBA reserved word and as such may not be good choice for name of field.

    HTH

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving Me Crazy (A2000 SR1)

    The following is now adding the fields<!t>[New]<!/t>&[R-R]

    =DSum("<!t>[new]<!/t>+[R-R]","qryAddFitAndBody","EstimateNo=" & [EstimateNo])

    I just need to add in
    "Supp=" & [Supp]
    &
    where [UnitId]="F" to solve my prob

    I'm stuck here now.

    Regards Dave

    p.s Thanks mark for the <img src=/S/new.gif border=0 alt=new width=35 height=15> tip

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Driving Me Crazy (A2000 SR1)

    Try something like this
    =DSum(" <!t>[new<!t>[+[R-R]","qryAddFitAndBody","[EstimateNo]=" & [EstimateNo] & " AND [Supp]=" & [Supp] & " AND [UnitId]='F' " )

    I would create separate queries for each job because then you push a lot of the syntax work back to the query, and I find it easier to get the syntax right there. You can use the expression builder for example.

    You could push all the work back to the query , so that the query did the summing, and selecting. On the form all that would be needed would be a lookup
    = dlookup("[Sumofsomething]","qrysumNewandrr")
    Regards
    John



  8. #8
    Lounger
    Join Date
    Nov 2001
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving Me Crazy (A2000 SR1)

    =Sum(Iif([ID]="F", <img src=/S/new.gif border=0 alt=new width=35 height=15>+[R-R],0)) as the control source on your form

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving Me Crazy (A2000 SR1)

    Thats it , I'm at the end of my tether now.
    I've attached a demo of my problem if some-one can help me out.

    The 4 fields on the form to the left work ok, Summing each column correctly.

    The 3 fields to the right is where I am struggling.

    I need to do the following:-

    Add New & R-R if UnitId = F
    Add New & REP if UnitId = B

    Relating to the current EstimateNo & Supp.

    Thanks all for participating.

    Dave
    Attached Files Attached Files

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Driving Me Crazy (A2000 SR1)

    Out of curiosity took look at sample DB. In sample there was no qryAddFitAndBody to be found, just 3 tables & 2 forms. One reason it may be difficult to create expression based on subform is that the UnitID field on subform is a lookup field from tblEstimateItems ItemID field, based on Item combo box. In any event to me simplest solution was to create new query (an noted found no existing ones) - only one is required. For sake of simplicity in attached file it's named qry1. The SQL looks like this:

    SELECT tblEstimateDetails.EstimateNo, tblEstimateDetails.Supp, tblEstimateDetails.Item, tblEstimateDetails.New, tblEstimateDetails.[R-R], tblEstimateDetails.Rep, tblEstimateDetails.Paint, tblEstimateItems.ItemId
    FROM tblEstimateDetails INNER JOIN tblEstimateItems ON tblEstimateDetails.Item = tblEstimateItems.EstimateItems
    WHERE (((tblEstimateDetails.EstimateNo)=[Forms]![frmEstimateDetails]![EstimateNo]) AND ((tblEstimateDetails.Supp)=[Forms]![frmEstimateDetails]![Supp]));

    Then on form, for Body testbox use following expression:
    <pre>=DSum("<!t>[New]<!/t>+[Rep]","qry1","[ItemId]='B'")</pre>

    For Fit textbox use:
    <pre>=DSum("<!t>[New]<!/t>+[R-R]","qry1","[ItemId]='F'")</pre>


    Also, need to add following code to subform's After Update event to force the DSum textboxes on main form to recalculate totals after subform is updated:
    <pre>Private Sub Form_AfterUpdate()
    Me.Parent.Form.Recalc
    End Sub</pre>

    The DSums are typically kind of slow, visibly so when recalculating. Opening a recordset in code using same query as the DSum to populate the textboxes may be quicker. Hopefully the attached revised DB file will give you some ideas.

    HTH
    Attached Files Attached Files

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving Me Crazy (A2000 SR1)

    Mark

    Thanks for the example, it's so close.

    When the form recalcs, I see what you mean.
    I had omitted the query for this reason as in my demo.

    I really wanted to reference straight from the subform. I hadn't realised the lookup was causing me so many problems.
    Also with the queries, Their is a periodic problem.

    If Lets say only one item in the sub ie.
    FB|Front Bumper|1.00|1.00|1.00|1.00|

    See what happens, the [rep] calculation is ommitted because its a "F" , I should really be able to calc repair also.

    Thanks for helping

    Regards Dave

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Driving Me Crazy (A2000 SR1)

    Dave,
    Is the attached any help - note: I haven't tested it much and I did alter one of your tables slightly but that's just my preference, you can change it back!
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving Me Crazy (A2000 SR1)

    Hi Rory

    I can't see what you've alterred, can you explain a bit.

    I can't see where you've got =Sum([body]) or Sum([fit]) from although it's working.
    What would really stamp it down is some-kind of block in the subform.

    If an item is NEW, prevent the user from entering an item into REP.

    Basically, if the user is replacing an item as in NEW, he doesn't need to Repair REP it.

    Regards Dave





    dave

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Driving Me Crazy (A2000 SR1)

    Dave,
    I amended the data source for your subform from a table to a query which calculates Fit and Body. There are 2 textboxes in the form footer that sum these fields.
    All I changed in your table was the storing of the estimateItem Code rather than its description in the EstimateDetails table.
    I would suggest code in the BeforeUpdate event of the New and Rep controls that cancels the update if appropriate (see attached).
    Hope that helps.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Driving Me Crazy (A2000 SR1)

    I'm away tomorrow on some business I have to sort out for our company.

    I wish to thank all who have helped me on this problem. It isn't quite right yet but I will be thinking about it tomorrow also.
    The problem is so easy when on paper in front of me but I am getting the feeling it's impossible to automate.
    For any one who wants to ponder on this a while longer, I'll try to explain the objective on why and how it should work.
    Perhaps some-one will see a different approach I should take.
    Here Goes.

    tblEstimateItems contains many parts.
    This table is used as a lookup because of the many hundreds of different types.

    tblEstimateDetails is where the user selects these parts and designates a length of time to complete the operation.
    There are 4 operations: New,R-R,Rep,Paint.
    New
    Remove & Refit
    Repair
    Paint.

    The paint operation is straight forward and only needs summing. Not a problem.

    Here's where the fun starts.

    An item can be replaced (new) and dependant on what the part is, the operation can be Body or Fit.

    If a new bumper is fitted, this will be a Fit operation because a Fitter will Fit it.
    If a Roof is fitted, this will require welding, although it's a (new) item, a Bodyman will fit it.
    ie I designed my tblEstimateItems table to donate a "F" or a "B". This I thought would be easy to query for adding the totals.

    This was working ok But !!!!!!!

    If an item needed repairing which was a body item, the R-R part was not adding to the totals because the parts prefix was "B".
    had it have been "F", it would have been ok and totalled up.

    So This is where the problem lies, should I be using the prefix's in the first place.

    Does any one know a different way of doing this. ??????

    I could have the user split the hrs into whatever catagory he wanted but really wanted to automate instead.

    Dave

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •