Advice on data entry and quality (1 Viewer)
- Thread starter cayham86
- Start date
What sorts of queries can I make?
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 11:09
- Joined
- May 7, 2009
- Messages
- 19,243
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
MVP / VIP
- Local time
- Today, 04:09
- Joined
- Jan 14, 2017
- Messages
- 18,221
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
- Yesterday, 23:09
- Joined
- Feb 19, 2002
- Messages
- 43,275
When you are doing data entry through a form, ALL your validation code goes in the form, specifically the form's BeforeUpdate or occasionally a control's Beforeupdate event so you can stop the record from being saved if there is something missing or invalid.
When you are importing data from a spreadsheet or other external file, then you would probably use queries to do the validation or conversion. We have no clue what you want to do so we can't offer anything specific. Just keep in mind, that doing validation on a bunch of records at once isn't as easy as it sounds since you need to decide how to communicate the various errors and then how to allow the user to fix them.
For one application I created, the data was converted from an old FoxPro database which did little to no validation. That means that there was a huge amount of bad data that had to be imported. It meant that I couldn't enforce certain types of validation at the table level because I needed to get the data into the app for people to work with. After they cleaned up the old mess, I was able to tighten things up by adding table level validation in addition to what was done with the forms. For this app, the clean up was ongoing for a couple of months. I created a form that ran a bunch of different queries. They could choose the query they wanted to run and all the "bad" rows would be selected and displayed in a continuous form. They could then fix up the data, one record at a time. Once they got each piece cleaned up, they would tell me and I would lock it down to prevent future errors.
When you are importing data from a spreadsheet or other external file, then you would probably use queries to do the validation or conversion. We have no clue what you want to do so we can't offer anything specific. Just keep in mind, that doing validation on a bunch of records at once isn't as easy as it sounds since you need to decide how to communicate the various errors and then how to allow the user to fix them.
For one application I created, the data was converted from an old FoxPro database which did little to no validation. That means that there was a huge amount of bad data that had to be imported. It meant that I couldn't enforce certain types of validation at the table level because I needed to get the data into the app for people to work with. After they cleaned up the old mess, I was able to tighten things up by adding table level validation in addition to what was done with the forms. For this app, the clean up was ongoing for a couple of months. I created a form that ran a bunch of different queries. They could choose the query they wanted to run and all the "bad" rows would be selected and displayed in a continuous form. They could then fix up the data, one record at a time. Once they got each piece cleaned up, they would tell me and I would lock it down to prevent future errors.
- Local time
- Yesterday, 22:09
- Joined
- Feb 28, 2001
- Messages
- 27,185
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.
- Local time
- Yesterday, 23:09
- Joined
- Feb 19, 2002
- Messages
- 43,275
Not sure why you would choose the Lost focus event to do validation. Do you really want to run validation code if the control value wasn't changed? Besides, certain kinds of validation cannot be performed in the control events anyway which is why except for ONE condition, I put all my validation code into the form level BeforeUpdate event just to keep it together and easier to edit. The ONE condition I check for in the control event is duplication. For example the SSN is a unique number assigned to all US citizens who earn wages. Therefore, when a SSN is entered, it is appropriate to look for duplicates in the control's BeforeUpdate event because you are not going to allow the user to save the record with a duplicate SSN no matter what. So, it is mean spirited to allow him to fill out the entire form before you tell him he can't save the data he just entered. However, you still need code for SSN in the form's BeforeUpdate event because if the control never gets the focus, none of the control level events will run so it is possible that the SSN is null and checking for nulls in required fields is necessary in the form's BeforeUpdate event as your last chance to prevent bad/missing data from being saved.
