My boss asked me to. . .

[hrenee]

do something that in my heart of hearts I know is not correct for a database but I can't figure out another way to do it.

background:

I have a table with a list of assets with assets IDs attached (these are given from corporate).

I have a new master table in which in many cases the assetID listed above is used numerous times. This is because originally the asset ID's were given out for whole projects rather than individual assets.

I have given corporate a breakdown of each of these things and new assetID will be assigned so that there will be no repeat numbers any more. BUT I want to track which asset they were originally broken out of.

Ex.
29166 Testing Equipment Project 9371 Cost $31,600

New
33333 Color Analyzer Cost $15,000


33334 Texture Analyzer Cost $13,000
29166 Testing Equipment Project 9371 Cost $3,600

My boss says, just add a new field that says orignal asset # or some such thing. The problem is that asset# will still be in use for many many of the items because I havne't been able to completely break it out. That means in a relationship I'd have a circular relationship right? I'd have that Asset ID table going to two different areas and that's all sorts of wrong. . .

Unfortunately I have a complete brain block on how to fix it. Ideas please?

[June7]

Not sure that is an issue. You can have the field for historical info but doesn't mean that field will be used in any relationships.

[hrenee]

Ok, but if I want to know what that original description was wouldn't I need it as a relationship?

So if my boss said, tell me if that color analyzer was broken out of what and what the original relationship was, I'd run a query on whether the original asset ID was not null, but he'd want the original description with that so it woudl have to be linked back to the first table right?

[June7]

That would be for historical review, maybe for audit purposes.

Would the old asset assignment have any association to new asset records?

It is also possible for a table to link to itself in a query.

[hrenee]

Unfortunately due to many handovers of this list it isn't always possible to be sure we've found 100% of the assets in a given original # (I'm an accountant by trade if you couldn't tell). So we tend to keep some "value" in the original # in case we missed something. If at some time we go to take an asset off the books, it would be helpful to know where it came from originally because maybe we need to get rid of that one too even if we aren't 100% positive as to what was originally in there.

But I think what you're saying is true, I can just run a Query that would give me the original descripton by use an if old asset Id = one already on there give me the description right? OR am I making this too difficult?

[June7]

I think you are on correct track.