Multiple Relationship Subform

[Sandpiper]

Hi. can anyone help me. I've spent the last two hours getting no where.

I have a tbl_Enquiry,tbl_EnquiryType and tbl_Questions
On the main form (frm_Enquiry), depending on which enquiry type is chosen, will depend on which questions are displayed.
this is a many to many relationship so I have a junction table, called tbl_TypeQuestionJunction.

The questions are displayed in a sub from when the enquiry type is selected, and this all works fine.

However, I then need to add a field where the user can write thier answers to the questions.
for this I have a table tbl_QAJunction with the fields
EnquiryID
QuestionID and
Answer

How can I get my subform to display the correct questions when the enquiry type is selected, with an answer box next to it and then pick up the EnquiryID, QuestionID and the answer and add them to the tbl_QAJunction.
I just can't get a query to work.

I hope this makes sense

This has really bugged me and is stopping me completing my database.
Am i going abotu this all wrong? If anyone can help, I'd be grateful
I've attached a cut down version of the DB so you can see what i'm on about

Thanks

 

[Pat Hartman]

Use a left join in the subform's query so that all questions will show even when their answer record has not yet been added.

 

[Sandpiper]

Sorry Still No Response,
If you have time to look at this and tell me if it is possible, i'd be sooooooo grateful

Thanks
A very desparate
Jo

 

[Pat Hartman]

Make sure that all tables involved in the query have primary keys defined.

 

[Sandpiper]

Sorry, i've still had no response. Any help and I would be soooo grateful.
A very desparate
Jo

Thanks for the response again. Unfortunately it hasn't worked.

I have re-attached the full database as it stands at the moment. it is still very much work in progress as i've been stuck on this bit for a week now and everything else has gone out of the Window

The main form to look at is frm_Enquiry
I hope you will be able to see what I'm trying to acheive.
When the Quote Type is selected from the drop down list, a list of questions with a box for the answer is displayed.
As each answer will be different for each enquiry, I have a junction table that stores the EnquiryID, QuestionID and the Answer, but I just can't get it to work.
Following your first piece of advice, I have managed to get the answer box to display, but nothing else.

Sorry if I am being really stupid, or maybe trying to make it too complicated.

Again, any advise would be greatly apprecited (on this and the database as a whole). It is really getting me down now.

Thanks very much

 

[Pat Hartman]

Can't look at this now. If no one has helped by tomorrow, pop the message to the top so I'll see it again.

 

[Pat Hartman]

Hi,
I popped this message for you. I still don't have time to download the database and look at it but perhaps someone else does.

 

[Pat Hartman]

You have a lot of work to do to clean up the relationships.
1. For your own sanity, use the pk name as the fk name whenever possible.
2. Make sure that the relationships are between pk and fk. You have some that are not pointing to the pk. For example, CompanyID in the contacts table, actually contains companyName rather than the real CompanyID.
3. Go through each table, field by field, and make sure that the field is set to required if that is appropriate. For example, CompanyName should be required in the company table. At the same time, make sure that the default value for all fk fields is null. Access defaults to 0. This will cause problems. Don't forget to set the fk's to required if the relationship is required.
4. When everything else is cleaned up, go into the relationships window and set the RI to be true. Set Cascade delete where appropriate. You won't be able to do this until the data is cleaned up.
5. Your problem is caused because the relationships are incorrect between the tables used on this subform.