How to show a date range in a report

Thanks Karl. You're awesome!! I have one more issue to deal with in the same
report. Your answer definitely has me in the right direction. Now I have an
issue of having the same exact events and locations but later in the year.
Using the qry you advised it now captures the date range for all the dates
with that specific event name and location.

The report is now showing :
January 1, 2008 - March 8, 2008 | Party#1 | Amy's House

I would like the report to show this:
January 1, 2008 - January 3, 2008 |Party #1| Amy's House
March 8, 2008 |Party #1 | Amy's House

Thanks again!




"KARL DEWEY" wrote:

> Use a totals query like this --
> SELECT [event names], [locations], Min([dates]) As Event_Start, Max([dates])
> As Event_End
> FROM YourTable
> GROUP BY [event names], [locations];
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "omshanti" wrote:
>
> > I have a table of event names ,dates and locations. I can run a report that
> > shows all these fields; but some events run 2-3 days long and I need to be
> > able to show the event name, location and date range in one line instead of
> > multiple rows.
> >
> > For Example, I now have a report that shows this:
> > January 1, 2008 |Party #1| Amy's House
> > January 2, 2008 |Party #1| Amy's House
> > January 3, 2008 |Party #1| Amy's House
> >
> > And want a report to show this:
> > January 1, 2008 - January 3, 2008 | Party#1 | Amy's House
> >
> >
> > How do I achieve this?
> > Thank you for your help in advance!
> >

Try using two more queries - Select query named OtherDates and union query.
SELECT [event names], [locations], [dates] As Event_Start, [dates] As
Event_End
FROM YourTable INNER JOIN [TotalsQuery] ON [YourTable].[event names] =
[TotalsQuery].[event names] And [YourTable].[locations] =
[TotalsQuery].[locations]
WHERE [dates] Not Between [TotalsQuery].[Event_Start] And
[TotalsQuery].[Event_End];

SELECT [TotalsQuery].[event names], [TotalsQuery].[locations],
[TotalsQuery].[Event_Start], [TotalsQuery].[Event_End]
FROM [TotalsQuery]
UNION SELECT [OtherDates].[event names], [OtherDates].[locations],
[OtherDates].[Event_Start], [OtherDates].[Event_End]
FROM [OtherDates];

--
KARL DEWEY
Build a little - Test a little


"omshanti" wrote:

> Thanks Karl. You're awesome!! I have one more issue to deal with in the same
> report. Your answer definitely has me in the right direction. Now I have an
> issue of having the same exact events and locations but later in the year.
> Using the qry you advised it now captures the date range for all the dates
> with that specific event name and location.
>
> The report is now showing :
> January 1, 2008 - March 8, 2008 | Party#1 | Amy's House
>
> I would like the report to show this:
> January 1, 2008 - January 3, 2008 |Party #1| Amy's House
> March 8, 2008 |Party #1 | Amy's House
>
> Thanks again!
>
>
>
>
> "KARL DEWEY" wrote:
>
> > Use a totals query like this --
> > SELECT [event names], [locations], Min([dates]) As Event_Start, Max([dates])
> > As Event_End
> > FROM YourTable
> > GROUP BY [event names], [locations];
> > --
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "omshanti" wrote:
> >
> > > I have a table of event names ,dates and locations. I can run a report that
> > > shows all these fields; but some events run 2-3 days long and I need to be
> > > able to show the event name, location and date range in one line instead of
> > > multiple rows.
> > >
> > > For Example, I now have a report that shows this:
> > > January 1, 2008 |Party #1| Amy's House
> > > January 2, 2008 |Party #1| Amy's House
> > > January 3, 2008 |Party #1| Amy's House
> > >
> > > And want a report to show this:
> > > January 1, 2008 - January 3, 2008 | Party#1 | Amy's House
> > >
> > >
> > > How do I achieve this?
> > > Thank you for your help in advance!
> > >

Hi Karl-
Thanks for your suggestions, but the formula you suggested is asking me for
a parameter value. There are so many people that need to access this report,
it would be easier if they could just select a report and not have to enter
parameter values. Is there another way I can do this?

Thanks!

"KARL DEWEY" wrote:

