Group report by day, but only for seven days

  • Thread starter Thread starter Steve Signell
  • Start date Start date
S

Steve Signell

I want to produce a report that extends out 7 days from a user-supplied
start date. I have designed a query that selects all records that fall
between a [start date] parameter and <=DateAdd("d",7,[start date]).

Now I want to produce a report that sorts by day but only for this range of
dates. Can anyone help me out here? I have searched through the posts and
haven't found anything relating to this.
 
I want to produce a report that extends out 7 days from a user-supplied
start date. I have designed a query that selects all records that fall
between a [start date] parameter and <=DateAdd("d",7,[start date]).

Now I want to produce a report that sorts by day but only for this range of
dates. Can anyone help me out here? I have searched through the posts and
haven't found anything relating to this.
Click to expand...


To sort in a report, use the report's Sorting and Grouping
window (View menu) and specify the date field. (Sorting and
filtering are independent operations that will not interfere
with each other.)
 
Marshall Barton said:
I want to produce a report that extends out 7 days from a user-supplied
start date. I have designed a query that selects all records that fall
between a [start date] parameter and <=DateAdd("d",7,[start date]).

Now I want to produce a report that sorts by day but only for this range of
dates. Can anyone help me out here? I have searched through the posts and
haven't found anything relating to this.
Click to expand...


To sort in a report, use the report's Sorting and Grouping
window (View menu) and specify the date field. (Sorting and
filtering are independent operations that will not interfere
with each other.)
Click to expand...


Let me clarify a little more: I have groups of guests coming to our
facility, each with an [arrival date] and a [departure date]. I would like
to produce a report tabulating the number of guests present on each day, for
any given week. I cannot use the arrival date field for the sorting, as
those are not the only days they're here. I need to set up a query that
takes a set of 7 days (user specified) and cycles through each day, finding
which groups are present that day and then totaling the number of guests.

For example if the raw data looks like this:

Group / Arrival Date / Departure Date / Group Size
A / 1-1 / 1-4 / 1
B / 1-3 / 1-6 / 5
C / 1-5 / 1-7 / 10

Then I want a report that looks like this:

Date / # guests
1-1 / 1
1-2 / 1
1-3 / 6
1-4 / 5
1-5 / 15
1-6 / 10
1-7 / 10
 
I generally add a table [tblDates] with a single date field [TheDate] with
records for every date that will ever be of interest. This allows me to
create a query like

SELECT tblScheduledVisits.*, [TheDate]
FROM tblScheduledVisits, tblDates
WHERE TheDate Between [Arrival Date] AND [Departure Date];

This creates a single record for each date so group size can be summed by
date.
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


Steve Signell said:
Marshall Barton said:
I want to produce a report that extends out 7 days from a user-supplied
start date. I have designed a query that selects all records that fall
between a [start date] parameter and <=DateAdd("d",7,[start date]).

Now I want to produce a report that sorts by day but only for this range of
dates. Can anyone help me out here? I have searched through the posts and
haven't found anything relating to this.
Click to expand...


To sort in a report, use the report's Sorting and Grouping
window (View menu) and specify the date field. (Sorting and
filtering are independent operations that will not interfere
with each other.)
Click to expand...


Let me clarify a little more: I have groups of guests coming to our
facility, each with an [arrival date] and a [departure date]. I would like
to produce a report tabulating the number of guests present on each day, for
any given week. I cannot use the arrival date field for the sorting, as
those are not the only days they're here. I need to set up a query that
takes a set of 7 days (user specified) and cycles through each day, finding
which groups are present that day and then totaling the number of guests.

For example if the raw data looks like this:

Group / Arrival Date / Departure Date / Group Size
A / 1-1 / 1-4 / 1
B / 1-3 / 1-6 / 5
C / 1-5 / 1-7 / 10

Then I want a report that looks like this:

Date / # guests
1-1 / 1
1-2 / 1
1-3 / 6
1-4 / 5
1-5 / 15
1-6 / 10
1-7 / 10
Click to expand...
 
Create a table named CountNumber with field CountNUM containing number 0
(zero) through your maximum spread.

SELECT DateAdd("d",[CountNUM],[Arrival Date]) AS [Date of stay],
Sum(GroupStay.[Group Size]) AS [SumOfGroup Size]
FROM CountNumber, GroupStay
WHERE (((DateAdd("d",[CountNUM],[Arrival Date])) Between [Enter start date]
And (DateAdd("d",[CountNUM],[Arrival Date]))<=DateAdd("d",7,[Enter start
date]) And (DateAdd("d",[CountNUM],[Arrival Date]))<=[Departure Date]))
GROUP BY DateAdd("d",[CountNUM],[Arrival Date]);

--
KARL DEWEY
Build a little - Test a little


Steve Signell said:
Marshall Barton said:
I want to produce a report that extends out 7 days from a user-supplied
start date. I have designed a query that selects all records that fall
between a [start date] parameter and <=DateAdd("d",7,[start date]).

Now I want to produce a report that sorts by day but only for this range of
dates. Can anyone help me out here? I have searched through the posts and
haven't found anything relating to this.
Click to expand...


