Update query using DSum

  • Thread starter Thread starter LarsM
  • Start date Start date
L

LarsM

To describe my problem let us assume I have a table ElPowerDistrPanels and a
table ElPowerConsumers. In each record of ElPowerConsumers I have a field
SupplFrom with a reference to a record in ElPowerDistrPanels telling which
distribution panel that particular consumer is supplied from. I also have a
form (view: single form) based on table ElPowerDistrPanels including a sub
form (view: continuous forms) based on table ElPowerConsumers. For each page
of the main form (i.e. for each distribution panel) the sub form indicates
the consumers supplied from that particular panel
([ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]). The form
footer of the sub form indicates calculated information like e.g. the sum of
rated powers of the consumers supplied from that panel. In the main form just
below these fields of the sub form I have fields where I indicate information
like e.g. the rated power of the whole panel. In the field in the main form
indicating the rated power of the panel I can manually type the automatically
calculated value "sum of rated power of consumers supplied from this panel"
from the form footer of the sub form. However I would like to automate this
function.

I have tried to make an update query to update table ElPowerDistrPanels so
that rated power of each panel will be equal to the sum of rated powers of
the consumers supplied from that panel. Initially I would expect an update
query like this to work:

UPDATE ElPowerDistrPanels INNER JOIN ElPowerConsumers ON
ElPowerDistrPanels.PanelID = ElPowerConsumers.SupplFrom SET
ElPowerDistrPanels.RatedPower =
DSum([ElPowerConsumers].[RatedPower],[ElPowerConsumers],[ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]);

I have tried several modifications of this update query, but none of them
work.
- "Help" indicates that each of the three arguments of the DSum function
must be a string, and the example in "Help" indicate use of quotation marks.
If I do this, I just get a message box indicating "Unknown" without any
explanation. If I omit the quotation marks (as above) the query at least
tries to do something.
- Instead of INNER JOIN I tried to use LEFT JOIN and even RIGHT JOIN, but
that doesn't help me either.
Does anyone have an idea what causes this problem, and what I can do to
solve it?

Actually the above description of my database is not completely correct, but
based on tests carried out on temporary tables I don't think, it makes any
difference to the cause of my problem. Actually I don't have any table
ElPowerDistrPanels. The distribution panels are just represented as records
in the same table as the consumers. The reason for this is, that a sub
distribution panel seen from "below" (consumer) is a distribution panel, but
seen from "above" (main distribution panel) the sub distribution panel itself
is a consumer. A plant can have several levels of sub distribution panels. By
running the update query as many times, as there are levels, I would update
one level each time from below and end up with the rated power of the highest
level main distribution panel i.e. the total electrical power consumption of
the whole plant.
 
If SupplFrom is numeric, try:

UPDATE ElPowerDistrPanels SET
ElPowerDistrPanels.RatedPower =
DSum("[RatedPower]","[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]=" &
[PanelID]);

If SupplFrom is text, try:

