My Healthcare Documents Manager

[Deleted Bruce 182381]

I am seeking an Access based media manager that allows me to index documents, images, and audio files so I can quickly locate, view, and attach selected files to email messages.

 

[DHookom]

What do you mean by “index”? Do you simply want an inventory of file names with specific extensions?

 

[arnelgp]

maybe what you need to start is to look for FileDialog or something.
see Form1 and Module1 on this demo.

 

[isladogs]

Why do this in Access?
The best file search tool is called Everything. It indexes all files on your computer so is lightning fast. Better still it’s free.

 

[KitaYama]

I can filter my searches.

The app that @isladogs mentioned can do that.
It comes with a pre-installed filters and you can add your own filters.
It can be as a sidebar or combo box at top.

You can also use a lot of settings for each filter to show them as you wish. Or limit your search to a set of pre-defined folders.

You can also use vba to run a search using any filter you like.

pth = YourPathToExeFile
SearchThis = "A part of your file name/extension/Or anything you know about the file"
fltr = YourFilterName
Shell pth & " -filter " & fltr & " -s" & " " & clsConst.Quote & SearchThis & clsConst.Quote, 1

 

[CJ_London]

I can always build it from scratch.

Way to go

I created an app for cataloging photos- it populated the file properties with keywords and used a tree view type form to create and catalog the keywords and view the files in a web browser control

But don’t think it meets your requirements as it is not generic enough

 

[Levi_79]

I am interested in something similar. I have a huge collection of movies, music videos, series.
I want to be able to tag files and search for tags and compile a list or a playlist (for example want to list all "rock" and "music video").
As i see Everything only searches in file names.

 

[isladogs]

@Levi_79
You could use my free app to do that

Extended File Properties

This shows a simple method of obtaining all the extended file properties for any type of file or group of files in a folder and its subfolders. There are over 320 extended properties including the dimensions of pictures and the duration of video files.

isladogs.co.uk

 

[DHookom]

Inventory in a table would need to be refreshed regularly. However I wanted to create a snapshot of all my files (mostly images) to look for duplicates across multiple hard drives. I asked AI to write the code for me. I just added the ability to store the file type based on this thread.

Sub ScanFilesToAccess()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fso As Object
    Dim folderPath As String
    Dim folder As Object
    Dim file As Object

    ' Prompt user to select folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Folder to Scan"
        If .Show <> -1 Then Exit Sub
        folderPath = .SelectedItems(1)
    End With

    ' Initialize FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(folderPath)

    ' Open Access table
    Set db = CurrentDb
    Set rs = db.OpenRecordset("FileInventory", dbOpenDynaset)

    ' Recursive scan
    Call ScanFolder(folder, rs)

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set fso = Nothing

    MsgBox "Scan complete!", vbInformation
End Sub

Sub ScanFolder(ByVal folder As Object, ByRef rs As DAO.Recordset)
    Dim subFolder As Object
    Dim file As Object
    'add FileType with SELECT CASE extension?
    Dim strFileType As String
    Dim strExtension As String
    ' Loop through files
    For Each file In folder.Files
        strExtension = Mid(file.Name, InStr(1, file.Name, ".") + 1)
        Select Case strExtension
            Case "jpeg", "jpg", "img", "mov", "png"
                strFileType = "Image"
            Case "ACCDB", "MDB", "ACCDE", "MDE"
                strFileType = "Access"
            ' === ADD MORE === or use a small lookup table
            Case Else
                strFileType = "Other"
        End Select
        rs.AddNew
        rs!FileName = file.Name
        rs!folderPath = file.Path
        rs!FileSize = file.Size
        rs!DateModified = file.DateLastModified
        rs!FileType = strFileType
        rs.Update
    Next file

    ' Loop through subfolders
    For Each subFolder In folder.SubFolders
        ScanFolder subFolder, rs
    Next subFolder
End Sub

These are the references (AI mentioned some but missed one)

AI also gave me the structure for the table (I added file type based on this thread)

 

[Imb]

I am seeking an Access based media manager that allows me to index documents, images, and audio files so I can quickly locate, view, and attach selected files to email messages.

Are you seeking something like this?

I have worked on an application with some 10,000 documents as Music sheets. Filenames and filepaths are stored in a Sheet_tbl.
There is a 1:m relation to a Feature_tbl. The Feature_tbl contains all kind of features regarding the Music sheet, like componist, performer, instrument(s), genre, easyness/level, tempo, ...
Features are grouped in a Category.
Now you can select from the Sheet_tbl with any combination of Features.
Features within the same Category are connected with an "OR", the different Categories are connected with an "AND", to extract the selected sheets.

 

[Imb]

I am seeking/building a Document Management System that allows healthcare patients to scan, store, view, and send medical records, using multiple selection criteria to filter desired documents. Below is my mockup ER.

Hi BlueSpruce,

At first sight of your diagram I thought that that all six related tables had the same structure, and thus ould be handled in the way of the Music sheets.
But then I saw it was only a mockup diagram, and all related tables have a different structure.

I agree, it is quite complex, especially with multi-selections form the same table.
Nevertheless, this kind of selection is standard available in all my applications.

