Need additional fields for every fifth report?

[zufie]

I successfully created a report that returns every tenth report for
the begin and end dates entered: counts the number of reports
(counting every tenth report), and returns percentages on the fields
within this report.

I am now creating a report that returns every fifth report for the
begin and end dates entered: counts the number of reports (counting
every fifth report), and returns percentages on the fields within
this
report.


My question is how do I logically go about this? That is, do I need
to
create a second set of fields for the same table from which the data
originates? Or can I use the same fields, the only difference being
that every fifth report will be returned?


I don't want to mess anything up.


Thanks,


John

 

[karl dewey]

You are using a term 'Tenth Report' as if you mean 10th record of the
database. Explain a little more.
originates? Or can I use the same fields,
Explain this also. Why do you think you might need addition fields? Do you
mean fields in the table, query, or where?

 

[zufie]

You are using a term 'Tenth Report' as if you mean 10th record of the
database.  Explain a little more.


originates? Or can I use the same fields,
Explain this also.  Why do you think you might need addition fields?  Do you
mean fields in the table, query, or where?

--
KARL DEWEY
Build a little - Test a little








- Show quoted text -

Yes, the user enters a begin and end date on the form, pushes the
command button and the report returns the 10th, the 20th, the 30th,
etc record (counting every ten record), gives the total number of
records (counting by 10, so if 100 records, it would give a total of
10 records), and returns percentages on the fields within the report.

This works fine when the user wants a report that returns every tenth
record. However, user also wants the same setup as above but for a
report that will count every fifth record (5, 10,15, etc...). The data
for both reports originates from the same table. Someone more
experienced than me looked at my database and suggested I need more
columns in the table from which the data originates.

It seems like all I would have to do is copy the queries that are
behind this report and just change the DCount to Mod 5 to return
every 5th report, and naturally change the names of the begin date and
end date fields/controls on the form where the user enters the time
period she interested in.

Thanks,

HTH,

John

 

[karl dewey]

I do not see why a change from every tenth record to every fifth record would
require adding fields to the table.
Post your report query. OPen in design view, click on SQL View, highlighjt
all, copy, and paste in a post.

 

[zufie]

I do not see why a change from every tenth record to every fifth record would
require adding fields to the table.
Post your report query.  OPen in design view, click on SQL View, highlighjt
all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little








- Show quoted text -

I was successful in getting the the report that returns every fifth
record!

My problem now is how to get the report that returns every tenth
record to utilize Begin and End Dates (in this case,
txtStartSumRptEvery10th and txtStopSumRptEvery10th) to return every
tenth record within a specific period of time.
For example, suppose I want the report to return every 10th record
between 09/04/2007 and 10/04/2007.

Here is the report query. However, there are 4 queries that contribute
to creating this report:

