 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / July 2008Comparison Report based on a query of only calculations.
Good morning, We have an access database that has the tables, forms and queries all completed. We have a small portion of the data entered to test things out. We need to create a report that is comparative by year and by number of cows. We would like to have the number of cows be in groups as listed below: Number of Cows: 0-100 100-500 500-800 800-1000 We are able to generate the report for each client correctly and in the layout and design consistant with other reports from our database. We are not sure how to set up a report that will compare Farm 1 with 500 cows to Farm 2 with 600 cows. If you need more information, please let me know. I am not sure what all is needed in order to get some guidence. Thank you, Amy  SignatureStill a Newbie, but learning :0) Use the report's sorting and grouping to group the herds by size. Create a calculated field in the report's record source query using the function below. Put the function in a standard module. In the query builder, it would look something like this: HerdSize: CowCount([NumberOfCows]) Then group by HerdSize in the report. Public Function CowCount(lngHowNow) As Long Select Case lngHowNow Case Is <= 100 CowCount = 1 Case Is <= 500 CowCount = 2 Case Is <= 800 CowCount = 3 Case Is <= 1000 CowCount = 4 Case Else 'Herds over 1000 CowCount = 5 End Select End Function SignatureDave Hargis, Microsoft Access MVP > Good morning, > [quoted text clipped - 20 lines] > Thank you, > Amy We will try to work on this tomorrow and let you know if we an get it to work as we need it to. Thank you, Amy and Jill SignatureStill a Newbie, but learning :0) > Use the report's sorting and grouping to group the herds by size. Create a > calculated field in the report's record source query using the function [quoted text clipped - 44 lines] > > Thank you, > > Amy Okay, post back if you have questions. One note, the code I posted does not include cows that have been tipped :) SignatureDave Hargis, Microsoft Access MVP > We will try to work on this tomorrow and let you know if we an get it to work > as we need it to. [quoted text clipped - 50 lines] > > > Thank you, > > > Amy I really tipped my cows now. Something is not working properly . . . I have the main form created from the Client table and then have a tab for each section of my financial statement: Sales, Livestock, Labor, Land, General, Other Income, Other FS Data, Non-FS Data, Notes and View Report Calculations. Each tab has a subform created and then placed on the tab. The first 8 and Notes are from the same table (FS Data). Non FS Data is a separate table. Report Calculations is created from a query of muliple calculations. Here is the SQL of the query: SELECT dbo_ajs_Ag_NFS_Data.ID, dbo_Clients.Cltnum, dbo_Clients.Cltname, dbo_ajs_Ag_NFS_Data.NFSID, dbo_ajs_Ag_NFS_Data.NFSYear, dbo_ajs_Ag_FS_Data.FSYear, dbo_Clients.Cltname, ([DryCowAvgYr]+[MilkCowAvgYr])/2 AS AvgNumberCows, (([CWTMilk]*100)/[MilkCowAvgYr])/365 AS Production, (([FStatementTotalsLessSales]+[SalesTotalLessMilk])/[CWTMilk])-[MilkPriceLessAvgClassIII] AS OverallCompetitivenessBreakEven, ([FStatementTotalsLessSalesLessIncome]-[SalesTotalLessMilk])/[CWTMilk] AS OverallCompetitivenessOperatingCostsLessIncome, ([Promotion]+[Hauling and Trucking])/[CWTMilk] AS MilkMarketingExpense, [LivestockandCrop]/[CWTMilk] AS CSPerCWTLivestockandCropExpense, [LaborTotal]/[CWTMilk] AS CSPerCWTLabor, [LandFacilitiesEquipmentTotal]/[CWTMilk] AS CSPerCWTLandFacilitiesEquip, [GeneralAdministrativeTotal]/[CWTMilk] AS CSPerCWTGeneralAdmin, [CSPerCWTLivestockandCropExpense]+[CSPerCWTLabor]+[CSPerCWTLandFacilitiesEquip]+[CSPerCWTGeneralAdmin] AS CSPerCWTTotal, [LivestockandCrop]/[AvgNumberCows] AS CSPerCowLivestockandCrop, [LaborTotal]/[AvgNumberCows] AS CSPerCowLabor, [LandFacilitiesEquipmentTotal]/[AvgNumberCows] AS CSPerCowLandFacilities, [GeneralAdministrativeTotal]/[AvgNumberCows] AS CSPerCowGeneralAdmin, [CSPerCowLivestockandCrop]+[CSPerCowLabor]+[CSPerCowLandFacilities]+[CSPerCowGeneralAdmin] AS CSPerCowTotal, [FeedMilkIncomeOver]/[AvgNumberCows] AS FeedMilkIncomeOverPerCow, [FeedExpenseLessMilk]/[AvgNumberCows] AS FeedExpensePerCow, [FeedExpenseLessMilk]/[CWTMilk] AS FeedExpensePerCWT, [Vet and Medicine]/[AvgNumberCows] AS HerdHealthIndicator, [Breeding]/[AvgNumberCows] AS ReproductionEfficiency, [AvgNumberCows]/[FTETotal] AS LaborCowsPerFTE, ([CWTMilk]*100)/[FTETotal] AS LaborNumberMilkPerFTE, [LaborLessOwnerSalariesBenefits]/[FTEEmployee] AS LaborTotalEEExpensePerFTEEmployee, [Owner Salaries and Benefits]/[FTEOwner] AS LaborOwnerSalariesBenefitsPerFTEOwner, [TotalLiabilities]/[AvgNumberCows] AS TotalLiabilitiesPerCow, [TotalLiabilities]/[CWTMilk] AS TotalLiabilitiesPerCWT, [InterestExpenseandTotalPrincipalRepayments]/[AvgNumberCows] AS DebtPaymentsPerCow, [InterestExpenseandTotalPrincipalRepayments]/[CWTMilk] AS DebtPaymentPerCWT, dbo_ajs_Ag_NFS_Data.AvgClassIIIMilkPrice, [qry:_ajs_Ag_NFS_Data_Totals].MilkPrice, [qry:_ajs_Ag_NFS_Data_Totals].MilkPriceLessAvgClassIII FROM (((dbo_ajs_Ag_NFS_Data INNER JOIN dbo_Clients ON dbo_ajs_Ag_NFS_Data.ID = dbo_Clients.ID) INNER JOIN dbo_ajs_Ag_FS_Data ON dbo_Clients.ID = dbo_ajs_Ag_FS_Data.ID) INNER JOIN [qry:_ajs_Ag_FS_Data_Totals] ON dbo_ajs_Ag_FS_Data.FSDataID = [qry:_ajs_Ag_FS_Data_Totals].FSDataID) INNER JOIN [qry:_ajs_Ag_NFS_Data_Totals] ON dbo_ajs_Ag_NFS_Data.NFSID = [qry:_ajs_Ag_NFS_Data_Totals].NFSID ORDER BY dbo_ajs_Ag_NFS_Data.NFSYear DESC; Ok, we enter the data in the subforms from our main form (Benchmark Project). I had our receptionist enter the data for the remaining clients (I had entered two clients as a test as I went along). I now have multple records on the FS Data table for one client instead of one record per client. The Non FS Data table is one record per client. I cannot figure out what the difference is between them. We are still confused on the comparative report. We would like to have the report to run vertical: Header info on the left margin top to bottom and then each client's info running top to bottom. Make sense? Basically, opposite of what a traditional report looks like. If there is anyway to send you an screen shot of my trouble issues, can you please let me know. It is hard to explain what our issues are. Thank you so much. I really, really appreciate your help with this! Amy SignatureStill a Newbie, but learning :0) > Okay, post back if you have questions. > One note, the code I posted does not include cows that have been tipped :) [quoted text clipped - 53 lines] > > > > Thank you, > > > > Amy With as many subforms as you have and not knowing the record source of each subform nor the relationships of the data, it would be impossible for me to troubleshoot your problem with multiple client records. As to seeing screen shots, that may or may not help, but would not be appropriate for the scope of these newsgroups. The concept here is to provide assistance and suggestions from which the entire community can benefit. So it would be not be correct for me to provide off line assistance. In addition, I, like you and many others here, make my living providing application and database solutions for my employer and other clients. If I were to provide this level of assistance, it would not be free of charge. As to your report question, you may consider a report/subreport format that would allow you to present your report in the format you want. I am sorry I can't be of more assitance. I hope you understand. I suggest you seek professional assistance if you can't work out the problem. SignatureDave Hargis, Microsoft Access MVP > I really tipped my cows now. Something is not working properly . . . > [quoted text clipped - 128 lines] > > > > > Thank you, > > > > > Amy Thank you. I will look for a local professional. I appreciate your candor. SignatureStill a Newbie, but learning :0) > With as many subforms as you have and not knowing the record source of each > subform nor the relationships of the data, it would be impossible for me to [quoted text clipped - 147 lines] > > > > > > Thank you, > > > > > > Amy Best of Luck, Amy. If you can tell me where you are located, I may know someone I can recommend. SignatureDave Hargis, Microsoft Access MVP > Thank you. I will look for a local professional. > [quoted text clipped - 151 lines] > > > > > > > Thank you, > > > > > > > Amy You are awesome. Thanks. Next step, I am trying to have data vertically be compared. That is ... all clients with less than 100 cows would appear on the same page. Now, they automatically go to a new page. Is there a way to have the data for each client in that filter to show up vertically? SignatureStill a Newbie, but learning :0) > Best of Luck, Amy. > If you can tell me where you are located, I may know someone I can recommend. [quoted text clipped - 154 lines] > > > > > > > > Thank you, > > > > > > > > Amy Sorry, Amy, I don't understand the question. SignatureDave Hargis, Microsoft Access MVP > You are awesome. Thanks. > [quoted text clipped - 164 lines] > > > > > > > > > Thank you, > > > > > > > > > Amy Dave, We have a data that looks like this: Client number 004576 Client Name Farm A Avg # of Cows 450 Production 10,000 Livestock 240,000 etc . . . We want the report to compare the all farm with an avg # of cows in the range of 100-500. Once that range is established, we want the data to appear as follows: Client number 004576 005515 006613 Client Name Farm A Farm B Farm C Avg # of Cows 450 200 300 Production 25,000 10,000 18,000 Livestock 240,000 85,000 100,000 etc . . . We got the report to work so that it sorts by Avg # of Cows and all the data is vertical on page as we would like but we cannot figure out how to get the other farms to appear on the same page. Does that make better sense? Thanks so much. By the way, we are location in Lancaster Pa. SignatureStill a Newbie, but learning :0) > Sorry, Amy, I don't understand the question. > [quoted text clipped - 166 lines] > > > > > > > > > > Thank you, > > > > > > > > > > Amy Okay, I understand. You might try experimenting with using columns in your report. Without the data in front of me to tinker with, it is hard to give an exact answer. SignatureDave Hargis, Microsoft Access MVP > Dave, > [quoted text clipped - 196 lines] > > > > > > > > > > > Thank you, > > > > > > > > > > > Amy I found the column set area. The issue I have is that the Help and How To is for a horizontal report. We need a vertical report. I cannot get the right formatting in place to accomodate a vertical report. Have you ever tried a vertical report in access? SignatureStill a Newbie, but learning :0) > Okay, I understand. > You might try experimenting with using columns in your report. [quoted text clipped - 201 lines] > > > > > > > > > > > > Thank you, > > > > > > > > > > > > Amy Only by using a cross tab query, but I don't know if that will work with the data you are using. I pretty much doubt it. Let me be sure I understand exactly, so I can see if I can get some additional help for you on this. The rows in the reports recordset are like this: Client number Client Name Avg # of Cows Production Livestock 004576 Farm A 450 25,000 240,000 005515 Farm B 200 10,000 85,000 006613 Farm C 300 18,000 100,000 And you want them to appear on the report like: Client number 004576 005515 006613 Client Name Farm A Farm B Farm C Avg # of Cows 450 200 300 Production 25,000 10,000 18,000 Livestock 240,000 85,000 100,000 Let me know, and I will see if I can find an answer for you SignatureDave Hargis, Microsoft Access MVP > I found the column set area. The issue I have is that the Help and How To is > for a horizontal report. We need a vertical report. I cannot get the right [quoted text clipped - 207 lines] > > > > > > > > > > > > > Thank you, > > > > > > > > > > > > > Amy You got it! Thanks for all your help with this. Jill and I really appreciate it. I will try the cross tab query suggestion in the meantime. Amy SignatureStill a Newbie, but learning :0) > Only by using a cross tab query, but I don't know if that will work with the > data you are using. I pretty much doubt it. [quoted text clipped - 225 lines] > > > > > > > > > > > > > > > > > > > > > > > > > > > > If you need more information, please let me know. I am not sure what all is Okay, I will consult with some others and see what I can come up with. SignatureDave Hargis, Microsoft Access MVP > You got it! Thanks for all your help with this. Jill and I really > appreciate it. [quoted text clipped - 220 lines] > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Number of Cows: Amy, Looking at it again, (my brain was wrapped up in a report I am working on at the moment), I think the column thing will work. If you can sort your recordset so that it comes out in the order you need, then from the menu while in report design, File, Page Setup, select the columns tab Set the number of columns you want. The row and spacing you can experiment with. You can also experiment with the width and height so it looks like you want it, and choose Across then down. So if that does what you want. SignatureDave Hargis, Microsoft Access MVP > Okay, I will consult with some others and see what I can come up with. > [quoted text clipped - 215 lines] > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Good morning, Dave, We will try that and let you know. SignatureThanks, Amy Still a Newbie, but learning :0) > Amy, > Looking at it again, (my brain was wrapped up in a report I am working on at [quoted text clipped - 215 lines] > > > > > > > > > > > > > > > > Case Is <= 100 > > > > > > > > > > > > > > > > CowCount = 1 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.