Update Query - One Field, Mutiple Posibilities...

I have a field in one table that must have one of 18 (string) different
values deplending on the value of a cost code. The table fields a

[tblActMap]![Act] - The cost code
[tblActMap]![Srt3] - The field that needs updating...

I tried creating an update query with this in the "Update To" row:

iif(val([tblActMap]![Act])=8999,"08",
iif(val([tblActMap]![Act])=9000 and val([tblActMap]![Act])=9999,"09",
iif(val([tblActMap]![Act])=10000 and val([tblActMap]![Act])=11999,"10",
iif(val([tblActMap]![Act])=12000 and val([tblActMap]![Act])=15999,"12",
iif(val([tblActMap]![Act])=16000 and val([tblActMap]![Act])=17999,"16",
iif(val([tblActMap]![Act])=18000 and val([tblActMap]![Act])=19999,"18",
iif(val([tblActMap]![Act])=20000 and val([tblActMap]![Act])=22999,"20",
iif(val([tblActMap]![Act])=23000 and val([tblActMap]![Act])=25999,"23",
iif(val([tblActMap]![Act])=26000 and val([tblActMap]![Act])=35999,"26",
iif(val([tblActMap]![Act])=36000 and val([tblActMap]![Act])=41999,"36",
iif(val([tblActMap]![Act])=42000 and val([tblActMap]![Act])=49999,"42",
iif(val([tblActMap]![Act])=50000 and val([tblActMap]![Act])=51999,"50",
iif(val([tblActMap]![Act])=52000 and val([tblActMap]![Act])=53999,"52",
iif(val([tblActMap]![Act])=54000 and val([tblActMap]![Act])=55999,"54",
iif(val([tblActMap]![Act])=56000 and val([tblActMap]![Act])=56999,"56",
iif(val([tblActMap]![Act])=57000 and val([tblActMap]![Act])=58999,"57",
iif(val([tblActMap]![Act])=59000 and
val([tblActMap]![Act])=59999,"59")))))))))))))))))

LOL - I thought it would be a long shot, and as exected, Access said "... to
complex...". I don't want to make a bunch of update queries (but could if
that's the only way).

Is there another function that would do this? I would be open to setting up
a tblSrt3 table, with high, low, and code values and update that way. Not
sure how to do this because the query would have to look up (dlookup?) a cos
code that falls between two values in anther table....

Any suggestions?

--
Thanks - John

I think a translation table is the right way to go but try it this way.
RateTable-Mileage --
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521

SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

--
KARL DEWEY
Build a little - Test a little


"John" wrote:

I have a field in one table that must have one of 18 (string) different
values deplending on the value of a cost code. The table fields a

[tblActMap]![Act] - The cost code
[tblActMap]![Srt3] - The field that needs updating...

I tried creating an update query with this in the "Update To" row:

iif(val([tblActMap]![Act])=8999,"08",
iif(val([tblActMap]![Act])=9000 and val([tblActMap]![Act])=9999,"09",
iif(val([tblActMap]![Act])=10000 and val([tblActMap]![Act])=11999,"10",
iif(val([tblActMap]![Act])=12000 and val([tblActMap]![Act])=15999,"12",
iif(val([tblActMap]![Act])=16000 and val([tblActMap]![Act])=17999,"16",
iif(val([tblActMap]![Act])=18000 and val([tblActMap]![Act])=19999,"18",
iif(val([tblActMap]![Act])=20000 and val([tblActMap]![Act])=22999,"20",
iif(val([tblActMap]![Act])=23000 and val([tblActMap]![Act])=25999,"23",
iif(val([tblActMap]![Act])=26000 and val([tblActMap]![Act])=35999,"26",
iif(val([tblActMap]![Act])=36000 and val([tblActMap]![Act])=41999,"36",
iif(val([tblActMap]![Act])=42000 and val([tblActMap]![Act])=49999,"42",
iif(val([tblActMap]![Act])=50000 and val([tblActMap]![Act])=51999,"50",
iif(val([tblActMap]![Act])=52000 and val([tblActMap]![Act])=53999,"52",
iif(val([tblActMap]![Act])=54000 and val([tblActMap]![Act])=55999,"54",
iif(val([tblActMap]![Act])=56000 and val([tblActMap]![Act])=56999,"56",
iif(val([tblActMap]![Act])=57000 and val([tblActMap]![Act])=58999,"57",
iif(val([tblActMap]![Act])=59000 and
val([tblActMap]![Act])=59999,"59")))))))))))))))))

