A Real Challenge...help Me Think Outside Box Please!!

BACKGROUND

The current challenge I have in MS Access in really giving me a headache. I can’t seem to find an answer or indeed think “outside the box” so I am hoping there is someone who can help.

Below I will outline the design of my database, the problem I have and my proposed solution. My solution is “What I want to do” rather than “How I do it”. If you have the time and patience to look at my problem and suggest how I proceed, I will be really grateful.

CURRENT DATABASE DESIGN

I created a database that imports daily telephone data. The design below is a simplified version of the real thing but it contains the essential information needed to understand my database.

I extract data from the phone system for “Lines” (3 digit code) e.g. ‘301’,’302’ which each have a corresponding line “Description” e.g. ‘New Customers’, ‘Accounts Queries’. There are three main daily extracts (1) Inbound Calls (2) Outbound Calls and (3) Time. Each extract has either “Line” or “Description” as the unique identifier but not both.

There are 50 lines with matching descriptions and each is allocated to one of 20 teams. I have a query which links all three extracts, groups the data by team and date. Therefore this query creates 20 records (teams) for each day (date).

CURRENT QUERY AND TABLES

Tbl Line Lookup (50 records)
Line
Description
Team

Tbl Inbound Calls
Line
Date
Offered Calls
Answered Calls
Abandoned Calls

Tbl Outbound Calls
Description
Date
Outgoing Calls

Tbl Time
Description
Date
Answer Time
Abandon Time
Talk Time
Wrap Up Time

Qry Grouped By Team Daily

Line – linked to line in Tbl Incoming Calls
Description – linked to description in both Tbl Outgoing Calls and Tbl Time

Team – Grouped field
Date – Grouped filed
Offered Calls - Sum
Answered Calls - Sum
Abandoned Calls - Sum
Outgoing Calls - Sum
Answer Time - Sum
Abandon Time - Sum
Talk Time - Sum
Wrap Up Time - Sum

PROBLEM

Customer Services “Team” is made up of six lines (301-306) and Business Partners “Team” is made up of three lines (307-309). For each day my query creates a record for each of these teams based on how the line is allocated in Tbl Line Lookup.

The business had decided that line 304 calls are part of the Business Partners with effect from 01/05/06. If I change the team name associated with 304 in Tbl Line Lookup this will, incorrectly, allocate all information (from 01/01/06 to present) to Business Partners.

I need a mechanism to allocate 304 to Customer Services prior to 30/04/06 and to Business Partners after 01/05/06.

PROPOSED SOLUTION

I want to introduce a new field in Tbl Line Lookup called Effective Date.

Tbl Line Lookup (50 records)
Line
Description
Team
Effective Date

Then I want to introduce a new query which links the data in the three data tables and allocates the correct team name by comparing the date in these extracts with the effective date in the Tbl Line Lookup.

I would then run my original query (Qry Grouped By Team Daily) over this query instead of the original tables.

Please can someone tell me if it is possible to look up values in another table to determine the value of a field in the way I have described?