Text recognition question (1 Viewer)

[gledav]

We are a music school and we send student report templates to our teachers for completion each year. They are Access reports generated from a query, converted into PDFs or Word Docs and emailed to them. When the completed PDFs or Docs are returned to us we print them out and post them to parents. This process is expensive and time consuming.

Is there a way for Access to somehow recognise a unique email address or studentID printed on the student report page (PDF or Word Doc) that could be linked back to a query in order to automate a procedure to send the completed reports to parents via email? Or any other way of achieving this?

Please note the teachers do not have MS Access and my current version is 2010 though I would be more than happy to upgrade if later versions have the functionality I require.

Any help or direction would be appreciated.

 

[Gasman]

Yes, very easy.
Have a field that holds the destination email address. Would probably want more details, so you could say 'Good morning Mrs Davies, please see your report for David'

Would probably have a Parent table. As you.might need to send to both or more parents these days.

 

[gledav]

Thanks Gasman, Perhaps I wasn't altogether clear. My question is really about how does Access 'see' the email field or StudentID field in the returned PDFs or Word Docs.

 

[Pat Hartman]

how does Access 'see' the email field or StudentID field in the returned PDFs or Word Docs.

Unless you have some software that can read a PDF like Adobe, it can't. If you use the .rtf format, the Word document is plain text so you would be able to parse it and pull out an email address.

Why is this information not collected at registration? That makes more sense to me. Then when you need to send out emails, the data is there. Your emailing process can send emails when there is an email address and it can print the report when it needs to be mailed.

 

[theDBguy]

Could you add the student id to the name of the file? If so, that could be one way to automatically determine the correct email address.

 

[MarkK]

To me the simplest solution is to put identification data in the filename. Then you don't need to open the file or parse its contents for your MS Access code to triage the file's origin, destination, purpose, etc... Imagine filenames like ...

1234 Johnny_Jones Report_Card.pdf
5678 Suzie_Smith Attendence.docx

That'd be dead simple to manage programmatically.

 

[gledav]

Unless you have some software that can read a PDF like Adobe, it can't. If you use the .rtf format, the Word document is plain text so you would be able to parse it and pull out an email address.

Why is this information not collected at registration? That makes more sense to me. Then when you need to send out emails, the data is there. Your emailing process can send emails when there is an email address and it can print the report when it needs to be mailed.

Hi Pat, Thanks for your reply. I do have the information. I have all the data required to personalise reports such as StudentID, parent email address etc. Any or all of this information can be and is placed on the individualised report templates I send to the teachers for completion. What I need is a way for Access to read that information after the teachers send back the completed reports so I can then email the individual reports to the relevant parents. I guess you have partially answered my question with "If you use the .rtf format, the Word document is plain text so you would be able to parse it and pull out an email address." If I know this is possible then that is all I require. I will seek out a tutorial now on how to achieve this as my programming skills are limited. If you know of any that may be useful I would appreciate you pointing me in the right direction. Otherwise, Thanks for your help as now I know I might be able to achieve what I have been looking for. Kind regards.

 

[Gasman]

You could perhaps use Excel workbook for sending to the teachers and process that. That would be relatively easy.
Then produce an Access report for the parents.

 

[gledav]

Thanks everyone. I appreciate all the advice and it gives me some hope that what I want to do is achievable. Now I know that, I'll explore the different methods that seem to be available and see which one is the best to implement.

 

[Pat Hartman]

The name of the file should include the ID of the student. There is no reason to try to extract information from the text. As @Gasman suggested, Excel is a better tool for passing actual data.

 

[azhar2006]

As I understood from your question you want Access to recognize the report when it is returned to you by the teachers, right? If so, you can use a barcode reader and put a barcode on each Word or PDF report. When the report is returned to you, Access will recognize the record of the report by the barcode on it through the barcode scanner. I think that is what I understood from your question.

 

[The_Doc_Man]

If I read this problem correctly, I think the barcode won't be displayed in a way that is readable because the returned format isn't electronically readable without the advanced version of Adobe's product. It would be possible with the Read/Write version of Acrobat (or whatever they call that product these days) to open the file to see - AND EXTRACT - its components. If the file is structured in a reliably repeatable way then you can extract the parts, find the one that contains what you want, extract it down to its source level, and see the contents as a text stream. That is because the advanced version of Adobe can "undo" what was done to put the report together.

The good news is that it is possible to control that advanced package with Access as the "driver." We have had articles here on the subject of manipulating the Adobe driver and it IS compatible with Component Object Model architecture. The bad news is that it ain't cheap. The slightly less bad news is you would only need one copy.

If you are using .PDF as your exchange format, someone will need the document builder version of the product. It won't matter much if you used some OTHER method to actually build the document - like having Access or Excel or Word build the file in .PDF format. But if what you want is buried in the .PDF, you will have to dig it up again.

 

[Gasman]

As I understood from your question you want Access to recognize the report when it is returned to you by the teachers, right? If so, you can use a barcode reader and put a barcode on each Word or PDF report. When the report is returned to you, Access will recognize the record of the report by the barcode on it through the barcode scanner. I think that is what I understood from your question.

