How to show a date range in a report

  • Thread starter Thread starter omshanti
  • Start date Start date
O

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!
 
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];
 
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 said:
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 said:
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!
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
What parameter is it asking for?
--
KARL DEWEY
Build a little - Test a little


omshanti said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
It asks for Start date, end date, location etc. it basically goes through all
of them...

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


omshanti said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
It asks for Start date, end date, location etc. it basically goes through all
of them...

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


omshanti said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Karl- I've gotten so far with your help :o). 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!

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

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


omshanti said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top