date range issues in reports

[Christina Barnett]

I am having issues with my date range in a report. I read
through Jeff Conrad's instructions from yesterday and
they worked great ("How do I add a date range" e-mail).
However, the particular report that I have created pulls
records over a week-long period, but the report shows
them sorted on a daily basis as opposed to a weekly
basis. Ex: Week of Sep 1- Sep 4th records are shown for
each day instead of in one group.

Please help!

 

[Jeff Conrad]

Hi Christina,

My apologies for missing this post earlier; I somehow skipped over this one.

The person I was helping yesterday was having a problem with a report based on the Contact
Management database that ships with Access or can be downloaded. Were you also having an issue with
this particular database or is this something completely different? If this does involve the Contact
Management database, which report were you having difficulty with or is this a report you created?

If this is a different database could you describe in more detail your table structure, what you are
trying to accomplish, and possibly some sample records of what you would like to see in the final
report layout? That would help me visualize things. From a quick read of your post it sounds like we
need to play around with the Sorting and Grouping for this report.

I will try and get back to this tomorrow, but for now I have a pillow calling my name......"Come
over here Jeff......"

 

[Christina Barnett]

Hi Jeff,

I am working with a completely different database. It is
based on weekly production numbers for multiple
processors. The table contains process names, and the
forms allow the processors to enter the number(s)
completed for each process on each day. The report I am
attempting to put together reflects the process name and
total number of items completed for each week. What I am
getting shows the total number completed by day.

I have set the grouping in the report to date by week,
but that didn't seem to make a difference. Do I need a
formula somewhere, or a change in the form or query?

Thank you.
~Christina

-----Original Message-----
Hi Christina,

My apologies for missing this post earlier; I somehow skipped over this one.

The person I was helping yesterday was having a problem

with a report based on the Contact

Management database that ships with Access or can be

downloaded. Were you also having an issue with

this particular database or is this something completely

different? If this does involve the Contact

Management database, which report were you having

difficulty with or is this a report you created?

If this is a different database could you describe in

more detail your table structure, what you are

trying to accomplish, and possibly some sample records

of what you would like to see in the final

report layout? That would help me visualize things. From

a quick read of your post it sounds like we

need to play around with the Sorting and Grouping for this report.

I will try and get back to this tomorrow, but for now I

have a pillow calling my name......"Come

 

[Jeff Conrad]

Hi Christina,

From the description so far it definitely looks like we need to be using the Sorting and Grouping
area combined with some unbound text boxes in the various group footers to calculate the total
number of items completed for each week.

Could you post a sample output of what your report looks like now so far with some sample records?

I have a hard time visualizing different report setups in the NG. It is much easier for me to work
through all the issues when I can see the report in front of me. That is why I do not usually answer
too many questions in the Reports NG. The person I helped yesterday was using an Access template
which made it easy for me to work through any issues and then post the solution here. If it becomes
necessary, would you be willing to send a cut-down version of the database with just the objects
necessary to illustrate the report problem?

 

[Christina Barnett]

Jeff,

Sure thing. Here's a sample portion of a report:
Date 09/03/2004
AIGA FSA
AIGA Telephone 5
AIGA Emails/Notescreens 0
AIGA Pays/Completes 0 AIGA Partial 0
Old FA Partial 0
AIGA Correspondence 2 Old FA
Correspondence 2
AIGA Misc Items 0 Old FA Misc Items 0
AIGA 2nd Request Letters 0 Old FA
2nd Request Letters 2
AIGA 5 Year Expiration Open 2 Old FA 5
Year Expiration Open 0
AIGA Return DC Letters 0 Old FA Return DC
Letters 0
AIGA Initial 2 AIGA Spousal 0
AIGA Open 0 AIGA 0

-----Original Message-----
Hi Christina,

From the description so far it definitely looks like we

need to be using the Sorting and Grouping

area combined with some unbound text boxes in the

various group footers to calculate the total

number of items completed for each week.

Could you post a sample output of what your report looks

like now so far with some sample records?

I have a hard time visualizing different report setups

in the NG. It is much easier for me to work

through all the issues when I can see the report in

front of me. That is why I do not usually answer

too many questions in the Reports NG. The person I

helped yesterday was using an Access template

which made it easy for me to work through any issues and

then post the solution here. If it becomes

necessary, would you be willing to send a cut-down

version of the database with just the objects

 

[Christina Barnett]

Jeff-
Please ignore my last posting- let me reformat everything
and post again.

-----Original Message-----
Hi Christina,

From the description so far it definitely looks like we

need to be using the Sorting and Grouping

area combined with some unbound text boxes in the

various group footers to calculate the total

number of items completed for each week.

Could you post a sample output of what your report looks

like now so far with some sample records?

I have a hard time visualizing different report setups

in the NG. It is much easier for me to work

through all the issues when I can see the report in

front of me. That is why I do not usually answer

too many questions in the Reports NG. The person I

helped yesterday was using an Access template

which made it easy for me to work through any issues and

then post the solution here. If it becomes

necessary, would you be willing to send a cut-down

version of the database with just the objects

 

[Guest]

Jeff,

Here it is again. Sample report:

