Inherited Database - Need Help

[OU18]

Hello all, this may get lengthy, but bare with me.

I have inherited an Access 2000 Database that needs alot of scrubbing

What I have is 1 Massive table that contains our companies circuit inventory. The problem is that one circuit can have multiple row entries associated to it. Each of the muliple Row entries have associated circuits to it also. I have been tasked with cleaning up this database and combining the multiple rows into one row of information without loosing anything.

The final product will end up being uploaded into another application that will better handle this inventory. Remedy ARS.

Here is the way the data looks currently, there are 5 Associated Circuit ID Fields total, but for space constraints I have only displayed 2


ID Circuit ID AssocCktID1 AssocCktId2 AssocCktID2
8520 Circuit 1 CircuitA CircuitB
8519 Circuit 2 Circuit 1 CircuitC CircuitD
4946 Circuit 3 Circuit 2 Circuit1 CircuitE
3152 Circuit 4 Circuit 1 Circuit2 CircuitF

What I need is everything in one line item as follows

ID Circuit ID AssocCktID1 AssocCktId2 AssocCktID3
8520 Circuit 1 Circuit2 Circuit3 Circuit4

but I can also not loose Circuit A, B, C, D, E and F

This is really difficult to explain. There are some 6500 records that need to be combined. I really do not want to perform this task manually.

Anyone that feels they can help in solving this problem, I will be happy to email an actual sample of the data to.

Thanks for everyones help
Steve

 

[jebry]

Hi Steve!

The first thing I would do would be to copy the database so you are not working on the original. Next, in the copy, select Tools/Analyze/Table and walk your way through the wizard. Hopefully, that would solve some of the difficulties.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[OU18]

Jeff,

Thanks for the insight, that definitely has helped with some of it. I think I still have some issues, but they maybe more of data cleanup then anything.

Thanks for embarassingly easy tip.

Steve