Query ommiting results where there is no child record

[krowe]

Hi

I posted this is tables but now think the answer lies more with the query, i have a feeling it depends on the type of join, but dont know how to sort this out as i only ever use design view and not sql.

The sql for the qry is currently:

SELECT tblCasenotes.Date, tblCasenotes.Transaction, tblCasenotes.Notes, tblCasenotes.ID, tblCasenotes.Adviser, tblPerson.Surname, tblPerson.Firstname, tblPerson.DOB, tblPerson.PartnerSurname, tblPerson.PartnerFirstname, tblPerson.P_DOB, tblAdditional.LocalConnection
FROM tblAdditional INNER JOIN (tblCasenotes INNER JOIN tblPerson ON tblCasenotes.ID = tblPerson.ID) ON tblAdditional.ID = tblPerson.ID
WHERE (((tblCasenotes.Transaction)="Duty"));

I have a main table (tblPerson) with a form to put the data in.

I then have a sperate table to put additional information in (a subform of tblPerson, linked by ID). Originally i thought this was best as not every entry in the main table will have additional info. However when I came to create queries using data from both tables, a main table entry without an additional info entry would be filtered from the query.

To resolve this I put a default value in the additional info form, thinking that this would create a record for each main table entry. However this does not appear to be the case.

How can I get the default value to create a record every time.

Or, how can i get a query to return results where the is not additional info record associated with the main record?

Thanks

Kev

 

[cookiegary01]

Hi,
The solution should be changing the join property.
In the Query design view 2x click on the join to display join properties.
You now have 3 options.
To my opinion you need the 2nd option.

 

[krowe]

Thats great, thanks Gary, I cant beleive i didn't know about that!!!

 

[cookiegary01]

Hi Kev,

That's what makes this Forum so great.

Glad to have been able to help you.