> Try using two more queries - Select query named OtherDates and union query.
> SELECT [event names], [locations], [dates] As Event_Start, [dates] As
> Event_End
> FROM YourTable INNER JOIN [TotalsQuery] ON [YourTable].[event names] =
> [TotalsQuery].[event names] And [YourTable].[locations] =
> [TotalsQuery].[locations]
> WHERE [dates] Not Between [TotalsQuery].[Event_Start] And
> [TotalsQuery].[Event_End];
>
> SELECT [TotalsQuery].[event names], [TotalsQuery].[locations],
> [TotalsQuery].[Event_Start], [TotalsQuery].[Event_End]
> FROM [TotalsQuery]
> UNION SELECT [OtherDates].[event names], [OtherDates].[locations],
> [OtherDates].[Event_Start], [OtherDates].[Event_End]
> FROM [OtherDates];
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "omshanti" wrote:
>
> > Thanks Karl. You're awesome!! I have one more issue to deal with in the same
> > report. Your answer definitely has me in the right direction. Now I have an
> > issue of having the same exact events and locations but later in the year.
> > Using the qry you advised it now captures the date range for all the dates
> > with that specific event name and location.
> >
> > The report is now showing :
> > January 1, 2008 - March 8, 2008 | Party#1 | Amy's House
> >
> > I would like the report to show this:
> > January 1, 2008 - January 3, 2008 |Party #1| Amy's House
> > March 8, 2008 |Party #1 | Amy's House
> >
> > Thanks again!
> >
> >
> >
> >
> > "KARL DEWEY" wrote:
> >
> > > Use a totals query like this --
> > > SELECT [event names], [locations], Min([dates]) As Event_Start, Max([dates])
> > > As Event_End
> > > FROM YourTable
> > > GROUP BY [event names], [locations];
> > > --
> > > KARL DEWEY
> > > Build a little - Test a little
> > >
> > >
> > > "omshanti" wrote:
> > >
> > > > I have a table of event names ,dates and locations. I can run a report that
> > > > shows all these fields; but some events run 2-3 days long and I need to be
> > > > able to show the event name, location and date range in one line instead of
> > > > multiple rows.
> > > >
> > > > For Example, I now have a report that shows this:
> > > > January 1, 2008 |Party #1| Amy's House
> > > > January 2, 2008 |Party #1| Amy's House
> > > > January 3, 2008 |Party #1| Amy's House
> > > >
> > > > And want a report to show this:
> > > > January 1, 2008 - January 3, 2008 | Party#1 | Amy's House
> > > >
> > > >
> > > > How do I achieve this?
> > > > Thank you for your help in advance!
> > > >

What parameter is it asking for?
--
KARL DEWEY
Build a little - Test a little


"omshanti" wrote:

> Hi Karl-
> Thanks for your suggestions, but the formula you suggested is asking me for
> a parameter value. There are so many people that need to access this report,
> it would be easier if they could just select a report and not have to enter
> parameter values. Is there another way I can do this?
>
> Thanks!
>
> "KARL DEWEY" wrote:
>
> > Try using two more queries - Select query named OtherDates and union query.
> > SELECT [event names], [locations], [dates] As Event_Start, [dates] As
> > Event_End
> > FROM YourTable INNER JOIN [TotalsQuery] ON [YourTable].[event names] =
> > [TotalsQuery].[event names] And [YourTable].[locations] =
> > [TotalsQuery].[locations]
> > WHERE [dates] Not Between [TotalsQuery].[Event_Start] And
> > [TotalsQuery].[Event_End];
> >
> > SELECT [TotalsQuery].[event names], [TotalsQuery].[locations],
> > [TotalsQuery].[Event_Start], [TotalsQuery].[Event_End]
> > FROM [TotalsQuery]
> > UNION SELECT [OtherDates].[event names], [OtherDates].[locations],
> > [OtherDates].[Event_Start], [OtherDates].[Event_End]
> > FROM [OtherDates];
> >
> > --
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "omshanti" wrote:
> >
> > > Thanks Karl. You're awesome!! I have one more issue to deal with in the same
> > > report. Your answer definitely has me in the right direction. Now I have an
> > > issue of having the same exact events and locations but later in the year.
> > > Using the qry you advised it now captures the date range for all the dates
> > > with that specific event name and location.
> > >
> > > The report is now showing :
> > > January 1, 2008 - March 8, 2008 | Party#1 | Amy's House
> > >
> > > I would like the report to show this:
> > > January 1, 2008 - January 3, 2008 |Party #1| Amy's House
> > > March 8, 2008 |Party #1 | Amy's House
> > >
> > > Thanks again!
> > >
> > >
> > >
> > >
> > > "KARL DEWEY" wrote:
> > >
> > > > Use a totals query like this --
> > > > SELECT [event names], [locations], Min([dates]) As Event_Start, Max([dates])
> > > > As Event_End
> > > > FROM YourTable
> > > > GROUP BY [event names], [locations];
> > > > --
> > > > KARL DEWEY
> > > > Build a little - Test a little
> > > >
> > > >
> > > > "omshanti" wrote:
> > > >
> > > > > I have a table of event names ,dates and locations. I can run a report that
> > > > > shows all these fields; but some events run 2-3 days long and I need to be
> > > > > able to show the event name, location and date range in one line instead of
> > > > > multiple rows.
> > > > >
> > > > > For Example, I now have a report that shows this:
> > > > > January 1, 2008 |Party #1| Amy's House
> > > > > January 2, 2008 |Party #1| Amy's House
> > > > > January 3, 2008 |Party #1| Amy's House
> > > > >
> > > > > And want a report to show this:
> > > > > January 1, 2008 - January 3, 2008 | Party#1 | Amy's House
> > > > >
> > > > >
> > > > > How do I achieve this?
> > > > > Thank you for your help in advance!
> > > > >

