Comparing columns to find mistakes

[jensss]

Hello Access World Forums!
My company, a powerplant, uses Access 2000 for typing in data for controll of all cables in the plant. There are lots of columns and every key must be typed in right.

One important thing is that the lenght of all cables is correct. All cables have an ID number that tells how long it is, for example it can look like this in the database:

ID From:
312
ID To:
282
Total Length:
30

Which means that: ID From - ID To = Total Length

Is it possible to troubleshoot the entire database to find type-in mistakes by letting access do some math on theese three columns?

Thanks
Jens

 

[KenHigg]

Just a note here - It's considered bad practice to store a value like this. You should always calculate it on the fly as you need it.

 

[jensss]

I'm sorry, can you describe what you mean by "a value like this"?

/Jens

 

[KenHigg]

First off, you are storing 'ID To' and 'ID From' in a table, right? And probably in the same table? And the probably storing the difference (Total Length) in the same table?

 

[maxmangion]

It means that calculated fields are not meant to be stored in tables. You should create a calculated field in your query and then do the math there i.e. your new field would be:

NewField: [IDTo] - [IDFrom]

 

[jensss]

It means that calculated fields are not meant to be stored in tables. You should create a calculated field in your query and then do the math there i.e. your new field would be:

NewField: [IDTo] - [IDFrom]

What are calculated fields ment to be stored in then?

 

[jensss]

Excuse me, i dont think im following here.
Im just asking if there is any function in the program that can search for mistakes in this matter, without adding or changing the fields i allready have

 

[dkinley]

It is not changing anything ...

NewField: [IDTo] - [IDFrom]

The "NewField" is an expression in a query. Think of it like a temporary variable. In the columns of your query, you can add anything you want to the query. Here, NewField: is using a temporary spot to subtract your two ID fields.

This is how you can do things 'on-the-fly' or in a 'as needed' manner without the need to use space by storing data. Just type anything in the Field of a query followed by a colon and then anything you want to perform analysis on.

-dK

 

[jensss]

Thanks for the help! I think i cna get this to work now