Populate junction table

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?

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?

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?

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?

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?

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" 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?

"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?

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" 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?

"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?

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?