UPDATE ElPowerDistrPanels SET
ElPowerDistrPanels.RatedPower =
DSum("[RatedPower]","[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]="""
& [PanelID] & """");

--
Duane Hookom
Microsoft Access MVP


LarsM said:
To describe my problem let us assume I have a table ElPowerDistrPanels and a
table ElPowerConsumers. In each record of ElPowerConsumers I have a field
SupplFrom with a reference to a record in ElPowerDistrPanels telling which
distribution panel that particular consumer is supplied from. I also have a
form (view: single form) based on table ElPowerDistrPanels including a sub
form (view: continuous forms) based on table ElPowerConsumers. For each page
of the main form (i.e. for each distribution panel) the sub form indicates
the consumers supplied from that particular panel
([ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]). The form
footer of the sub form indicates calculated information like e.g. the sum of
rated powers of the consumers supplied from that panel. In the main form just
below these fields of the sub form I have fields where I indicate information
like e.g. the rated power of the whole panel. In the field in the main form
indicating the rated power of the panel I can manually type the automatically
calculated value "sum of rated power of consumers supplied from this panel"
from the form footer of the sub form. However I would like to automate this
function.

I have tried to make an update query to update table ElPowerDistrPanels so
that rated power of each panel will be equal to the sum of rated powers of
the consumers supplied from that panel. Initially I would expect an update
query like this to work:

UPDATE ElPowerDistrPanels INNER JOIN ElPowerConsumers ON
ElPowerDistrPanels.PanelID = ElPowerConsumers.SupplFrom SET
ElPowerDistrPanels.RatedPower =
DSum([ElPowerConsumers].[RatedPower],[ElPowerConsumers],[ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]);

I have tried several modifications of this update query, but none of them
work.
- "Help" indicates that each of the three arguments of the DSum function
must be a string, and the example in "Help" indicate use of quotation marks.
If I do this, I just get a message box indicating "Unknown" without any
explanation. If I omit the quotation marks (as above) the query at least
tries to do something.
- Instead of INNER JOIN I tried to use LEFT JOIN and even RIGHT JOIN, but
that doesn't help me either.
Does anyone have an idea what causes this problem, and what I can do to
solve it?

Actually the above description of my database is not completely correct, but
based on tests carried out on temporary tables I don't think, it makes any
difference to the cause of my problem. Actually I don't have any table
ElPowerDistrPanels. The distribution panels are just represented as records
in the same table as the consumers. The reason for this is, that a sub
distribution panel seen from "below" (consumer) is a distribution panel, but
seen from "above" (main distribution panel) the sub distribution panel itself
is a consumer. A plant can have several levels of sub distribution panels. By
running the update query as many times, as there are levels, I would update
one level each time from below and end up with the rated power of the highest
level main distribution panel i.e. the total electrical power consumption of
the whole plant.
Click to expand...
 
SupplFrom is text, so I tried to add "" & [PanelID] & """.
My problem isn't solved yet, but I may be a little closer to a solution. I
get an error message telling about a type conversion failure. In your example
(and in Help (F1) in Access) you are using quotation marks around all three
parameters of DSum. If I use quotation marks around the first parameter, I
just get a popup saying "Unknown" without any explanation, and DSum is not
able to do anything at all. If I omit these quotation marks, DSum at least
tries to do something. If I don't use quotation marks around the second
parameter I get a popup asking for a value of [ElPowerConsumers]. I can
choose to continue without typing a value, and then DSum tries to do
something. If I have the quotation marks around this parameter, I don't get
the popup asking for a value of [ElPowerConsumers]. This tells me, that
regarding the first parameter there can't be any quotation marks, and
regarding the secondparameter there should be quotation marks. Regarding the
third parameter till now it doesn't seem to make any difference, if the
quotation marks are included or not.

I was thinking about what type of value DSum is returning. In my tables
[RatedPower] is type Single. If DSum pr deafult returns a type Double, that
could maybe cause the trouble. So I added a CSng to the expression:
ElPowerDistrPanels.RatedPower =
CSng(DSum([RatedPower],"[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]="" & [PanelID] &"""))
This did not solve the problem either.

I have been thiking about, where there could be type conversions going
wrong, but I haven't found the solution yet. Now at least I know, that I have
to look for the error somewhere around type conversions.



Duane Hookom said:
If SupplFrom is numeric, try:

UPDATE ElPowerDistrPanels SET
ElPowerDistrPanels.RatedPower =
DSum("[RatedPower]","[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]=" &
[PanelID]);

If SupplFrom is text, try:

UPDATE ElPowerDistrPanels SET
ElPowerDistrPanels.RatedPower =
DSum("[RatedPower]","[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]="""
& [PanelID] & """");

--
Duane Hookom
Microsoft Access MVP


LarsM said:
To describe my problem let us assume I have a table ElPowerDistrPanels and a
table ElPowerConsumers. In each record of ElPowerConsumers I have a field
SupplFrom with a reference to a record in ElPowerDistrPanels telling which
distribution panel that particular consumer is supplied from. I also have a
form (view: single form) based on table ElPowerDistrPanels including a sub
form (view: continuous forms) based on table ElPowerConsumers. For each page
of the main form (i.e. for each distribution panel) the sub form indicates
the consumers supplied from that particular panel
([ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]). The form
footer of the sub form indicates calculated information like e.g. the sum of
rated powers of the consumers supplied from that panel. In the main form just
below these fields of the sub form I have fields where I indicate information
like e.g. the rated power of the whole panel. In the field in the main form
indicating the rated power of the panel I can manually type the automatically
calculated value "sum of rated power of consumers supplied from this panel"
from the form footer of the sub form. However I would like to automate this
function.

I have tried to make an update query to update table ElPowerDistrPanels so
that rated power of each panel will be equal to the sum of rated powers of
the consumers supplied from that panel. Initially I would expect an update
query like this to work:

UPDATE ElPowerDistrPanels INNER JOIN ElPowerConsumers ON
ElPowerDistrPanels.PanelID = ElPowerConsumers.SupplFrom SET
ElPowerDistrPanels.RatedPower =
DSum([ElPowerConsumers].[RatedPower],[ElPowerConsumers],[ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]);

I have tried several modifications of this update query, but none of them
work.
- "Help" indicates that each of the three arguments of the DSum function
must be a string, and the example in "Help" indicate use of quotation marks.
If I do this, I just get a message box indicating "Unknown" without any
explanation. If I omit the quotation marks (as above) the query at least
tries to do something.
- Instead of INNER JOIN I tried to use LEFT JOIN and even RIGHT JOIN, but
that doesn't help me either.
Does anyone have an idea what causes this problem, and what I can do to
solve it?

Actually the above description of my database is not completely correct, but
based on tests carried out on temporary tables I don't think, it makes any
difference to the cause of my problem. Actually I don't have any table
ElPowerDistrPanels. The distribution panels are just represented as records
in the same table as the consumers. The reason for this is, that a sub
distribution panel seen from "below" (consumer) is a distribution panel, but
seen from "above" (main distribution panel) the sub distribution panel itself
is a consumer. A plant can have several levels of sub distribution panels. By
running the update query as many times, as there are levels, I would update
one level each time from below and end up with the rated power of the highest
level main distribution panel i.e. the total electrical power consumption of
the whole plant.
Click to expand...
Click to expand...
 
You stated ___ so I tried to add "" & [PanelID] & """ ___
It looks like you didn't include as many quotes as I did in my suggestion.

Please try what I suggested and if it didn't work, come back with your full
SQL statement and the exact error message.

--
Duane Hookom
Microsoft Access MVP


LarsM said:
SupplFrom is text, so I tried to add "" & [PanelID] & """.
My problem isn't solved yet, but I may be a little closer to a solution. I
get an error message telling about a type conversion failure. In your example
(and in Help (F1) in Access) you are using quotation marks around all three
parameters of DSum. If I use quotation marks around the first parameter, I
just get a popup saying "Unknown" without any explanation, and DSum is not
able to do anything at all. If I omit these quotation marks, DSum at least
tries to do something. If I don't use quotation marks around the second
parameter I get a popup asking for a value of [ElPowerConsumers]. I can
choose to continue without typing a value, and then DSum tries to do
something. If I have the quotation marks around this parameter, I don't get
the popup asking for a value of [ElPowerConsumers]. This tells me, that
regarding the first parameter there can't be any quotation marks, and
regarding the secondparameter there should be quotation marks. Regarding the
third parameter till now it doesn't seem to make any difference, if the
quotation marks are included or not.

I was thinking about what type of value DSum is returning. In my tables
[RatedPower] is type Single. If DSum pr deafult returns a type Double, that
could maybe cause the trouble. So I added a CSng to the expression:
ElPowerDistrPanels.RatedPower =
CSng(DSum([RatedPower],"[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]="" & [PanelID] &"""))
This did not solve the problem either.

