Creating Forms and Reports for a simple Many To Many relationship

[aflat362]

If you look at this page on relational database design:

http://r937.com/relational.html

And scroll down to the "Many-to-Many Relationships" Section you will
see a common database structure.

I can not find a simple way to create forms and reports in Access that
allow you to enter and display data in a structure such as this.

It is easy to create a form with the wizard that allows you to enter
data into a single table.

If I want to populate the middle table (Which I will call the "Linking
Table") I don't want to have to key in the unique Identifiers of the
other 2 tables.

How can one set up a form in Access that allows you to select data
(say, from a combo box) from each of the 2 data tables to populate the
linking table?

Also - how can you produce a report that would look something like the
following (loosely based on the tables from the URL I posted above)

Patient 1: Earle
Insurer: Sentry Insurance
Insurer: Wausau Insurance

Patient 2: Earle's Brother
Insurer: State Farm

Patient 3: Earle's Mom
Insurer: AllState
Insurer: Sentry Insurance

[Bob Quintal]

"aflat362" <fl*****@gmail.comwrote in
news:11**********************@79g2000cws.googlegro ups.com:

If you look at this page on relational database design:

http://r937.com/relational.html

And scroll down to the "Many-to-Many Relationships" Section
you will see a common database structure.

I can not find a simple way to create forms and reports in
Access that allow you to enter and display data in a structure
such as this.

Using the example structure,
I would use a subform on the patient table that displays the
tblPtInsurancePgm rows for that patient and a combobox whose
rowsource is tblInsurer is bound to the
tblPtInsurancePgm.InsurerID

A subform will automatically populate the related parent field
when creating a new record, so the task is then reduced to
clicking on the * row of the subform and choosing a supplier
from the combobox.

Use a subreport in the same fashion.

>

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[aflat362]

Bob Quintal wrote:

"aflat362" <fl*****@gmail.comwrote in
news:11**********************@79g2000cws.googlegro ups.com:

If you look at this page on relational database design:

http://r937.com/relational.html

And scroll down to the "Many-to-Many Relationships" Section
you will see a common database structure.

I can not find a simple way to create forms and reports in
Access that allow you to enter and display data in a structure
such as this.

Using the example structure,
I would use a subform on the patient table that displays the
tblPtInsurancePgm rows for that patient and a combobox whose
rowsource is tblInsurer is bound to the
tblPtInsurancePgm.InsurerID

A subform will automatically populate the related parent field
when creating a new record, so the task is then reduced to
clicking on the * row of the subform and choosing a supplier
from the combobox.

Use a subreport in the same fashion.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Thanks - I created a new form using the wizard and selected subform.

I notice that its default behavior is to allow me to manually enter
data into the form.

Going back to the patient / insurer example - In order to link a
patient to an insurer
I have to type the Insurer info in again. Not good since if I type it
wrong I just get duplicates.

So - I went into design view of the form and made the ID field a combo
box and set the row source to a SQL query of my equivalent of the
Insured table.

Now the drop down is populating but when I select a row in the drop
down - it is not being magically added to my subform as I had wished.

Any idea what I'm missing? If you feel like it - I can email you my
database (its small)

[Bob Quintal]

"aflat362" <fl*****@gmail.comwrote in
news:11**********************@j44g2000cwa.googlegr oups.com:

>
Bob Quintal wrote:

>"aflat362" <fl*****@gmail.comwrote in
news:11**********************@79g2000cws.googlegr oups.com:

If you look at this page on relational database design:

http://r937.com/relational.html

And scroll down to the "Many-to-Many Relationships" Section
you will see a common database structure.

I can not find a simple way to create forms and reports in
Access that allow you to enter and display data in a
structure such as this.

Using the example structure,
I would use a subform on the patient table that displays the
tblPtInsurancePgm rows for that patient and a combobox whose
rowsource is tblInsurer is bound to the
tblPtInsurancePgm.InsurerID

A subform will automatically populate the related parent
field when creating a new record, so the task is then reduced
to clicking on the * row of the subform and choosing a
supplier from the combobox.

Use a subreport in the same fashion.

>

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Thanks - I created a new form using the wizard and selected
subform.

I notice that its default behavior is to allow me to manually
enter data into the form.

Going back to the patient / insurer example - In order to link
a patient to an insurer
I have to type the Insurer info in again. Not good since if I
type it wrong I just get duplicates.

So - I went into design view of the form and made the ID field
a combo box and set the row source to a SQL query of my
equivalent of the Insured table.

Now the drop down is populating but when I select a row in the
drop down - it is not being magically added to my subform as I
had wished.

Any idea what I'm missing? If you feel like it - I can email
you my database (its small)

Ok, send it to me. I'll try to figure out what's missing.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[aflat362]

On Dec 13, 5:54 pm, Bob Quintal <[e-mail]:

"aflat362" <[e-mail] innews:11**********************@j44g2000cwa.google groups.com:

Bob Quintal wrote:

"aflat362" <[e-mail] in
news:11**********************@79g2000cws.googlegr oups.com:

If you look at this page on relational database design:

http://r937.com/relational.html

And scroll down to the "Many-to-Many Relationships" Section
you will see a common database structure.

I can not find a simple way to create forms and reports in
Access that allow you to enter and display data in a
structure such as this.

Using the example structure,
I would use a subform on the patient table that displays the
tblPtInsurancePgm rows for that patient and a combobox whose
rowsource is tblInsurer is bound to the
tblPtInsurancePgm.InsurerID

A subform will automatically populate the related parent
field when creating a new record, so the task is then reduced
to clicking on the * row of the subform and choosing a
supplier from the combobox.

Use a subreport in the same fashion.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account fromhttp://www.teranews.com

Thanks - I created a new form using the wizard and selected
subform.

I notice that its default behavior is to allow me to manually
enter data into the form.

Going back to the patient / insurer example - In order to link
a patient to an insurer
I have to type the Insurer info in again. Not good since if I
type it wrong I just get duplicates.

So - I went into design view of the form and made the ID field
a combo box and set the row source to a SQL query of my
equivalent of the Insured table.

Now the drop down is populating but when I select a row in the
drop down - it is not being magically added to my subform as I
had wished.

Any idea what I'm missing? If you feel like it - I can email
you my database (its small)Ok, send it to me. I'll try to figure out what's missing.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account fromhttp://www.teranews.com

Thank you,

I'm on vacation this week and I'll send it out next week ( no big hurry
)

--Allan