Query with dates help

[Carlos]

Hello

i have table A

| Emp | Start | End |
| 100 | 2006-06-01 | 2006-06-03 |

it is possible create a view like this

| Emp | Date |
| 100 | 2006-06-01 |
| 100 | 2006-06-02 |
| 100 | 2006-06-03 |

thank's

 

[John Vinson]

Hello

i have table A

| Emp | Start | End |
| 100 | 2006-06-01 | 2006-06-03 |

it is possible create a view like this

| Emp | Date |
| 100 | 2006-06-01 |
| 100 | 2006-06-02 |
| 100 | 2006-06-03 |

thank's

Yes, it's even possible to include an indication that each date is a
start or end. A UNION query is the trick here; you need to do it in
the SQL window, not the query grid.

SELECT [Emp], [Start] As TheDate {, "Start" As EventType}
FROM tablename
{WHERE [Start] IS NOT NULL}
UNION ALL
SELECT [Emp], [End], {, "End"}
FROM tablename
{WHERE [End] IS NOT NULL}

Leave out the curly brackets, and leave out the expressions in them if
you don't want the EventType field or if you will never have NULL
values in the date fields.

John W. Vinson[MVP]

 

[Guest]

You first need to create an auxiliary Calendar table. This is simply a table
of all dates over a period of time. An easy way to create one is to serially
fill down a column in Excel with dates from 1 January 2006 to 31 December
2015 for instance, and import it into Access as a table with one column
called CalDate say. Then join your existing table to the Calendar table like
so:

SELECT Emp, CalDate
FROM A, Calendar
WHERE CalDate BETWEEN Start AND End
ORDER BY Emp,CalDate;

Or:

SELECT Emp, CalDate
FROM A INNER JOIN Calendar
ON Calendar.CalDate >= A.Start
AND Calendar.CalDate <= A.End
ORDER BY Emp,CalDate;

Ken Sheridan
Stafford, England

 

[Guest]

John:

I think you've misread the OP. That will only give the start and end dates
per Emp value, but not the intermediate dates in each range.

Ken Sheridan
Stafford, England

 

[John Vinson]

John:

I think you've misread the OP. That will only give the start and end dates
per Emp value, but not the intermediate dates in each range.

Thanks Ken... quite correct of course!

John W. Vinson[MVP]

 

[Carlos]

thank's Ken you save my day.

You first need to create an auxiliary Calendar table. This is simply a
table
of all dates over a period of time. An easy way to create one is to
serially
fill down a column in Excel with dates from 1 January 2006 to 31 December
2015 for instance, and import it into Access as a table with one column
called CalDate say. Then join your existing table to the Calendar table
like
so:

SELECT Emp, CalDate
FROM A, Calendar
WHERE CalDate BETWEEN Start AND End
ORDER BY Emp,CalDate;

Or:

SELECT Emp, CalDate
FROM A INNER JOIN Calendar
ON Calendar.CalDate >= A.Start
AND Calendar.CalDate <= A.End
ORDER BY Emp,CalDate;

Ken Sheridan
Stafford, England