Populate junction table

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" wrote:

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" wrote in message
...


"scubadiver" wrote:


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.

"Warren" wrote:



"scubadiver" wrote:

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

"Warren" wrote:

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" wrote:


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" wrote:

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" wrote:


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

"Warren" wrote:

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?

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" wrote in message
...
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" wrote:

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" wrote in message
...


"scubadiver" wrote:


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.

"Warren" wrote:



"scubadiver" wrote:

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

"Warren" wrote:

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" wrote:


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" wrote:

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" wrote:


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

"Warren" wrote:

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?

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.

"BruceM" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...


"scubadiver" wrote:


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.

"Warren" wrote:



"scubadiver" wrote:

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

"Warren" wrote:

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" wrote:


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" wrote:

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" wrote:


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

"Warren" wrote:

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?