Access2000: Query help

[Arvi Laanemets]

Hi

I have 2 tables
Calendar: CalDay, ....
(CalDay is a date, and is Primary Key in table. At moment the table contains
all dates in interval 29.12.2003 - 03.01.2011)

Employees: TabN, Depatrment, From, To, ...
(Primary Key is Employee=TabN+From. From and To mark date intrerval, the
given record is valid for employee. I.e. whenever there is some change in
employees status, an additional record for this employee is added. Unless
the employee leaved the company, the To field on latest (ie.e. current)
entry remains empty, for all other entries it must be Not Null. And no
overlaying time intervals are allowed for same employee.)

Now I need a query (CalDay, TabN, Department), which returns for every
employee in every department a row for every date in every month until
current one, this employee pertained to this department. Quite a messy
explanation, so here are some examples.

TabN=111, Department=11, From=21.07.2005, To=30.07.2005
for this employee the query must return 31 records (01.07.2005 through
31.07.2005)

TabN=222, Department=11, From=05.09.2005, To=Null
for ths employee the query must return 61 records (01.09.2005 through
31.10.2005)

TabN=333, Department=11, From=01.09.2005, To=20.09.2005
TabN=333, Department=12, From=21.09.2005, To=Null
for this employee the query must return 91 records (01.09.2005 through
30.09.2005, with Department=11, and 01.09.2005 through 31.10.2005 with
Department=12)

TabN=444, Department=12, From=01.09.2005, To=20.09.2005
TabN=444, Department=12, From=21.09.2005, To=Null (there was some other
change, p.e. occupation, department remaining same)
for this employee the query must return 61 records (01.09.2005 through
31.10.2005)

Somehow I'm out of ideas. Thanks in advance for your help!

 

[Baz]

Hi

I have 2 tables
Calendar: CalDay, ....
(CalDay is a date, and is Primary Key in table. At moment the table contains
all dates in interval 29.12.2003 - 03.01.2011)

Employees: TabN, Depatrment, From, To, ...
(Primary Key is Employee=TabN+From. From and To mark date intrerval, the
given record is valid for employee. I.e. whenever there is some change in
employees status, an additional record for this employee is added. Unless
the employee leaved the company, the To field on latest (ie.e. current)
entry remains empty, for all other entries it must be Not Null. And no
overlaying time intervals are allowed for same employee.)

Now I need a query (CalDay, TabN, Department), which returns for every
employee in every department a row for every date in every month until
current one, this employee pertained to this department. Quite a messy
explanation, so here are some examples.

TabN=111, Department=11, From=21.07.2005, To=30.07.2005
for this employee the query must return 31 records (01.07.2005 through
31.07.2005)

TabN=222, Department=11, From=05.09.2005, To=Null
for ths employee the query must return 61 records (01.09.2005 through
31.10.2005)

TabN=333, Department=11, From=01.09.2005, To=20.09.2005
TabN=333, Department=12, From=21.09.2005, To=Null
for this employee the query must return 91 records (01.09.2005 through
30.09.2005, with Department=11, and 01.09.2005 through 31.10.2005 with
Department=12)

TabN=444, Department=12, From=01.09.2005, To=20.09.2005
TabN=444, Department=12, From=21.09.2005, To=Null (there was some other
change, p.e. occupation, department remaining same)
for this employee the query must return 61 records (01.09.2005 through
31.10.2005)

Somehow I'm out of ideas. Thanks in advance for your help!

Here's my stab at it. Let us know if it works!

SELECT DISTINCT CalDay, TabN, Department FROM Employees E INNER JOIN
Calendar C ON (C.CalDay >= DateSerial(Year(E.from),Month(E.From),1) AND
C.CalDay <=
DateAdd("d",-1,DateSerial(Year(Nz(E.to,Date())),Month(Nz(E.to,Date()))+1,1))
)

 

[Arvi Laanemets]

Hi

I´ll give it a try tomorrow at work. Thanks anyway!


Arvi Laanemets

 

[Arvi Laanemets]

Hi


It worked exactly as needed. Btw., I modified the query slightly:
SELECT DISTINCT CalDay, TabN, Department FROM Employees AS e INNER JOIN
Calendar AS c ON c.CalDay >= DateSerial(Year(e.from),Month(e.From),1) AND
c.CalDay <=DateSerial(Year(Nz(e.to,Date())),Month(Nz(e.to,Date()))+1,0);