Table creation advice

[Padawan]

Hey all, super newbie here. I am using Access 2007 to create a network circuit tracking database. I have two tables [Circuit] and [Segment]. There are two segments per circuit. The Segment table lists the connection details for that segment. The Circuit table then lists the segments that comprise that circuit.

Circuit
Circuit.Name - primary key
Circuit.1 - lookup field, Segment.Name of 1st segment
Circuit.2 - lookup field, Segment.Name of 2nd segment

Segment
Segment.Name - primary key
Circuit.Name - lookup field, key field from Circuit
Segment.A - one end of the segment
Segment.Z - the other end of the segment



What I am trying to do is as I enter new records in Circuits, the lookup field for Circuit.1 and Circuit.2 only show those records that are not already present in the Circuit table.

I thought I might be able to use a Query for the lookup field, but it doesn't look like it. And I need the information to remain in the Circuit table even after it is removed from that query lookup.

Any help is appreciated

[NTC]

you don't want to lookup in both directions.

if the Circuit table has the two look ups of segments. that's fine. you do not put any look up into the segments table back to circuits. this presumes that the segments table is a static list that has a sole purpose of providing info to the circuit table - sort of like the list of 50 states table which is looked up for an address field.

on the otherhand if each segment is unique to a circuit - and it is not a static list - then what you really want is a subform/table relationship. Don't use any look ups at all. make them 2 separate tables. put into the segment table your cross reference field back to the circuit table id (not a lookup - just a field to hold the circuit ID). Then join these into Main form/ subform using a form.

[Padawan]

on the otherhand if each segment is unique to a circuit - and it is not a static list - then what you really want is a subform/table relationship.

Each segment is unique to a circuit. I am not sure about it being a static list or not. All data will be entered manually, and segments will not be deleted unless the circuit it is part of is deleted.

put into the segment table your cross reference field back to the circuit table id (not a lookup - just a field to hold the circuit ID). Then join these into Main form/ subform using a form.

I think I understand what you mean, I will have to look into form/sub-forms more.

Thank you

[Padawan]

this presumes that the segments table is a static list that has a sole purpose of providing info to the circuit table

Actually, I will need to link the segment.name in other tables, plus I need to link the segment table to Visio ShapeData, so each line connector on a diagram can be linked to a circuit segment to identify the port IDs the circuit connects to.

Does that change anything?

[NTC]

it does seem clear that segments are essentially the 'details' of a unique circuit. and so you will have a circuit ID field in your segment table. and that is a fixed relationship.

in the segment table there can easily be a field for the Visio ID or link. or on the otherhand potentially the Visio data can have a field/link to the segment ID (I don't know visio). either way is fine - logically - it is really a matter of the user experience in terms of how/when data is being entered.

hope this helps.

[Padawan]

Thanks NTC, that helped me a lot.

Ok so now I have a basic db with a form and sub-form I can use to enter a circuit and its corresponding segments at the same time.

What I need to do now is for the "Segment A-end" and "Segment Z-end" fields to be some sort of lookup. I want the user to have to select an end-point. I need only the "available" end-points to appear. If an end-point has already been selected as a "Segment -end" than it should must not be selected again.

How can I do this? I have attached the db I am working with. So for example I need to use the Circuits form to add a new record, and have the Segment fields actually pull from the "Fiber Cassette" table somehow. Each cassette only has so many ports for fiber circuits. So I guess I would need to add these ports into a column of "Ports"? But then how to only be able to select ones not already assigned to a segment somewhere?

[Padawan]

****bump****