SELECT Sum(Abs([MinOfAgencyContactYes])) AS AgencyContactYes, Sum(Abs
([MinOfAgencyContactNo])) AS AgencyContactNo, Sum(Abs
([MinOfDaysBeforeHeardFromAgency1Week])) AS
DaysBeforeHeardFromAgency1Week, Sum(Abs
([MinOfDaysBeforeHeardFromAgency2Weeks])) AS
DaysBeforeHeardFromAgency2Weeks, Sum(Abs
([MinOfDaysBeforeHeardFromAgency3orMoreWeeks])) AS
DaysBeforeHeardFromAgency3orMoreWeeks, Sum(Abs
([MinOfBecomeIBCCPClientYes])) AS BecomeIBCCPClientYes, Sum(Abs
([MinOfBecomeIBCCPClientNo])) AS BecomeIBCCPClientNo, Sum(Abs
([MinOfBecomeIBCCPClientDontKnow])) AS BecomeIBCCPClientDontKnow, Sum
(Abs([MinOfSatisfiedWithHelpReceivedYes])) AS
SatisfiedWithHelpReceivedYes, Sum(Abs
([MinOfSatisfiedWithHelpReceivedNo])) AS SatisfiedWithHelpReceivedNo,
Sum(Abs([MinOfInNeedFurtherAssistanceYes])) AS
InNeedFurtherAssistanceYes, Sum(Abs([MinOfInNeedFurtherAssistanceNo]))
AS InNeedFurtherAssistanceNo, Sum(Abs([MinOfAgencyContactYes]))+Sum(Abs
([MinOfAgencyContactNo])) AS AgencyContactTL, Sum(Abs
([MinOfDaysBeforeHeardFromAgency1Week]))+Sum(Abs
([MinOfDaysBeforeHeardFromAgency2Weeks]))+Sum(Abs
([MinOfDaysBeforeHeardFromAgency3orMoreWeeks])) AS
DaysBeforeHeardFromAgencyTL, Sum(Abs([MinOfBecomeIBCCPClientYes]))+Sum
(Abs([MinOfBecomeIBCCPClientNo]))+Sum(Abs
([MinOfBecomeIBCCPClientDontKnow])) AS BecomeIBCCPClientTL, Sum(Abs
([MinOfSatisfiedWithHelpReceivedYes]))+Sum(Abs
([MinOfSatisfiedWithHelpReceivedNo])) AS SatisfiedWithHelpReceivedTL,
Sum(Abs([MinOfInNeedFurtherAssistanceYes]))+Sum(Abs
([MinOfInNeedFurtherAssistanceNo])) AS InNeedFurtherAssistanceTL,
([AgencyContactYes])/([AgencyContactTL]) AS PrcntAgencyContactYes,
[AgencyContactNo]/[AgencyContactTL] AS PrcntAgencyContactNo,
[DaysBeforeHeardFromAgency1Week]/[DaysBeforeHeardFromAgencyTL] AS
PrcntDaysBeforeHeardFromAgency1Week, [DaysBeforeHeardFromAgency2Weeks]/
[DaysBeforeHeardFromAgencyTL] AS PrcntDaysBeforeHeardFromAgency2Weeks,
[DaysBeforeHeardFromAgency3orMoreWeeks]/[DaysBeforeHeardFromAgencyTL]
AS PrcntDaysBeforeHeardFromAgency3orMoreWeeks, [BecomeIBCCPClientYes]/
[BecomeIBCCPClientTL] AS PrcntBecomeIBCCPClientYes,
[BecomeIBCCPClientNo]/[BecomeIBCCPClientTL] AS
PrcntBecomeIBCCPClientNo, [BecomeIBCCPClientDontKnow]/
[BecomeIBCCPClientTL] AS PrcntBecomeIBCCPClientDontKnow,
[SatisfiedWithHelpReceivedYes]/[SatisfiedWithHelpReceivedTL] AS
PrcntSatisfiedWithHelpReceivedYes, [SatisfiedWithHelpReceivedNo]/
[SatisfiedWithHelpReceivedTL] AS PrcntSatisfiedWithHelpReceivedNo,
[InNeedFurtherAssistanceYes]/[InNeedFurtherAssistanceTL] AS
PrcntInNeedFurtherAssistanceYes, [InNeedFurtherAssistanceNo]/
[InNeedFurtherAssistanceTL] AS PrcntInNeedFurtherAssistanceNo, (Sum(Abs
([SumOfCountOfCaller ID]))) AS TLQACallsMade10th, Sum(Abs
([MinOfQualityAssurance])) AS TLQACallersResponded10th
FROM qrySummaryReportResponsesTEST
WITH OWNERACCESS OPTION;

Thanks,

John

 

[KARL DEWEY]

Ah Ha, you have been saying every fifth REPORT instead of fifth record.
I do not see a field that looks like it has a date.
I still do not know what you are talking about adding fields.

One way to pull every fifth record is to create a temp table sorted (this
case I assume in date order) and add an Autonumber field.
Create your select query with added field like this --
Something: ([Autonumber]/5)- ([Autonumber]\5)
and use 0 (zero) as criteria.

([Autonumber]/5) devides by 5
([Autonumber]\5) devides by 5 and makes it an integer (whole number)

Any multiple of 5/5 - 5\5 will equal o (zero).

--
KARL DEWEY
Build a little - Test a little

I do not see why a change from every tenth record to every fifth record would
require adding fields to the table.
Post your report query. OPen in design view, click on SQL View, highlighjt
all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little















- Show quoted text -

I was successful in getting the the report that returns every fifth
record!

My problem now is how to get the report that returns every tenth
record to utilize Begin and End Dates (in this case,
txtStartSumRptEvery10th and txtStopSumRptEvery10th) to return every
tenth record within a specific period of time.
For example, suppose I want the report to return every 10th record
between 09/04/2007 and 10/04/2007.

Here is the report query. However, there are 4 queries that contribute
to creating this report:

