If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Form/Subform
"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) |
#3
|
|||
|
|||
Form/Subform
"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? |
#4
|
|||
|
|||
Form/Subform
"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) |
#5
|
|||
|
|||
Form/Subform
"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 |
#6
|
|||
|
|||
Form/Subform
"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) |
#7
|
|||
|
|||
Form/Subform
"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. |
#8
|
|||
|
|||
Form/Subform
"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) |
#9
|
|||
|
|||
Form/Subform
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) |
#10
|
|||
|
|||
Form/Subform
"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) |
Thread Tools | |
Display Modes | |
|
|