make qry updatable - tweaking sql?

  • Thread starter Thread starter r
  • Start date Start date
R

r

Can someone help me tweak this so that the Bud_CatBudandCodes table is
updatable? Right now I can edit the data. Help!

SELECT Bud_CatBudandCodes.CustomerID,

Bud_CatBudandCodes.CategoryID,

Bud_CatBudandCodes.CatBudget,

Bud_CatBudandCodes.CatBudPercent,

Bud_CatBudandCodes.HIST_ACTIVITY,

Bud_CatItemSum.SumOfHIST_DIST_AMOUNT

FROM Bud_CatBudandCodes LEFT JOIN Bud_CatItemSum ON

(Bud_CatBudandCodes.CategoryID =

Bud_CatItemSum.catcode) AND

(Bud_CatBudandCodes.CustomerID = Bud_CatItemSum.jobnum);
 
Dear r:

After studying my edited version of your query:

SELECT C.CustomerID, C.CategoryID, C.CatBudget, C.CatBudPercent,
C.HIST_ACTIVITY, S.SumOfHIST_DIST_AMOUNT
FROM Bud_CatBudandCodes C
LEFT JOIN Bud_CatItemSum S
ON C.CategoryID = S.catcode AND C.CustomerID = S.jobnum;

Your best bet is to use Inconsistent Updates, and option available on your
form.

If your Bud_CatItemSum is a table storing a sum of other data, you've got
trouble. Always derive data, such as sums, live at the moment you need it.
Do not store such information that can be derived. This is a very good
RULE!

Tom Ellison
 
Is SumOfHIST_DIST_AMOUNT a Calculated Field from another Query?

If this is the case, your query is not updatable since the Source (a Total
Query) is not updatable.
 
Van,

The SumOfHIST... field is a total in a temporary table. It is editable, but
I don't want to change it since it represents the Sum of the field
indicated.

With regard to Tom's comment - I realize that's the best way to go about
this. However, because this data is pulled down from the actual systems
that created it and is used only for analysis, using this method is not a
problem. Additionally, there are so many records that calculating the sum
live takes about a minute. I'm not concerned, in any way, about the method
I'm using here.

Having said all that - is what I need to do doable?
 
Back
Top