Query Not Updatable 2

[fredka]

I am having a real hard time understanding how to make this work - I am getting the "query not updatable" error on the following

UPDATE tblSmEmployerGrps INNER JOIN qrySmallEmployerGroups7 ON tblSmEmployerGrps.MAIN_GRP_ID = qrySmallEmployerGroups7.MAIN_GRP_ID SET tblSmEmployerGrps.TotalContracts = qrySmallEmployerGroups7.[CountOfSUBS_ID];


I looked at previous posts and I am just missing it. Any help would be greatly appreciated!!! thanks!!!

Fred

 

[lespaul]

you're missing part of the query. The structure for an UPDATE is:

UPDATE tablename [b]SET FieldName = SomeValue[/b] WHERE conditions

In access you can join into other tables between the tablename and the SET condition, but you still need a SET somewhere.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[fredka]

Thanks for helping me!! I do have a Set in my sql?

Fred

 

[lespaul]

I guess there is a SET buried in there....my bad!

not sure what the problem is then.....

 

[PHV]

What is the SQL code of qrySmallEmployerGroups7 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fredka]

I am taking a table with main_grp_id and then counting the people that are enrolled with each main_grp_id. The tblSmEmployerGrps has the main groups numbers and the ENL_SUBS_PRD table has individuals enrolled. Thanks!!

Here is the sql for qrySmallEmployerGroups7:

SELECT qrySmallEmployerGroups6.MAIN_GRP_ID, Count(qrySmallEmployerGroups6.SUBS_ID) AS CountOfSUBS_ID
FROM qrySmallEmployerGroups6
GROUP BY qrySmallEmployerGroups6.MAIN_GRP_ID;

and here is the sql for qrySmallEmployerGroups6:

SELECT tblSmEmployerGrps.MAIN_GRP_ID, ENL_SUBS_PRD.SUBS_ID, ENL_SUBS_PRD.SP_TRMDT, ENL_SUBS_PRD.PRD_ID
FROM ENL_SUBS_PRD INNER JOIN tblSmEmployerGrps ON ENL_SUBS_PRD.MAIN_GRP_ID = tblSmEmployerGrps.MAIN_GRP_ID
WHERE (((ENL_SUBS_PRD.SP_TRMDT)=#12/31/9999#) AND ((ENL_SUBS_PRD.PRD_ID) Like "G*" Or (ENL_SUBS_PRD.PRD_ID) Like "H*" Or (ENL_SUBS_PRD.PRD_ID) Like "B*"));

 

[PHV]

you may try this:
UPDATE tblSmEmployerGrps
SET TotalContracts = DCount("SUBS_ID", "qrySmallEmployerGroups6", "MAIN_GRP_ID=" & [MAIN_GRP_ID])

Assumptions: MAIN_GRP_ID is defined as numeric in tblSmEmployerGrps

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fredka]

I'm sorry, it is a text field.

Fred

 

[PHV]

it is a text field

UPDATE tblSmEmployerGrps
SET TotalContracts=DCount("SUBS_ID","qrySmallEmployerGroups6","MAIN_GRP_ID='" & [MAIN_GRP_ID] & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

it is a text field.

then you just need to add tick marks:

UPDATE tblSmEmployerGrps
SET TotalContracts = DCount("SUBS_ID", "qrySmallEmployerGroups6", "MAIN_GRP_ID=[COLOR=red]'[/color]" & [MAIN_GRP_ID] &"[COLOR=red]'[/color]")

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[fredka]

Thanks to you both sooooo much!!!!! Can you explain why this works but the original does not ? thanks again!!!!

Fred

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886