Query doesn't load results (1 Viewer)

[remcopeters]

I got a query "Offertes query" (offertes means "quotes")

This one is supposed to show me all quotes and for each quotes the already specified fields.
Unfortunately the query doesn't load the requested results. Can someone please check why it isn't loading the results?

 

[plog]

It fails because you have no ContactpersoonID=1 value in your Contactpersonen table.

Because of the types of link you've made between your tables in the query (INNER JOINs), every link is in effect applying criteria to that query. If there is no match between Klanten.KlantID and Contactpersonen.KlantID your query will return no results. If there is no match between Contactpersonen.ContactpersoonID and Offertes.ContactpersoonID your query will return no results. If there is no match between Offertes.GebruikerID and Gebruikers.GebruikerID you query will return no results.

You might want a LEFT JOIN. Google that.

 

[remcopeters]

So basically I got values in my 'offertes' table which aren't in my 'contactpersonen' table?

I read about LEFT join but don't think I completely understand it:
Does LEFT join just display me all data in 'offertes', also if it is not corresponding with another table?

 

[plog]

So basically I got values in my 'offertes' table which aren't in my 'contactpersonen' table?

Yes.

A LEFT JOIN will show all the records from whichever table you designate when making the link. You can make a LEFT JOIN from Offertes to Contactpersonen or you can make a LEFT JOIN from Contactpersonen to Offertes depending on which table should be the one whose data should show.

 

[remcopeters]

Aan okay. Offertes should display its data.

How do I make a LEFT join? Just by changing 'INNER' to 'LEFT'?

 

[plog]

To change the type of join between two tables open the query in design view, right click on the line that links them and click on the 'Join Properties' menu item that pops up. Click the box next to the number that best describes the relationship you want.

Now, because you have a downstream link (Klanten is linked to Contactpersonen) you need to make the link between them the same as the link between Offertes and Contactpersonen.

 

[remcopeters]

That did it, thanks