I have been thiking about, where there could be type conversions going
wrong, but I haven't found the solution yet. Now at least I know, that I have
to look for the error somewhere around type conversions.



Duane Hookom said:
If SupplFrom is numeric, try:

UPDATE ElPowerDistrPanels SET
ElPowerDistrPanels.RatedPower =
DSum("[RatedPower]","[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]=" &
[PanelID]);

If SupplFrom is text, try:

UPDATE ElPowerDistrPanels SET
ElPowerDistrPanels.RatedPower =
DSum("[RatedPower]","[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]="""
& [PanelID] & """");

--
Duane Hookom
Microsoft Access MVP


LarsM said:
To describe my problem let us assume I have a table ElPowerDistrPanels and a
table ElPowerConsumers. In each record of ElPowerConsumers I have a field
SupplFrom with a reference to a record in ElPowerDistrPanels telling which
distribution panel that particular consumer is supplied from. I also have a
form (view: single form) based on table ElPowerDistrPanels including a sub
form (view: continuous forms) based on table ElPowerConsumers. For each page
of the main form (i.e. for each distribution panel) the sub form indicates
the consumers supplied from that particular panel
([ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]). The form
footer of the sub form indicates calculated information like e.g. the sum of
rated powers of the consumers supplied from that panel. In the main form just
below these fields of the sub form I have fields where I indicate information
like e.g. the rated power of the whole panel. In the field in the main form
indicating the rated power of the panel I can manually type the automatically
calculated value "sum of rated power of consumers supplied from this panel"
from the form footer of the sub form. However I would like to automate this
function.

I have tried to make an update query to update table ElPowerDistrPanels so
that rated power of each panel will be equal to the sum of rated powers of
the consumers supplied from that panel. Initially I would expect an update
query like this to work:

UPDATE ElPowerDistrPanels INNER JOIN ElPowerConsumers ON
ElPowerDistrPanels.PanelID = ElPowerConsumers.SupplFrom SET
ElPowerDistrPanels.RatedPower =
DSum([ElPowerConsumers].[RatedPower],[ElPowerConsumers],[ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]);

I have tried several modifications of this update query, but none of them
work.
- "Help" indicates that each of the three arguments of the DSum function
must be a string, and the example in "Help" indicate use of quotation marks.
If I do this, I just get a message box indicating "Unknown" without any
explanation. If I omit the quotation marks (as above) the query at least
tries to do something.
- Instead of INNER JOIN I tried to use LEFT JOIN and even RIGHT JOIN, but
that doesn't help me either.
Does anyone have an idea what causes this problem, and what I can do to
solve it?

Actually the above description of my database is not completely correct, but
based on tests carried out on temporary tables I don't think, it makes any
difference to the cause of my problem. Actually I don't have any table
ElPowerDistrPanels. The distribution panels are just represented as records
in the same table as the consumers. The reason for this is, that a sub
distribution panel seen from "below" (consumer) is a distribution panel, but
seen from "above" (main distribution panel) the sub distribution panel itself
is a consumer. A plant can have several levels of sub distribution panels. By
running the update query as many times, as there are levels, I would update
one level each time from below and end up with the rated power of the highest
level main distribution panel i.e. the total electrical power consumption of
the whole plant.
Click to expand...
Click to expand...
Click to expand...
 
In my actual database things are a little more complex than described until
now:
1. The table ElPowerDistrPanels doesn't exist as a separate table - it is
actually included in the same table as the consumers. In the SQL statement
from my actual database PowerCons_1 represents the consumers and PowerCons
represents the distribution panels.
2. The record identification is not just one field. It is actually a
combination of three fields.

The actual SQL statement (version after the latest changes based on your
suggestion, as I understand it) is:
UPDATE PowerCons INNER JOIN PowerCons AS PowerCons_1 ON (PowerCons.TagNum =
PowerCons_1.FromTagN) AND (PowerCons.TagAlpha = PowerCons_1.FromTagA) AND
(PowerCons.System = PowerCons_1.FromSys) SET PowerCons.Power =
DSum("[Power]","[PowerCons_1]","[PowerCons_1].[FromSys]=""" &
[PowerCons].[System] & """ AND [PowerCons_1].[FromTagA]=""" &
[PowerCons].[TagAlpha] & """ AND [PowerCons_1].[FromTagN]=""" &
[PowerCons].[TagNum] & """");

The error message is:
Microsoft Office Access didn't update 124 field(s) due to a type conversion
failure, 0 record(s) due to key violations, 0 record(s) due to lock
violations, an 0 record(s) due to validation rule violations.

124 is the total number of records that should be updated, which means that
no fields are updated.

LarsM


Duane Hookom said:
You stated ___ so I tried to add "" & [PanelID] & """ ___
It looks like you didn't include as many quotes as I did in my suggestion.

Please try what I suggested and if it didn't work, come back with your full
SQL statement and the exact error message.

--
Duane Hookom
Microsoft Access MVP


LarsM said:
SupplFrom is text, so I tried to add "" & [PanelID] & """.
My problem isn't solved yet, but I may be a little closer to a solution. I
get an error message telling about a type conversion failure. In your example
(and in Help (F1) in Access) you are using quotation marks around all three
parameters of DSum. If I use quotation marks around the first parameter, I
just get a popup saying "Unknown" without any explanation, and DSum is not
able to do anything at all. If I omit these quotation marks, DSum at least
tries to do something. If I don't use quotation marks around the second
parameter I get a popup asking for a value of [ElPowerConsumers]. I can
choose to continue without typing a value, and then DSum tries to do
something. If I have the quotation marks around this parameter, I don't get
the popup asking for a value of [ElPowerConsumers]. This tells me, that
regarding the first parameter there can't be any quotation marks, and
regarding the secondparameter there should be quotation marks. Regarding the
third parameter till now it doesn't seem to make any difference, if the
quotation marks are included or not.

I was thinking about what type of value DSum is returning. In my tables
[RatedPower] is type Single. If DSum pr deafult returns a type Double, that
could maybe cause the trouble. So I added a CSng to the expression:
ElPowerDistrPanels.RatedPower =
CSng(DSum([RatedPower],"[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]="" & [PanelID] &"""))
This did not solve the problem either.

