Convert Date Range to Date List

[IAmTodd]

I have a table of data is formatted as follows:

Acct#, Admit Date, Discharge Date
1 14/1/2010 16/1/2010

I am comparing it to another table that lists out dates individually. Is there a way to convert the first table via a query to list the dates as a single list instead of a date range? Something like this:

Acct# , Date of Service
1 14/1/2010
1 15/1/2010
1 16/1/2010


Best regards,

 

[datAdrenaline]

Yes ...

First ... create a query of contiguous dates. There are a couple of ways to do this. One method can be found by clicking here. Or from a more generic mindset of always needing a series of contiguous values for something, I will often create a single table in my db's called tblNumbers. It only has one field named "Num" and that field is tagged as the Primary Key, plus I set a validation rule that ensures the value of "Num" is < 10. The table only has ten records: 0 through 9. Now to use that table to create a contiguous set of dates you would create a Query object with a SQL statement that looks something like this:

SELECT DateAdd("d",[TheNumber],#2000-1-1#) AS TheDate
FROM (SELECT vTbl1.Num * 10000 + vTbl2.Num * 1000 +  vTbl3.Num * 100 + vTbl4.Num * 10 + vTbl5.Num As TheNumber FROM
(SELECT Num FROM tblNumbers) As vTbl1,
(SELECT Num FROM tblNumbers) As vTbl2,
(SELECT Num FROM tblNumbers) As vTbl3,
(SELECT Num FROM tblNumbers) As vTbl4,
(SELECT Num FROM tblNumbers) As vTbl5
) AS vCross
ORDER BY vCross.TheNumber;

The query will return a span of dates starting from 2000-1-1 to 2273-10-15. You can limit the range by including a WHERE clause, or by adjusting the structure of the CROSS join of the FROM clause and the Expression, or if you can adjust the literal start date to something more appropriate for your needs.

We'll call the above contiguous date query selContiguousDates. NEXT, what we want to do is JOIN the contiguous date query with your table with the date range. We can do the JOIN in two ways. One option is called a "Theta JOIN". The other is called an ANSI JOIN, which is the typical way see things JOINed in a Query object. The ANSI JOIN will be based on an expression that can not be represented in the Access query designer, thus forcing you to create the Query in SQL view. The Theta JOIN method can be represented in the Query designer since the "JOIN" is actually a criteria setting in the WHERE clause. In my experience, I have found Theta joins to be slower, so my preference if for the ANSI JOIN technique --- but you will likely want to try both methods to see which one produces the results in the timeliest manner.

So with those descriptions, here is the actual SQL statements of each technique (I will assume your admit date table is named tblAdmitDates):

ANSI JOIN:

SELECT [Acct#], TheDate As ServiceDate
FROM tblAdmitDates
     INNER JOIN selContiguousDates
     ON (selContiguousDates.TheDate
         BETWEEN tblAdmitDates.[Admit Date] And tblAdmitDates.[Discharge Date])
ORDER BY [Acct#], TheDate

THETA JOIN:

SELECT [Acct#], TheDate As ServiceDate
FROM tblAdmitDates, selContiguousDates
WHERE TheDate BETWEEN [Admit Date] And [Discharge Date]
ORDER BY [Acct#], TheDate

-----

Either technique will produce the output you desire.

Hope that helps!

 

[IAmTodd]

Thanks for the suggestions. Between me posting and you posting I figured out the Theta Join but I just tried the ANSI and it works great, much faster.

Again, thanks for the help and insight.