Group report by day, but only for seven days

[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.

 

[Marshall Barton]

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

 

[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.

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

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

 

[Duane Hookom]

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

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

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

 

[KARL DEWEY]

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

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

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

 

[KARL DEWEY]

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

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

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

 

[Marshall Barton]

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

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'm with Karl on using a numbers table instead of a dates
table.