How to show a date range in a report

[omshanti]

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!

 

[KARL DEWEY]

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];

 

[omshanti]

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!

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

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!

 

[KARL DEWEY]

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

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!

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

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!

 

[omshanti]

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!

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

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!

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


:

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!

 

[KARL DEWEY]

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

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!

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

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!




:

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


:

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!

 

[omshanti]

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

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

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!

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


:

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!




:

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


:

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!

 

[omshanti]

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!

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

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

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!

:

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


:

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!




:

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


:

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!

 

[omshanti]

Karl- I've gotten so far with your help ). The biggest issue now is to not
show the end date if it's the same as the start date (when the event is only
one day). How can we write the command that says if the start and end date
are the same, don't show it?
Thanks!

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

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

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!

:

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


:

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!




:

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


:

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!