If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Return multiple records from a single entry.
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] |
#3
|
|||
|
|||
Return multiple records from a single entry.
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. |
Thread Tools | |
Display Modes | |
|
|