To explain that, I have to go back in the evolution of my applications.
For the beginning on of my work with Access I have worked towards generalization. But only the forms were a hard nut to crack.
Then I realized that the binding of the form to the RecordSource was the killing factor in the generalization, and thus flexibilty. So I changed over to (User)control-binding. This means that the binding of a control is done on the moment that you focus on the control. With this binding the control knows where to get and store its value, but the control also knows how to act according to the user's wish, including the Before- and AfterUpdate functionality.

This makes it possible to use unbound forms, that are fully dynamical, completely independant of any data. In fact, the forms is "reduced" to a container with a number of anonymous controls, that are tuned with some generalized code, and displayed as such to the user. For all my applications I use only 2 forms: one continuous form, and one unbound form.

So, with this approach I can tune that one unbound form ready to make a dynamical SQL-string, for any table, with almost any condition depending on the usertype-definition for the fields. These usertype-definitions are more differentiated than the fieldtype definitions itself.

In my applications almost all underlying processes are generalized. This means that when starting with a new application, almost 90% of its future functionality is already there.

As an example the form to construct the WHERE-part of an SQL-string.
Focus is now on a "relation" (Vader), which happen to be the related records in the same table.

Further consequences of this approach is that I don't use Comboboxes, Subforms, Querydefs, Control classes, ... in trying to add more flexibility.

Imb.

 

[MarkK]

A link makes sense if a child entity is multi-dimensional, but if the child only has one dimension, just store that single data-point directly in the parent. You can put a combo on the field, and look up a finite list of data if you want, but when you link to a single field, the complexity of your link exceeds the complexity of the data you are linking to. This is a cost with no payoff.

 

[isladogs]

It's my understanding that user-defined properties can be added to files. Can your tool edit existing properties, or it just reads them?

Its read only.
I did experiment with setting extended file properties using VBA a few years ago. Unfortunately, it can only be done for a limited range of Office file types - those in XML format e.g. .xlsx, .docx, .pptx.

 

[Imb]

It's my understanding that user-defined properties can be added to files. Can your tool edit existing properties, or it just reads them?

I wonder why you want to do that. If the user-defined properties are used for selection, it is as loosing a search key.
For (filtered) selection you have to process ALL files to find a matching property.

 

[GaP42]

I am seeking/building a Document Management System that allows healthcare patients to scan, store, view, and send medical records, using multiple selection criteria to filter desired documents.

So this is an application that the healthcare patients use - they supply the docs (through scanning, attaching), and review and then may send those to a medical record - is that external? If so the process for accepting such records may/will need stringent verification/validation, which may affect the data needed in your application.
Re the schema - this appears to be a simple star schema - optimal for reporting purposes, however, given the input side of the coin, there is no facility indicated/ needed (as far as data is concerned) to manage the patient details beyond the most basic, like who is doing the scanning (user management)?
Reflecting on the combining the dimensional data into a single table - yes - however, do you envisage any dimensions that change - slowly changing dimensions, in reporting databases, support date ranges in which values are valid - this may not apply to your proposal. I would be very surprised if all the dimensions a single attributes - but I may not be understanding what you want. eg Provider - is this simply the name of the Dr. What role does the Dr have - the referring Dr, the specialist? What is Patientclass?, Can a document belong to multiple types? eg An x-ray, and raw/png/jpeg ... and can documents be related to each other - eg the x-ray image is linked to the report by the cardiologist?
Also depending on how you expect/envisage users being able to add additional classification values, if you simply follow @MarkK 's suggestion, then you either lose control of the allowable values (users type in values they use - and then standardisation is lost) or the list is not updateable (except by allowing users to add to the list of accepted values themselves in design view - leading to the same loss of standardisation), however I'm sure you are across those issues.

 

[GaP42]

Cerner EHR cannot access records stored in Epic, AthenaHealth, and other EHR systems because there's no interoperability between these EHR's, or the provider network restricted access to only within their organization.

Yes, the sensitivities of personal health information (and consequent regulation), as well as the breadth and depth of health data makes interoperability extremely complex. While your application is simply to provide a (set) of health documents, they remain outside the commercial EHR application. HL7 is a widely used protocol that is employed to support exchange of data in health apps (however usually only within the bounds of the defined organisation). IIRC there has been work done within HL7 to define the exchange of structured medical docs (like hosptial discharge summaries). A genuine EHR scope extends beyond the bounds of a commercial app implemented within an organisation - it has to be able to support (capture, store (or point to) and present) the full set of health events over the lifespan of the individual - no matter what, where or when those health events occurred.

 

[DHookom]

You already have a document type field. I’m not sure if you could simply categorize the document types.

 

[GaP42]

Can documents be categorised as both medical and financial or ... ? Is a medical certificate/ health fund membership a credential? As this is a patient populated record of documents, the categorization is probably best focused on simplicity.

 

[GaP42]

How would "Date of service" relate to some of these doc types? Effective date? Date of document creation, Date registered in application?
And how would you want to handle update / replacements eg wills, advanced care directives. Do you wish to be able to enable a master document to have related sub documents?
A further problem is the documents are located potentially anywhere - and sometimes the user re-arranges / deletes documents, so the links no longer work - user beware. While it may not be possible to prevent external manipulation of the files, and your app can copy the documents to your designated folders specific to your application, do you need an in-application facility to enable the users to migrate storage of files?

 

[DHookom]

Some environments don’t allow access to files in the root directory of C.