Gender Pronoun Agreement

[Robbyp2001]

Hello folks, I wonder if anyone can solve this problem for me. I have a table with a memo field that contains teacher comments. Each student in teh database has been assigned a gender. I have discovered that some comments have been copied and pasted and as a result there are incorrect pronouns amongst the records.

For example: One female student may have references to 'he' or 'his' whilst a male student might have 'she' or her'. Usually this will be picked up by the user, however some do manage to fall through the net.

My question is this, would it be possible to create a query that can select all female/male students that have incorrect personal pronouns. I have tried Female + *him* but this will also return all words containing the letters 'him' like 'shim', 'thimble' etc. Female + 'his' will return 'this', thistle, whistle etc.

The same problem occurs when search for 'her', 'she', 'her's', etc.

So, is it possible to filter records based on the precise pronouns he, his, himself, she, her, her's, herself?

Regards

Rob

 

[MarkK]

To search for the word 'his' and not return thistle, try including the surrounding spaces in the search, like ...

' his '

 

[Robbyp2001]

Dear Lagbolt, many thanks, this works perfectly. One other quick question if I may. Is it possible to link the same criteria in the query to a drop down list?

I'd like to have a form where I can select Year Group then Gender then Pronoun. This would allow me to investigate all the possible permutations of Gender and Pronoun, using the same query with the results being displayed on the same continuous form.

I can apply 'Year Group' and 'Gender' to the field criteria based on the combos, but not the 'Pronoun', especially with the surrounding spaces that you recommended. Is such a thing possible? If so, how is this entered in the query criteria box?

Thanks again

Rob

 

[nanscombe]

If you start looking for words surrounded by spaces, ie ' him ', don't forget other punctuation that may affect your results like commas and full stops (periods).

' him ' would find ' him ' but not ' him.' or ' him, '

 

[jdraw]

I see the issue as more a clean up of data in a table. And then possibly a check on each new entry at the time of entry.

I would recommend a table along these lines :

each pronoun you can identify for each gender, and with each punctuation.
Then a separate check of the comment fields to identify what you would change (before actually making the change - just to make sure the results are what you need).

You may need to include some inconsistent punctuation eg ",him "

tblPronounCheck
id
Gender m or f
PNoun combinations of he/she/him/her/his/hers .....
with data

m " him "
m " him."
m " him,"
m ",him "
....
f " her "
f " her,"
f " her."
f ",her "
...

Then loop through the records and check the gender,then parse the comments looking for each of the opposite gender pronouns strings, and write a record saying WHAT should be changed -- then review the list and the records to see if it is a necessary change. When you are happy with the code, run it for real. ---and make a backup of your data beforehand.

Hope this helps. Good luck.

 

[MarkK]

I can apply 'Year Group' and 'Gender' to the field criteria based on the combos, but not the 'Pronoun'

Why not? Specifically. Describe the process, describe the failure.

Is such a thing possible?

Anything is possible, ask a better question.

 

[Robbyp2001]

I see the issue as more a clean up of data in a table. And then possibly a check on each new entry at the time of entry.

I would recommend a table along these lines :

each pronoun you can identify for each gender, and with each punctuation.
Then a separate check of the comment fields to identify what you would change (before actually making the change - just to make sure the results are what you need).

You may need to include some inconsistent punctuation eg ",him "

tblPronounCheck
id
Gender m or f
PNoun combinations of he/she/him/her/his/hers .....
with data

m " him "
m " him."
m " him,"
m ",him "
....
f " her "
f " her,"
f " her."
f ",her "
...

Then loop through the records and check the gender,then parse the comments looking for each of the opposite gender pronouns strings, and write a record saying WHAT should be changed -- then review the list and the records to see if it is a necessary change. When you are happy with the code, run it for real. ---and make a backup of your data beforehand.

Hope this helps. Good luck.

============================================
Thanks JD

I think this, if it worked, would be an excellent solution. Unfortunately it's beyond my knowledge. I have no idea how to do this.

Many thanks for you suggestion

Rob

 

[jdraw]

In which part do you not have experience?

 

[Robbyp2001]

