RE: 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)
.

Relevant Pages