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
--
Still 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.
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
--
Dave 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
formatting in place to accomodate a vertical report.
Have you ever tried a vertical report in access?
--
Still a Newbie, but learning :0)
:
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.
--
Dave Hargis, Microsoft Access MVP
:
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.
--
Still a Newbie, but learning :0)
:
Sorry, Amy, I don't understand the question.
--
Dave Hargis, Microsoft Access MVP
:
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)
:
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
:
Thank you. I will look for a local professional.
I appreciate your candor.
--
Still 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
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
:
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)
:
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
:
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)
:
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
