If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Comparison 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 -- Still a Newbie, but learning :0) |
#2
|
|||
|
|||
Comparison Report based on a query of only calculations.
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 -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) |
#3
|
|||
|
|||
Comparison Report based on a query of only calculations.
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 -- Still a Newbie, but learning :0) "Klatuu" wrote: 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 -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) |
#4
|
|||
|
|||
Comparison Report based on a query of only calculations.
Okay, post back if you have questions.
One note, the code I posted does not include cows that have been tipped -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) "Klatuu" wrote: 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 -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) |
#5
|
|||
|
|||
Comparison Report based on a query of only calculations.
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 -- Still a Newbie, but learning :0) "Klatuu" wrote: Okay, post back if you have questions. One note, the code I posted does not include cows that have been tipped -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) "Klatuu" wrote: 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 -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) |
#6
|
|||
|
|||
Comparison Report based on a query of only calculations.
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. -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) "Klatuu" wrote: Okay, post back if you have questions. One note, the code I posted does not include cows that have been tipped -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) "Klatuu" wrote: 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 -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) |
#7
|
|||
|
|||
Comparison Report based on a query of only calculations.
Thank you. I will look for a local professional.
I appreciate your candor. -- Still a Newbie, but learning :0) "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) "Klatuu" wrote: Okay, post back if you have questions. One note, the code I posted does not include cows that have been tipped -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) "Klatuu" wrote: 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 -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) |
#8
|
|||
|
|||
Comparison Report based on a query of only calculations.
Best of Luck, Amy.
If you can tell me where you are located, I may know someone I can recommend. -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: Thank you. I will look for a local professional. I appreciate your candor. -- Still a Newbie, but learning :0) "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) "Klatuu" wrote: Okay, post back if you have questions. One note, the code I posted does not include cows that have been tipped -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) "Klatuu" wrote: 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 -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) |
#9
|
|||
|
|||
Comparison Report based on a query of only calculations.
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? -- Still a Newbie, but learning :0) "Klatuu" wrote: Best of Luck, Amy. If you can tell me where you are located, I may know someone I can recommend. -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: Thank you. I will look for a local professional. I appreciate your candor. -- Still a Newbie, but learning :0) "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) "Klatuu" wrote: Okay, post back if you have questions. One note, the code I posted does not include cows that have been tipped -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) "Klatuu" wrote: 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 -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) |
#10
|
|||
|
|||
Comparison Report based on a query of only calculations.
Sorry, Amy, I don't understand the question.
-- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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? -- Still a Newbie, but learning :0) "Klatuu" wrote: Best of Luck, Amy. If you can tell me where you are located, I may know someone I can recommend. -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: Thank you. I will look for a local professional. I appreciate your candor. -- Still a Newbie, but learning :0) "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) "Klatuu" wrote: Okay, post back if you have questions. One note, the code I posted does not include cows that have been tipped -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) "Klatuu" wrote: 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 -- Dave Hargis, Microsoft Access MVP "Amy Schmid" wrote: 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 -- Still a Newbie, but learning :0) |
|
Thread Tools | |
Display Modes | |
|
|