help with query using start and end dates

[sandbolt]

Hi,
I need to create a query that assigns salesreps to customers billing data.
The assignment table includes:
Customer
Rep
Assignment StartDate
Assignment EndDate

A customer can only have one rep assigned to it at a time.

The sales data includes
Customer
Period (month in which sales occurred)
Amount


I need to create a query that assigns reps to the Customers in the sales data table. However, I need it to recognize the start and end dates for the assignments.

Bob Smith is the rep for ABC Customer from Jan – Mar
Jane Doe is the rep for the same customer starting in April


The sales from Jan – March need to show Bob Smith as the rep.
The sales starting in April need to show Jane Doe as the rep.

Please help.

 

[PHV]

A starting point:
SELECT S.Customer, A.Rep, S.Period, S.Amount
FROM tblSales S INNER JOIN tblAssignment A
ON S.Customer = A.Customer AND S.Period Between A.StartDate And A.EndDate

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

 

[sandbolt]

PHV,
Thanks. It worked when I entered Assignment End Dates for everyone.
Any idea how to make it work if end dates are left blank?

 

[PHV]

SELECT S.Customer, A.Rep, S.Period, S.Amount
FROM tblSales S INNER JOIN tblAssignment A
ON S.Customer = A.Customer AND S.Period Between A.StartDate And Nz(A.EndDate, #12/21/2049#)

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

 

[sandbolt]

Awesome!

Thanks for your help.