A real challenge for someone out there? Please help!!!!!!!

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?

Hi Paul,

You need to solidify your data structure first -- instead of
organizing your data the same way you get it in, make strong
structures and fill in IDs when you convert. Here is a
stronger basic structu

*Lines*
LineID, autonumber
LineCode, text, 3 -- ie: 301
Descrip, text -- ie: New Customers

-- Description and Desc are both reserved words, which is
why I named the field something different, "Descrip"

*LineTeams*
LineTeamID, autonumber
LineID, long integer
TeamID, long integer
EffDate, date

when you join to this table, use LineID. To get the TeamID,
one way is to use criteria to pull the right date:

field -- EffDate
table -- LineTeams
criteria -- = (SELECT MAX(EffDate)
FROM LineTeams as LT
WHERE LT.LineID = Lines.LineID
AND EffDate = Inbound.InDate

where Lines is a fieldlist in the query
LineTeams will also be in the query
and the Inbound table is one of the fieldlists you are
linked to -- since each table has dates for everyday, it
really doesn;t matter which table you use in the subquery as
long as your Inbound, Outbound, and Times tables are also
related on date using Links or criteria.

with this method, the LineTeams table will be limited to
that TeamID you are looking for

*Teams*
TeamID, autonumber
Team

*Inbound*
InboundID, autonumber
LineID, long integer -- fill when you transfer the data
InDate, date -- "Date" by itself is a reserved word
InOffer
InAns
InAban

*Outbound*
OutboundID, autonumber
LineID, long integer -- fill when you transfer the data
OutDate, date
OutCall

*Times*
TimeID, autonumber
LineID, long integer -- fill when you transfer the data
TimeDate
TimeAns
TimeAban
TimeTalk
TimeWrapUp

Is there at least one entry on each date for Inbound,
Outbound, and Times? I have assumed that there is

Don't use spaces in fieldnames. Use long integers to link
your data, not text fields -- it is MUCH more efficient!

~~~~~

here is code you can use to add an ID field to your Import
table so that you can fill out ID's before you transfer
records to your working table.

Sub AddField_ID( _
pTablename As String, _
pFieldname As String)

'Crystal
'strive4peace2007 at yahoo dot com
' 5-22-06

'NEEDS REFERENCE
'Microsoft DAO Library

On Error GoTo Proc_Err

Dim Db As DAO.database
Dim Tdf As DAO.TableDef

Set Db = CurrentDb


Set Tdf = Db.TableDefs([pTablename])

With Tdf
.Fields.Append .CreateField([pFieldname], dbLong)
End With

Db.TableDefs.Refresh
DoEvents

MsgBox pFieldname _
& " has been added to " & pTablename _
, , "Done"

Proc_Exit:
On Error Resume Next
Set Tdf = Nothing
Set Db = Nothing
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " AddIDField"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit

End Sub
'~~~~~~~~~~~



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

PaulStandere wrote:
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?