Updating Three Tables from a Form

[rj51cxa]

I have three tables - Master Address List, Surveyor Details and TblPassportDetails (Names were created before I learned more about databases). Each of these contains different pieces of information about a particular person, with the Master Address List containing details of all the company's contacts (Address, telephone number etc).

The primary key (PK) details in each of the tables are as follows:

Master Address List Record ID
Surveyor Details Alias
TblPassportDetails Alias

I have created two queries - QrySurveyorDetails, which extracts the relevant fields from Surveyor Details and TblPassportDetails, and QrySurveyorDetails2, which combines QrySurveyorDetails with fields from Master Address List.

The two queries are as follows:

QrySurveyorDetails

 SELECT [Surveyor details].Alias, [Surveyor details].[Surveyor Name], [Surveyor details].Employed, TblPassportDetails.DateOfBirth, TblPassportDetails.PlaceOfBirth, TblPassportDetails.CountyOfBirth, TblPassportDetails.CountryOfBirth, TblPassportDetails.Nationality, TblPassportDetails.P1PassportNo, TblPassportDetails.P1PlaceOfIssue, TblPassportDetails.P1DateOfIssue, TblPassportDetails.P1DateOfExpiry, TblPassportDetails.P2PassportNo, TblPassportDetails.P2PlaceOfIssue, TblPassportDetails.P2DateOfIssue, TblPassportDetails.P2DateOfExpiry, TblPassportDetails.USVisaType, TblPassportDetails.VDateOfIssue, TblPassportDetails.VDateOfExpiry, TblPassportDetails.VPlaceOfIssue, TblPassportDetails.DischargeBookNo, TblPassportDetails.DBDateOfIssue, TblPassportDetails.DBPlaceOfIssue, TblPassportDetails.Height, TblPassportDetails.EyeColour, TblPassportDetails.Sex, TblPassportDetails.UKDL, TblPassportDetails.USDL, TblPassportDetails.Notes
FROM TblPassportDetails RIGHT JOIN [Surveyor details] ON TblPassportDetails.Alias = [Surveyor details].Alias;

QrySurveyorDetails2

