databse attached

from what i can see i have it setup correctly, but there's something going wonky which means my subform isnt showing the currect results any ideas why?

Hi,

you have given the fieldnames in the query that is the RecordSource of the subform aliases. Your subform Master/Child links are on ID_Key_Customer, whereas that field does not exist in teh subform - it is called tbl_Contact_ID_Key_Customer.

Adjust your master/child links again.

You probably don't nee to have 2 version of the ID_Key_Contact in the subform RecordSource either.


hth,

d

thanks for the reply

i changed the child link to "tbl_Contact_ID_Key_Customer" as you suggested, however the issue remains

i have also found that the sub form picks up the correct results if i use a table as its source, but not if i use a query (combining the Contact and Document info together).

In your attachment tbl_Document contains no records, so no records are returned by the query.

aha! yes thats the problem. thanks

whats the best way of getting it to display the info for all Contacts, regardless of Document data?

Try using this as the RecordSource of the subform:

SELECT c.ID_Key_Customer As [tbl_Contact_ID_Key_Customer], c.ID_Key_Contact, c.Title, c.Forename, c.Surname, d.Doc_Order_Acknowledgement, d.Doc_Estimate, d.Doc_Teardown_Report, d.Doc_Engine_Despatch, d.Doc_Shipping, d.Doc_Invoice, d.Doc_SC_Report, d.Other_1, d.Other_1_Comments, d.Other_2, d.Other_2_Comments
FROM tbl_Contact AS c
LEFT JOIN tbl_Document AS d
ON c.ID_Key_Contact = d.ID_Key_Contact;



hth,

d

thank you very much for your help i'm off home in a minute, but i'll give it a go tomorrow and let you know what the outcome is

No probs - let us know how you get on.

d

appears to be working perfectly with that code! thank you!

out of interest, so i can learn from this and apply this method to other databases, what is now triggering the records to display as i wish?

Hi there, you are welcome!

The key is in the type of Join between the 2 tables. The most common form of join is an INNER JOIN. This returns all records from the main table that have a related document in the other table. There is also another type of join called an OUTER JOIN. You will see that you are selecting:

From tbl_Contact c
LEFT JOIN tbl_Document d
ON c.ID_Key_Contact = d.ID_Key_Contact

This is a 'LEFT OUTER JOIN'. It will return ALL records from the main table (tbl_Contact) and any related records in tbl_Document.

Had you used a 'RIGHT JOIN' you would have returned ALL records in tbl_Document and any related in tbl_Contact.

If you are using the query builder to make your queries then when you create a join between 2 tables you can right-click on the line that joins them and go to the join properties. This will give you the options (in words) to select the type of join you require. The part of the line that joins to the table in the diagram will change accordingly with a little arrow.

Check the Access help on JOINS and also serach UA for further discussions and post back if you still have more questions.

hth,

d