I guess if I can't figure it out, I will drop the report into Excel and fix it there.
I guess if I can't figure it out, I will drop the report into Excel and fix it there.
Yes - without the sensitive data .
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.
We've all been there. No one was born a perfect developer. I shudder when I have to look at my first databases.
Nope. Could you tell me how to fix my computer by looking at the hard drive?
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.
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.
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).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))
Hope that makes sense.
Last edited by ROCKPICKER; 10-30-2019 at 10:02 PM.
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.
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.
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.
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?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
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.
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.
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!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.
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.
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.
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.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
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.