Best Practice: multiple values

southbymidwest

New member
Local time
Yesterday, 21:24
Joined
Feb 25, 2008
Messages
5
Hi All,

I'm a relative newbie to Access - I am wondering the best way to solve a problem. I am the admin for a database used to track projects and the quality control process. With this process we must keep up with the review of another database, the pieces of information entered which are erroneous and when the individual responsible corrects the data. To date, the errors have been manually typed into a memo field which is then pushed out to the end user in a report. The errors are very consistent and I want to create functionality that will allow me to select the errors (there are approximately 50 standard errors) from a list or using a button or whatever is wisest. Basically, I would love some best practice opinion here as I am totally stymied at this point.

Any advice for a weary admin?
 
What is an example or two of a common error (one of the 50) and it's correction? If it's what I think it is, this will be easy, but I want to see an example first.
 
Examples are "Field X: Misspelling, please review and revise" or "Field Y: Projected Date of completion expired, please follow up"

Does this help? I really appreciate your insight. I've tried as many options as I can and I feelt like a new perspective will be very valuable.
SxMW
 
if you can standardise on the error types, then it would be easier to store a table of error types, each with a numric key. then your errors can be logged by numeric example and date/report.

the main thing is that it retains consistency, - it ensures the same error is always reported in the same way, and therefore you can easily find all error 21's eg
 
Gemma, Thanks.

I do have that but my issue stems from there...Namely - what is the best method of selecting/input of these errors and then reporting them. I've tried creating a List box with Multi-select but have been unsuccessful in the copying of those errors into a memo field to then feed back to the data entry employees. I am feeling like my goal is beyond my ability, and therefore thought that perhaps one of you experienced and sophisticated users may be able to better guide me. (I hope the flattery only helps - I mean it sincerely as I'm very aggravated by my inabilities).
Thanks for your post and any ideas are appreciated.
 
I have to drive to the office and stuff, but if no one gets to it before me, I'll get to it in the next hour or so.
 
Im no architect but this comes to mind:

Error table
Fields: Err #, Name, description

Instance table (err# = FK into error)
Fields: Err #, user, date time, etc...

Populate a dropdown box with Names from Error table.. select from instance table where err#=err# from error table (the one the user selected).
 
Gemma, Thanks.

I do have that but my issue stems from there...Namely - what is the best method of selecting/input of these errors and then reporting them. I've tried creating a List box with Multi-select but have been unsuccessful in the copying of those errors into a memo field to then feed back to the data entry employees. I am feeling like my goal is beyond my ability, and therefore thought that perhaps one of you experienced and sophisticated users may be able to better guide me. (I hope the flattery only helps - I mean it sincerely as I'm very aggravated by my inabilities).
Thanks for your post and any ideas are appreciated.
--

I don't think you have approached this the right way..
its the use of a memo field thats I think is wrong -anything in a memo field kinda goes against the grain in respect of a list .

I think a datasheet list would look( and manage better) - how to do this -?? I would have to go and have a few drinks to think this one through ..
putting them into a memo field would look good and is relativelty easy to do - I have a sample d/b on how to do that - but before you go down that route - pause for a second , would a spreadsheet(datasheet) feel- look better ??
this would then give an unique id to each fault etc and a detail etc ...

memos have there place - but in this instance I don't think you need it -

just my view

regards
 
SxMW, before I write this, let me make sure I get it right.

You have multiple fields, something like this:

Field1 - Text
Field2 - Date
Field3 - Number
.
.
FieldX - DataType

You need to go through these fields and say things like, "Field1, if your value is X and it should be Y, display this message." and "Field2, if your date is >= Today, then you're expired."

This is a little more complex than I thought initially, and you may be able to get a lot of this with validation rules, but do I have the idea right?
 
I'll add that if you take the "table of known errors" and a combobox or listbox dropdown to select the error, you are (1) improving the normalization of the database vis-a-vis this common error list and (2) making it possible to add new errors by just editing the error list, with no other logic involved (usually).
 
I'll add that if you take the "table of known errors" and a combobox or listbox dropdown to select the error, you are (1) improving the normalization of the database vis-a-vis this common error list and (2) making it possible to add new errors by just editing the error list, with no other logic involved (usually).

