SQL Newbie! Joining tables and getting them to update

[Greyfleck]

I have two simple tables
linked address table (LAT) and a Properties table (PT)

the LAT has fields

LinkCode Text (from one of many types of contact)
LinkId Number( As above )
AddressId Number (The Primary Key of the PT)

The PT has fields

AddressId Autnum
followed by address details

I have a query that I use to access data it looks like:

SELECT [Address Link].[Link Code], [Address Link].[Link Id], Properties.*
FROM Properties RIGHT JOIN [Address Link] ON Properties.[Property Code] = [Address Link].AddressId;


What I am trying to achieve is a reuse of addresses - what I get is just an increase of the same address and the links remain un-updated.

Can anyone see fundamental flaws in this and point me in the right direction please ....

 

[Remou]

You may wish to read

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

I do not know why you are using [Property Code] in the place of AddressID.

You should check that you have primary indexes.

SELECT [Address Link].[Link Code], [Address Link].[Link Id], Properties.*
FROM Properties LEFT JOIN [Address Link] ON Properties.AddressId = [Address Link].AddressId;

Spaces in field and table names should be avoided. It will save you a lot of work.
Generally, .* should be avoided and field names used.

 

[Remou]

You may wish to read this, too:

http://msdn.microsoft.com/en-us/library/aa198446.aspx

 

[Greyfleck]

Thanks Remou. I reviewed the site you suggested and it gave me a better view of what left and right joins do. HOWEVER ....

I decided to go back one more step just to make sure that I got it right.

I created two tables
Contacts
contactid autnum PK
TextData string
Addresslink num

and

Address
Addresslink autonum pk
and then all the address fields

I have tried inner, right and left joins but always end up with the address fields protected on the form.

The current sql is:
SELECT *
FROM tblcontacts LEFT JOIN TBLaddress ON Tbladdress.Addressid =TBLContacts.Addressid;

Can you help explain why the fields are protected and what I have to do to be able to enter the address.

Many Many thanks ....
Jon

 

[Golom]

As a practical example, suppose you had

[tt]tblContacts
ContactID TextData AddressLink
1 A 5
2 B 6
3 C 9

tblAddress
AddressLink Other
5 123 Main Street

[/tt]
and then you ran

SELECT * FROM tblContacts As C LEFT JOIN tblAddress As A
         ON A.Addressid = C.Addressid;

[tt]Result
ContactID TextData AddressLink AddressLink Other
1 A 5 5 123 Main Street
2 B 6 NULL NULL
3 C 9 NULL NULL
[/tt]
The LEFT JOIN pulls all records from tblContacts and those matching records from tblAddress where the AddressLink field matches. For records in tblContacts where there is no matching AddressLink in tblAddresses, SQL provides NULLs for the fields from tblAddress.

So ... you can't type in an address for those records because the record you are attempting to update does not exist. Outer joins (i.e. LEFT JOIN or RIGHT JOIN) are examples of non-updatable queries.

The structure of your tables that use tblAddress.AddressLink as an Autonumber field means that, not only can you not update other fields, you also can't insert a record into that table that will have an AddressLink value that you need it to have. Access assigns Autonumber values and you can't (or at least can't easily) control what numbers it assigns.

If AddressLink in tblAddress was just a Long Integer field (rather than Autonumber) then you could

INSERT INTO tblAddress (AddressLink)
Select AddressLink From tblContacts
Where AddressLink NOT IN (Select AddressLink From tblAddress)

that inserts the AddressLink records into tblAddress that were missing. Then

SELECT * FROM tblContacts As C [red]INNER[/red] JOIN tblAddress As A
         ON A.Addressid = C.Addressid;

Is an updatable query and you can enter the other address fields.

 

[Greyfleck]

Thanks Golom. I have attempted to put your theories into practice but have chased my tail up a dark place!

I now have a contact table as before but have changed the address table to remove the autonum and just make it a long integer (something I will have to manage but will worry about that later)

I now have 2 contacts but neither have an address and the address table is empty.

What I am trying to do (and it may be completely wrong) is to parse each contact separately and then have the query enable me to enter as many different addresses as I wish.

SELECT *
FROM tbladdress INNER JOIN tblcontacts ON tblContacts.Addressid=tbladdress.Addressid
WHERE tblContacts.contactid=4;

is the query



What I get is a blank query. I cannot get it to display ANYTHING and hold the contact whilst I enter addresses.

Any Clues?

 

[Golom]

You obviously didn't do this

INSERT INTO tblAddress (AddressID)
Select AddressID From tblContacts
Where AddressID NOT IN (Select AddressID From tblAddress)

Your query (INNER JOIN) is not displaying any records because there are no records in the two tables where there is a match on the AddressID field. The above INSERT statement will insure that every AddressID in tblContacts also exists in tblAddress.

 

[Remou]

This is the forms forum, so if you intend to use this in a form, you would be best skipping the whole idea of joins and use a subform based on the links table (link child and master set to link id) and a combo box based on the address table with the addressid as the bound column and the address as the visible column.

You will find suitable examples in the Northwind sample database.