Return multiple records from a single entry.

I have an access 2000 database which has records showing when activity takes
place throughout the year. Each activity has a start date and an end date so
forinstance the table could look like this.

Event :- Major Race
Specific :- Uk National
Start Date :- 01/01/10
Wnd Date :- 31/01/10

Now I want to query if an event occurs on a particular week commencing
(sunday) for instance 03/01/10.

From my table I can see that the event started on 01/01/10 and finished
31/01/10 so would be applicable for week commencing 03/01/10.

What I need to know is how to return this within a query? Again if I
entered the week commencing as 10/01/10 I would need to see again that this
event was in progress.

I guess it's sort of the opposite of a between query where we look for any
records between two dates. I need to look for all records between two dates
in different fields on the same row.

Please help!!! Thanks in advance.

On Mon, 22 Feb 2010 08:11:01 -0800, Matty G
wrote:

I have an access 2000 database which has records showing when activity takes
place throughout the year. Each activity has a start date and an end date so
forinstance the table could look like this.

Event :- Major Race
Specific :- Uk National
Start Date :- 01/01/10
Wnd Date :- 31/01/10

Now I want to query if an event occurs on a particular week commencing
(sunday) for instance 03/01/10.

From my table I can see that the event started on 01/01/10 and finished
31/01/10 so would be applicable for week commencing 03/01/10.

What I need to know is how to return this within a query? Again if I
entered the week commencing as 10/01/10 I would need to see again that this
event was in progress.

I guess it's sort of the opposite of a between query where we look for any
records between two dates. I need to look for all records between two dates
in different fields on the same row.

Please help!!! Thanks in advance.

You can do this with the help of an auxiliary table. I'll routinely have a
table NUM with one long integer field N, filled with values from 0 to 10000 or
so. You can use the DateAdd function to calculate each date in a range:

SELECT [Event], [Specific], DateAdd("d", N, [Start Date]) AS EventDate
FROM [yourtable], [NUM]
WHERE N DateDiff("d", [Start Date], [End Date]);

Or you can fill a table with the possible dates (for the next ten years or so)
using Excel's Fill Series and copying and pasting into Access, and do the same
kind of join.
--

John W. Vinson JVinson *at* Wysard Of Info *dot* com

--

John W. Vinson [MVP]

Enter in the correct table name for tblDates. This first one has the date
hard coded. Notice that it is the USA mm/dd/yyyy format.

SELECT [Event],
[Specific],
[Start Date],
[Wnd Date],
#2/17/2010# AS TheDate
FROM tblDates
WHERE #2/17/2010# Between [Start Date] And [Wnd Date];

Below is the same thing but will prompt you for a date. You'll need to test
to make sure if you need the dd/mm/yyyy or mm/dd/yyyy format.

PARAMETERS [Enter the Date] DateTime;
SELECT [Event],
[Specific],
[Start Date],
[Wnd Date],
[Enter the Date] AS TheDate
FROM tblDates
WHERE [Enter the Date] Between [Start Date] And [Wnd Date];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Matty G" wrote:

I have an access 2000 database which has records showing when activity takes
place throughout the year. Each activity has a start date and an end date so
forinstance the table could look like this.

Event :- Major Race
Specific :- Uk National
Start Date :- 01/01/10
Wnd Date :- 31/01/10

Now I want to query if an event occurs on a particular week commencing
(sunday) for instance 03/01/10.

From my table I can see that the event started on 01/01/10 and finished
31/01/10 so would be applicable for week commencing 03/01/10.

What I need to know is how to return this within a query? Again if I
entered the week commencing as 10/01/10 I would need to see again that this
event was in progress.

I guess it's sort of the opposite of a between query where we look for any
records between two dates. I need to look for all records between two dates
in different fields on the same row.

Please help!!! Thanks in advance.