It asks for Start date, end date, location etc. it basically goes through all
of them...

"KARL DEWEY" wrote:

> What parameter is it asking for?
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "omshanti" wrote:
>
> > Hi Karl-
> > Thanks for your suggestions, but the formula you suggested is asking me for
> > a parameter value. There are so many people that need to access this report,
> > it would be easier if they could just select a report and not have to enter
> > parameter values. Is there another way I can do this?
> >
> > Thanks!
> >
> > "KARL DEWEY" wrote:
> >
> > > Try using two more queries - Select query named OtherDates and union query.
> > > SELECT [event names], [locations], [dates] As Event_Start, [dates] As
> > > Event_End
> > > FROM YourTable INNER JOIN [TotalsQuery] ON [YourTable].[event names] =
> > > [TotalsQuery].[event names] And [YourTable].[locations] =
> > > [TotalsQuery].[locations]
> > > WHERE [dates] Not Between [TotalsQuery].[Event_Start] And
> > > [TotalsQuery].[Event_End];
> > >
> > > SELECT [TotalsQuery].[event names], [TotalsQuery].[locations],
> > > [TotalsQuery].[Event_Start], [TotalsQuery].[Event_End]
> > > FROM [TotalsQuery]
> > > UNION SELECT [OtherDates].[event names], [OtherDates].[locations],
> > > [OtherDates].[Event_Start], [OtherDates].[Event_End]
> > > FROM [OtherDates];
> > >
> > > --
> > > KARL DEWEY
> > > Build a little - Test a little
> > >
> > >
> > > "omshanti" wrote:
> > >
> > > > Thanks Karl. You're awesome!! I have one more issue to deal with in the same
> > > > report. Your answer definitely has me in the right direction. Now I have an
> > > > issue of having the same exact events and locations but later in the year.
> > > > Using the qry you advised it now captures the date range for all the dates
> > > > with that specific event name and location.
> > > >
> > > > The report is now showing :
> > > > January 1, 2008 - March 8, 2008 | Party#1 | Amy's House
> > > >
> > > > I would like the report to show this:
> > > > January 1, 2008 - January 3, 2008 |Party #1| Amy's House
> > > > March 8, 2008 |Party #1 | Amy's House
> > > >
> > > > Thanks again!
> > > >
> > > >
> > > >
> > > >
> > > > "KARL DEWEY" wrote:
> > > >
> > > > > Use a totals query like this --
> > > > > SELECT [event names], [locations], Min([dates]) As Event_Start, Max([dates])
> > > > > As Event_End
> > > > > FROM YourTable
> > > > > GROUP BY [event names], [locations];
> > > > > --
> > > > > KARL DEWEY
> > > > > Build a little - Test a little
> > > > >
> > > > >
> > > > > "omshanti" wrote:
> > > > >
> > > > > > I have a table of event names ,dates and locations. I can run a report that
> > > > > > shows all these fields; but some events run 2-3 days long and I need to be
> > > > > > able to show the event name, location and date range in one line instead of
> > > > > > multiple rows.
> > > > > >
> > > > > > For Example, I now have a report that shows this:
> > > > > > January 1, 2008 |Party #1| Amy's House
> > > > > > January 2, 2008 |Party #1| Amy's House
> > > > > > January 3, 2008 |Party #1| Amy's House
> > > > > >
> > > > > > And want a report to show this:
> > > > > > January 1, 2008 - January 3, 2008 | Party#1 | Amy's House
> > > > > >
> > > > > >
> > > > > > How do I achieve this?
> > > > > > Thank you for your help in advance!
> > > > > >