I have been thiking about, where there could be type conversions going
wrong, but I haven't found the solution yet. Now at least I know, that I have
to look for the error somewhere around type conversions.



Duane Hookom said:
If SupplFrom is numeric, try:

UPDATE ElPowerDistrPanels SET
ElPowerDistrPanels.RatedPower =
DSum("[RatedPower]","[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]=" &
[PanelID]);

If SupplFrom is text, try:

UPDATE ElPowerDistrPanels SET
ElPowerDistrPanels.RatedPower =
DSum("[RatedPower]","[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]="""
& [PanelID] & """");

--
Duane Hookom
Microsoft Access MVP


:

To describe my problem let us assume I have a table ElPowerDistrPanels and a
table ElPowerConsumers. In each record of ElPowerConsumers I have a field
SupplFrom with a reference to a record in ElPowerDistrPanels telling which
distribution panel that particular consumer is supplied from. I also have a
form (view: single form) based on table ElPowerDistrPanels including a sub
form (view: continuous forms) based on table ElPowerConsumers. For each page
of the main form (i.e. for each distribution panel) the sub form indicates
the consumers supplied from that particular panel
([ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]). The form
footer of the sub form indicates calculated information like e.g. the sum of
rated powers of the consumers supplied from that panel. In the main form just
below these fields of the sub form I have fields where I indicate information
like e.g. the rated power of the whole panel. In the field in the main form
indicating the rated power of the panel I can manually type the automatically
calculated value "sum of rated power of consumers supplied from this panel"
from the form footer of the sub form. However I would like to automate this
function.

I have tried to make an update query to update table ElPowerDistrPanels so
that rated power of each panel will be equal to the sum of rated powers of
the consumers supplied from that panel. Initially I would expect an update
query like this to work:

UPDATE ElPowerDistrPanels INNER JOIN ElPowerConsumers ON
ElPowerDistrPanels.PanelID = ElPowerConsumers.SupplFrom SET
ElPowerDistrPanels.RatedPower =
DSum([ElPowerConsumers].[RatedPower],[ElPowerConsumers],[ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]);

I have tried several modifications of this update query, but none of them
work.
- "Help" indicates that each of the three arguments of the DSum function
must be a string, and the example in "Help" indicate use of quotation marks.
If I do this, I just get a message box indicating "Unknown" without any
explanation. If I omit the quotation marks (as above) the query at least
tries to do something.
- Instead of INNER JOIN I tried to use LEFT JOIN and even RIGHT JOIN, but
that doesn't help me either.
Does anyone have an idea what causes this problem, and what I can do to
solve it?

Actually the above description of my database is not completely correct, but
based on tests carried out on temporary tables I don't think, it makes any
difference to the cause of my problem. Actually I don't have any table
ElPowerDistrPanels. The distribution panels are just represented as records
in the same table as the consumers. The reason for this is, that a sub
distribution panel seen from "below" (consumer) is a distribution panel, but
seen from "above" (main distribution panel) the sub distribution panel itself
is a consumer. A plant can have several levels of sub distribution panels. By
running the update query as many times, as there are levels, I would update
one level each time from below and end up with the rated power of the highest
level main distribution panel i.e. the total electrical power consumption of
the whole plant.
Click to expand...
Click to expand...
Click to expand...
 
Do we have all the facts now? I guess it would help to have a better
understanding of your data. However if all the fields other than Power are
text, try just this:

UPDATE PowerCons
SET PowerCons.Power = DSum("[Power]","[PowerCons]","[FromSys]=""" & [System]
& """ AND [FromTagA]=""" & [TagAlpha] & """ AND [FromTagN]=""" & [TagNum] &
"""");

--
Duane Hookom
Microsoft Access MVP


LarsM said:
In my actual database things are a little more complex than described until
now:
1. The table ElPowerDistrPanels doesn't exist as a separate table - it is
actually included in the same table as the consumers. In the SQL statement
from my actual database PowerCons_1 represents the consumers and PowerCons
represents the distribution panels.
2. The record identification is not just one field. It is actually a
combination of three fields.

The actual SQL statement (version after the latest changes based on your
suggestion, as I understand it) is:
UPDATE PowerCons INNER JOIN PowerCons AS PowerCons_1 ON (PowerCons.TagNum =
PowerCons_1.FromTagN) AND (PowerCons.TagAlpha = PowerCons_1.FromTagA) AND
(PowerCons.System = PowerCons_1.FromSys) SET PowerCons.Power =
DSum("[Power]","[PowerCons_1]","[PowerCons_1].[FromSys]=""" &
[PowerCons].[System] & """ AND [PowerCons_1].[FromTagA]=""" &
[PowerCons].[TagAlpha] & """ AND [PowerCons_1].[FromTagN]=""" &
[PowerCons].[TagNum] & """");

The error message is:
Microsoft Office Access didn't update 124 field(s) due to a type conversion
failure, 0 record(s) due to key violations, 0 record(s) due to lock
violations, an 0 record(s) due to validation rule violations.

124 is the total number of records that should be updated, which means that
no fields are updated.

LarsM


Duane Hookom said:
You stated ___ so I tried to add "" & [PanelID] & """ ___
It looks like you didn't include as many quotes as I did in my suggestion.

Please try what I suggested and if it didn't work, come back with your full
SQL statement and the exact error message.

--
Duane Hookom
Microsoft Access MVP


LarsM said:
SupplFrom is text, so I tried to add "" & [PanelID] & """.
My problem isn't solved yet, but I may be a little closer to a solution. I
get an error message telling about a type conversion failure. In your example
(and in Help (F1) in Access) you are using quotation marks around all three
parameters of DSum. If I use quotation marks around the first parameter, I
just get a popup saying "Unknown" without any explanation, and DSum is not
able to do anything at all. If I omit these quotation marks, DSum at least
tries to do something. If I don't use quotation marks around the second
parameter I get a popup asking for a value of [ElPowerConsumers]. I can
choose to continue without typing a value, and then DSum tries to do
something. If I have the quotation marks around this parameter, I don't get
the popup asking for a value of [ElPowerConsumers]. This tells me, that
regarding the first parameter there can't be any quotation marks, and
regarding the secondparameter there should be quotation marks. Regarding the
third parameter till now it doesn't seem to make any difference, if the
quotation marks are included or not.

I was thinking about what type of value DSum is returning. In my tables
[RatedPower] is type Single. If DSum pr deafult returns a type Double, that
could maybe cause the trouble. So I added a CSng to the expression:
ElPowerDistrPanels.RatedPower =
CSng(DSum([RatedPower],"[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]="" & [PanelID] &"""))
This did not solve the problem either.

