Query with dates help

C

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
 
J

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]
 
K

Ken Sheridan

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
 
K

Ken Sheridan

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
 
J

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]
 
C

Carlos

thank's Ken you save my day.

Ken Sheridan said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top