This is not an aggregate calc and really doesn't belong in Report footer. It calculates based on last record loaded. Try moving into Detail section.
However, the other textbox is apparently showing value from first record loaded.
I have tested with my report and observe these behaviors.
Editing report RecordSource query should be just as simple as calc in textbox.
In my detail, the first line has species listed and the other lines do not. In the summary, I need to calculate totals differently if the species = beef....but I can't seem to get it to recognize the species in the summary, even though I display it right above my IIf statement and it displays the correct species. I think you might be correct that the IIF statement is looking at the last detail record which has no species listed and the textbox is showing the first record loaded which shows the species. I don't know how to move it into the detail when I am needing to calculate summaries of all the detail lines. Also, can you give me an example of how to do the last thing you said..."Editing report RecordSource query should be just as simple as calc in textbox". Thank you!
Both are basic Access functionality.
You can click, grab, drag textbox to Detail section or delete and create a new one.
As for calculating fields in query, review https://www.fontstuff.com/access/acctut02.htm
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 created a small table named Species and added the a Text field "Species". I then entered "Beef", "Pork", "Chicken" And "Cat" in the Species field.
I then created a query Called Check Result. I added the Species field in the first field of the query.
To the right of the Species field, I then typed CheckResult:IIf([Species]="Beef","True","False")
The Is The SQL View of the query.
SELECT [Species], IIf([Species]="Beef","True","False") AS CheckResult
FROM Species;
When I ran the query It Returned True in the CheckResult field for Beef and False for the "Pork", "Chicken" And "Cat" data.
Hope this helps you solve your problem.
Best Wishes,
Richard
Last edited by rapsr59; 10-22-2019 at 10:54 PM. Reason: Forgot the SQL View
[QUOTE=June7;441320]This is not an aggregate calc and really doesn't belong in Report footer. It calculates based on last record loaded. Try moving into Detail section.
However, the other textbox is apparently showing value from first record loaded.
I have tested with my report and observe these behaviors.
Editing report RecordSource query should be just as simple as calc in textbox.[/QUOTE}
In my detail, the first line has the species in it and the other lines do not so I think you are right in saying that the footer calculates on the last record loaded, which does not have species in it and that the text box in the footer is showing the first record loaded, which does have the species. I don't know how to move what I am trying to do into the detail because I am summarizing amounts on all of the detail but need to do a different calculation if the species = beef.
Also can you explain or show an example of "Editing report RecordSource query should be as simple as calc in textbox". I don't know what you mean.
Thank you!
I created a small table named Species that contains one Field. Species that is a Text Field.
I entered "Beef", "Pork", "Chicken" And "Cat" in the Species field. I Then ran the query Called "CheckResult"
The Below Is The SQL View if the query.
SELECT [Species], IIf([Species]="Beef","True","False") AS CheckResult
FROM Species;
The results was True for the Beef and False for the "Pork", "Chicken" And "Cat" data.
Hope this help you resolve you problem.
Best Wishes,
Richard
Results will be "True" or "False", not True or False!
The real vales for True and False will be -1 and 0.
RockPicker
I Changed the query to True and False as you will note below.
SELECT [Species].[Species], IIf([Species]="Beef",True,False) AS CheckResult
FROM Species;
The result was that showed -1 for the Beef, which is True, and 0 which is False for the Pork. Chicken and Cat.
Note:
Microsoft Access uses the values of -1 For True and 0 for False .
Microsoft Access also uses the values of Yes for True and No for False.
Microsoft Access also uses the values of On for True and Off For False.
I hope this helps to clarify the use of a Yes/No Data Type in an Access Table.
Good Luck
Richard
I know that this should be an easy fix but....this is a join query where I am joining unlike records together to get a total amount so the detail looks somewhat like:
MemberID Species Weight GrossAmt FloorPrice TotalDue.......................................(Bl ockSaleDetail - There is one of these records per member)
MemberID Null.......Null.....Null.........Null.........Tota lDue.......................................(AddOnD etail - There are zero to multiple records of this type per member)
MemberID Null.......Null.....Null.........Null.........Tota lDue
etc.
The Summary is
MemberID SumTotalDue
When the species is "beef", I need to edit the SumTotalDue. Even though i display the species in the footer and it says "beef", I believe it is really set to "Null" in the IIf statement because it says "False". I believe that June7 had it right saying that the textbox showing the species is looking at the first record in the detail but the IIf statement is looking at the last record in the detail that has a Null.
I need the detail to show in the order that is shown. And I don't know how to get the species from the detail to use it to make calculations in the footer.
Hope that makes sense and that someone can help.
In Post #14, Paul asked about you posting the dB.
It would really be a help if you could attach the dB here....
I don't know about others, but I an still having a hard time visualizing your dB structure and report.
If you want to calculate a total based on species, why not use report Sorting & Grouping with aggregate calcs?
Otherwise, do conditional aggregate calc in subreport footer section, like:
=Sum(IIf(Species="Beef", [fieldname], Null))
Really, time to provide db for analysis because I am ready to give up.
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.
If you want to calculate a total based on species, why not use report Sorting & Grouping with aggregate calcs?
Otherwise, do conditional aggregate calc in subreport footer section, like:
=Sum(IIf(Species="Beef", [fieldname], Null))
Really, time to provide db for analysis because I am ready to give up.
How do I provide the db for analysis? I was going to copy the Join Query...which I could do, and then a copy of the report...which I couldn't seem to do...sigh
SELECT tblDonationDetail.MemberID,
tblDonationDetail.BuyerID,
tblBuyers.BuyerLastName, tblBuyers.BuyerFirstName, tblBuyers.BuyerAddress, tblBuyers.BuyerCity, tblBuyers.BuyerStateOrProvince, tblBuyers.BuyerPostalCode, tblBuyers.BuyerPhoneNumber,
tblDonationDetail.BidAmount,
[BidAmount]*CInt([Weight]) AS GrossAmt,
[FloorPrice]*CInt([Weight]) AS FloorPriceAmt,
IIf(([Disposition]="Resale" And [Species]="Beef") Or ([Disposition]="Resale" And [Species]="Swine") Or [Disposition]="Home/No Resale",[GrossAmt]-[FloorPriceAmt],[GrossAmt]) AS TotalDue,
tblDonationDetail.Disposition,
tblDonationDetail.CustomSlaughter,
tblFinalSaleSheet.ID,
tblFinalSaleSheet.Species,
tblFinalSaleSheet.Order,
tblFinalSaleSheet.TagNumber,
tblFinalSaleSheet.FirstName, tblFinalSaleSheet.LastName, tblFinalSaleSheet.City,
tblFinalSaleSheet.Weight,
tblFinalSaleSheet.FloorPrice,
tblMemberFees.MASFee1,
tblMemberFees.MASFee2,
tblMemberFees.MASFee3,
tblMemberFees.MASFee4,
tblMemberFees.MASFee5,
tblMemberFees.MASFee6,
tblMemberFees.MASFee7,
tblMemberFees.MASFee8,
tblMemberFees.MASFee9,
tblMemberFees.MASFee10
FROM (tblFinalSaleSheet LEFT JOIN tblMemberFees ON tblFinalSaleSheet.ID = tblMemberFees.MemberID) INNER JOIN ((tblBuyers LEFT JOIN qryBlockSalePymtSum ON tblBuyers.BuyerID = qryBlockSalePymtSum.BuyerID) INNER JOIN tblDonationDetail ON tblBuyers.BuyerID = tblDonationDetail.BuyerID) ON tblFinalSaleSheet.ID = tblDonationDetail.MemberID;
UNION ALL SELECT [Members And Buyers].MemberID,
[Members And Buyers].BuyerID,
tblBuyers.BuyerLastName, tblBuyers.BuyerFirstName, tblBuyers.BuyerAddress, tblBuyers.BuyerCity, tblBuyers.BuyerStateOrProvince, tblBuyers.BuyerPostalCode, tblBuyers.BuyerPhoneNumber,
Null,
Null,
Null,
[Members And Buyers].DonationAmt AS TotalDue,
Null,
Null,
Null,
Null,
Null,
Null,
tblMembers.FirstName, tblMembers.LastName, tblMembers.City,
Null,
Null,
tblMemberFees.MASFee1,
tblMemberFees.MASFee2,
tblMemberFees.MASFee3,
tblMemberFees.MASFee4,
tblMemberFees.MASFee5,
tblMemberFees.MASFee6,
tblMemberFees.MASFee7,
tblMemberFees.MASFee8,
tblMemberFees.MASFee9,
tblMemberFees.MASFee10
FROM ((tblBuyers LEFT JOIN qryAddOnPymtSum ON tblBuyers.BuyerID = qryAddOnPymtSum.BuyerID) INNER JOIN ([Members And Buyers] INNER JOIN tblMembers ON [Members And Buyers].MemberID = tblMembers.MemberID) ON tblBuyers.BuyerID = [Members And Buyers].BuyerID) INNER JOIN tblMemberFees ON [Members And Buyers].MemberID = tblMemberFees.MemberID;
Provide db per instructions at bottom of my post or click the button at top for more info.
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.
Are you saying to send the whole program? How do people clean up the sensitive data and still have things work? Also, it is a mess. I wrote it years ago because I was tired of doing it all by hand and kind of piece mealed it together and always meant to go back and clean it up. I am too embarrassed to upload the whole mess. Doesn't my report and join query that I posted help?
I have joined unlike type records together in the report based on the member key...1 Block Sale record per member with multiple Add On records per member (see report). I just need to know in the summary, if the Block Sale record said Beef and Resale and I also need to know if one of the multiple Add On records per member had a "Resale" buyer (Buyer First Name = "Resale). I tried coding it in the Join Query....which seems to work for the BeefResale question, which I assume is because there is only one record for the Block Sale. I can display that it is a Resale Buyer in the detail but it doesn't show up in the summary. I need to know it in the summary to do calculations differently.
Is there some way to set a field to True and keep it True if it was True ONCE in the detail to show it in the summary? If there is a ResaleBuyer as one of the multiple Add On Records for a member, I would like to set ResaleBuyer = "True" and keep it true just for that member's summary.
Please don't give up on me....I'm hoping you can use my report and JoinQuery instead of the "whole big crappy mess".....
Thank you for your help.
I guess if I can't figure it out, I will drop the report into Excel and fix it there.