Problem when adding extra query/table

[RCheesley]

Hi all,

I have a query which is working OK, but I want to add another query (the data needs to be in separate tables).

This code works:

SELECT tbl_patientdetails.[Patient ID], tbl_patientdetails.FirstName, tbl_patientdetails.Surname, tbl_patientdetails.NHSNo, tbl_patientdetails.DoB, tbl_patientdetails.Ethnicity, tbl_patientdetails.Address1, tbl_patientdetails.Address2, tbl_patientdetails.Town, tbl_patientdetails.County, tbl_patientdetails.Postcode, qry_referrer.ReferrerID, qry_referrer.RefFirstName, qry_referrer.RefSurname, qry_referrer.GPPractice
FROM tbl_patientdetails INNER JOIN qry_referrer ON tbl_patientdetails.NHSNo = qry_referrer.NHSNo;

But when I add the second query, it no longer works - in that, the query doesn't allow for new records to be added:

SELECT tbl_patientdetails.[Patient ID], tbl_patientdetails.FirstName, tbl_patientdetails.Surname, tbl_patientdetails.NHSNo, tbl_patientdetails.DoB, tbl_patientdetails.Ethnicity, tbl_patientdetails.Address1, tbl_patientdetails.Address2, tbl_patientdetails.Town, tbl_patientdetails.County, tbl_patientdetails.Postcode, qry_referrer.ReferrerID, qry_referrer.RefFirstName, qry_referrer.RefSurname, qry_referrer.GPPractice
FROM (tbl_patientdetails INNER JOIN qry_referrer ON tbl_patientdetails.NHSNo = qry_referrer.NHSNo) INNER JOIN qry_ipptreatments ON tbl_patientdetails.NHSNo = qry_ipptreatments.NHSNo;

Pretty sure this is something daft (I am still learning access/sql) but anyone care to point out the obvious?

Many thanks in advance.

Ruth

 

[James Dudden]

Both of your code snippets are the same?

 

[RCheesley]

Nope, the second makes reference to the query
qry_ipptreatments

 

[James Dudden]

Sorry, my mistake . If you post your db i could take a look.
Regards

 

[RCheesley]

Sorry I can't do that, hence why just pasting the sql.

Ruth

 

[James Dudden]

Ok, if you post a screenshot of your query in design view that may help.

 

[RCheesley]

Hope this helps

 

[James Dudden]

Do you have any kind of grouping in the other query? If so this will be the cause of the problem.

 

[RCheesley]

Hi James,

Can you clarify what you mean by grouping? A bit confoozled

Ruth

 

[boblarson]

This may help also with the identification as to why it is read only:
http://allenbrowne.com/ser-61.html

 

[RCheesley]

Not sure if that does help, the only one I think it might be is The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields perhaps?

 

[boblarson]

If Access can't identify a unique record (row) then it can't add data.

 

[RCheesley]

OK will look at it when I get back from holiday Many thanks in advance!