Data in Existing Table Needs Correcting

[Armand]

Let me preface by saying I'm more familiar with Excel than Access, but
the data I'm presently working with is too large for Excel.

I have discovered that the database has some inconsistencies in the
data in one table. The table has, among others, the following fields:
[Well] , [Param] , [SampDate] , [Num]. For each [Well], there are
numerous [Param] with corresponding [SampDate] and [Num] or each.

Prior to 1998, data in the [Num] field for one specific [Param] ("EC")
was input correctly. Since 1998, the input data for "EC" (the
specific [Param]) has been divided by 1000. I want to correct this
data in the table but am a little stumped how best to achieve it.

Somewhere, I need to use an IIF where both the [Param] = "EC" and the
[SampDate]>Jan 1/98.

i.e., iif(and[Param]="EC",[SampDate]>Jan 1/98),[Num]*1000,[Num])

I'm not quite sure how to go about this (an update table query
perhaps? - I tried one but wasn't sure if it was going to wipeout the
other data in the table while updating the EC data!). and any
direction would be appreciated.

Thanks,
Armand

 

[Joseph Meehan]

Let me preface by saying I'm more familiar with Excel than Access, but
the data I'm presently working with is too large for Excel.

I have discovered that the database has some inconsistencies in the
data in one table. The table has, among others, the following fields:
[Well] , [Param] , [SampDate] , [Num]. For each [Well], there are
numerous [Param] with corresponding [SampDate] and [Num] or each.

Prior to 1998, data in the [Num] field for one specific [Param] ("EC")
was input correctly. Since 1998, the input data for "EC" (the
specific [Param]) has been divided by 1000. I want to correct this
data in the table but am a little stumped how best to achieve it.

Somewhere, I need to use an IIF where both the [Param] = "EC" and the
[SampDate]>Jan 1/98.

i.e., iif(and[Param]="EC",[SampDate]>Jan 1/98),[Num]*1000,[Num])

I'm not quite sure how to go about this (an update table query
perhaps? - I tried one but wasn't sure if it was going to wipeout the
other data in the table while updating the EC data!). and any
direction would be appreciated.

Thanks,
Armand

Set up an update query. filter the query to [SampDate]>#01/01/1998#

Run the query and make sure it is working as expected.

Next I suggest adding the Num field then changing the query to an update
query. Then use the "[Num]*1000" in the update for the Num field. Run the
query and it should be done.

Note: make a copy of your database (or at least the table) first. Better
safe than sorry.

 

[Armand]

Set up an update query. filter the query to [SampDate]>#01/01/1998#

Run the query and make sure it is working as expected.

Next I suggest adding the Num field then changing the query to an update
query. Then use the "[Num]*1000" in the update for the Num field. Run the
query and it should be done.

Note: make a copy of your database (or at least the table) first. Better
safe than sorry.

I think I understand your instructions, but I'm not getting the
expected result with [Num]*1000. Nonetheless, I've tried to run the
query on a copy of the data just for kicks. I receive and error
saying it can't update the table due to "key violations". Now I'm
really lost! I'm thinking it may be easier to manually update the 316
records that need changing and be done with it! Suggestions?

Armand

 

[Joseph Meehan]

Set up an update query. filter the query to
[SampDate]>#01/01/1998#

Run the query and make sure it is working as expected.

Next I suggest adding the Num field then changing the query to an
update query. Then use the "[Num]*1000" in the update for the Num
field. Run the query and it should be done.

Note: make a copy of your database (or at least the table) first.
Better safe than sorry.

I think I understand your instructions, but I'm not getting the
expected result with [Num]*1000.

What results are you getting? Is the filed Num a number field or a text
field with numbers?

Nonetheless, I've tried to run the
query on a copy of the data just for kicks. I receive and error
saying it can't update the table due to "key violations". Now I'm
really lost! I'm thinking it may be easier to manually update the 316
records that need changing and be done with it! Suggestions?

I am going to take a guess here. Is the field in question a primary key
field? Is it used in relationship or join?