Update With Group By Query

[SkiGeek]

I have been struggling with this for a while now and cant seem to get this update to work.

I have a table that holds a project id, a line ID and then the percent allocation to each service line. I am trying to calculate the percent allocation based on the number of repeat project id's IE: 1 project id appears in the table you get 1 for the percent allocation, 2 you get .5, 3 you get .3333333 and so on.

Here is my current query:

UPDATE 
     rlnAPOppServLine AS A, 
     (SELECT rlnAPOppServLine.SAPOMID, 1/Count(rlnAPOppServLine.SAPOMID) AS AutoPercent
FROM rlnAPOppServLine
GROUP BY rlnAPOppServLine.SAPOMID) AS B 
SET A.PercentAlloc = [B].[AutoPercent]
WHERE (((A.SAPOMID)=[B].[SAPOMID]));

Thanks

 

[Ian Mac]

Hi,

I wouldn't do what you are trying to do. Don't have calculated fields in your table, just have them in a query at run time.
You'll be forever updating your table as new data is added.

Anyway, you Inner query shouldn't be working as you are asking for 2 fields to be returned. But I urge you not to try and fix it and stick with it being in a query when ever you need the AutoPercent.

Cheers

 

[SkiGeek]

I knew I was going to get that response. I should of explained in a little more detail.

This is just a utility function that is ran once after a mass upload of data. the data is uploaded from an excel sheet to a temp table I then go through and Append the columns i need to the appropriate tables.

I am not given the percent allocated for each row just that project X has 4 alocated lines. In the future this will be updated based on user input (so not always split evenly, not always calculated). I am just trying to build a run once function that sets a default allocation to each line.

Thanks

 

[Jon K]

In Access, a query which is joined/linked with a Totals query or contains the SQL statement of a Totals query is non-updatable.

Instead, you can use a domain aggregate function:-
UPDATE rlnAPOppServLine SET rlnAPOppServLine.PercentAlloc = 1/DCount("*","rlnAPOppServLine","SAPOMID='" & [SAPOMID] & "'")

If SAPOMID is a numeric field, remove the single quotes:-
UPDATE rlnAPOppServLine SET rlnAPOppServLine.PercentAlloc = 1/DCount("*","rlnAPOppServLine","SAPOMID=" & [SAPOMID])
.

 

[SkiGeek]

Thank you!!!! Works Great!