Form/Subform

I have a form that displays a subject and a subform that is supposed to
display all incidents related to that subject. I have tried several ways
including creating from the wizard and drag and drop but the info displayed
never seems to be right.

What is being displayed if I relate the subform to the subjID (autonum) is
only one incident instaed of every incident.

If I relate the Subform to the complaintnum (autonum) it lists that
occurrence with every subject involved in that occurence and not every
occurence for the individual subject.

Further explanation may be needed.

Scott

"Srowe" wrote in message
...
I have a form that displays a subject and a subform that is supposed to
display all incidents related to that subject. I have tried several ways
including creating from the wizard and drag and drop but the info
displayed
never seems to be right.

What is being displayed if I relate the subform to the subjID (autonum) is
only one incident instaed of every incident.

If I relate the Subform to the complaintnum (autonum) it lists that
occurrence with every subject involved in that occurence and not every
occurence for the individual subject.


Do you have a table of Subjects and another table of Incidents? The table
of Subjects should have a primary key (is it SubjID, in your case?) and the
table of Incidents should have its own primary key (is it ComplaintNum, in
your case?). Are there more than one incident per subject? (From your
post, I think the answer is yes.) Are there more than one subject per
incident? (I'm not sure from what you posted.)

If there's only one subject per incident, then your Incidents table should
also have a SubjID field in it, though in this table that field would be
Long Integer, not autonumber. This field is thus used to relate the
incident to the subject. Your subform would be based on this table.

If, on the other hand, there could be more than one subject per incident,
then you need a third table, SubjectsIncidents (or some such name) to link
them. Each record in this table would have both a SubjID field and a
ComplaintNum field, and would represent the association of that subject with
that incident. In this case, your subform would be based on the
SubjectsIncidents table (or a query of it), not the Incidents table.

In either case, the subform's Link Master Fields and Link Child Fields
properties would be set to "SubjID". The main form, of course,would be
based on the Subjects table.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Dirk Goldgar" wrote:

"Srowe" wrote in message
...
I have a form that displays a subject and a subform that is supposed to
display all incidents related to that subject. I have tried several ways
including creating from the wizard and drag and drop but the info
displayed
never seems to be right.

What is being displayed if I relate the subform to the subjID (autonum) is
only one incident instaed of every incident.

If I relate the Subform to the complaintnum (autonum) it lists that
occurrence with every subject involved in that occurence and not every
occurence for the individual subject.


Do you have a table of Subjects and another table of Incidents? The table
of Subjects should have a primary key (is it SubjID, in your case?) and the
table of Incidents should have its own primary key (is it ComplaintNum, in
your case?). Are there more than one incident per subject? (From your
post, I think the answer is yes.) Are there more than one subject per
incident? (I'm not sure from what you posted.)

If there's only one subject per incident, then your Incidents table should
also have a SubjID field in it, though in this table that field would be
Long Integer, not autonumber. This field is thus used to relate the
incident to the subject. Your subform would be based on this table.

If, on the other hand, there could be more than one subject per incident,
then you need a third table, SubjectsIncidents (or some such name) to link
them. Each record in this table would have both a SubjID field and a
ComplaintNum field, and would represent the association of that subject with
that incident. In this case, your subform would be based on the
SubjectsIncidents table (or a query of it), not the Incidents table.

In either case, the subform's Link Master Fields and Link Child Fields
properties would be set to "SubjID". The main form, of course,would be
based on the Subjects table.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I'm glad you speak confused!

Your right on with how my forms and tables are set up. And yes there can any
number of subjects associated with each incident.

This third table, would there be a primary key or would I just have the two
fields in it, complaintnum and subjid?

"Srowe" wrote in message
news

Your right on with how my forms and tables are set up. And yes there can
any
number of subjects associated with each incident.

This third table, would there be a primary key or would I just have the
two
fields in it, complaintnum and subjid?


What I would do is let those two fields together be a compound primary key.
You can do that in the table design view by selecting the two fields and
clicking the "key" button.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Dirk Goldgar" wrote:

"Srowe" wrote in message
news

Your right on with how my forms and tables are set up. And yes there can
any
number of subjects associated with each incident.

This third table, would there be a primary key or would I just have the
two
fields in it, complaintnum and subjid?


What I would do is let those two fields together be a compound primary key.
You can do that in the table design view by selecting the two fields and
clicking the "key" button.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I have made a new table using the complaintnum and subjid fields. In both
cases I have made them a number rather than a autonum. I have established the
relationship between the Subjectincident table and the subject table. I made
both the fields in the subjectincident table primary keys.

I have created the subform using the subjectincident table with the subjid
being the first field and the field the form is based on.

Still doesn't work. Now I have no data at all. I am missing something simple
I know. I just can't figure it out.

Scott

"Srowe" wrote in message
...

I have made a new table using the complaintnum and subjid fields. In both
cases I have made them a number rather than a autonum. I have established
the
relationship between the Subjectincident table and the subject table. I
made
both the fields in the subjectincident table primary keys.

Sounds good so far.

I have created the subform using the subjectincident table with the subjid
being the first field and the field the form is based on.

I'm not sure I understand that, though it may well be right. The subform's
recordsource is the subjectincident table? Both fields from that table are
on the subform?

Still doesn't work. Now I have no data at all. I am missing something
simple
I know. I just can't figure it out.

What are the Link Master Fields and Link Child Fields of the subform
control?

Is the subjectincident table populated at all yet?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Dirk Goldgar" wrote:

"Srowe" wrote in message
...

I have made a new table using the complaintnum and subjid fields. In both
cases I have made them a number rather than a autonum. I have established
the
relationship between the Subjectincident table and the subject table. I
made
both the fields in the subjectincident table primary keys.

Sounds good so far.

I have created the subform using the subjectincident table with the subjid
being the first field and the field the form is based on.

I'm not sure I understand that, though it may well be right. The subform's
recordsource is the subjectincident table? Both fields from that table are
on the subform?

Still doesn't work. Now I have no data at all. I am missing something
simple
I know. I just can't figure it out.

What are the Link Master Fields and Link Child Fields of the subform
control?

Is the subjectincident table populated at all yet?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

The Recordsource is the subjectincident table. Both the Link Child and
Master fields are the SubjID field.

When you ask if the subjectincident table populated does it not populate
with the existing data in the database. I assumed it would if I used the same
fields.

I have played with it a bit and the subjid number comes up in the subform
but nothing else.

"Srowe" wrote in message
...

The Recordsource is the subjectincident table.
Both the Link Child and Master fields are the SubjID field.

Okay, that's all fine.

When you ask if the subjectincident table populated does it not populate
with the existing data in the database. I assumed it would if I used the
same
fields.

I may be misunderstanding you, but if I'm not, then you are misunderstanding
how this works. The table exists to represent links between Subjects and
Incidents. But until you put records in that table, it doesn't know
anything about any existing links. By adding a record to that table,
whether through your subform or by some other means, you *create* the link
between a subject and an incident.

Did you previously have a SubjID field in your Incidents table? If so, that
would be a different representation of a link between a subject and an
incident, but it was an inadequate link, since it only allowed one subject
per incident. However, we can use that previous information to load the
SubjectIncident table, by running an appropriate append query.

I don't know for sure if what I've described is actually your situation. If
it is, then you can load the SubjectIncident table with an append query with
SQL along these lines:

INSERT INTO SubjectIncident (SubjID, ComplaintNum)
SELECT SubjID, ComplaintNum FROM Incidents
WHERE SubjID Is Not Null;

I think I have that SQL correct, but I don't know for sure the names of your
tables and fields.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Not sure if I'm totally understanding this. Where would I insert the sql code
that you provided?


Yes there is a subjid field in the incident table. however I do not have
that field on my incident input form.

Scott


"Dirk Goldgar" wrote:

"Srowe" wrote in message
...

The Recordsource is the subjectincident table.
Both the Link Child and Master fields are the SubjID field.

Okay, that's all fine.

When you ask if the subjectincident table populated does it not populate
with the existing data in the database. I assumed it would if I used the
same
fields.

I may be misunderstanding you, but if I'm not, then you are misunderstanding
how this works. The table exists to represent links between Subjects and
Incidents. But until you put records in that table, it doesn't know
anything about any existing links. By adding a record to that table,
whether through your subform or by some other means, you *create* the link
between a subject and an incident.

Did you previously have a SubjID field in your Incidents table? If so, that
would be a different representation of a link between a subject and an
incident, but it was an inadequate link, since it only allowed one subject
per incident. However, we can use that previous information to load the
SubjectIncident table, by running an appropriate append query.

I don't know for sure if what I've described is actually your situation. If
it is, then you can load the SubjectIncident table with an append query with
SQL along these lines:

INSERT INTO SubjectIncident (SubjID, ComplaintNum)
SELECT SubjID, ComplaintNum FROM Incidents
WHERE SubjID Is Not Null;

I think I have that SQL correct, but I don't know for sure the names of your
tables and fields.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Srowe" wrote in message
...
Not sure if I'm totally understanding this. Where would I insert the sql
code
that you provided?

Create a new query. Switch that query into SQL view. Copy and paste the
SQL I gave you into the SQL View window. Correct any table or field names I
guessed at that are incorrect. Click the Run button to run the query. If
it runs, it will prompt you for confirmation, and tell you how many records
it added to the table.

Note: before doing this, make a backup copy of your database, just in case.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)