Update Query Type Conversion Failure

[Guest]

I am trying to run an update query and MS Access fails to
update any of the records because of type conversion
failure. I am not sure what this means, since the field I
am updating and the fields whose data I am updating from
are all set to currency data type. I have a feeling it
might also have something to do with the data type of my
query parameter not being explicitly defined, but I have
no idea how to define it.

Here is my update query:

UPDATE POHeader INNER JOIN PODetails ON [POHeader].
[Control Job #]=[PODetails].[Control Job#] SET POHeader.
[Total charge] = DSum("[p1chrg]+[p2chrg]+[p3chrg]+
[p4chrg]","PODetails","[Control Job#]=" & [Control Job
#]);

Would appreciate any help!

 

[Allen Browne]

Is there any way we could talk you into doing this a different way?

You should never store a calculated field (such as your total), unless there
are valid cases where the amount should be different from the calculation.
In this case, it would appear to be disastrous if the [Total charge] were
different from the sum of the elements, so there is no way I would consider
taking on the responsibility of storing the total and putting in all the
work you must do to ensure that it can never be different than the sum of
the elements. Waste of effort.

Further, where ever you see fields repeating such as p1chrg, p2chrg, p3chrg,
.... they *always* need to be in a related table. This might be as simple as
adding another field to your PODetails table to indicate what kind of entry
it is (whatever distinguishes p1chrg from p2charg from ...). Alternatively,
if the p1crg, p2chrg etc are actually related to some other sub-element of
the PO, you may need a different foreign key field, or you may even need an
intermediate table between POHeader and PODetails to resolve the
relationship correctly.

Once you have done that, the total for the invoice is a simple sum of one
column in a table. It does not get stored back to the POHeader table.


If you are determined to take the non-normalized approach anyway, the query
already has a join on PODetails.[Control Job#], so the DSum() probabably is
not needed. Without knowing the whole store, it would seem to be asking for:
UPDATE POHeader INNER JOIN PODetails
ON [POHeader].[Control Job #]=[PODetails].[Control Job#]
SET POHeader.[Total charge] = [p1chrg]+[p2chrg]+[p3chrg]+[p4chrg];

What bothers me is that people who go that route generally don't understand
why it's not a good idea, so can't guarantee their results will be right in
all cases, so their whole project is compromised.

 

[Guest]

Yes, I understand what you're saying. That [Total Charge]
field in the POHeader table is redundant. But there seems
to be no other way around it. Actually, let me explain
the structure of my tables a little bit.

PODetails contains the line items of the POHeader. So
each POHeader has anywhere from 1-10 corresponding
PODetail records. That's why I needed to sum the total.
SET POHeader.[Total charge] = [p1chrg]+[p2chrg]+[p3chrg]+
[p4chrg] only gives me the total of the first line item.

Also, my real objective here is to change the status of
records in POHeader to "closed" once they find a matching
record in an invoice table. And to match them properly, I
need to compare the total charges with the invoice amount
(which is on a total basis, not per line item). At first,
I tried using the PODetails table directly, doing a 3-way
join and setting the status of the POHeader to "closed"
once the invoice amount matched the sum of the charges in
PODetails, but it says I cannot have an aggregate
function in my where clause. That's why I resorted to
adding a [Total Charge] in my POHeader.

I've been working on this for a week and am just about
out of ideas. I'd really, really appreciate any help on
this.

Thanks!

-----Original Message-----
Is there any way we could talk you into doing this a different way?

You should never store a calculated field (such as your total), unless there
are valid cases where the amount should be different from the calculation.
In this case, it would appear to be disastrous if the [Total charge] were
different from the sum of the elements, so there is no way I would consider
taking on the responsibility of storing the total and putting in all the
work you must do to ensure that it can never be different than the sum of
the elements. Waste of effort.

Further, where ever you see fields repeating such as p1chrg, p2chrg, p3chrg,
.... they *always* need to be in a related table. This might be as simple as
adding another field to your PODetails table to indicate what kind of entry
it is (whatever distinguishes p1chrg from p2charg from ...). Alternatively,
if the p1crg, p2chrg etc are actually related to some other sub-element of
the PO, you may need a different foreign key field, or you may even need an
intermediate table between POHeader and PODetails to resolve the
relationship correctly.

Once you have done that, the total for the invoice is a simple sum of one
column in a table. It does not get stored back to the POHeader table.


If you are determined to take the non-normalized approach anyway, the query
already has a join on PODetails.[Control Job#], so the DSum() probabably is
not needed. Without knowing the whole store, it would seem to be asking for:
UPDATE POHeader INNER JOIN PODetails
ON [POHeader].[Control Job #]=[PODetails].[Control Job#]
SET POHeader.[Total charge] = [p1chrg]+[p2chrg]+[p3chrg]+ [p4chrg];

What bothers me is that people who go that route generally don't understand
why it's not a good idea, so can't guarantee their results will be right in
all cases, so their whole project is compromised.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to run an update query and MS Access fails to
update any of the records because of type conversion
failure. I am not sure what this means, since the field I
am updating and the fields whose data I am updating from
are all set to currency data type. I have a feeling it
might also have something to do with the data type of my
query parameter not being explicitly defined, but I have
no idea how to define it.

Here is my update query:

UPDATE POHeader INNER JOIN PODetails ON [POHeader].
[Control Job #]=[PODetails].[Control Job#] SET POHeader.
[Total charge] = DSum("[p1chrg]+[p2chrg]+[p3chrg]+
[p4chrg]","PODetails","[Control Job#]=" & [Control Job
#]);

Would appreciate any help!

.

 

[Allen Browne]

Having repeating fields like:
p1chrg
p2chrg
p3chrg
p4chrg
is probably unnormalized also.

But you can still get the total. Base the subform on a query.
In the query, enter a calculated field:
RowTotal: CCur(Nz([p1chrg],0) + Nz([p2chrg],0) + Nz([p3chrg],0) +
Nz([p4chrg],0))
Then in the Form Footer section of your subform:
=Sum([RowTotal])

For a purchase order to be invoiced, you would normally want some kind of
StockReceived table, to check off the quantities received since even onr row
from a PODetail may be paritally filled and partially on back order. You
would then have a process that auto-generates payments for the unpaid
StockReceived at the end of the period (month?).

Or are your POs actually client orders, if you are invoicing them? If so,
consider adding an InvoiceID foreign key to the POHeader table. The field is
Null until the invoice is created, and at that point you record the matching
invoice number into the field. (That doesn't handle the case where you did
not have enough stock to send, and so had to partially back-order the
client's order.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes, I understand what you're saying. That [Total Charge]
field in the POHeader table is redundant. But there seems
to be no other way around it. Actually, let me explain
the structure of my tables a little bit.

PODetails contains the line items of the POHeader. So
each POHeader has anywhere from 1-10 corresponding
PODetail records. That's why I needed to sum the total.
SET POHeader.[Total charge] = [p1chrg]+[p2chrg]+[p3chrg]+
[p4chrg] only gives me the total of the first line item.

Also, my real objective here is to change the status of
records in POHeader to "closed" once they find a matching
record in an invoice table. And to match them properly, I
need to compare the total charges with the invoice amount
(which is on a total basis, not per line item). At first,
I tried using the PODetails table directly, doing a 3-way
join and setting the status of the POHeader to "closed"
once the invoice amount matched the sum of the charges in
PODetails, but it says I cannot have an aggregate
function in my where clause. That's why I resorted to
adding a [Total Charge] in my POHeader.

I've been working on this for a week and am just about
out of ideas. I'd really, really appreciate any help on
this.

Thanks!

-----Original Message-----
Is there any way we could talk you into doing this a different way?

You should never store a calculated field (such as your total), unless there
are valid cases where the amount should be different from the calculation.
In this case, it would appear to be disastrous if the [Total charge] were
different from the sum of the elements, so there is no way I would consider
taking on the responsibility of storing the total and putting in all the
work you must do to ensure that it can never be different than the sum of
the elements. Waste of effort.

Further, where ever you see fields repeating such as p1chrg, p2chrg, p3chrg,
.... they *always* need to be in a related table. This might be as simple as
adding another field to your PODetails table to indicate what kind of entry
it is (whatever distinguishes p1chrg from p2charg from ...). Alternatively,
if the p1crg, p2chrg etc are actually related to some other sub-element of
the PO, you may need a different foreign key field, or you may even need an
intermediate table between POHeader and PODetails to resolve the
relationship correctly.

Once you have done that, the total for the invoice is a simple sum of one
column in a table. It does not get stored back to the POHeader table.


If you are determined to take the non-normalized approach anyway, the query
already has a join on PODetails.[Control Job#], so the DSum() probabably is
not needed. Without knowing the whole store, it would seem to be asking for:
UPDATE POHeader INNER JOIN PODetails
ON [POHeader].[Control Job #]=[PODetails].[Control Job#]
SET POHeader.[Total charge] = [p1chrg]+[p2chrg]+[p3chrg]+ [p4chrg];

What bothers me is that people who go that route generally don't understand
why it's not a good idea, so can't guarantee their results will be right in
all cases, so their whole project is compromised.

I am trying to run an update query and MS Access fails to
update any of the records because of type conversion
failure. I am not sure what this means, since the field I
am updating and the fields whose data I am updating from
are all set to currency data type. I have a feeling it
might also have something to do with the data type of my
query parameter not being explicitly defined, but I have
no idea how to define it.

Here is my update query:

UPDATE POHeader INNER JOIN PODetails ON [POHeader].
[Control Job #]=[PODetails].[Control Job#] SET POHeader.
[Total charge] = DSum("[p1chrg]+[p2chrg]+[p3chrg]+
[p4chrg]","PODetails","[Control Job#]=" & [Control Job
#]);

Would appreciate any help!