cleaning up duplicative data table

  • Thread starter james_keegan via AccessMonster.com
  • Start date
J

james_keegan via AccessMonster.com

Hey folks: Stumped again!

I have a data table from my phone switch that looks like this

dn, name, type, keynum, fcode, param
2000, Sally Smith, m2006, 0, scn, 2000
2000, Sally Smith, m2006, 1, trn,
2000, Sally Smith, m2006, 2, cnf,
2000, Sally Smith, m2006, 3, adl, 5252
2001, Joe Schmoe, m2008, 0, scn, 2001
2001, Joe Schmoe, m2008, 1, cnf

What I'm trying to do is to make this into a table that I can index on dn
(dialed number, aka 'extension) so that it looks like this:

dn, name, type, key0code, key0param, key1code, key2code, key2param, ...
key15param
2000, Sally Smith, m2006, scn, 2000, trn, [null],cnf,[null], adl, 5252
2001, Joe Schmoe, m2008, scn, 2001, cnf, [null]

with only one record for each extension.

Is this something I can do without looping through the recordset with VB, and
doing a whole bunch of ifs and creating the records for the new table one by
one? I think I could do this with VB, but I was hoping there was a simpler
way!

Any clues or suggestions?
Or should I abandon the idea, and simply put the keycodes in a 'sub table'
linked by [dn] on a one-to-many basis?

jk
 
J

John Spencer

Its a bit difficult to say what your table structure should be without
knowing more details on waht the elements represent.

I would probably design this as one table as you have it now unless DN,
Name, and Type are always the same. Does DN 2000 always equate to Sally
Smith and m2006? and the same for all DN? In that case you probably
want two tables. Table one with DN, Name, and Type and Table two with
DN, Keynum, Fcode, and Param.

You can come close to building what you want with a crosstab query if
you will accept FCode as column Names and Param as the value.

TRANSFORM First(Param) as PValue
SELECT Dn, Name, Type
FROM [YourTable]
GROUP BY Dn, Name, Type
Pivot FCode

That would return the fields (note that Keycode is missing)
Dn, Name, Type, Adl, Cnf, Scn, Trn


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Top