Iff sum of group <1000 than all members of group are 0

[Dado]

I want to make an update query and I need the function which will an group of
items which sum value is less than 1000€, automaticly update to 0€ value for
each item of that group

 

[Duane Hookom]

Please type 2-3 fields of ten records and how you would expect them to look
before and after the update. It wouldn't take long and would remove all the
guesswork from answering.

 

[Dado]

Example: AssedID; Current Value; Category;
1 150€ Eqiupment
2 2000€ Vehicles
3 450€ Eqiupment
AFTER UPTADE
1 0€ Eqiupment
2 2000€ Vehicles
3 0€ Eqiupment

Because the sum of the Category equipment is less than 1000€

 

[Duane Hookom]

Backup your table and then try this:
UPDATE [tblYours]
SET [CurrentValue] =
IIf(
DSum("[CurrentValue]","[tblYours]","[Category] = """ & [Category] &
"""") <1000,0,[CurrentValue])

I would seriously question the business need for this since the sum can
always be calculated when needed.

 

[Dado]

UPDATE [Grupe kategorija] INNER JOIN ([Asset Categories] INNER JOIN Table1 ON
[Asset Categories].AssetCategoryID = Table1.AssetCategoryID) ON [Grupe
kategorija].IDGrupekategorije = [Asset Categories].[ID Grupe kategorija] SET
Table1.Trenutnavrijednost = IIf(DSum("[Table1].Trenutnavrijednost","[Grupe
kategorija] INNER JOIN ([Asset Categories] INNER JOIN Table1 ON [Asset
Categories].AssetCategoryID = Table1.AssetCategoryID) ON [Grupe
kategorija].IDGrupekategorije = [Asset Categories].[ID Grupe
kategorija]","[Grupe kategorija].Grupakategorija=""&[Grupe
kategorija].Grupakategorija&"""<1000,0,[Table1]![Trenutnavrijednost])
WHERE ((([Grupe kategorija].Grupakategorija)=4) AND
((Table1.Finansijkovodjenje)=Yes));

This is the SQL statement in my update query but the system replied syntax
error (missing operator), please can you help me about making above sentece
right. Problem is the IIf part. Query is above three tables Asset Categories,
Table1, Grupe kategorija.

Backup your table and then try this:
UPDATE [tblYours]
SET [CurrentValue] =
IIf(
DSum("[CurrentValue]","[tblYours]","[Category] = """ & [Category] &
"""") <1000,0,[CurrentValue])

I would seriously question the business need for this since the sum can
always be calculated when needed.

--
Duane Hookom
MS Access MVP
--

Example: AssedID; Current Value; Category;
1 150? Eqiupment
2 2000? Vehicles
3 450? Eqiupment
AFTER UPTADE
1 0? Eqiupment
2 2000? Vehicles
3 0? Eqiupment

Because the sum of the Category equipment is less than 1000?

 

[Duane Hookom]

You can only have one table or query name in the domain argument of a DSum()
function. You would need to create a query for this to work in your DSum().

--
Duane Hookom
MS Access MVP
--

UPDATE [Grupe kategorija] INNER JOIN ([Asset Categories] INNER JOIN Table1
ON
[Asset Categories].AssetCategoryID = Table1.AssetCategoryID) ON [Grupe
kategorija].IDGrupekategorije = [Asset Categories].[ID Grupe kategorija]
SET
Table1.Trenutnavrijednost = IIf(DSum("[Table1].Trenutnavrijednost","[Grupe
kategorija] INNER JOIN ([Asset Categories] INNER JOIN Table1 ON [Asset
Categories].AssetCategoryID = Table1.AssetCategoryID) ON [Grupe
kategorija].IDGrupekategorije = [Asset Categories].[ID Grupe
kategorija]","[Grupe kategorija].Grupakategorija=""&[Grupe
kategorija].Grupakategorija&"""<1000,0,[Table1]![Trenutnavrijednost])
WHERE ((([Grupe kategorija].Grupakategorija)=4) AND
((Table1.Finansijkovodjenje)=Yes));

This is the SQL statement in my update query but the system replied syntax
error (missing operator), please can you help me about making above
sentece
right. Problem is the IIf part. Query is above three tables Asset
Categories,
Table1, Grupe kategorija.

Backup your table and then try this:
UPDATE [tblYours]
SET [CurrentValue] =
IIf(
DSum("[CurrentValue]","[tblYours]","[Category] = """ & [Category] &
"""") <1000,0,[CurrentValue])

I would seriously question the business need for this since the sum can
always be calculated when needed.

--
Duane Hookom
MS Access MVP
--

Example: AssedID; Current Value; Category;
1 150? Eqiupment
2 2000? Vehicles
3 450? Eqiupment
AFTER UPTADE
1 0? Eqiupment
2 2000? Vehicles
3 0? Eqiupment

Because the sum of the Category equipment is less than 1000?

:

Please type 2-3 fields of ten records and how you would expect them to
look
before and after the update. It wouldn't take long and would remove
all
the
guesswork from answering.

--
Duane Hookom
MS Access MVP


I want to make an update query and I need the function which will an
group
of
items which sum value is less than 1000?, automaticly update to 0?
value
for
each item of that group

 

[Dado]

IIf(DSum([Trenutnavrijednost],"vrijednost grupe ispod 1000
donji",[Grupakategorija]=4 And
[Finansijkovodjenje]=Yes)<1000,"0",[Trenutnavrijednost])

I've made un update query above query, after run the results are unchanged

Dado

You can only have one table or query name in the domain argument of a DSum()
function. You would need to create a query for this to work in your DSum().

--
Duane Hookom
MS Access MVP
--

UPDATE [Grupe kategorija] INNER JOIN ([Asset Categories] INNER JOIN Table1
ON
[Asset Categories].AssetCategoryID = Table1.AssetCategoryID) ON [Grupe
kategorija].IDGrupekategorije = [Asset Categories].[ID Grupe kategorija]
SET
Table1.Trenutnavrijednost = IIf(DSum("[Table1].Trenutnavrijednost","[Grupe
kategorija] INNER JOIN ([Asset Categories] INNER JOIN Table1 ON [Asset
Categories].AssetCategoryID = Table1.AssetCategoryID) ON [Grupe
kategorija].IDGrupekategorije = [Asset Categories].[ID Grupe
kategorija]","[Grupe kategorija].Grupakategorija=""&[Grupe
kategorija].Grupakategorija&"""<1000,0,[Table1]![Trenutnavrijednost])
WHERE ((([Grupe kategorija].Grupakategorija)=4) AND
((Table1.Finansijkovodjenje)=Yes));

This is the SQL statement in my update query but the system replied syntax
error (missing operator), please can you help me about making above
sentece
right. Problem is the IIf part. Query is above three tables Asset
Categories,
Table1, Grupe kategorija.

Backup your table and then try this:
UPDATE [tblYours]
SET [CurrentValue] =
IIf(
DSum("[CurrentValue]","[tblYours]","[Category] = """ & [Category] &
"""") <1000,0,[CurrentValue])

I would seriously question the business need for this since the sum can
always be calculated when needed.

--
Duane Hookom
MS Access MVP
--

Example: AssedID; Current Value; Category;
1 150? Eqiupment
2 2000? Vehicles
3 450? Eqiupment
AFTER UPTADE
1 0? Eqiupment
2 2000? Vehicles
3 0? Eqiupment

Because the sum of the Category equipment is less than 1000?

:

Please type 2-3 fields of ten records and how you would expect them to
look
before and after the update. It wouldn't take long and would remove
all
the
guesswork from answering.

--
Duane Hookom
MS Access MVP


I want to make an update query and I need the function which will an
group
of
items which sum value is less than 1000?, automaticly update to 0?
value
for
each item of that group

 

[Duane Hookom]

You might want to read up on the DSum() syntax.
If [Trenutnavrijednost] is numeric, then you would not want to try to update
it with a string value ("0").
Also, the Field and "criteria/where" argument should be in quotes.
If Finansijkov... is Yes/No type then use:
IIf(DSum("[Trenutnavrijednost]", "[vrijednost grupe ispod 1000 donji]",
"[Grupakategorija]=4 And
[Finansijkovodjenje]=-1")<1000,0,[Trenutnavrijednost])


--
Duane Hookom
MS Access MVP

IIf(DSum([Trenutnavrijednost],"vrijednost grupe ispod 1000
donji",[Grupakategorija]=4 And
[Finansijkovodjenje]=Yes)<1000,"0",[Trenutnavrijednost])

I've made un update query above query, after run the results are unchanged

Dado

You can only have one table or query name in the domain argument of a
DSum()
function. You would need to create a query for this to work in your
DSum().

--
Duane Hookom
MS Access MVP
--

UPDATE [Grupe kategorija] INNER JOIN ([Asset Categories] INNER JOIN
Table1
ON
[Asset Categories].AssetCategoryID = Table1.AssetCategoryID) ON [Grupe
kategorija].IDGrupekategorije = [Asset Categories].[ID Grupe
kategorija]
SET
Table1.Trenutnavrijednost =
IIf(DSum("[Table1].Trenutnavrijednost","[Grupe
kategorija] INNER JOIN ([Asset Categories] INNER JOIN Table1 ON [Asset
Categories].AssetCategoryID = Table1.AssetCategoryID) ON [Grupe
kategorija].IDGrupekategorije = [Asset Categories].[ID Grupe
kategorija]","[Grupe kategorija].Grupakategorija=""&[Grupe
kategorija].Grupakategorija&"""<1000,0,[Table1]![Trenutnavrijednost])
WHERE ((([Grupe kategorija].Grupakategorija)=4) AND
((Table1.Finansijkovodjenje)=Yes));

This is the SQL statement in my update query but the system replied
syntax
error (missing operator), please can you help me about making above
sentece
right. Problem is the IIf part. Query is above three tables Asset
Categories,
Table1, Grupe kategorija.

:

Backup your table and then try this:
UPDATE [tblYours]
SET [CurrentValue] =
IIf(
DSum("[CurrentValue]","[tblYours]","[Category] = """ & [Category]
&
"""") <1000,0,[CurrentValue])

I would seriously question the business need for this since the sum
can
always be calculated when needed.

--
Duane Hookom
MS Access MVP
--

Example: AssedID; Current Value; Category;
1 150? Eqiupment
2 2000? Vehicles
3 450? Eqiupment
AFTER UPTADE
1 0? Eqiupment
2 2000? Vehicles
3 0? Eqiupment

Because the sum of the Category equipment is less than 1000?

:

Please type 2-3 fields of ten records and how you would expect them
to
look
before and after the update. It wouldn't take long and would remove
all
the
guesswork from answering.

--
Duane Hookom
MS Access MVP


I want to make an update query and I need the function which will
an
group
of
items which sum value is less than 1000?, automaticly update to
0?
value
for
each item of that group