fields available in other tables - specify relationship - unexpected results

[Sc0tt1e]

Attached are 2 screen shots to assist.

I am trying to add the Ltd Co name on to a from and it will not work the way I want.

I would like the value blank unless someones associated to a Limited Company in which case the field is populated.

I think I have my relationships set up correctly.

When I try to add the field via layout mode - add existing field the value I want is in 'Fields available in another table'. When I select it it asks to validate the relationship so I set IDCustomer and IDLtd as the joins but it reduces the number of customers and shows the Ltd Co names in order:

There are 21 customers only 3 Ltd Co names and 5 people are associated to Ltd Co purchase (2 couples and a single individual). When I set the relationship there are only 3 customers and each has a different Ltd Co name and it is against the incorrect customer (customers 1-3 have Ltd Co names 1-3 against them.

I hope that all makes sense.

Please help I've no idea what's going on. I can get my head around SQL codes but building these forms is driving me insane.

[moke123]

i think you need to add the table containing the field to the recordsource of the form and join on the common field.

[Sc0tt1e]

The field I want to insert is from another table (Ltd Co) which doesn't show in the list of allowable fields when trying to set the record source.

[moke123]

Add JcustLTD and tLTDco to the forms recordsource.

If you can post your Db it would be helpful.

[Sc0tt1e]

Hahaha yes it would be very helpful but I cant get it under 600kb and 500 is the max size.

I have posted screen shots of the relationship page and the form I'm trying to update for reference

[moke123]

delete anything not specific to the problem, do a compact and repair and then zip it in a zipfile?

Have you tried what I suggested?

[Sc0tt1e]

Lets see if the ZIP worked

[moke123]

You did not include all the needed tables.

This is what you want?

[Sc0tt1e]

That's what I want!

When I try and add, I get DAV against Viki Bell and only 3 customers are visible (one for each Ltd Co name).

[moke123]

Try this and see if its what you want

Your joins were off I've changed it and uploaded below

[Sc0tt1e]

Looks good, so what did you do?

I need to understand so I can replicate it and ensure I don't make the same mistakes again later in the build?

[moke123]

Open the recordsource query of your form.
Right click on the lines joining the different tables and adjust the join properties.
You need to adjust them until you get the results you want.

[moke123]

Heres one article about joins. A google search should turn up plenty of articles.
https://www.informit.com/articles/article.aspx?p=330333&seqNum=6

check the joins in your relationships also.

[Sc0tt1e]

I'm confused as the image you have above is how I have it set up?
I thought I was creating an outer join with TCustomer as the parent and TLtd Co as the child

[mike60smart]

Hi

I just took a look at your database and not understanding why you have your Forms setup in the way you have.

The Customers Form linked to Properties are both not updateable. Why ?

The usual setup is that you can add properties in the Subform.