DATE 09/01/2004
COMPANY TOTAL
AIGA Telephone 5
AIGA Emails/Notescreens 0
AIGA Pays/Completes 0
AIGA Partial 0
AIGA Correspondence 2
AIGA Misc Items 0
AIGA 2nd Request Letters 0
AIGA 5 Year Expiration Open 2
AIGA Return DC Letters 0
AIGA Initial 2
AIGA Spousal 0
AIGA Open Claims 0

The same data is repeated for 9/2/2004, 9/3/2004, and
9/4/2004 (with different totals). I would like the date
to show 9/1/2004 - 9/4/2004, and total the TOTALS columns
for each day into one overall total.

If this is not enough, let me know. Thanks for your help!
~Christina

 

[Jeff Conrad]

Hi Christina,

That helps a lot thank you. I think I understand everything, but I'm making a assumption that you
simply want to see a total for all items completed each day as well as a total for all items
completed during whatever time span you would like. My instructions below will NOT total the number
of items processed by EACH individual item for the time frame specified. That is something
different.

Doing a quick test with some sample data I *believe* I was able to get the results you desire by
just using the report wizard. Open the Sorting and Grouping dialog box ( View | Sorting and
Grouping ) and tell me what you see there. In my report I have the following item:

Field/Expression: Sort Order:
ProcessDate Ascending

(I named my field ProcessDate)

In the lower half of this sorting box I have this:
Group Header: Yes
Group Footer: Yes
Group On: Day
Group Interval: 1
Keep Together: No

What this is doing is telling Access to sort the date by each day and create a section Header and
Footer for this particular group. This is the first step.

The next step is to create an unbound text box in the ProcessDate Footer section of the report. Here
we will total all the items completed. I used this as the Control Source for the text box:
=Nz(Sum([ItemsCompleted]))

This gives me a count of all the items completed for that day. (My field was called ItemsCompleted)

Next, I put another text box in the Report Footer section of the report. Here we will total all the
items completed for the time period specified. I use the same Control Source:
=Nz(Sum([ItemsCompleted]))

When I run the report I see each day's processes listed, followed by a total for that day. At the
end of the report I see a total for all the days specified.

Is this what you are looking for?

 

[Christina Barnett]

Jeff~

That worked well. Thanks for your help. I'm sure I will
have more questions in the future.

~Christina

-----Original Message-----
Hi Christina,

That helps a lot thank you. I think I understand

everything, but I'm making a assumption that you

simply want to see a total for all items completed each

day as well as a total for all items

completed during whatever time span you would like. My

instructions below will NOT total the number

of items processed by EACH individual item for the time

frame specified. That is something

different.

Doing a quick test with some sample data I *believe* I

was able to get the results you desire by

just using the report wizard. Open the Sorting and

Grouping dialog box ( View | Sorting and

Grouping ) and tell me what you see there. In my report I have the following item:

Field/Expression: Sort Order:
ProcessDate Ascending

(I named my field ProcessDate)

In the lower half of this sorting box I have this:
Group Header: Yes
Group Footer: Yes
Group On: Day
Group Interval: 1
Keep Together: No

What this is doing is telling Access to sort the date by

each day and create a section Header and

Footer for this particular group. This is the first step.

The next step is to create an unbound text box in the

ProcessDate Footer section of the report. Here

we will total all the items completed. I used this as

the Control Source for the text box:

=Nz(Sum([ItemsCompleted]))

This gives me a count of all the items completed for

that day. (My field was called ItemsCompleted)

Next, I put another text box in the Report Footer

section of the report. Here we will total all the

items completed for the time period specified. I use the same Control Source:
=Nz(Sum([ItemsCompleted]))

When I run the report I see each day's processes listed,

followed by a total for that day. At the

 

[Jeff Conrad]

Excellent, glad to help.
Come back any time; we're open every day.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Jeff~

That worked well. Thanks for your help. I'm sure I will
have more questions in the future.

~Christina

-----Original Message-----
Hi Christina,

That helps a lot thank you. I think I understand

everything, but I'm making a assumption that you

simply want to see a total for all items completed each

day as well as a total for all items

completed during whatever time span you would like. My

instructions below will NOT total the number

of items processed by EACH individual item for the time

frame specified. That is something

different.

Doing a quick test with some sample data I *believe* I

was able to get the results you desire by

just using the report wizard. Open the Sorting and

Grouping dialog box ( View | Sorting and

Grouping ) and tell me what you see there. In my report I have the following item:

Field/Expression: Sort Order:
ProcessDate Ascending

(I named my field ProcessDate)

In the lower half of this sorting box I have this:
Group Header: Yes
Group Footer: Yes
Group On: Day
Group Interval: 1
Keep Together: No

What this is doing is telling Access to sort the date by

each day and create a section Header and

Footer for this particular group. This is the first step.

The next step is to create an unbound text box in the

ProcessDate Footer section of the report. Here

we will total all the items completed. I used this as

the Control Source for the text box:

=Nz(Sum([ItemsCompleted]))

This gives me a count of all the items completed for

that day. (My field was called ItemsCompleted)

Next, I put another text box in the Report Footer

section of the report. Here we will total all the

items completed for the time period specified. I use the same Control Source:
=Nz(Sum([ItemsCompleted]))

When I run the report I see each day's processes listed,

followed by a total for that day. At the

end of the report I see a total for all the days specified.

Is this what you are looking for?