Query turned read only

[hudaz]

Hi Guys,

I wonder if you may be able to help me as I'm stumped as to how to solve / work around this one.

I have a query that i use to update dates relating to jobs and at what stage of development they are in. It worked fine until I started to pull in some data (Prism) to help update the user when the project is planned in to sample and when mass production starts (the field this is displayed in is not enabled). when I've incorporated this it I have no longer been able to update any field and my query has turned read only. Could anyone advise ?

I have posted the SQL below if it makes scense to anyone (it doesn't really to me) as i'm abit of a keen novice to be honest.

Thanks for your help in advance.

SELECT dbo_NPD_Container.PENumber, dbo_NPD_Container.JobNumber, dbo_NPD_Container.ProjectTitle, dbo_NPD_Container.Status, dbo_NPD_Sales.InitialQuery, dbo_NPD_Sales.InitialQueryDate, dbo_NPD_Sales.SpecificationIssued, dbo_NPD_Sales.ApprovalRecived, dbo_NPD_Sales.TuDrawIssued, dbo_NPD_Sales.TuDeliveryDate, dbo_NPD_Sales.TrialRequestIssued, dbo_NPD_Sales.SampleDate, dbo_NPD_Sales.CustomerAppRecived, dbo_NPD_Sales.BalanceDrawIssued, dbo_NPD_Sales.BalanceDeliveryDate, dbo_NPD_Sales.InitialProductionDate, dbo_NPD_Sales.[Other Project Notes], dbo_NPD_Sales.TargetSample, dbo_NPD_Sales.TargetProdution, dbo_NPD_Sales.Position, dbo_NPD_Sales.Waiting, dbo_NPD_Container.ProposalNumber, dbo_NPD_Container.GlassType, dbo_NPD_ProjectNotes.ProjectNotes, PRISM_SAMPLEDATE.PLANNEDSTARTDATE, PRISM_SAMPLEDATE.Factory, PRISM_PRODUCTIONDATE.LINENUMBER, PRISM_PRODUCTIONDATE.Date
FROM (PRISM_SAMPLEDATE RIGHT JOIN ((dbo_NPD_ProjectNotes INNER JOIN (dbo_NPD_Container INNER JOIN dbo_NPD_Sales ON dbo_NPD_Container.PENumber = dbo_NPD_Sales.PENumber) ON dbo_NPD_ProjectNotes.PENumber = dbo_NPD_Container.PENumber) INNER JOIN Design_ProjectNotes ON (Design_ProjectNotes.MaxOfSubmissionDate = dbo_NPD_ProjectNotes.SubmissionDate) AND (dbo_NPD_Container.PENumber = Design_ProjectNotes.PENumber)) ON PRISM_SAMPLEDATE.[Job Number] = dbo_NPD_Container.JobNumber) LEFT JOIN PRISM_PRODUCTIONDATE ON dbo_NPD_Container.JobNumber = PRISM_PRODUCTIONDATE.[Job Number]
ORDER BY dbo_NPD_Container.PENumber, PRISM_PRODUCTIONDATE.Date;

 

[namliam]

First off, dont just splash sql on the forum, use the # button on the post menu to give it code tags and post it that way.

second format sql something like so:

SELECT dbo_NPD_Container.PENumber
     , dbo_NPD_Container.JobNumber     
     , dbo_NPD_Container.ProjectTitle
...
FROM (      PRISM_SAMPLEDATE 
RIGHT JOIN (              (dbo_NPD_ProjectNotes 
               INNER JOIN (dbo_NPD_Container 
               INNER JOIN  dbo_NPD_Sales ON dbo_NPD_Container.PENumber = dbo_NPD_Sales.PENumber) 
                                                   ON ....
... etc...

To promote readability....

Reasons why the query may be read only...
- You are mixing local and server data or data from different servers
- You have a "group by" query someplace which may be indicated by the use of MaxOfSubmissionDate
- You do not have the required keys (joined) properly in your query
- You dont have insert/update rights on the user used to connect to the server.
- Mixing left and right joins can cause uneditable recordsets

Good luck out there in database world

 

[hudaz]

Apologies for the etiquette error. a bit of a novice. :-S

Secondly thanks for taking the time to reply. I can see i have a lot to work on .

I'm going to have a go at some of the suggestions you've brought up.

Once again thanks.

Greatly appreciated.