Req: An Asset -> Fixture relationship problem: how to maintain 2 dependant tables.

[RealClass]

Hi All,

I hope someone has the time and patience to follow this - as I usually make a meal of my problem explanations. The problem is quite simple but the explanation is just a bit waffly.

I have 2 tables: Assets (all assets of a desk - base unit, mouse, keyboard, vdu etc.) - usually lumped together to make a Station - and Fixtures (those little boxes on the wall that connect base
units, telephones etc. onto the network.

My problem is on how to 'manage' the relationships between the two tables. Bearing in mind here that at the moment there is no fixture information held against any of the assets on the Asset table -
it's all held on the Fixtures table. Here is a brief description of where I've been so far...

1. I had a field on the form to which I built a Asset -> Fixture relationship which showed which fixture belonged to the asset. In effect the recordsource for this field was just the relationship. At
this stage my original Fixture table wasn't well defined and didn't have a primary key. So this relationship allowed me to actually make changes to the Fixtures table. This approach worked to an
extent but left records with blank fixture id's when one was removed from an asset.

I really needed a more robust approach to maintaining not just the Fixtures but my plan is to add a Faults table to it.

2. I have since wrote all the VB code to allow me to make all the changes to the Fixtures table - I tightened up the primary key (no duplicates). I then put an unbound filed on the form and hoped to
update it by reading each record as the assets were being built up into their respective Stations. This didn't work well because each value turns out the same - as I have since found that it will with
unbound controls. So the dummy field acting as a conduit to get to my Fixtures table idea didn't work well at all.

That was a shame because a dummy field - not stored anywhere but just used only to display the assets' fixture seemed nice and unobtrusive.

3. I then went back to idea no. 1. and built the relationship back up and stuck it behind a control on the form again, modified the code which looked at records as they were being built up, and hoped
to still use the remaining code to 'manage' the fixtures. Trouble here, now that the Fixtures table is tight and there are no duplicates in the primary key, as soon as I delete the fixture from the
Asset, Jet steps in and says that the relationship is invalid as there would be a record with no key. Point taken - it's right. Trouble is, processing doesn't even load my code so I can't code around
it.

My questions then (amongst many)...

Q1. Do I hold fixture details against the asset 'and' the fixture ie. duplicating the details just in order for me to run all the code myself. Obviously this would in effect just be a 'dummy' field
held aagainst each asset - I would have to ascertain if what the user was doing was valid or not ? This seems such an overhead for each record though.

Q2. Is there a way of displaying a field in a datasheet view but not have it related (and updated) in any way to the original table ? If this is possible I don't know how to do it.

I need to some guidance here please. I'm sorry for the lengthy explanation, but I didn't want to be vague. I feel I'm losing my way a little, can someone bring me back onto the design
straight-and-narrow and point me where I need to go ?

Regards,
Dave

 

[Fred]

Please pardon the directness which is an attempt to be helpful.

Advice is to do these 4 steps:

1. Define your data storage mission and your functionality mission

2. Define the content, nature, organization and real world relationships of
the data to be stored

3. Define a table structure which will support accomplishment of your
missions.

4. Create other items (queries, forms, reports, code etc.) to accomplish
the required functionality.


Your post sort of shows thinking that skips the first three steps. And,
other than your second paragraph, the rest of your posts is describing what
didn't work for step 4 rather that giving the important info.

Things to clarify for yourself (or for us understand) under steps 1 & 2
(and a temporary guess at answers.

Is it your mission to list all assets? Yes
Is it your mission to list all fixtures: Yes
Is it your mission to list all stations? Yes
Is it your mission to record all "part of" type relationships between assets
and stations? Yes
Is it your mission to record all "connected to" type relationships between
items and fixtures? Yes
What has the "connected to" relationship to the fixtures: Assets (e.g. Base
Units) or Stations? Assets

Do you have to a allow for the possibility multiple assets connected to one
fixture? NO Multiple fixtures connected to one asset? Yes


If all of the guesses were right, I'd suggest 4 main tables: Assets,
AssetStationAssignments Stations and Fixtures. Content of 3 of these is
obvious, AssetStationAssignments has a record for each instance of an Asset
being connected to a station.
Each would have fields to name the entity and record desired nformation on
it, plus the following:

In assets: AssetNumber (PK)

In Stations StationNumber (PK)

In AssettStationAssignments AssetNumber (FK) linked to AssetNumber in
Assets StationNumber (FK) Linked to Station Number in Stations

In Fixtures AssetNumber (FK) linked to AssetNumber in asseets

Get all of your data moved into these tables. Now you have a solid
foundation for accomplishing everything that you want to do under step 4.

Hope this helps a little.

Sincerely,

Fred