Count Records in an Update Query!

[MarkNie]

Hi All

Hope you are well!!

Is there anyway that you can use the count function in the update query. When I use the append query it works fine with the count, but when I change it to an update query I can seem to get it to count.

Here is the code I am using any help would be great.

Code:
UPDATE ((((tblTracking LEFT JOIN xqryNoRunDeadlinePerDay0 ON tblTracking.PortfolioCode = xqryNoRunDeadlinePerDay0.PortfolioCode) LEFT JOIN xqryNoRunDeadlines1 ON tblTracking.PortfolioCode = xqryNoRunDeadlines1.PortfolioCode) LEFT JOIN xqryNoRunDeadlinesSignedOff2 ON tblTracking.PortfolioCode = xqryNoRunDeadlinesSignedOff2.PortfolioCode) LEFT JOIN xqryNoRunDeadlineswMet3 ON tblTracking.PortfolioCode = xqryNoRunDeadlineswMet3.PortfolioCode) LEFT JOIN xqryNoRunDeadlineswMissed4 ON tblTracking.PortfolioCode = xqryNoRunDeadlineswMissed4.PortfolioCode SET xqryNoRunDeadlinePerDay0.CountOfRunDeadline = [ytblRunDeadlineSummary]![Run Cumulative Target], xqryNoRunDeadlines1.CountOfRunDeadline = [ytblRunDeadlineSummary]![Run Number Deadlines per Day], xqryNoRunDeadlinesSignedOff2.CountOfRunDeadline = [ytblRunDeadlineSummary]![Run Number Deadlines Signed Off], xqryNoRunDeadlineswMet3.CountOfRunDeadline = [ytblRunDeadlineSummary]![Run Deadlines met], xqryNoRunDeadlineswMissed4.CountOfRunDeadline = [ytblRunDeadlineSummary]![Run Deadlines missed]
WITH OWNERACCESS OPTION;

Thanks
Mark

 

[Golom]

What is

[ytblRunDeadlineSummary]?

Is that a table or query?
The some control on a form?

If it's a table then you haven't mentioned it in your joins.

I don't see anything here that is doing a COUNT ... Perhaps that's happening in one of the queries.

Just cleaning up the code a bit and adding some aliases, I get

UPDATE ((((tblTracking T 
       LEFT JOIN xqryNoRunDeadlinePerDay0     DO 
       ON T.PortfolioCode = D0.PortfolioCode) 

       LEFT JOIN xqryNoRunDeadlines1          D1 
       ON T.PortfolioCode = D1.PortfolioCode) 

       LEFT JOIN xqryNoRunDeadlinesSignedOff2 D2 
       ON T.PortfolioCode = D2.PortfolioCode) 

       LEFT JOIN xqryNoRunDeadlineswMet3      D3 
       ON T.PortfolioCode = D3.PortfolioCode) 

       LEFT JOIN xqryNoRunDeadlineswMissed4   D4 
       ON T.PortfolioCode = D4.PortfolioCode 

SET D0.CountOfRunDeadline = 
       [ytblRunDeadlineSummary]![Run Cumulative Target]

  , D1.CountOfRunDeadline = 
       [ytblRunDeadlineSummary]![Run Number Deadlines per Day]

  , D2.CountOfRunDeadline = 
       [ytblRunDeadlineSummary]![Run Number Deadlines Signed Off]

  , D3.CountOfRunDeadline = 
       [ytblRunDeadlineSummary]![Run Deadlines met]

  , D4.CountOfRunDeadline = 
       [ytblRunDeadlineSummary]![Run Deadlines missed]

WITH OWNERACCESS OPTION;

I'm not even sure that this would work because OUTER JOINs are not usually updatable.