Syntax error [Missing Operator] in query expression
Access 2010.
The query is suppose to update the table WorksheetName with the row count from the table BusinessRelocation based on WorksheetId
This is the message:
Syntax error [Missing Operator] in query expression 'Rowcnt.RecCount
FROM WorksheetName
INNER JOIN
(SELECT BusinessRelocation.[Worksh
FROM BusinessRelocation
GROUP BY WorksheetId) As RowCnt
ON WorksheetName.WorksheetId = RowCnt.WorksheetId
HERE is the script
UPDATE WorksheetName
SET WorksheetName.RowCnt = RowCnt.RecCount
FROM WorksheetName
INNER JOIN
(SELECT BusinessRelocation.[Worksh
FROM BusinessRelocation
GROUP BY WorksheetId) As RowCnt
ON WorksheetName.WorksheetId = RowCnt.WorksheetId
The query is suppose to update the table WorksheetName with the row count from the table BusinessRelocation based on WorksheetId
This is the message:
Syntax error [Missing Operator] in query expression 'Rowcnt.RecCount
FROM WorksheetName
INNER JOIN
(SELECT BusinessRelocation.[Worksh
FROM BusinessRelocation
GROUP BY WorksheetId) As RowCnt
ON WorksheetName.WorksheetId = RowCnt.WorksheetId
HERE is the script
UPDATE WorksheetName
SET WorksheetName.RowCnt = RowCnt.RecCount
FROM WorksheetName
INNER JOIN
(SELECT BusinessRelocation.[Worksh
FROM BusinessRelocation
GROUP BY WorksheetId) As RowCnt
ON WorksheetName.WorksheetId = RowCnt.WorksheetId
ASKER
The count is stored in the table call WorksheetName, the count is the number of rows in BusinessRelocation that match the WorksheetNameID
Did you try my suggestion? It should return the number of instances of WorksheetID, in BusinessRelocation.
What is the point of storing rowcounts of one table in rows of another table? As soon as the first table is updated, the data in the second table becomes suspect and therefore unreliable. The rules of normalization are not arbitrary. They were defined to prevent data anomalies like this. Always do the calculation as it is needed for your report rather than storing it and letting it get stale.
If you tell us what your objective is, we may be able to suggest a more reliable solution.
If you tell us what your objective is, we may be able to suggest a more reliable solution.
ASKER
I am migrating data so for now i want to store the count of one table into another.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT BusinessRelocation.[Worksh
FROM BusinessRelocation GROUP BY WorksheetId