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

Relevant Pages

  • RE: Comparison Report based on a query of only calculations.
    ... I really tipped my cows now. ... Report Calculations is created from a query of muliple calculations. ... records on the FS Data table for one client instead of one record per client. ... CowCount = 1 ...
    (microsoft.public.access.reports)
  • RE: Comparison Report based on a query of only calculations.
    ... With as many subforms as you have and not knowing the record source of each ... troubleshoot your problem with multiple client records. ... would allow you to present your report in the format you want. ... We need to create a report that is comparative by year and by number of cows. ...
    (microsoft.public.access.reports)
  • Re: Comparing documents confusion...
    ... > someone could expand on the help instructions and explain the odd alert ... I send a report to a client. ... Compare anyway?² In fact, ...
    (microsoft.public.mac.office.word)
  • RE: Comparison Report based on a query of only calculations.
    ... Client number 004576 ... We got the report to work so that it sorts by Avg # of Cows and all the data ... Dave Hargis, Microsoft Access MVP ...
    (microsoft.public.access.reports)
  • RE: Comparison Report based on a query of only calculations.
    ... Dave Hargis, Microsoft Access MVP ... "Amy Schmid" wrote: ... Client number Client Name Avg # of Cows Production Livestock ... And you want them to appear on the report like: ...
    (microsoft.public.access.reports)