LOL - I thought it would be a long shot, and as exected, Access said "... to
complex...". I don't want to make a bunch of update queries (but could if
that's the only way).

Is there another function that would do this? I would be open to setting up
a tblSrt3 table, with high, low, and code values and update that way. Not
sure how to do this because the query would have to look up (dlookup?) a cos
code that falls between two values in anther table....

Any suggestions?

--
Thanks - John

Karl,

I think I see where you are heading, but I am kind of new at this. Did I
omit that this had to be an update query? The user imports monthly cost
codes and this mapping table allows us to sort and subtotal by the mapped
value. The newly imported codes could be newly added this month, so all new
codes are appended to the maping table. They then have to be updated with
craft code, a magement code, a cost owner, etc. So I have two questions:

1. After ceating the translation table, do I just put it in the update query
along with the activity map, with no join between them?

2. You are showing a SELECT statement, what would the UPDATE statement look
like? (kind of being lazy here, I could look it up, but if you don't mind...)


Thanks,

--
John


"KARL DEWEY" wrote:

I think a translation table is the right way to go but try it this way.
RateTable-Mileage --
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521

SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

--
KARL DEWEY
Build a little - Test a little


"John" wrote:

I have a field in one table that must have one of 18 (string) different
values deplending on the value of a cost code. The table fields a

[tblActMap]![Act] - The cost code
[tblActMap]![Srt3] - The field that needs updating...

I tried creating an update query with this in the "Update To" row:

iif(val([tblActMap]![Act])=8999,"08",
iif(val([tblActMap]![Act])=9000 and val([tblActMap]![Act])=9999,"09",
iif(val([tblActMap]![Act])=10000 and val([tblActMap]![Act])=11999,"10",
iif(val([tblActMap]![Act])=12000 and val([tblActMap]![Act])=15999,"12",
iif(val([tblActMap]![Act])=16000 and val([tblActMap]![Act])=17999,"16",
iif(val([tblActMap]![Act])=18000 and val([tblActMap]![Act])=19999,"18",
iif(val([tblActMap]![Act])=20000 and val([tblActMap]![Act])=22999,"20",
iif(val([tblActMap]![Act])=23000 and val([tblActMap]![Act])=25999,"23",
iif(val([tblActMap]![Act])=26000 and val([tblActMap]![Act])=35999,"26",
iif(val([tblActMap]![Act])=36000 and val([tblActMap]![Act])=41999,"36",
iif(val([tblActMap]![Act])=42000 and val([tblActMap]![Act])=49999,"42",
iif(val([tblActMap]![Act])=50000 and val([tblActMap]![Act])=51999,"50",
iif(val([tblActMap]![Act])=52000 and val([tblActMap]![Act])=53999,"52",
iif(val([tblActMap]![Act])=54000 and val([tblActMap]![Act])=55999,"54",
iif(val([tblActMap]![Act])=56000 and val([tblActMap]![Act])=56999,"56",
iif(val([tblActMap]![Act])=57000 and val([tblActMap]![Act])=58999,"57",
iif(val([tblActMap]![Act])=59000 and
val([tblActMap]![Act])=59999,"59")))))))))))))))))

LOL - I thought it would be a long shot, and as exected, Access said "... to
complex...". I don't want to make a bunch of update queries (but could if
that's the only way).

Is there another function that would do this? I would be open to setting up
a tblSrt3 table, with high, low, and code values and update that way. Not
sure how to do this because the query would have to look up (dlookup?) a cos
code that falls between two values in anther table....

Any suggestions?

--
Thanks - John

this mapping table allows us to sort and subtotal by the mapped value.
What gets sorted and what is totaled?

They then have to be updated with craft code, a magement code, a cost owner, etc.
I am lost here. Why can you not use a join instead of updating and retain
historical data?

Post the table structure and sample data.

--
KARL DEWEY
Build a little - Test a little


"John" wrote:

Karl,

I think I see where you are heading, but I am kind of new at this. Did I
omit that this had to be an update query? The user imports monthly cost
codes and this mapping table allows us to sort and subtotal by the mapped
value. The newly imported codes could be newly added this month, so all new
codes are appended to the maping table. They then have to be updated with
craft code, a magement code, a cost owner, etc. So I have two questions:

1. After ceating the translation table, do I just put it in the update query
along with the activity map, with no join between them?

2. You are showing a SELECT statement, what would the UPDATE statement look
like? (kind of being lazy here, I could look it up, but if you don't mind...)


Thanks,

--
John


"KARL DEWEY" wrote:

I think a translation table is the right way to go but try it this way.
RateTable-Mileage --
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521

SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

--
KARL DEWEY
Build a little - Test a little


"John" wrote:

I have a field in one table that must have one of 18 (string) different
values deplending on the value of a cost code. The table fields a

[tblActMap]![Act] - The cost code
[tblActMap]![Srt3] - The field that needs updating...

I tried creating an update query with this in the "Update To" row:

iif(val([tblActMap]![Act])=8999,"08",
iif(val([tblActMap]![Act])=9000 and val([tblActMap]![Act])=9999,"09",
iif(val([tblActMap]![Act])=10000 and val([tblActMap]![Act])=11999,"10",
iif(val([tblActMap]![Act])=12000 and val([tblActMap]![Act])=15999,"12",
iif(val([tblActMap]![Act])=16000 and val([tblActMap]![Act])=17999,"16",
iif(val([tblActMap]![Act])=18000 and val([tblActMap]![Act])=19999,"18",
iif(val([tblActMap]![Act])=20000 and val([tblActMap]![Act])=22999,"20",
iif(val([tblActMap]![Act])=23000 and val([tblActMap]![Act])=25999,"23",
iif(val([tblActMap]![Act])=26000 and val([tblActMap]![Act])=35999,"26",
iif(val([tblActMap]![Act])=36000 and val([tblActMap]![Act])=41999,"36",
iif(val([tblActMap]![Act])=42000 and val([tblActMap]![Act])=49999,"42",
iif(val([tblActMap]![Act])=50000 and val([tblActMap]![Act])=51999,"50",
iif(val([tblActMap]![Act])=52000 and val([tblActMap]![Act])=53999,"52",
iif(val([tblActMap]![Act])=54000 and val([tblActMap]![Act])=55999,"54",
iif(val([tblActMap]![Act])=56000 and val([tblActMap]![Act])=56999,"56",
iif(val([tblActMap]![Act])=57000 and val([tblActMap]![Act])=58999,"57",
iif(val([tblActMap]![Act])=59000 and
val([tblActMap]![Act])=59999,"59")))))))))))))))))

LOL - I thought it would be a long shot, and as exected, Access said "... to
complex...". I don't want to make a bunch of update queries (but could if
that's the only way).

Is there another function that would do this? I would be open to setting up
a tblSrt3 table, with high, low, and code values and update that way. Not
sure how to do this because the query would have to look up (dlookup?) a cos
code that falls between two values in anther table....

Any suggestions?

--
Thanks - John

The tblActMap has one unique cost code with 4 sort codes that are used in
various queries. Structu

Job - Project Job Number
Act - (cost code and primary key)
ActTitle - cost code description
JVID - Management sort field
CstOwnIni - cost owner (responsible party) initials
Srt1 - Cost Type (Labor, Matrial, Equip, Subcontract, Supplies)
Srt2 - Cost Category (Direct, Indirect)
Srt3 - Craft (the one we are working on - 18 different crafts
Srt4 - defined by the project, for their use...
PlnStart - date field plan start of work on this code
PlnFinish - date field, plan finish of work on this code
SumQty - sum quantities for this code when grouping several cost codes

The cost code table (tblMonthly) has a cost report for each month. There is
a 1 to many relationship from the tblActMap (1 Act) to the tblMonthly joined
on tblMonthly.act = tblActMap.act. This allows us to do a query and sum up
all direct labor in the civil (18) craft for example for any given month. Or
do a crosstab query by craft by month showing installed quantities, or actual
cost each month, or etc.

Each cost report for any given month will have about 1000 cost codes. Each
project lasts about 5 years, so we end up with about 60000 records in the
table, each cost code (assuming is was there when the project started and was
there when it finished) will have 60 about 60 records, one for each month in
the project.

I can't use a join because the cost code range for Civil Craft for example
are codes within range of 18000 to 19999. Any codes (exsisting or new ones
in the future) need to be "mapped" to craft "18". The best way to handle
this is with a mapping table that has one unique code and all it's assciated
mapped values.

I hope this makes sense...

--
Thanks - John


"KARL DEWEY" wrote:

this mapping table allows us to sort and subtotal by the mapped value.
What gets sorted and what is totaled?

They then have to be updated with craft code, a magement code, a cost owner, etc.
I am lost here. Why can you not use a join instead of updating and retain
historical data?

Post the table structure and sample data.

--
KARL DEWEY
Build a little - Test a little


"John" wrote:

Karl,

I think I see where you are heading, but I am kind of new at this. Did I
omit that this had to be an update query? The user imports monthly cost
codes and this mapping table allows us to sort and subtotal by the mapped
value. The newly imported codes could be newly added this month, so all new
codes are appended to the maping table. They then have to be updated with
craft code, a magement code, a cost owner, etc. So I have two questions:

1. After ceating the translation table, do I just put it in the update query
along with the activity map, with no join between them?

2. You are showing a SELECT statement, what would the UPDATE statement look
like? (kind of being lazy here, I could look it up, but if you don't mind...)


Thanks,

--
John


"KARL DEWEY" wrote:

I think a translation table is the right way to go but try it this way.
RateTable-Mileage --
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521

SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

--
KARL DEWEY
Build a little - Test a little


"John" wrote:

I have a field in one table that must have one of 18 (string) different
values deplending on the value of a cost code. The table fields a

[tblActMap]![Act] - The cost code
[tblActMap]![Srt3] - The field that needs updating...

I tried creating an update query with this in the "Update To" row:

iif(val([tblActMap]![Act])=8999,"08",
iif(val([tblActMap]![Act])=9000 and val([tblActMap]![Act])=9999,"09",
iif(val([tblActMap]![Act])=10000 and val([tblActMap]![Act])=11999,"10",
iif(val([tblActMap]![Act])=12000 and val([tblActMap]![Act])=15999,"12",
iif(val([tblActMap]![Act])=16000 and val([tblActMap]![Act])=17999,"16",
iif(val([tblActMap]![Act])=18000 and val([tblActMap]![Act])=19999,"18",
iif(val([tblActMap]![Act])=20000 and val([tblActMap]![Act])=22999,"20",
iif(val([tblActMap]![Act])=23000 and val([tblActMap]![Act])=25999,"23",
iif(val([tblActMap]![Act])=26000 and val([tblActMap]![Act])=35999,"26",
iif(val([tblActMap]![Act])=36000 and val([tblActMap]![Act])=41999,"36",
iif(val([tblActMap]![Act])=42000 and val([tblActMap]![Act])=49999,"42",
iif(val([tblActMap]![Act])=50000 and val([tblActMap]![Act])=51999,"50",
iif(val([tblActMap]![Act])=52000 and val([tblActMap]![Act])=53999,"52",
iif(val([tblActMap]![Act])=54000 and val([tblActMap]![Act])=55999,"54",
iif(val([tblActMap]![Act])=56000 and val([tblActMap]![Act])=56999,"56",
iif(val([tblActMap]![Act])=57000 and val([tblActMap]![Act])=58999,"57",
iif(val([tblActMap]![Act])=59000 and
val([tblActMap]![Act])=59999,"59")))))))))))))))))

LOL - I thought it would be a long shot, and as exected, Access said "... to
complex...". I don't want to make a bunch of update queries (but could if
that's the only way).

Is there another function that would do this? I would be open to setting up
a tblSrt3 table, with high, low, and code values and update that way. Not
sure how to do this because the query would have to look up (dlookup?) a cos
code that falls between two values in anther table....

Any suggestions?

--
Thanks - John