Re: A real challenge for someone out there? Please help!!!!!!!
- From: strive4peace <"strive4peace2006 at yahoo dot com">
- Date: Tue, 23 May 2006 23:35:06 -0400
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 structure:
*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?
- References:
- A real challenge for someone out there? Please help!!!!!!!
- From: PaulStandere
- A real challenge for someone out there? Please help!!!!!!!
- Prev by Date: Re: 2 primary keys?
- Next by Date: Six of one, half dozen?
- Previous by thread: A real challenge for someone out there? Please help!!!!!!!
- Next by thread: Re: How do you put a validation rule for ID numbers in Access?
- Index(es):
Relevant Pages
|
