Automatic Standardisation Within a Table

[diggerydoom]

Hello,

I have just recently started using access for my first database, and it is actually going quite well!

I have hit a small problem however. I have a table which contains one field of data extracted from a document. The next field is a standardisation of this data. I have created a Look Up table so that I do not accidently enter an incorrect word, however is it possible to get the standardisation field to automatically fill in depending on what is in the data field?

I have seen a few threads regarding similar problems, but these appear to be over multiple tables, so I am not sure how to proceed.

Any help would be greatly appreciated!

Regards,
Sam

 

[jdraw]

Please describe standardisation as you are using it. Also, could you describe your tables and the data entry process?

 

[diggerydoom]

the data I am using comes from medieval documents and has irregular spelling and terminology. Even the language can change. Therefore to make it useful I am including a field of, for example, 'occupations/jobs' as they appear in the documents and then a second field next to it with a standardised version of the data.

as an example, a person could be a 'carp', 'carpen' or 'carpent'. These would all appear in the standardisation field as 'carpenter'. I currently have a look up table with this infomation in, to stop data entry errors, so the main table has limited options at present.

Currently I have not begun data entry, or building any forms to facilitate it.

 

[sparks80]

Hi,

Firstly you will need to create a form for data entry

Lets say you create a form with two textboxes called "txtValue" and "txtStandardisedValue"

The user enters data into the textbox "txtValue". Use the AfterUpdate event for the textbox and enter something like the following:

Private Sub txtValue_AfterUpdate()
Dim strStandardisedValue as string

' This will find the standardised value or return a null string
strStandardisedValue = nz(Dlookup("Standardised_Field_Name", "Standardised_Table_Name", "Field_Name=" & Me!txtValue), vbNullString)

' This will update the standardised value textbox if a standardised value was found
If len(strStandardisedValue) > 0 then
  Me!txtStandardisedValue = strStandardisedValue
End If
End Sub

If you need some help with the VBA code then let me know.

 

[jdraw]

As a start, do not use blanks or special characters "/" in your field or object names. You can use underscore "_", but most use camelcase or capitalize names which is this sort of thing:
OccupationJob, FirstName, FamilyName, StandardTerm....

Part of your question really depends on what you want to do with the collected data.
You may want to write down in a few lines - what exactly you are dealing with.

For example, I have a number (yet to be determined) of medieval documents. I want to record the title, author, and date of each document, and for each document I will record significant terms and variations in their spelling. For each term and spelling variant I will identify a standard term (spelling and meaning). I want to show the source of the term......

You may want to build an entity relationship model of the "things" involved and how they relate to make sure your database will support what you want to do.

See Normalization and Entity Relationship Diagramming at
www.rogersaccesslibrary.com/forum/topic238.html

 

[diggerydoom]

Thanks for the responses and the suggestions. Just to clarify, it is only possible to do this sort of automated look up via a form? It is not possible to create an after update event for a table?

 

[jdraw]

That is correct - events are not associated with Tables.
Events are associated with Forms and controls on Forms.

 

[Pat Hartman]

In a lookup situation, there is no reason to store the lookup value. When you need to show the lookup value, use a query that joins the two tables.
I can't tell by your description if your data entry field will be limited to what is in the selection list. If it will always be chosen from the combo, you'll need to add code to the NotInList event which will pop up a form that lets the user enter the raw value and the standardized value. When the form closes, the list is requeried and the newly added value is now in the list and available for selection. If you want some raw data to be accepted as is without conversion then LimitToList must be set to No. That means that the query that joins the main table to the lookup table must use a Left join and a "calculated" field that returns either the raw value or the lookup value.

Select m.fld1, m.fld2, IIf(IsNull(l.fld3), m.fld3, l.fld4) As Field3
From tbl1 as m Left join tbl2 as l On m.fld3 = l.fld3;