Hi
I have run into a problem where I can select a group of records from different tables but I can not enter information into the records.
I have 3 tables
1) ClientBasic key ContactID
2) ClientAddressLink keys ContactID, AddressID
3) ClientEmployerAddress key ClientEmployerID
It is possible for a client to have a number of addresses usually private and business address. The SQL for this is given below and I can enter information into the query.
SELECT tblClientAddressLink.Residencial, TblContactBasic.Title, tblClientEmployerAddress.DepartmentName
FROM tblClientEmployerAddress INNER JOIN (TblContactBasic INNER JOIN tblClientAddressLink ON TblContactBasic.ContactID = tblClientAddressLink.ContactID) ON tblClientEmployerAddress.ClientEmployerID = tblClientAddressLink.AddressID;
Now I add an additional table Jobsheets which represents interactions with our services. So a client can have anynumber of jobsheets.
4) JobSheet keys JobsheetID, ContactID
I now lose the ability to write to the tables. The query SQL is given below.
SELECT tblClientAddressLink.Residencial, TblContactBasic.Title, tblClientEmployerAddress.DepartmentName, TblJobSheet.ServiceID
FROM (tblClientEmployerAddress INNER JOIN (TblContactBasic INNER JOIN tblClientAddressLink ON TblContactBasic.ContactID = tblClientAddressLink.ContactID) ON tblClientEmployerAddress.ClientEmployerID = tblClientAddressLink.AddressID) INNER JOIN TblJobSheet ON TblContactBasic.ContactID = TblJobSheet.ContactID;
What am I doing wrong? I want to create a form with the clients details ,address and jobsheet details and to be able to update the fields as necessary.
Thanks for any help.
Alan
I have run into a problem where I can select a group of records from different tables but I can not enter information into the records.
I have 3 tables
1) ClientBasic key ContactID
2) ClientAddressLink keys ContactID, AddressID
3) ClientEmployerAddress key ClientEmployerID
It is possible for a client to have a number of addresses usually private and business address. The SQL for this is given below and I can enter information into the query.
SELECT tblClientAddressLink.Residencial, TblContactBasic.Title, tblClientEmployerAddress.DepartmentName
FROM tblClientEmployerAddress INNER JOIN (TblContactBasic INNER JOIN tblClientAddressLink ON TblContactBasic.ContactID = tblClientAddressLink.ContactID) ON tblClientEmployerAddress.ClientEmployerID = tblClientAddressLink.AddressID;
Now I add an additional table Jobsheets which represents interactions with our services. So a client can have anynumber of jobsheets.
4) JobSheet keys JobsheetID, ContactID
I now lose the ability to write to the tables. The query SQL is given below.
SELECT tblClientAddressLink.Residencial, TblContactBasic.Title, tblClientEmployerAddress.DepartmentName, TblJobSheet.ServiceID
FROM (tblClientEmployerAddress INNER JOIN (TblContactBasic INNER JOIN tblClientAddressLink ON TblContactBasic.ContactID = tblClientAddressLink.ContactID) ON tblClientEmployerAddress.ClientEmployerID = tblClientAddressLink.AddressID) INNER JOIN TblJobSheet ON TblContactBasic.ContactID = TblJobSheet.ContactID;
What am I doing wrong? I want to create a form with the clients details ,address and jobsheet details and to be able to update the fields as necessary.
Thanks for any help.
Alan
However, would it be possible to use one query to fill a form. Using a sub form complicates the form use and I need it to be accessable to screen reader software ( mainly JAWS )