By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,508 Members | 2,949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,508 IT Pros & Developers. It's quick & easy.

Creating Forms and Reports for a simple Many To Many relationship

P: n/a
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

Dec 12 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"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

Dec 12 '06 #2

P: n/a

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)

Dec 13 '06 #3

P: n/a
"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

Dec 14 '06 #4

P: n/a


On Dec 13, 5:54 pm, Bob Quintal <rquin...@sPAmpatico.cawrote:
"aflat362" <flat...@gmail.comwrote innews:11**********************@j44g2000cwa.google groups.com:


Bob Quintal wrote:
"aflat362" <flat...@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 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

Dec 19 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.