I have been thiking about, where there could be type conversions going
wrong, but I haven't found the solution yet. Now at least I know, that I have
to look for the error somewhere around type conversions.



:

If SupplFrom is numeric, try:

UPDATE ElPowerDistrPanels SET
ElPowerDistrPanels.RatedPower =
DSum("[RatedPower]","[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]=" &
[PanelID]);

If SupplFrom is text, try:

UPDATE ElPowerDistrPanels SET
ElPowerDistrPanels.RatedPower =
DSum("[RatedPower]","[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]="""
& [PanelID] & """");

--
Duane Hookom
Microsoft Access MVP


:

To describe my problem let us assume I have a table ElPowerDistrPanels and a
table ElPowerConsumers. In each record of ElPowerConsumers I have a field
SupplFrom with a reference to a record in ElPowerDistrPanels telling which
distribution panel that particular consumer is supplied from. I also have a
form (view: single form) based on table ElPowerDistrPanels including a sub
form (view: continuous forms) based on table ElPowerConsumers. For each page
of the main form (i.e. for each distribution panel) the sub form indicates
the consumers supplied from that particular panel
([ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]). The form
footer of the sub form indicates calculated information like e.g. the sum of
rated powers of the consumers supplied from that panel. In the main form just
below these fields of the sub form I have fields where I indicate information
like e.g. the rated power of the whole panel. In the field in the main form
indicating the rated power of the panel I can manually type the automatically
calculated value "sum of rated power of consumers supplied from this panel"
from the form footer of the sub form. However I would like to automate this
function.

I have tried to make an update query to update table ElPowerDistrPanels so
that rated power of each panel will be equal to the sum of rated powers of
the consumers supplied from that panel. Initially I would expect an update
query like this to work:

UPDATE ElPowerDistrPanels INNER JOIN ElPowerConsumers ON
ElPowerDistrPanels.PanelID = ElPowerConsumers.SupplFrom SET
ElPowerDistrPanels.RatedPower =
DSum([ElPowerConsumers].[RatedPower],[ElPowerConsumers],[ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]);

I have tried several modifications of this update query, but none of them
work.
- "Help" indicates that each of the three arguments of the DSum function
must be a string, and the example in "Help" indicate use of quotation marks.
If I do this, I just get a message box indicating "Unknown" without any
explanation. If I omit the quotation marks (as above) the query at least
tries to do something.
- Instead of INNER JOIN I tried to use LEFT JOIN and even RIGHT JOIN, but
that doesn't help me either.
Does anyone have an idea what causes this problem, and what I can do to
solve it?

Actually the above description of my database is not completely correct, but
based on tests carried out on temporary tables I don't think, it makes any
difference to the cause of my problem. Actually I don't have any table
ElPowerDistrPanels. The distribution panels are just represented as records
in the same table as the consumers. The reason for this is, that a sub
distribution panel seen from "below" (consumer) is a distribution panel, but
seen from "above" (main distribution panel) the sub distribution panel itself
is a consumer. A plant can have several levels of sub distribution panels. By
running the update query as many times, as there are levels, I would update
one level each time from below and end up with the rated power of the highest
level main distribution panel i.e. the total electrical power consumption of
the whole plant.
Click to expand...
Click to expand...
Click to expand...
 
I tried your much simpler SQL statement, and now the update query at least
does something, and I don't get eny error message. To me this was a surprise,
as I can't see which type conversions have disappeared compared to my more
complex SQL statement. I still don't understand the difference between these
two SQL statements regarding type conversions.

My next step will be to correct the update query / SQL statement, so that it
not just does something, but does the intended action and nothing else. I
think I have to do that myself. The criteria will be more complex than in the
actual very simple version. I just hope, this won't make my problems return.

Thanks a lot for the help so far!


Duane Hookom said:
Do we have all the facts now? I guess it would help to have a better
understanding of your data. However if all the fields other than Power are
text, try just this:

UPDATE PowerCons
SET PowerCons.Power = DSum("[Power]","[PowerCons]","[FromSys]=""" & [System]
& """ AND [FromTagA]=""" & [TagAlpha] & """ AND [FromTagN]=""" & [TagNum] &
"""");

--
Duane Hookom
Microsoft Access MVP


LarsM said:
In my actual database things are a little more complex than described until
now:
1. The table ElPowerDistrPanels doesn't exist as a separate table - it is
actually included in the same table as the consumers. In the SQL statement
from my actual database PowerCons_1 represents the consumers and PowerCons
represents the distribution panels.
2. The record identification is not just one field. It is actually a
combination of three fields.

The actual SQL statement (version after the latest changes based on your
suggestion, as I understand it) is:
UPDATE PowerCons INNER JOIN PowerCons AS PowerCons_1 ON (PowerCons.TagNum =
PowerCons_1.FromTagN) AND (PowerCons.TagAlpha = PowerCons_1.FromTagA) AND
(PowerCons.System = PowerCons_1.FromSys) SET PowerCons.Power =
DSum("[Power]","[PowerCons_1]","[PowerCons_1].[FromSys]=""" &
[PowerCons].[System] & """ AND [PowerCons_1].[FromTagA]=""" &
[PowerCons].[TagAlpha] & """ AND [PowerCons_1].[FromTagN]=""" &
[PowerCons].[TagNum] & """");

The error message is:
Microsoft Office Access didn't update 124 field(s) due to a type conversion
failure, 0 record(s) due to key violations, 0 record(s) due to lock
violations, an 0 record(s) due to validation rule violations.

124 is the total number of records that should be updated, which means that
no fields are updated.

LarsM


Duane Hookom said:
You stated ___ so I tried to add "" & [PanelID] & """ ___
It looks like you didn't include as many quotes as I did in my suggestion.

Please try what I suggested and if it didn't work, come back with your full
SQL statement and the exact error message.

--
Duane Hookom
Microsoft Access MVP


:

SupplFrom is text, so I tried to add "" & [PanelID] & """.
My problem isn't solved yet, but I may be a little closer to a solution. I
get an error message telling about a type conversion failure. In your example
(and in Help (F1) in Access) you are using quotation marks around all three
parameters of DSum. If I use quotation marks around the first parameter, I
just get a popup saying "Unknown" without any explanation, and DSum is not
able to do anything at all. If I omit these quotation marks, DSum at least
tries to do something. If I don't use quotation marks around the second
parameter I get a popup asking for a value of [ElPowerConsumers]. I can
choose to continue without typing a value, and then DSum tries to do
something. If I have the quotation marks around this parameter, I don't get
the popup asking for a value of [ElPowerConsumers]. This tells me, that
regarding the first parameter there can't be any quotation marks, and
regarding the secondparameter there should be quotation marks. Regarding the
third parameter till now it doesn't seem to make any difference, if the
quotation marks are included or not.

I was thinking about what type of value DSum is returning. In my tables
[RatedPower] is type Single. If DSum pr deafult returns a type Double, that
could maybe cause the trouble. So I added a CSng to the expression:
ElPowerDistrPanels.RatedPower =
CSng(DSum([RatedPower],"[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]="" & [PanelID] &"""))
This did not solve the problem either.

