By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,606 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

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

jonnycakes
P: 24
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.
Jun 27 '12 #1

✓ answered by Rabbit

Something like this.
Expand|Select|Wrap|Line Numbers
  1. SELECT ...
  2. FROM aTable
  3. INNER JOIN bTable
  4. ON aTable.ID = bTable.ID
  5.    AND aTable.someDate BETWEEN bTable.startDate AND bTable.endDate

Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,315
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.
Jun 27 '12 #2

jonnycakes
P: 24
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.
Jun 27 '12 #3

Rabbit
Expert Mod 10K+
P: 12,315
Something like this.
Expand|Select|Wrap|Line Numbers
  1. SELECT ...
  2. FROM aTable
  3. INNER JOIN bTable
  4. ON aTable.ID = bTable.ID
  5.    AND aTable.someDate BETWEEN bTable.startDate AND bTable.endDate
Jun 27 '12 #4

jonnycakes
P: 24
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?
Jun 27 '12 #5

Rabbit
Expert Mod 10K+
P: 12,315
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.
Jun 28 '12 #6

jonnycakes
P: 24
I love you. Thanks again.
Jun 28 '12 #7

Rabbit
Expert Mod 10K+
P: 12,315
No problem, good luck.
Jun 28 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.