I'm really close to the report I need. The biggest issue with how far we've
gotten is that if the event is only on one day it shows start and end dates
as the same date. Is there a way to say if start and end date are the same,
then don't show the end date?

Thanks!

"omshanti" wrote:

> It asks for Start date, end date, location etc. it basically goes through all
> of them...
>
> "KARL DEWEY" wrote:
>
> > What parameter is it asking for?
> > --
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "omshanti" wrote:
> >
> > > Hi Karl-
> > > Thanks for your suggestions, but the formula you suggested is asking me for
> > > a parameter value. There are so many people that need to access this report,
> > > it would be easier if they could just select a report and not have to enter
> > > parameter values. Is there another way I can do this?
> > >
> > > Thanks!
> > >
> > > "KARL DEWEY" wrote:
> > >
> > > > Try using two more queries - Select query named OtherDates and union query.
> > > > SELECT [event names], [locations], [dates] As Event_Start, [dates] As
> > > > Event_End
> > > > FROM YourTable INNER JOIN [TotalsQuery] ON [YourTable].[event names] =
> > > > [TotalsQuery].[event names] And [YourTable].[locations] =
> > > > [TotalsQuery].[locations]
> > > > WHERE [dates] Not Between [TotalsQuery].[Event_Start] And
> > > > [TotalsQuery].[Event_End];
> > > >
> > > > SELECT [TotalsQuery].[event names], [TotalsQuery].[locations],
> > > > [TotalsQuery].[Event_Start], [TotalsQuery].[Event_End]
> > > > FROM [TotalsQuery]
> > > > UNION SELECT [OtherDates].[event names], [OtherDates].[locations],
> > > > [OtherDates].[Event_Start], [OtherDates].[Event_End]
> > > > FROM [OtherDates];
> > > >
> > > > --
> > > > KARL DEWEY
> > > > Build a little - Test a little
> > > >
> > > >
> > > > "omshanti" wrote:
> > > >
> > > > > Thanks Karl. You're awesome!! I have one more issue to deal with in the same
> > > > > report. Your answer definitely has me in the right direction. Now I have an
> > > > > issue of having the same exact events and locations but later in the year.
> > > > > Using the qry you advised it now captures the date range for all the dates
> > > > > with that specific event name and location.
> > > > >
> > > > > The report is now showing :
> > > > > January 1, 2008 - March 8, 2008 | Party#1 | Amy's House
> > > > >
> > > > > I would like the report to show this:
> > > > > January 1, 2008 - January 3, 2008 |Party #1| Amy's House
> > > > > March 8, 2008 |Party #1 | Amy's House
> > > > >
> > > > > Thanks again!
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "KARL DEWEY" wrote:
> > > > >
> > > > > > Use a totals query like this --
> > > > > > SELECT [event names], [locations], Min([dates]) As Event_Start, Max([dates])
> > > > > > As Event_End
> > > > > > FROM YourTable
> > > > > > GROUP BY [event names], [locations];
> > > > > > --
> > > > > > KARL DEWEY
> > > > > > Build a little - Test a little
> > > > > >
> > > > > >
> > > > > > "omshanti" wrote:
> > > > > >
> > > > > > > I have a table of event names ,dates and locations. I can run a report that
> > > > > > > shows all these fields; but some events run 2-3 days long and I need to be
> > > > > > > able to show the event name, location and date range in one line instead of
> > > > > > > multiple rows.
> > > > > > >
> > > > > > > For Example, I now have a report that shows this:
> > > > > > > January 1, 2008 |Party #1| Amy's House
> > > > > > > January 2, 2008 |Party #1| Amy's House
> > > > > > > January 3, 2008 |Party #1| Amy's House
> > > > > > >
> > > > > > > And want a report to show this:
> > > > > > > January 1, 2008 - January 3, 2008 | Party#1 | Amy's House
> > > > > > >
> > > > > > >
> > > > > > > How do I achieve this?
> > > > > > > Thank you for your help in advance!
> > > > > > >