To sort in a report, use the report's Sorting and Grouping
window (View menu) and specify the date field. (Sorting and
filtering are independent operations that will not interfere
with each other.)
Click to expand...


Let me clarify a little more: I have groups of guests coming to our
facility, each with an [arrival date] and a [departure date]. I would like
to produce a report tabulating the number of guests present on each day, for
any given week. I cannot use the arrival date field for the sorting, as
those are not the only days they're here. I need to set up a query that
takes a set of 7 days (user specified) and cycles through each day, finding
which groups are present that day and then totaling the number of guests.

For example if the raw data looks like this:

Group / Arrival Date / Departure Date / Group Size
A / 1-1 / 1-4 / 1
B / 1-3 / 1-6 / 5
C / 1-5 / 1-7 / 10

Then I want a report that looks like this:

Date / # guests
1-1 / 1
1-2 / 1
1-3 / 6
1-4 / 5
1-5 / 15
1-6 / 10
1-7 / 10
Click to expand...
 
You need to remove the equal (=) sign from WHERE as <[Departure Date]))

SELECT DateAdd("d",[CountNUM],[Arrival Date]) AS [Date of stay],
Sum(GroupStay.[Group Size]) AS [SumOfGroup Size]
FROM CountNumber, GroupStay
WHERE (((DateAdd("d",[CountNUM],[Arrival Date])) Between [Enter start date]
And (DateAdd("d",[CountNUM],[Arrival Date]))<=DateAdd("d",7,[Enter start
date]) And (DateAdd("d",[CountNUM],[Arrival Date]))<[Departure Date]))
GROUP BY DateAdd("d",[CountNUM],[Arrival Date]);

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Create a table named CountNumber with field CountNUM containing number 0
(zero) through your maximum spread.

SELECT DateAdd("d",[CountNUM],[Arrival Date]) AS [Date of stay],
Sum(GroupStay.[Group Size]) AS [SumOfGroup Size]
FROM CountNumber, GroupStay
WHERE (((DateAdd("d",[CountNUM],[Arrival Date])) Between [Enter start date]
And (DateAdd("d",[CountNUM],[Arrival Date]))<=DateAdd("d",7,[Enter start
date]) And (DateAdd("d",[CountNUM],[Arrival Date]))<=[Departure Date]))
GROUP BY DateAdd("d",[CountNUM],[Arrival Date]);

--
KARL DEWEY
Build a little - Test a little


Steve Signell said:
Marshall Barton said:
Steve Signell <Steve (e-mail address removed)>
wrote:

I want to produce a report that extends out 7 days from a user-supplied
start date. I have designed a query that selects all records that fall
between a [start date] parameter and <=DateAdd("d",7,[start date]).

Now I want to produce a report that sorts by day but only for this range of
dates. Can anyone help me out here? I have searched through the posts and
haven't found anything relating to this.


To sort in a report, use the report's Sorting and Grouping
window (View menu) and specify the date field. (Sorting and
filtering are independent operations that will not interfere
with each other.)
Click to expand...


Let me clarify a little more: I have groups of guests coming to our
facility, each with an [arrival date] and a [departure date]. I would like
to produce a report tabulating the number of guests present on each day, for
any given week. I cannot use the arrival date field for the sorting, as
those are not the only days they're here. I need to set up a query that
takes a set of 7 days (user specified) and cycles through each day, finding
which groups are present that day and then totaling the number of guests.

For example if the raw data looks like this:

Group / Arrival Date / Departure Date / Group Size
A / 1-1 / 1-4 / 1
B / 1-3 / 1-6 / 5
C / 1-5 / 1-7 / 10

Then I want a report that looks like this:

Date / # guests
1-1 / 1
1-2 / 1
1-3 / 6
1-4 / 5
1-5 / 15
1-6 / 10
1-7 / 10
Click to expand...
Click to expand...
 
Steve said:
Marshall Barton said:
I want to produce a report that extends out 7 days from a user-supplied
start date. I have designed a query that selects all records that fall
between a [start date] parameter and <=DateAdd("d",7,[start date]).

Now I want to produce a report that sorts by day but only for this range of
dates. Can anyone help me out here? I have searched through the posts and
haven't found anything relating to this.
Click to expand...


To sort in a report, use the report's Sorting and Grouping
window (View menu) and specify the date field. (Sorting and
filtering are independent operations that will not interfere
with each other.)
Click to expand...

Let me clarify a little more: I have groups of guests coming to our
facility, each with an [arrival date] and a [departure date]. I would like
to produce a report tabulating the number of guests present on each day, for
any given week. I cannot use the arrival date field for the sorting, as
those are not the only days they're here. I need to set up a query that
takes a set of 7 days (user specified) and cycles through each day, finding
which groups are present that day and then totaling the number of guests.

For example if the raw data looks like this:

Group / Arrival Date / Departure Date / Group Size
A / 1-1 / 1-4 / 1
B / 1-3 / 1-6 / 5
C / 1-5 / 1-7 / 10

Then I want a report that looks like this:

Date / # guests
1-1 / 1
1-2 / 1
1-3 / 6
1-4 / 5
1-5 / 15
1-6 / 10
1-7 / 10
Click to expand...


I'm with Karl on using a numbers table instead of a dates
table.
 
Back
Top