SELECT Sum(Abs([MinOfAgencyContactYes])) AS AgencyContactYes, Sum(Abs
([MinOfAgencyContactNo])) AS AgencyContactNo, Sum(Abs
([MinOfDaysBeforeHeardFromAgency1Week])) AS
DaysBeforeHeardFromAgency1Week, Sum(Abs
([MinOfDaysBeforeHeardFromAgency2Weeks])) AS
DaysBeforeHeardFromAgency2Weeks, Sum(Abs
([MinOfDaysBeforeHeardFromAgency3orMoreWeeks])) AS
DaysBeforeHeardFromAgency3orMoreWeeks, Sum(Abs
([MinOfBecomeIBCCPClientYes])) AS BecomeIBCCPClientYes, Sum(Abs
([MinOfBecomeIBCCPClientNo])) AS BecomeIBCCPClientNo, Sum(Abs
([MinOfBecomeIBCCPClientDontKnow])) AS BecomeIBCCPClientDontKnow, Sum
(Abs([MinOfSatisfiedWithHelpReceivedYes])) AS
SatisfiedWithHelpReceivedYes, Sum(Abs
([MinOfSatisfiedWithHelpReceivedNo])) AS SatisfiedWithHelpReceivedNo,
Sum(Abs([MinOfInNeedFurtherAssistanceYes])) AS
InNeedFurtherAssistanceYes, Sum(Abs([MinOfInNeedFurtherAssistanceNo]))
AS InNeedFurtherAssistanceNo, Sum(Abs([MinOfAgencyContactYes]))+Sum(Abs
([MinOfAgencyContactNo])) AS AgencyContactTL, Sum(Abs
([MinOfDaysBeforeHeardFromAgency1Week]))+Sum(Abs
([MinOfDaysBeforeHeardFromAgency2Weeks]))+Sum(Abs
([MinOfDaysBeforeHeardFromAgency3orMoreWeeks])) AS
DaysBeforeHeardFromAgencyTL, Sum(Abs([MinOfBecomeIBCCPClientYes]))+Sum
(Abs([MinOfBecomeIBCCPClientNo]))+Sum(Abs
([MinOfBecomeIBCCPClientDontKnow])) AS BecomeIBCCPClientTL, Sum(Abs
([MinOfSatisfiedWithHelpReceivedYes]))+Sum(Abs
([MinOfSatisfiedWithHelpReceivedNo])) AS SatisfiedWithHelpReceivedTL,
Sum(Abs([MinOfInNeedFurtherAssistanceYes]))+Sum(Abs
([MinOfInNeedFurtherAssistanceNo])) AS InNeedFurtherAssistanceTL,
([AgencyContactYes])/([AgencyContactTL]) AS PrcntAgencyContactYes,
[AgencyContactNo]/[AgencyContactTL] AS PrcntAgencyContactNo,
[DaysBeforeHeardFromAgency1Week]/[DaysBeforeHeardFromAgencyTL] AS
PrcntDaysBeforeHeardFromAgency1Week, [DaysBeforeHeardFromAgency2Weeks]/
[DaysBeforeHeardFromAgencyTL] AS PrcntDaysBeforeHeardFromAgency2Weeks,
[DaysBeforeHeardFromAgency3orMoreWeeks]/[DaysBeforeHeardFromAgencyTL]
AS PrcntDaysBeforeHeardFromAgency3orMoreWeeks, [BecomeIBCCPClientYes]/
[BecomeIBCCPClientTL] AS PrcntBecomeIBCCPClientYes,
[BecomeIBCCPClientNo]/[BecomeIBCCPClientTL] AS
PrcntBecomeIBCCPClientNo, [BecomeIBCCPClientDontKnow]/
[BecomeIBCCPClientTL] AS PrcntBecomeIBCCPClientDontKnow,
[SatisfiedWithHelpReceivedYes]/[SatisfiedWithHelpReceivedTL] AS
PrcntSatisfiedWithHelpReceivedYes, [SatisfiedWithHelpReceivedNo]/
[SatisfiedWithHelpReceivedTL] AS PrcntSatisfiedWithHelpReceivedNo,
[InNeedFurtherAssistanceYes]/[InNeedFurtherAssistanceTL] AS
PrcntInNeedFurtherAssistanceYes, [InNeedFurtherAssistanceNo]/
[InNeedFurtherAssistanceTL] AS PrcntInNeedFurtherAssistanceNo, (Sum(Abs
([SumOfCountOfCaller ID]))) AS TLQACallsMade10th, Sum(Abs
([MinOfQualityAssurance])) AS TLQACallersResponded10th
FROM qrySummaryReportResponsesTEST
WITH OWNERACCESS OPTION;

Thanks,

John