SELECT QrySurveyorDetails.Alias, QrySurveyorDetails.[Surveyor Name], QrySurveyorDetails.Employed, QrySurveyorDetails.DateOfBirth, QrySurveyorDetails.PlaceOfBirth, QrySurveyorDetails.CountyOfBirth, QrySurveyorDetails.CountryOfBirth, QrySurveyorDetails.Nationality, QrySurveyorDetails.P1PassportNo, QrySurveyorDetails.P1PlaceOfIssue, QrySurveyorDetails.P1DateOfIssue, QrySurveyorDetails.P1DateOfExpiry, QrySurveyorDetails.P2PassportNo, QrySurveyorDetails.P2PlaceOfIssue, QrySurveyorDetails.P2DateOfIssue, QrySurveyorDetails.P2DateOfExpiry, QrySurveyorDetails.USVisaType, QrySurveyorDetails.VDateOfIssue, QrySurveyorDetails.VDateOfExpiry, QrySurveyorDetails.VPlaceOfIssue, QrySurveyorDetails.DischargeBookNo, QrySurveyorDetails.DBDateOfIssue, QrySurveyorDetails.DBPlaceOfIssue, QrySurveyorDetails.Height, QrySurveyorDetails.EyeColour, QrySurveyorDetails.Sex, QrySurveyorDetails.UKDL, QrySurveyorDetails.USDL, QrySurveyorDetails.Notes, [Master Address List].Category, [Master Address List].[Contact First Name], [Master Address List].[Contact Second Name], [Master Address List].[Contact Third Name], [Master Address List].[Contact Surname], [Master Address List].Position, [Master Address List].[Mobile Telephone], [Master Address List].[Email Address], [Master Address List].[Home Address 1], [Master Address List].[Home Address 2], [Master Address List].[Home Address 3], [Master Address List].[Home Address 4], [Master Address List].[Town (Home)], [Master Address List].[County+Zip/Post Code (Home)], [Master Address List].[Country (Home)], [Master Address List].[Home Telephone], [Master Address List].[Home Fax], [Master Address List].[Partner's Name]
FROM QrySurveyorDetails LEFT JOIN [Master Address List] ON QrySurveyorDetails.Alias = [Master Address List].Alias;

I have created a form based on QrySurveyorDetails2 in which I can edit a record already created but not add a new record.

I think the problem is with the allocation of Primary Keys and the way the tables are joined but I cannot figure out where I am going wrong. I've read all the relevant threads but I still cannot sort it out.

I would really appreciate a bit of help here.

Best Regards
John

 

[SoCalAccessPro]

Why not use VBA ? Open the underlying recordset and use the .addnew method ?

That way, you can have some string validation before the record is written to the underlying table(s).

You're right though, unless you have your PKs and FKs defined, and referential integrity set up, Access defaults to read-only record sets. It CAN be frustrating when basing forms on queries.

Try checking the referential integrity checkboxes and see what Access tells you. If it says the join typer is inderterminate, than you have orphaned records in one or both tables and have to do some clean up.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[fneily]

Did you see:
When can I update data from a query?

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

 

[Remou]

I think you will need an INNER JOIN for this, rather than LEFT JOIN. Make sure you include the key fields for all relevant tables. Another approach may be to use a form / subform set-up.

Here are some documents you may wish to read, particularly one and two:

Understanding SQL Joins:

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

When can I update data from a query?

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

Fundamental Microsoft Jet SQL for Access 2000 (includes download AcFundSQL.exe) :

http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx

Intermediate Microsoft Jet SQL for Access 2000 (includes download AcIntSQL.exe):

http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

Advanced Microsoft Jet SQL for Access 2000 (includes download AcAdvSQL.exe):

http://msdn2.microsoft.com/en-us/library/aa139977(office.10).aspx

As an aside, there is a lot to be said for aliases, they make SQL more readable. For example:

SELECT q.Alias, q.[Surveyor Name], q.Employed, m.Category, m.[Contact First Name]
FROM QrySurveyorDetails q 
LEFT JOIN [Master Address List] m
ON q.Alias = m.Alias;

It is also generally advisable to avoid spaces in field and table names, it will save you a lot of typing and general grief.

 

[rj51cxa]

Thanks SoCalAccessPro, fneily and Remou. You've given me a lot to think about. I'll get back to you when I've had a chance to go through it all.

Thanks to Remou for the comment about spaces in field names and tables. The ones in question were set up when I first started working with Access. I've learned my lessons the hard way.

Best Regards
John

 

[rj51cxa]

The reason for having three tables is to limit the number of fields that each one contained. What I am trying to do is to draw together specific information for each "Alias" into a single form so that it can be updated.

Table Surveyor Details is in my working database and I moved tables Master Address List and Passport Details into a separate database in order to keep the working database to reasonable proportions. The two tables are linked to my working database which is currently about 10mb in size, so am I making problems for myself by having the tables in a separate database?

Following the suggestion from SoCalAccessPro, I set “Alias” in the Surveyor Details table as the PK and then tried to create relationships with the other two tables. I can create a "one to one" relationship between Surveyor Details and Master Address List but for some reason I can only create a "one to many" relationship between Surveyor Details and TblPassportDetails. There is only one record in each table that will refer to a particular Alias, so I would think that a "one to one" relationship is what I am looking for.

On further problem appears to be that, because they are linked tables, I cannot enforce referential integrity.

Would I be correct in thinking that, once I can establish referential integrity, my queries will automatically follow the relationships and enable me to edit all three tables?

 

[rj51cxa]

I thought I would try importing the two linked tables into the working database and then try to create a one to one relationship with Table surveyor Details. This worked and I am now able to add and amend records from my form.

I think my problem is solved now, even though the database is slightly larger.

Thanks to all who tried to help.

Best Regards
John