Update Query and Format issues

[Simone]

I have two tables made from queries of summarized data. I am trying to update
one table with information from the other using the following sql:

UPDATE [Retention Superior Perf], [Superior Totals] SET [Retention Superior
Perf].CountOfEmpLastName = IIf([Latest Perf
Rating]="Total",[SumofCountofEmpLastName],IIf([Latest Perf Rating]="YTD
Leavers",[YTD Terms],IIf([Latest Perf
Rating]="YTD%",[YTD],[CountofEmpLastName])));

All my records update properly except the "YTD%" record. I keep getting a
error - "conversion fail/ Key violation/ lock violation/ validation role
violation", for the one record. Not quite sure how to resolve, but I assume
its because its an integer (.0155), while the other records that did update
are whole numbers. How can I fix to have this record updated in my query.

I have tried to update this one record by individually using:

UPDATE [Retention Superior Perf], [Superior Totals] SET [Retention Superior
Perf].CountOfEmpLastName = [YTD]
WHERE ((([Retention Superior Perf].[Latest Perf Rating])="YTD%"));

While I do not get an error message, it shows up as a zero but should be
..0155 as displayed in the table.

(FYI - no relationship in either table. I have also tried a left join in
first SQL, but all of the above records show up as null)

 

[John Spencer MVP]

I suspect that the field has a field size of Long Integer or Integer. If you
want to store decimal fractions then you need a field that has a field size of
Double or single or that is defined as a currency field.

Your other option if you can't or won't change the field Size in the table
would be to multiply your percentage figure by 100 or 1000 or 10,000 to get
whole number that you can use to represent the percentage. Of course 155
would not be clear to anyone that did not understand that it represents 1.55
percent (that is you must divide number by 100 in order to get the nominal
percentage).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I have two tables made from queries of summarized data. I am trying to update
one table with information from the other using the following sql:

UPDATE [Retention Superior Perf], [Superior Totals] SET [Retention Superior
Perf].CountOfEmpLastName = IIf([Latest Perf
Rating]="Total",[SumofCountofEmpLastName],IIf([Latest Perf Rating]="YTD
Leavers",[YTD Terms],IIf([Latest Perf
Rating]="YTD%",[YTD],[CountofEmpLastName])));

All my records update properly except the "YTD%" record. I keep getting a
error - "conversion fail/ Key violation/ lock violation/ validation role
violation", for the one record. Not quite sure how to resolve, but I assume
its because its an integer (.0155), while the other records that did update
are whole numbers. How can I fix to have this record updated in my query.

If the field CountOfEmployeeLastName is an Integer or Long Integer field, as
you imply, then you cannot store 0.0155 in it: an Integer is by definition a
whole number, and any fractional portion will be truncated.

I see a real design flaw in that you're apparently trying to use this one
field for three different purposes - and given the datatype issue, three
different INCOMPATIBLE purposes. That's above and beyond the problems with
storing derived data in your table in the first place!