Thus, why I suggested it.

When adding a new instance of an error, check to see if the ID exists in the error table then add the instance to the instance table (or occurance table...)
 
Hey Moniker - I wish I was making validation statements. Rather, this is a separate database. I review the data entered into a database which was not programmed to perform validations on it's own data and we cannot get IT support to add this to our current system; therefore I work with a separate Access db which tracks all of the errors made, time between notification of error, reconcilation made etc. To date the errors (which are largely consistent) have been entered in a memo field manually and then generated in a report weekly. I wish to avoid typing these errors and started by creating a table of the 50+ standard errors.

Does this make sense?
 
i still think you going down a doggy route but heres my memo writer - this was thrown together - in esseence it takes a date and time and a text field and appends this into a memo field - finished - you can add a combo box to populate the text field etc push the buttonand it adds to the memo field -

it will not track error
personally I feel you need a structured approach - - with tables of faults tied to projects and the ability to anaylise (?) them
 

Attachments

I have just added a tbl with faults in it

so what this does is add into the memo field at the end
a date and a time and a fault then 2 carrage returns

scruffy and rough -
 

Attachments

Yes, it makes sense. The structure of it should contain both the error and the resolution, and there should be a unique ID in there (an autonumber is fine in this case) so that the same error isn't entered twice.

The trick is that you have different types of errors. Some are spelling errors, some are "date out of range" type errors, and there may be more. Can you enumerate the types of errors as well? That's important because you'll handle spelling mistakes one way, dates in another way, and so on.

With the types of errors enumerated, you can then select from your error table and filter on error type, run some code specific to that type of error, and then move to the next type of error, run code specific to that type, wash, rinse, and repeat.

For example:

1 - Spelling Error
2 - Date Error

SELECT * FROM t_Errors WHERE ErrorType = 1
With these records, search for this misspelling and replace it with this correct spelling.

SELECT * FROM t_Errors WHERE ErrorType = 2
With these records, check the date entered. If it's < or > then DateX, then do something.

This is how I think you're going to have to do it. So long as the error types enumerate to just a handful or so though, you're heading in the right direction.

You can even set the whole thing up in a loop:

For x = 1 To TotalErrorTypeCount

SELECT * FROM t_Errors WHERE ErrorType = x;

Select Case X

Case 1
--- Do something here ---

Case 2
--- Do something else here ---

Case X
--- Blah blah

Case Else
End Select

Next
 
Yes, it makes sense. The structure of it should contain both the error and the resolution, and there should be a unique ID in there (an autonumber is fine in this case) so that the same error isn't entered twice.

The trick is that you have different types of errors. Some are spelling errors, some are "date out of range" type errors, and there may be more. Can you enumerate the types of errors as well? That's important because you'll handle spelling mistakes one way, dates in another way, and so on.

With the types of errors enumerated, you can then select from your error table and filter on error type, run some code specific to that type of error, and then move to the next type of error, run code specific to that type, wash, rinse, and repeat.

For example:

1 - Spelling Error
2 - Date Error

SELECT * FROM t_Errors WHERE ErrorType = 1
With these records, search for this misspelling and replace it with this correct spelling.

SELECT * FROM t_Errors WHERE ErrorType = 2
With these records, check the date entered. If it's < or > then DateX, then do something.

This is how I think you're going to have to do it. So long as the error types enumerate to just a handful or so though, you're heading in the right direction.

thats how I would appraoch this (ish)- but that is not how i have read this - might be me - won't be the first time i have mis read something ....
 
Hi There all - sorry for lag in response.

Moniker, I am not sure if I understand what you are saying - I am basically logging all errors that I find from data in another system and formulate report from the text I input into the Access system. This is, unfortunately, a largely text driven activity.

Gary, I believe you understand what I am facing. My initial thought had been to create hundreds of fields and make them Y/N check boxes but that seems totally unwieldy. Perhaps the only way to do this is how I have been...manually typing text into a memo field.

Sorry if I left any questions unanswered or necessary details out and many thanks to all.

Megan
 

Users who are viewing this thread

Back
Top Bottom