What a way to make it way more complicated than it needs to be.

 

[GPGeorge]

What a way to make it way more complicated than it needs to be.

Back when I was still serving clients' needs with Access, I had a client who sent out 10's of thousands of junk mail marketing letters every month. Guess how we identified the returns....

Yup, the contract mailing service used barcodes. That allowed them to route all of our return pieces to us, and all of their other customers' return pieces to them in batches.

That was also the only time I recall using a primary key field as something other than that. We printed the prospects' Primary Keys (Customer ID) on the marketing letters and that allowed our data processors to quickly find the appropriate record in our database of over a million prospects.

To be honest, if I were handling that volume of printed correspondence today, I'd lobby for a $25-$50 bar code reader for data processors and use that bar code to encode our prospect Primary Keys on each letter that went out.

It's not the worst way to manage large volumes of printed material.

In this case, where the return is digital, not printed, of course, it would be more complicated, but it follows one of my beliefs about the role of automation. If you can turn mundane tasks -- like recognizing the bar code and hence the client ID -- over to a computer, you should. People make random mistakes and like to take coffee breaks.

 

[GPGeorge]

The name of the file should include the ID of the student. There is no reason to try to extract information from the text. As @Gasman suggested, Excel is a better tool for passing actual data.

This would be the most efficient approach, IMO. The file name should include the appropriate ID along with the date on which that iteration was sent.

 

[The_Doc_Man]

That was also the only time I recall using a primary key field as something other than that.

I'm going to quibble over that, George. You were using the PK to more rapidly find the record in question. The fact that it was in a non-standard location doesn't matter. You were using the PK for the exact purpose of a PK - find a record fast. So you were NOT using a PK incorrectly. Just oddly placed.

 

[GPGeorge]

I'm going to quibble over that, George. You were using the PK to more rapidly find the record in question. The fact that it was in a non-standard location doesn't matter. You were using the PK for the exact purpose of a PK - find a record fast. So you were NOT using a PK incorrectly. Just oddly placed.

It was printed on the letter. The data entry person typed it into a control on a form to find the record. So, I suppose you could take that approach; it was a hybrid use, part human, part computer. I would have more accurately noted that it was the only time I allowed either a user or the recipient of the letter to see and use the PK ....

 

[Edgar_]

Or any other way of achieving this?

There is a much better solution for that which consists of filling out forms online through a web application. If you had this intermediary between your current Access application and your teachers, you could remove the hassle of managing filenames, barcodes and OCR technology (solutions that are easily altered and prone to errors). This would also help your school provide better service to both employees and customers. Plus, I'm almost sure they're asking for it.

The workflow would be like this:
> Student information is submitted to the web application from the Access application.
> Teachers search the student from the application through a computer, smartphone, tablet, etc. (They only need an internet browser, nothing more).
> Teachers write what they must. The application could easily include some template data to help your teachers avoid having to write the same stuff over and over again.
> The Access application would receive the information and send it through emails. In fact, the teachers could send the reports from the web application, if that was necessary.

Additionally, everyone is on Whatsapp nowadays, this task is one that can be solved with that application as well.

 

[Gasman]

Back when I was still serving clients' needs with Access, I had a client who sent out 10's of thousands of junk mail marketing letters every month. Guess how we identified the returns....

Yup, the contract mailing service used barcodes. That allowed them to route all of our return pieces to us, and all of their other customers' return pieces to them in batches.

That was also the only time I recall using a primary key field as something other than that. We printed the prospects' Primary Keys (Customer ID) on the marketing letters and that allowed our data processors to quickly find the appropriate record in our database of over a million prospects.

To be honest, if I were handling that volume of printed correspondence today, I'd lobby for a $25-$50 bar code reader for data processors and use that bar code to encode our prospect Primary Keys on each letter that went out.

It's not the worst way to manage large volumes of printed material.

In this case, where the return is digital, not printed, of course, it would be more complicated, but it follows one of my beliefs about the role of automation. If you can turn mundane tasks -- like recognizing the bar code and hence the client ID -- over to a computer, you should. People make random mistakes and like to take coffee breaks.

I do not think we are talking about that much volume here George?
In your example, that would make sense, however I dare say they had machinery in place to sort the returns, not humans?

I still think that would be overkill for this particular situation.

@Edgar_ 's idea could be carried out with Google Forms

 

[gledav]

As I understood from your question you want Access to recognize the report when it is returned to you by the teachers, right? If so, you can use a barcode reader and put a barcode on each Word or PDF report. When the report is returned to you, Access will recognize the record of the report by the barcode on it through the barcode scanner. I think that is what I understood from your question.

Hi azhar2006, Thanks for your reply. I guess you mean a physical barcode reader attached to a computer port? What I really need to be able to do is have a unique identifying feature, such as a barcode, printed on the reports I send to the teachers to complete. Remember these reports have been converted into PDFs or Word Docs. When these 'reports' are returned to me, via email, I want Access to be able to recognise each individual report from it's barcode (or other unique identifier) and match it up with a parent email address in order to send it to that parent. There are almost 2,000 of these reports so I don't want to print and physically scan each one's barcode.