Update Query/Sql; populate data that depends on date field between variable range

[jonnycakes]

I had a terrible time coming up with a title for this question and an even worse time trying to solve my issue. I'm here seeking guidance yet again from the masters.

Let me outline the two data tables i'm working with and then i'll present you with my issue.

Data Table 1: "tblOffer"
tblOffer consists of close to 1 million records. In each of these records you will find an employee name, employee ID, type of offer, events (accepted offer, declined offer, & about 20 others), event date, & Group(this is my main problem as it's currently null)

Example of tblOffer:

[emp_name]|[emp_id]| [evt] | [evt_dt] |[Group]
Emp1 | qwerty | Declined | 01/02/12 | null
Emp2 | asdfg | Offer | 01/03/11 | null
Emp1 | qwerty | Approved | 05/02/12 | null
Emp3 | zxcvb | Accepted | 07/03/10 | null
Emp1 | qwerty | Counter | 06/15/12 | null

Table 2: "tblEmpInfo "
tblEmpInfo consists of several fields that describe the employee. Fields such as: Employee ID, Employee Name, Group (a, b, c, d, etc) Group End date(the date the employee left the group). Its important to know that employees often leave one group and move into another.

Example of tblEmpInfo:
[emp_name]|[emp_id]|[group]|[group_end_dt]
Emp1 | qwerty | a | 01/11/11
Emp1 | qwerty | b | 04/17/12
Emp1 | qwerty | a | 05/11/12
Emp1 | qwerty | d | Null (current group)

So, you may have guessed what i'm trying to do, but in case you haven't.. i'm trying to update the field "Group" in tblOffers with the group that the employee belonged to when the event occured.

Example of what i'm trying to do:

[emp_name]|[emp_id]| [evt] | [evt_dt] |[Group]
Emp1 | qwerty | Declined | 01/02/12 | b
Emp1 | qwerty | Approved | 05/02/12 | a
Emp1 | qwerty | Counter | 06/15/12 | d

Groups will not overlap, there is no min or max number of groups per employee, and every active employee will have a null group_end_date.

I only have access to MS Access 2000 & Excel 2007(nice huh?) with a basic understanding of SQL.

I tried very hard to provide everything you need. if i've failed to do so, please feel free to ask me any questions you have.

Thank you in advance for any guidance/solution that you may be able to provide.

[Rabbit]

You should have put in a group start date, then you can join the tables using the BETWEEN operator. As it is, you're missing that information so you'll have to calculate it. You can do it by joining the table to itself or you can use a subquery in the SELECT clause. The first option is probably faster.

[jonnycakes]

Rabbit, thank you for your reply! The good news is that as it turns out I do have a start date :). Of course I'm not expecting you to just tell me the answer, but could you expand on how I would go about the between statement?

This is the BEST forum out there IMHO.

[Rabbit]

Something like this.

SELECT ...
FROM aTable
INNER JOIN bTable
ON aTable.ID = bTable.ID
   AND aTable.someDate BETWEEN bTable.startDate AND bTable.endDate

[jonnycakes]

Outstanding, i've made great progress, thank you! I'm not sure why, but when using the between operator in my access query I recieved an error message mentioning something like "expression missing and operator".

I got around this by replacing the "between" and "AND" with >= & <=, but this doesn't seem to work completely as I'm left with thousands of records that weren't updated. I believe this is due to the null end date for the current group the employee belongs to.

Any ideas?

[Rabbit]

Use the Nz() function to convert the nulls to an arbitrarily large date. Or use on OR for those where it's greater than the start and the end is null.

[jonnycakes]

I love you. Thanks again.

[Rabbit]

No problem, good luck.