Page 3 of 3 FirstFirst 123
Results 31 to 44 of 44
  1. #31
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    I guess if I can't figure it out, I will drop the report into Excel and fix it there.

  2. #32
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by ROCKPICKER View Post
    Are you saying to send the whole program?
    Yes - without the sensitive data .
    Quote Originally Posted by ROCKPICKER View Post
    How do people clean up the sensitive data and still have things work?
    Deleting/changing the DATA shouldn't affect how the dB works.
    1) Edit the sensitive data -change names, addresses, phone numbers, etc.
    2) Make a COPY of the dB. Delete all the records and create a few examples using cartoon names/addresses. Just need enough to see how forms/reports work.


    Quote Originally Posted by ROCKPICKER View Post
    I am too embarrassed to upload the whole mess.
    We've all been there. No one was born a perfect developer. I shudder when I have to look at my first databases.

    Quote Originally Posted by ROCKPICKER View Post
    Doesn't my report and join query that I posted help?
    Nope. Could you tell me how to fix my computer by looking at the hard drive?

  3. #33
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    Quote Originally Posted by June7 View Post
    Provide db per instructions at bottom of my post or click the button at top for more info.

    Here we go...hopefully this works. The report I am working with is called rptJoinAddOnsAndBlockSaleByMembers.

    I am now trying to figure out how to do the following: If the the buyer's first name is Resale, I need to subtract that Resale Buyer's Total Amount from the AddOn Sum Total amount and multiple that amount by 3%.

    Thank you for your help! And if anyone looks at the code and thinks What The ?....sorry.
    Attached Files Attached Files

  4. #34
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I just re-read "When the species is "beef", I need to edit the SumTotalDue." and this triggered something for me.

    What exactly do you want to do differently when species is Beef? You say you need to edit SumTotalDue but exactly how? What formula needs to be coded? How should Beef records be treated differently from other species? Should they be excluded from the Sum or do they need a premium added to value?

    =Sum([TotalDue] + IIf(Species = "Beef", some value, 0))

    Not seeing any conditional calcs for Beef in report.

    So now if buyer's name is Resale, do something different. You say subtract that amount - do you mean exclude it? Then you want to multiply by 3%. Do you mean you want to include the value but apply a different percentage calculation?

    =Sum([TotalDue] * IIf([Buyer]="Resale", 0.03, 0.02))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #35
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    Quote Originally Posted by June7 View Post
    I just re-read "When the species is "beef", I need to edit the SumTotalDue." and this triggered something for me.

    What exactly do you want to do differently when species is Beef? You say you need to edit SumTotalDue but exactly how? What formula needs to be coded? How should Beef records be treated differently from other species? Should they be excluded from the Sum or do they need a premium added to value?

    =Sum([TotalDue] + IIf(Species = "Beef", some value, 0))

    Not seeing any conditional calcs for Beef in report.

    So now if buyer's name is Resale, do something different. You say subtract that amount - do you mean exclude it? Then you want to multiply by 3%. Do you mean you want to include the value but apply a different percentage calculation?

    =Sum([TotalDue] * IIf([Buyer]="Resale", 0.03, 0.02))
    Originally I was looking at if Species = Beef and Disposition = Resale....and then, if the BuyerFirstName="Resale" I want to subtract that Resale Buyer's Total Due amount from the Sum of all the Add On's and then multiply the Sum of the Add On's - the Resale Buyers total amount by 3% for a true Add On Fee ( the Resale Buyer amount is not really an Add On....it is to take care of beef carcass grade and hanging weight differences from the original floor price amount. If it is a Beef/Resale, we take the Gross amount less the Floor Price amount...which is the amount the buyer pays but then we do an Add On to reflect the carcass grade and hanging weight and the floor price. The new user of this program wants to calculate a 3% fee on the Add On true amount (less any Resale Buyer amounts).

    Hope that makes sense.
    Last edited by ROCKPICKER; 10-30-2019 at 10:02 PM.

  6. #36
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want Sum of TotalDue where RecordType="AddOn" and not BuyerFirstName="Resale" multiply that by 3% then add to Sum of TotalDue where RecordType="AddOn" and not BuyerFirstName="Resale"?

    Something like:

    =Sum(IIf([RecordType]="AddOn" And [BuyerFirstName]<>"Resale", [TotalDue])) * 0.03 + Sum(IIf([RecordType]="AddOn" And [BuyerFirstName]<>"Resale", [TotalDue]))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #37
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    Quote Originally Posted by June7 View Post
    You want Sum of TotalDue where RecordType="AddOn" and not BuyerFirstName="Resale" multiply that by 3% then add to Sum of TotalDue where RecordType="AddOn" and not BuyerFirstName="Resale"?

    Something like:

    =Sum(IIf([RecordType]="AddOn" And [BuyerFirstName]<>"Resale", [TotalDue])) * 0.03 + Sum(IIf([RecordType]="AddOn" And [BuyerFirstName]<>"Resale", [TotalDue]))

    Just the =Sum(IIf([RecordType]="AddOn" and [BuyerFirstName]<>"Resale", [TotalDue])) * 0.03)

    Because only the Beef/Resale records will have a resale buyer add on amount that needs to be removed before calculating the 3% Add On fee.

    And I need it in the summary where the fee's are.

  8. #38
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you try it?

    That would be the FirstName footer section?

    Don't see any records with "Resale" as BuyerFirstName value. BuyerFirstName is often empty (Null). The following will produce different output.

    =Sum(IIf([RecordType]="AddOn" and Nz([BuyerFirstName],"")<>"Resale", [TotalDue])) * 0.03
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #39
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    Quote Originally Posted by June7 View Post
    Did you try it?

    That would be the FirstName footer section?

    Don't see any records with "Resale" as BuyerFirstName value. BuyerFirstName is often empty (Null). The following will produce different output.

    =Sum(IIf([RecordType]="AddOn" and Nz([BuyerFirstName],"")<>"Resale", [TotalDue])) * 0.03
    I was able to get this equation to work and display in the BuyerLastName footer (I changed the Resale Buyer to have Resale in the Last Name vs the First Name), but I can't seem to get a Sum of these amounts in the Summary. The equation is: =(IIf([RecordType]="AddOn" And [Species]="Beef" And [BuyerLastName] Not Like "*Resale*",[TotalDue])*0.03) and it displays the TotalDue * 3% amount next to every Beef AddOn line that isn't a Resale Buyer. I titled this box AddOnTotalDueLessResaleBuyerAmt. But how do I get a summary at the bottom?

    In the report, page 159, pg 167, and pg 168 have Resale Beef with a Resale Buyer AddOn amount that needs to be subtracted out of the AddOn Sum amount. The member on page 168 actually has two Resale Buyer amounts that need to be subtracted.

    I know we are close...but it's the summary per member that I need.

  10. #40
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, one record with "Resale Buyer" as value in BuyerFirstName field.

    Yes, can do NOT LIKE "*Resale*". However, records with Null are ignored and not considered in calc, so again may need Nz().

    Expression worked for me. What do you mean by "couldn't get it to work" - error message, wrong result, nothing happens?

    The expression is in a group footer. If you want a grand total summary in report footer, would have to repeat expression in that section.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #41
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    Quote Originally Posted by June7 View Post
    Okay, one record with "Resale Buyer" as value in BuyerFirstName field.

    Yes, can do NOT LIKE "*Resale*". However, records with Null are ignored and not considered in calc, so again may need Nz().

    Expression worked for me. What do you mean by "couldn't get it to work" - error message, wrong result, nothing happens?


    The expression is in a group footer. If you want a grand total summary in report footer, would have to repeat expression in that section.
    Ok...that worked! Wow! Thank you so much for your help and persistence! Thank you for sticking with me. We got it done before 11/1....Hooray!

  12. #42
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    Quote Originally Posted by ROCKPICKER View Post
    Ok...that worked! Wow! Thank you so much for your help and persistence! Thank you for sticking with me. We got it done before 11/1....Hooray!
    Since you have been looking at this report and helping me, I wondered if you could tell me the best way to display the fee titles. I have them "hardcoded" in....is there a way to do a lookup table or ?

    Thank you for your help...again.

  13. #43
    ROCKPICKER is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2019
    Posts
    21
    June7...since you have been looking at this report, can you tell me the best way to display the fee titles in the summary? I have them "hardcoded" in...is there a way to do a look up table or?

    Also, when looking at that report, is there a much better way of getting the data needed for it? The Join Query seems fairly "messy"...

    Thank you for your help in getting the first solution to my problem...I really appreciate it and I appreciate this site! And thank you if you have ideas on these questions.

  14. #44
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    As for showing fee titles in report - by 'hard coding' do you mean label captions? If so, then you have fee titles displayed.

    Multiple similar name fields as in
    tblMemberFees indicates non-normalized data structure. Instead of field names like MASFee1 why not SaleFee? Otherwise, normalize tblMemberFees so it can be joined to tblMASFees. However, if you do normalize then to show summary of each fee would require conditional expressions.

    I guess the query looks 'messy' because it is a UNION. I have not analyzed data structure enough to determine why you needed this solution but resorting to UNION is indication of non-normalized data structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. The problem with the function sum()
    By end in forum Access
    Replies: 2
    Last Post: 01-31-2016, 03:41 PM
  2. Error Checking in a text box
    By Paul H in forum Forms
    Replies: 2
    Last Post: 07-23-2015, 01:02 PM
  3. Error 0 problem with error checking
    By SemiAuto40 in forum Programming
    Replies: 4
    Last Post: 12-07-2011, 12:21 PM
  4. Problem with IIF function
    By Hulk in forum Forms
    Replies: 3
    Last Post: 03-20-2011, 12:59 PM
  5. Problem checking for Null Value of a Textbox.
    By SIGMA248 in forum Programming
    Replies: 3
    Last Post: 08-09-2010, 08:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums