Access Query to list all dates between start and end date 1

[DrSmyth]

Hi,

I've got three columns of data in my table:
User, Start Date, End Date.

I want to run a query that will list the name and all the individual dates between the start and end dates (All dates in English Format).

EG:
Table
User Start_Date End_Date
Andy 01/01/2004 04/01/2004
Dave 06/08/2004 08/08/2004
Andy 21/10/2004 23/10/2004

Query should show
User Date
Andy 01/01/2004
Andy 02/01/2004
Andy 03/01/2004
Andy 04/01/2004
Andy 21/10/2004
Andy 22/10/2004
Andy 23/10/2004
Dave 06/08/2004
Dave 07/08/2004
Dave 08/08/2004

Can anybody help???

Doc

 

[Crowley16]

I don't think it's possible to do that using only sql...

what you'd need to do is to loop through the start and end dates, and then add 1 to each date, using date serial function, and then display those results...

probably the easiest way is to create a temporary table, and fill it with loop of some sort...

Procrastinate Now!

 

[tubbers]

Could you maybe expand a bit on the looping process. I find that I need to do almost the same thing and am not familiar with looping.

Thanks.

 

[Crowley16]

erm, how are you with coding?

what you'd have is probably a recordset of some sort, probably opened with an sql statement to select the relevant records...

then using the move commands, you would step through each record in the recordset, and do any commands that are relevant...

for the above example, you would probably have something similar to:

   docmd.runsql("create table tmpTable(fields... ")

   dim tmpDte as Date

   dim rs as dao.recordset
   set rs = currentdb.openrecordset("Select * from tblBlah where ...")
   with rs
         
      [b]do while .eof = false[/b]
            [b]do while tmpDte < .fields("EdDate")[/b]
                  docmd.runsql("insert into tmpTable blah...")
                  tmpDte = DateSerial(Year(tmpDte), Month(tmpDte), Day(tmpDte) + 1)
               loop
            .movenext
         loop
   end with

that's just psudocode, so don't expect cut and paste to work...

Procrastinate Now!

 

[dhookom]

The easiest method is to create a table of all possible dates (tblDates with field TheDate). This is fairly simple using Excel to create your values and then pasting them into an Access table.

SELECT User, TheDate
FROM tblYourTable, tblDates
WHERE TheDate Between StartDate and EndDate;

Note, there are no join lines in this query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[tubbers]

Thanks for the help. My coding capabilities are minimal at best but I managed to solve my first problem using the DateAdd function. I now have another issue which I've started in another post since I don't think it pertains to this question...
Thanks

 

[MichaelRed]

Duane,

Your proposed 'soloution' may be easy for simplistic situations, but it might also engender a large(huge?) recordset of all possible dates (the sample data mey not be representative?) or frequent recourse to Excel to modify / replace the date range.

Further, the proposed query could become quite slow, depending on the sizes of the two recordsets.

MichaelRed

 

[dhookom]

MichaelRed,
20 years of dates would be only 7300 records which isn't much in Access. If the tables are properly indexed, there shouldn't be much noticeable delay in running the query.

I would personally maintain and make additions to the table of dates using code.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

Duane's solution can be slightly modified to use a table of integers instead of dates that would be used to create the list of dates from a starting date using DateAdd(). That is flexible for any range of dates.

It could also be used to create a range of months or whatever.

John

http://www.interautomateconsulting.com

 

[dhookom]

John,
I have done this before with a simple table of 10 records to create 1000s or more. It was a bit beyond what I wanted to present, not knowing DrSmyth's capabilities (or interests).

Anybody heard from the OPs (original posters)? It's been a good discussion thread.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[DrSmyth]

Hi, i'm back, and how this thread has grown. My original post was for a graphically representing a holiday database because i was struggling to do it with the original "Range" Dates.

However, I've solved this now so I suppose I should have let you know...

Soz everybody, but I think that the thread has developed into quite a useful one anyway...

Doc

 

[MichaelRed]

... original post was for a graphically representing a holiday database ...

????


Really? I see nno mention of graphic anywhere in your original post.





MichaelRed

 

[DrSmyth]

It was more for finding an easier way of referencing the dates.... EG it's easier to vlookup a single date than looking up whether a date is between two other dates...

 

[BNPMike]

Duane

I think you need a star there for giving an intesting example of a cartesian cross product. I wouldn't have thought of doing it that way, but your solution at least looks logically correct.

 

[r937]

for other examples of using an integers table to generate data, see thread183-566369 and thread220-749447

rudy

http://r937.com/

SQL Consulting