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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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€
 
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.
 
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.

Duane Hookom said:
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
--

Dado said:
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?
Click to expand...
Click to expand...
 
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
--

Dado said:
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.

Duane Hookom said:
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
--

Dado said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Duane Hookom said:
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
--

Dado said:
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.

Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Dado said:
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

Duane Hookom said:
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
--

Dado said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top