I have been thiking about, where there could be type conversions going
wrong, but I haven't found the solution yet. Now at least I know, that I have
to look for the error somewhere around type conversions.



:

If SupplFrom is numeric, try:

UPDATE ElPowerDistrPanels SET
ElPowerDistrPanels.RatedPower =
DSum("[RatedPower]","[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]=" &
[PanelID]);

If SupplFrom is text, try:

UPDATE ElPowerDistrPanels SET
ElPowerDistrPanels.RatedPower =
DSum("[RatedPower]","[ElPowerConsumers]","[ElPowerConsumers].[SupplFrom]="""
& [PanelID] & """");

--
Duane Hookom
Microsoft Access MVP


:

To describe my problem let us assume I have a table ElPowerDistrPanels and a
table ElPowerConsumers. In each record of ElPowerConsumers I have a field
SupplFrom with a reference to a record in ElPowerDistrPanels telling which
distribution panel that particular consumer is supplied from. I also have a
form (view: single form) based on table ElPowerDistrPanels including a sub
form (view: continuous forms) based on table ElPowerConsumers. For each page
of the main form (i.e. for each distribution panel) the sub form indicates
the consumers supplied from that particular panel
([ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]). The form
footer of the sub form indicates calculated information like e.g. the sum of
rated powers of the consumers supplied from that panel. In the main form just
below these fields of the sub form I have fields where I indicate information
like e.g. the rated power of the whole panel. In the field in the main form
indicating the rated power of the panel I can manually type the automatically
calculated value "sum of rated power of consumers supplied from this panel"
from the form footer of the sub form. However I would like to automate this
function.

I have tried to make an update query to update table ElPowerDistrPanels so
that rated power of each panel will be equal to the sum of rated powers of
the consumers supplied from that panel. Initially I would expect an update
query like this to work:

UPDATE ElPowerDistrPanels INNER JOIN ElPowerConsumers ON
ElPowerDistrPanels.PanelID = ElPowerConsumers.SupplFrom SET
ElPowerDistrPanels.RatedPower =
DSum([ElPowerConsumers].[RatedPower],[ElPowerConsumers],[ElPowerConsumers].[SupplFrom]=[ElPowerDistrPanels].[PanelID]);

I have tried several modifications of this update query, but none of them
work.
- "Help" indicates that each of the three arguments of the DSum function
must be a string, and the example in "Help" indicate use of quotation marks.
If I do this, I just get a message box indicating "Unknown" without any
explanation. If I omit the quotation marks (as above) the query at least
tries to do something.
- Instead of INNER JOIN I tried to use LEFT JOIN and even RIGHT JOIN, but
that doesn't help me either.
Does anyone have an idea what causes this problem, and what I can do to
solve it?

Actually the above description of my database is not completely correct, but
based on tests carried out on temporary tables I don't think, it makes any
difference to the cause of my problem. Actually I don't have any table
ElPowerDistrPanels. The distribution panels are just represented as records
in the same table as the consumers. The reason for this is, that a sub
distribution panel seen from "below" (consumer) is a distribution panel, but
seen from "above" (main distribution panel) the sub distribution panel itself
is a consumer. A plant can have several levels of sub distribution panels. By
running the update query as many times, as there are levels, I would update
one level each time from below and end up with the rated power of the highest
level main distribution panel i.e. the total electrical power consumption of
the whole plant.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top