Return a value that is based on a range between records

[verizonrep]

I am trying make a query that takes order data for reps and adds their program "PlanID" based on their start date. The challenge has been that they can change programs mid year, so I need it to reflect the correct plan with the correct month. Here's an example (note that there are really 500,000 lines of data carrying multiple salespeople). The data is in a "Bookings_Tbl" and the employees plan is in a "Positions_Tbl". Note that all months (both PayMonth and StartMonth are as of the 1st.

Bookings_Tbl
OrderNumber EmployeeID PayMonth
O54687 11111 1/1/2005
O65487 22222 1/1/2005
O879461 33333 1/1/2005
(list goes on for 500,000 records for months 1-6 of this year).

Position_Tbl
EmployeeID StartMonth PlanID
11111 1/1/2005 5001
22222 1/1/2005 5001
33333 1/1/2005 5001
22222 3/1/2005 3001


I want to run the query against orders and have it return the proper PlanID. This has a order number, process month, and employee number (in order to keep this short, I'll just focus on data for rep 22222 since he was the only one with a change).

Order ProcessMonth EmployeeID PlanID
O12345 1/1/2005 22222 5001
O45677 2/1/2005 22222 5001
O46884 3/1/2005 22222 3001

Anyone have any ideas? This one has given me nothing but problems for quite a while. I've tried to use Domain Aggregate functions, but I don't know how to do this based on two fields (EmployeeID and ProcessMonth).

Thanks

Verizonrep

 

[markgrizzle]

Add both tables in a new query grid, then drag the connecting fields from one table to the matching field in the second.

In this case, you'd have a join line connecting bookings_tbl.EmployeeID to Position_tbl.EmployeeID, as well as a join between Bookings_tbl.PayMonth and Position_tbl.StartMonth

Bookings
Position --------
-------- OrderNumber
EmployeeID------EmployeeID
StartMonth------PayMonth

Hopefully that displayed properly on your monitor.
PlanID

 

[OhioSteve]

Well, first we ought to define the problem clearly. In your position table, I get the impression that on a certain month an employee can CHANGE plans. So this is possible:

EmployeeID StartMonth PlanID
1 1/2000 5001
2 5/2000 5001
1 2/2005 5002

I am also assuming that an employee has one and only one discount plan.

You want to answer a question like "If Joe Public bought a phone plan from employee 1 in Jan 2005, then what was the discount plan?"

Once we define the question we can develop a strategy for fixing the problem.

 

[verizonrep]

I'm not sure what you mean by "discount plan". Basically, the PlanID is what compensation payment plan that person is on. In the beginning of the year, they can be on one plan (in the example above, you show that employee 1 started on plan 5001 on 1/2000, but changed to 5002 on 2/2005). Using your example, I'm trying to get it so that all information in the orders file for 2000 would show that employee 1 was on the 5001 plan and all the orders from 2/2005 would be tagged with the 5002 plan number.

Thanks,

Verizonrep.

 

[PHV]

A starting point:
SELECT B.OrderNumber, B.EmployeeID, B.PayMonth, P.PlanID
FROM Bookings_Tbl AS B INNER JOIN Position_Tbl AS P ON B.EmployeeID=P.EmployeeID
WHERE P.StartMonth = (SELECT Max(StartMonth) FROM Position_Tbl WHERE EmployeeID=B.EmployeeID AND StartMonth<=B.PayMonth);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OhioSteve]

You need to normalize your database design.

currently, you have something like this design:

employee table
employeeID (pk)
[other fields]

plan table
planID (pk)
[other fields]

order table
orderID (pk)
[other fields]

You need something like this design~

employee table
employeeID (pk)
[other fields]

plan table
planID (pk)
[other fields]

order table
orderID (pk)
planID
employeeID
[other fields]

junction table (compound pk)
employeeID (pk)
planID (pk)
startDate

There are a number of advantages to a normalized design. You will make alot fewer visits to these boards, because your design will work properly. Queries will be easy to write, and it will be much easier to keep accurate records. Plus, the db will run queries much faster. In the short term, you would get an immediate benefit...planID would be in the same table as orderID.

I'm suspect that PHV is aware that your design is flawed. He is very skilled with sql, and he might be able to write a monster query that overcomes these systemic problems on this particular occasion. But you will just need to write more monster queries later.

You might adopt a hybrid approach to the problem. You could write a monster query to fix this particular issue. But then you could deal with the systemic issues over several weeks.

 

[verizonrep]

Yep...I wish we could get an order table with the plan ID in it. Unfortunately, it doesn't have that information from the source. The DB we are trying to develop is supposed to make that reference.

 

[OhioSteve]

You mentioned that "it doesn't have that information from the source". Is the "source" a legacy system that will go away after your db is completed? Or will you have to deal with this data source indefinetely? What is the table structure of the data source? Maybe you could provide a list of the fields in each table, their data types, and which fields form the pk.

I know that I keep asking questions. I don't want to frustrate you but I want to give really acurate answers. I have gotten alot of help in the more advanced forums, and I'm more than willing to pay it forward.

 

[PHV]

verizonrep, just to know, have you tried my suggestion ?

 

[verizonrep]

Hi PHV,

Sorry I did not get back to you earlier. The company needed me to run off to the east coast for a few days (one of the querks of the job).

I gave it a try, but the data just returns the first value that person has. For example, I have a person who was a position 5001 in January and a 3001 in February. All of his data for the year showed that he was a 5001. It didn't reflect the 3001 position.

I also notice that (as with any of these types of queries)it becomes REALLY slow. The query takes about 3 minutes. I wish they would build some kind of wizard function in Access to build something like this. I can't be the only one who is doing something like this.