Unable to amend data via a query

[bxgti4x4]

I have a query (QryPersonalDetails)which extracts data from two tables, using a common field (Alias). TblMaster_Address_List contains 600 records and TblPassportDetails contains 21. My problem is that the data is extracted OK but I cannot amend it.

I assume that Access will not allow me to amend data in two tables at once, even though the query specifies the table from which the information is drawn.

I would be grateful if someone could confirm this or, if my assumption is incorrect, advise me how I should proceed.

Best Regards
John

 

[lupins46]

It depends on a number of things such as the nature of the link between the two tables, and whether each table has a unique key.
You would need to provide more info on your tables for anyone to judge.

 

[Remou]

I do not think this is true. Please post the SQL for you query.

 

[bxgti4x4]

Remou,

The SQL is as follows:

SELECT QryPersonalDetails2.Alias, QryPersonalDetails2.DateOfBirth, QryPersonalDetails2.PlaceOfBirth, QryPersonalDetails2.CountyOfBirth, QryPersonalDetails2.CountryOfBirth, QryPersonalDetails2.Nationality, QryPersonalDetails2.P1PassportNo, QryPersonalDetails2.P1PlaceOfIssue, QryPersonalDetails2.P1DateOfIssue, QryPersonalDetails2.P1DateOfExpiry, QryPersonalDetails2.P2PassportNo, QryPersonalDetails2.P2PlaceOfIssue, QryPersonalDetails2.P2DateOfIssue, QryPersonalDetails2.P2DateOfExpiry, QryPersonalDetails2.USVisaType, QryPersonalDetails2.VDateOfIssue, QryPersonalDetails2.VDateOfExpiry, QryPersonalDetails2.VPlaceOfIssue, QryPersonalDetails2.DischargeBookNo, QryPersonalDetails2.DBDateOfIssue, QryPersonalDetails2.DBPlaceOfIssue, QryPersonalDetails2.Height, QryPersonalDetails2.EyeColour, QryPersonalDetails2.Sex, QryPersonalDetails2.UKDL, QryPersonalDetails2.USDL, QryPersonalDetails2.Notes, QryPersonalDetails1.[Contact First Name], QryPersonalDetails1.[Contact Second Name], QryPersonalDetails1.[Contact Third Name], QryPersonalDetails1.[Contact Surname], QryPersonalDetails1.[Mobile Telephone], QryPersonalDetails1.[Home Address 1], QryPersonalDetails1.[Home Address 2], QryPersonalDetails1.[Home Address 3], QryPersonalDetails1.[Home Address 4], QryPersonalDetails1.[Town (Home)], QryPersonalDetails1.[County+Zip/Post Code (Home)], QryPersonalDetails1.[Country (Home)], QryPersonalDetails1.[Home Telephone]
FROM QryPersonalDetails1 RIGHT JOIN QryPersonalDetails2 ON QryPersonalDetails1.Alias = QryPersonalDetails2.Alias
ORDER BY QryPersonalDetails2.Alias;

Thanks a lot,
John

 

[Remou]

This seems to be a query based on another query. I have misunderstood your question. I had expected to see references to TblMaster_Address_List and TblPassportDetails. In addition, unless there is something fairly simple in the sql, you will need to supply the information mentioned by lupins46.

 

[bxgti4x4]

Sorry Remou, I copied the wrong query - I had been experimenting with various ideas. The correct code is:

SELECT TblPassportDetails.Alias, 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, [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].[Mobile Telephone], [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]
FROM TblPassportDetails LEFT JOIN [Master Address List] ON TblPassportDetails.Alias = [Master Address List].Alias;

The Primary keys for both tables are autonumbers. The link is based on "Alias".

Hope this clarifies things.
Best Regards
John

 

[Remou]

Is Alias unique in either table, and if so, is it indexed as such?

 

[bxgti4x4]

Remou,

Alias is unique in both tables but neither is indexed. In TblPassport Details there is a record for each Alias Name, whereas Tbl Master Address List contains about 600 records, only 30 of which contain have an entry in the Alias field.

Best Regards
John

 

[Remou]

I think if you add a unique index to the Alias field in one or both tables, you will be able to update.

 

[bxgti4x4]

Remou,

Do you mean that I should add a second primary key, or is indexing a more complex process?

Best Regards
John

 

[Remou]

It is quite simple, just choose Yes (No duplicates) from the Indexed property dropdown list for the Alias field. I guess I ought to refer you to some links, as there seems to be some problem with Primary keys:
Relational links (the first two are the same article, different format):

http://www.alphabetcitydataworks.com/fundamentals.htm

http://r937.com/relational.html

283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

 

[bxgti4x4]

Remou,

Thanks for all your help, I'll try this and let you know the outcome.

Best Regards
John

 

[PHV]

The problem is the (at least) the left join.
Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bxgti4x4]

Remou,

That did the trick, I set both indexes to Yes(No duplicates) and I can now add update as necessary.

PHV,

Thanks for taking a look, it looks as though Remou's advice was enough to solve my problem.

Thanks to you all for your help.

Best Regards
John