Dear JD

I understand the new table set up and each record entered, but I don't understand the code behind the search. My knowledge of code is very basic unfortunately, but can I assume that the code where the records are looped and parsed is activated by the After Update event?

A example of the code would be a great help if you happen to have it. I hate to be so forward, but I'm afraid I would not know where to start when trying to create it.

Rob

 

[jdraw]

Can you post a copy of your data base with just a few records that show the problem?

I don't need real names, so Jane and John Doe type info is fine. I'll set up a routine to test out the approach.

 

[Robbyp2001]

Many thanks JD. I'll trim it down and post to you.

Rob

 

[jdraw]

I have set up a test, but have found an issue with the pronouns. Funny how things seem simple from 10000 ft, but not so obvious in the details.

I set up a test with 3 records. The his her him seems to be an issue.

Here are my sample records, I call then orig and revised, to show the effects of my routine.

orig  
This is a short test text about Jane's progress. He is doing quite well. Her marks are above expectation. I wish him every success in future. She is so bright.

Revised   
This is a short test text about Jane's progress. she is doing quite well. Her marks are above expectation. I wish her every success in future. She is so bright.

orig  
Eliza is the class clown. He disrupts other students. His attitude sucks. She is too out spoken. He needs an attitude adjustment if he is to achieve his expectations.

Revised   
Eliza is the class clown. she disrupts other students. hers attitude sucks. She is too out spoken. she needs an attitude adjustment if she is to achieve hers expectations.

orig  
I   am sorry to tell you that Ezra seems incapable of keeping up with her studies. She is falling behind the class and despite  my efforts he is not improving. I wish her attitude were more positive. Without extra effort and work she is doomed to repeat her year again.

Revised   
I   am sorry to tell you that Ezra seems incapable of keeping up with him studies. he is falling behind the class and despite  my efforts he is not improving. I wish him attitude were more positive. Without extra effort and work he is doomed to repeat him year again.

The issue is in the use of her his and her him

I used this TermLkup structure

termID	Gender	Term	ReplTerm
1	m	 he	she
2	m	 his	hers
3	m	 him	her
4	m	,he	,she
5	m	,his	,hers
6	m	,him	,her
7	m	 he,	 she,
8	m	 his,	 hers,
9	m	 him,	 her,
10	m	 he.	she.
11	m	 his.	hers.
12	m	 him.	her.
13	m	 he?	she?
14	m	 his?	hers?
15	m	 him?	her?
16	f	 she	he
17	f	 hers	his
18	f	 her	him
19	f	,she	,he
20	f	,hers	,his
21	f	,her	,him
22	f	 she,	he,
23	f	 hers,	his,
24	f	 her,	him,
25	f	 she.	he.
26	f	 hers.	his.
27	f	 her.	him.
28	f	 she?	he?
29	f	 hers?	his?
30	f	 her?	him?

Hers is obviously wrong, but I'm not sure where you want tog o with this at this point.

Here is the sub I created

Sub ParseGenderText()
' http://www.access-programmers.co.uk/forums/showthread.php?p=1222107#post1222107

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim RevComment As String
Dim i As Integer
Dim arr() As String
Dim sqlm As String
sqlm = "select term, replterm from TermsLkup where gender ='m'"
Dim sqlf As String
sqlf = "select term, replterm from TermsLkup where gender ='f'"

Set db = CurrentDb
Set rs1 = db.OpenRecordset("StudentGenderSample")

'Loop for each student record
Do While Not rs1.EOF
 If rs1!studgender = "f" Then
  Set rs2 = db.OpenRecordset(sqlm)
 Else
  Set rs2 = db.OpenRecordset(sqlf)
 End If
 Debug.Print "orig  " & vbCrLf & rs1!studcomment & vbCrLf
 arr() = Split(rs1!studcomment, " ")  'get the comment text original into arr()
  
 '
 rs2.MoveLast
 rs2.MoveFirst
  Do While Not rs2.EOF
  'Debug.Print rs2.RecordCount
   For i = 0 To UBound(arr)
    If arr(i) = Trim(rs2!term) Then
    arr(i) = Trim(rs2!replterm)
     'Debug.Print rs2!term & "  *  " & i & "  " & rs2!replterm & " " & arr(i)  '  for debugging
    Else
    End If
   Next i
  rs2.MoveNext
  Loop 'rs2
  
