Populate junction table

[Warren]

I currently have 3 tables I am working with tEquipment, tEquipmentCircuit and
tCircuit.
One piece of equipment can be powered by more than one circuit and one
circuit can power many equipment so I have tEquipmentCircuit as a junction
table between tEquipment and tCircuit. I believe this is set up correctly
with just the primary keys from each main table comprising the junction table.
Is there a way that I can create one form with one subform and populate all
three tables simultaneously?

 

[scubadiver]

You can't populate the junction table without populating the two main tables
first.

 

[Warren]

I guess I am confused on how this supposed to work.
Any chance of a generic example of how to enter the information using the
table arrangement I have described keeping in mind that the customer does not
want to have to go to two different table to enter the data?

 

[scubadiver]

Your tables are set up correctly but users enter information into forms NOT
tables.

Despite this, what you are suggesting goes against normalisation rules
because like information is stored in one table only. The customer *will*
have to enter information in two different tables (so to speak) so you can't
get really get away from that.

If you have a main form (tEquipment) and a subform (tEquipmentCircuit) the
user enters the information for 'tEquipment' into the main form so they are
adding new pieces of equipment as necessary.

As far as 'tcircuit' is concerned what I would do is put a button on the
form that will open the main form for the circuit information. Then I would
use that information from 'tcircuit' for a combo box in the subform that is
bound to the junction table.

 

[Warren]

Sorry I am up very late and exhausted from running in circles with this, I
did mean to say enter into the form/subform
My original design was to have a seperate form to enter the circuits into
and then later after the fact an audit would be performed on the equipment
that was installed at this point a simple form with subform to link the
circuit an equipment with junction table would be relatively simple.
The dilema as usual is that there are several thousand pieces of equipment
already in use with approx. double the amount of circuits which will make for
a long time entering data.
I think that for sake of my sanity and correct data entry later down the
road I will convince that they just use the two forms.

 

[scubadiver]

ARGH NO!

If the design calls for a many-to-many relationship then you must have it!

If the information is currently stored in Excel then you only have to import
the information into Access.

 

[Warren]

ARGH NO!

If the design calls for a many-to-many relationship then you must have it!

Have what?

If the information is currently stored in Excel then you only have to import
the information into Access.

No excel sheets; information is being compiled on paper and will be entered
directly into the database later.

Of course one option would be to enter it into excel or a large temporary
table via form and then utilize queries to get it into it's proper place in
the database

 

[scubadiver]

A many-to-many relationship consists of two main tables and a junction table
which is wht you have.

Why is the data being compiled on paper. Surely that is double the work?

 

[Warren]

A many-to-many relationship consists of two main tables and a junction table
which is wht you have.

Yes I will have a form based off of main table tCircuit to enter circuit
information.
A form based off of tEquipment to enter equipment information.
A subform based off of tEquipmentCircuit that will be on the equipment form
and will tie the equipment table to the circuit table via the junction table.

Why is the data being compiled on paper. Surely that is double the work?

The data is being compiled on a large computer floor where the people
auditing don't have access to desktop or laptop (not my choice I might add).
Once all of the data is collected someone else will be entering into database.

 

[BruceM]

I haven't quite followed the whole discussion, but it sounds as if you are
on the right track. In a many-to-many relationship one of the "many" tables
tends to be more or less static. I would guess that is tCircuit. The most
straightforward way to enter the Circuit information is by way of a form
bound to tCircuit (or to a query based on tCircuit). If the circuit data
are already in a spreadsheet or other flat file you can import them to
populate the tCircuit initially, but I wouldn't create a spreadsheet for
importing.

The other "many" table (tEquipment) is a more dynamic (in the sense of
changeable) table. A form based on tEquipment will have a subform based on
tCircuitEquipment, as you have suggested. The subform has a combo box based
on tCircuit. The combo box bound column is CircuitID (the PK from
tCircuit). Other columns may be displayed as needed to make it easiest for
the user. The combo box is bound to CircuitID in tCircuitEquipment. Note
that CircuitID in tCircuitEquipment is not a PK, but is rather a field of
the same data type as CircuitID in tCircuit, except if CircuitID in tCircuit
is autonumber, it is a Number field in tCircuitEquipment. The subform is
linked to the main form through EquipmentID.

Presumably you know what circuits you have, so the first step may be to
create the circuit records in tCircuit by way of the form bound to tCircuit.
Then, when entering the Equipment information, select the appropriate
circuit(s) in the subform.

The situation may be the opposite of what I have guessed, in which case
reverse the names of the main tables in the description above.

 

[Warren]

Would it be possible to have a form that you could enter the Equipment
information into equipment table and a continuous subform circuit information
which would put information into the circuit table and then have a command
button labeled save that would fill in the junction table with required
joining info (equipment identifier and circuit identifier)?
This is ultimately what I would prefer if at all possible.

 

[BruceM]

How would you determine which piece of equipment goes with which circuit and
vice versa? You can put information into the Equipment table and into the
Circuit table, but what is the connection between a particular piece of
equipment and a particular circuit?

 

[Warren]

Could a save button save the information to the first two tables (equipment
and circuit tables respectively), then run an query that would append to the
equipmentcircuit table where the criteria for the equipment id and the
criteria for circuit id were linked back to the main form and the subform and
then advance to a new record for the user?
Would the query list one record for each circuit in the subform and place
the equipment id in each record?
I will try and see if this works.