Advice on data entry and quality
- Thread starter cayham86
- Start date
What sorts of queries can I make?
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 14:36
- Joined
- May 7, 2009
- Messages
- 20,681
it depends on "what" you are proof reading.
I want to check all sorts from codes to descriptions to the way they are written etc but was posting this topic to see if anyone had any ideas for me
isladogs
Access MVP / VIP
- Local time
- Today, 06:36
- Joined
- Jan 14, 2017
- Messages
- 19,297
Your question is too vague for anyone to give a detailed answer.
To view/check existing data, use a SELECT query.
To alter existing data, use an UPDATE query.
However, as already stated, much better to ensure valid data is entered in the first place by using validation rules.
To view/check existing data, use a SELECT query.
To alter existing data, use an UPDATE query.
However, as already stated, much better to ensure valid data is entered in the first place by using validation rules.
- Local time
- Today, 00:36
- Joined
- Feb 28, 2001
- Messages
- 30,899
The problem with using a table's field validation codes is that you cannot test all possibilities. It is very difficult to test against another table's contents, for example.
You have two automatic sets of tests available that can help in some cases.
Fields can have validation rules such as ">0", "Required" (i.e. cannot be left blank), etc.
Fields can ALSO have validation if they are related to another table and Referential Integrity is turned on, which is how you would check whether the code in a code field is represented in the code's translator/validator table.
The last, most complex, and non-automatic validation would be to put some kind of VBA code in the field's "BEFOREUPDATE" event or in the "LOSTFOCUS" event, either of which would be able to trap invalid values.
Typically, you cannot point to a single method of validation.
It IS possible to identify bad data using queries, but the trick is that, OK, you've found a bad record. SO... what are you going to do about it? The queries you might write would identify validation issues but rarely would they be able to fix the problem. I'm not saying "never" because, let's face it, your question was so wide open that almost anything is possible. Just understand that some things are more likely than other things in terms of data automatic cleanup. In one sense, the sky is the limit. In another, more practical sense, your knowledge and imagination are the real limits.
You have two automatic sets of tests available that can help in some cases.
Fields can have validation rules such as ">0", "Required" (i.e. cannot be left blank), etc.
Fields can ALSO have validation if they are related to another table and Referential Integrity is turned on, which is how you would check whether the code in a code field is represented in the code's translator/validator table.
The last, most complex, and non-automatic validation would be to put some kind of VBA code in the field's "BEFOREUPDATE" event or in the "LOSTFOCUS" event, either of which would be able to trap invalid values.
Typically, you cannot point to a single method of validation.
It IS possible to identify bad data using queries, but the trick is that, OK, you've found a bad record. SO... what are you going to do about it? The queries you might write would identify validation issues but rarely would they be able to fix the problem. I'm not saying "never" because, let's face it, your question was so wide open that almost anything is possible. Just understand that some things are more likely than other things in terms of data automatic cleanup. In one sense, the sky is the limit. In another, more practical sense, your knowledge and imagination are the real limits.
Similar threads