'reset the revcomment variable
RevComment = " "
'output the revised comment
  For i = 0 To UBound(arr)
    RevComment = RevComment & arr(i) & " "
  Next i
  
     Debug.Print "Revised   " & vbCrLf & Trim(RevComment) & vbCrLf
  rs1.MoveNext  'get another student record
Loop 'rs1
End Sub

 

[Robbyp2001]

JD, I have attached three files. One is the slimmed down version of the dbase, one is the spreadsheet list of effected records from the dbase and the Word document is a description of the procedure for opening the screen in which the reports are created.

Your code looks even more complicated than I had envisaged andf I am suitably impressed, though none the wiser!

I'd be grateful if you could have a look at form to ascertain whether or not your code will work.

Many thanks once again

Rob

 

[jdraw]

No attachments were received.
However, I have adjusted my sample and have attached a database (created in 2010 and saved as mdb)

Start with the form; then review the tables; then look at the module and function.

 

[Robbyp2001]

Hi JD

I'm not sure what happened to my attachments. I'll have another look.

Many thanks for your sample dbase. All I can say is wow!

Actually, it's way above and beyond what I was aiming for, in fact, it might even be too good. What I was hoping to achieve was a module that would run after the memo field has been exited (afterupdate). The code would create a msgbox warning the user that it had found a gender agreement error and that he/she should check the contents again. It would not have to highlight which actual words were erroneous, just that something in the comments was wrong. The user then would be forced to re-check.

The comments box is limited to approximately 200 words, but in reality the average comments are about 100 words, so physically re-checking an occasional record is not particularly demanding, and is (dare I say it) good work practice.

So,

Gender/pronoun disagreement = msgbox
Gender/pronoun agreement = no action

Your solution is excellent, but would require the user to actually physically check each record. Bearing in mind that each teacher has to complete, on average, 150 reports twice per year, I would not trust them to remember to do this every time.

Thanks once again for taking the time to help. It is truly appreciated.

Rob

 

[Robbyp2001]

View attachment aaess.zip

View attachment DBase Instructions.zip

View attachment Gender Probs.zip

 

[jdraw]

The sample was meant to show an approach - to show some steps that could be used.
2 tables and a function behind a form.
You could add msgbox when a gender pronoun issue is found. You could out put the "potential gender pronoun conflict" in a text box on the form; then a button to make the change....
Many options exist. You can add more terms in the termLkup table...

Hopefully some of it you can follow, test, modify to help get you where you want to go.

Good luck.

 

[Robbyp2001]

Many thanks JD, I will study what you've done and have a go at integrating it into my dbase. I think it will be beyond my limited cpabilities, but certainly worth trying.

Rob

 

[Robbyp2001]

Hello JD, you may remember we had a discussion a while ago regarding finding gender pronoun disagreement. Well, I have managed to solve the problem with a query. Now the user clicks a button and a list of all the reports that appear to have a problem appears. It works quite well, although several unexpected issues appeared. They are all minor, so not worth worrying about.

I do have a question for you though in relation to this. Whilst the query successfully finds the problem reports, sometimes it is difficult to sport exactly where the problem is, especially if the report is fairly lengthy. I wondered if it would be possible to find and highlight all the gender pronouns in the report. That way the user can save a lot of time by simply checking the highlighted words rather then proof reading the entire report.

Is it possible to write a piece of code that can search and highlight all pronouns? It doesn't have to find the problem pronoun in particular as there will not be many and looking at each would be simple and quick. Also there is the possibility that there may be more than one error in the report.

Thanks in advance

Rob

 

[jdraw]

I'm sure it is possible. Can you provide some sample data? Do you have a list of the pronouns involved? If so, can you provide that too?

It's funny, as I read you post tonight, some of it was familiar, but I do not remember creating the database or the details. But I've answered a lot of posts over the last month.