A real challenge...help me think outside box please!!

[Paulstandere]

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?

 

[KeithG]

Can you post your db? I am sure this can be done but it is hard to think about without the database in front of me.

 

[FoFa]

We basically do that all the time. Typically our lookup table will have an END DATE associated with a row. This END DATE, if populated stops it from being a selection in the lists, but maintains history as well. There is usually some date associated with the records that keep the FK that points to this lookup, and queries select based on that date.

 

[Paulstandere]

Keith,

Thanks for the offer of help. I will create a simplier version of my db (due to size issues) and post it shortly.

The field names will be different because I tried to make the example in my original post easier to understand. (Unfortunately this is the first time I have gone to any forum for help so (1) I didn't know if "simplification" was a good idea or not) and (2) I didn't think to post the database.

Bear with me and the db will be posted

 

[Paulstandere]

Here Is The Database

Keith,

As I suggested this is a little more complicated than I described in my original post. However it isn't over complicated and the same logic is there.

Line - is called SplitSkill and will be a three digit number
Description - can be called Splitskill or Description and will be a text field

Qry Grouped By Team Daily - This is really query Qry SplitSkillMasterDaily. Open this is design view to understand the relationships between the three data extracts (which are actually 3 queries based 3 tables)

I need a query before Qry SplitSkillMasterDaily does the grouping to resolve my problem.

Thanks

 

[Paulstandere]

FoFa...more detail please if possible

Fofa,

Sounds like you know what I mean and how to solve. could you give me detial about how you actually achieve this.

What do you do in the query to make it return the right value?