Import-export permit tracking system

[yrstrulysa]

Hi everyone,
I'm working on optimizing an import-export permit tracking system and need some suggestions on improving document storage, data linking, and retrieval.

Files is too big to upload, find link to it in txt file attached.

Current Setup:​

• tblPermitInformation – Stores both import & export permits (permit number, authorization, shipment details, quarantine status).
• tblDocumentStorage – Holds related permit documents (PDFs, invoices, phytosanitary certificates), linked by PermitID.
• Forms & Queries – Used for permit tracking and data entry (frmImportRecords, frmTblPermitInformation).

What I’ve Done So Far:​

Core Tables Created:

• tblPermitInformation – Stores all permits.
• tblDocumentStorage – Stores related files.

What’s Needed:​

1. Differentiate Import vs. Export Permits – Add a PermitType field.
2. Link Imports to Exports – Create a tblImportExportLink table for batch tracking.
3. Improve Document Retrieval – Use a VBA button to open files or a subform for multiple documents.
4. Optimize Queries & Alerts – Ensure missing documents are flagged & track upcoming permit expirations.

Export Process Issues:​

• No centralized system for storing export data.
• No linkage between export documentation and import records.
• No tracking of essential export details.

Proposed Solutions:​

• Create a dedicated export management section in the database.
• Make fields mandatory: Variety, Clone, Requestor, Permit No, Validity Date, Date Shipped, Destination Country, Material Type, Quantity.
• Implement a document repository similar to import storage.

New Feature: Import-Export Traceability​

• Automatically link records based on batch numbers or permit references.

Import Document Storage Issues:​

• Lack of structured document categorization.
• No link between import documents & other database records.

Proposed Fix:

• Implement structured document storagewith:
  • Dedicated fields for permit documents (PDF/Word).
  • Dropdown selection for document types (DHL docs, Phytosanitary, Import Authorization, etc.).
  • Bulk document upload for multiple related permits.

Would love to hear any insights on best practices for linking imports & exports, automating document retrieval, and optimizing tracking! Thanks in advance.

---

 

[Mark_]

I would, personally, never store documents or images in a database. I'd instead have them stored in a directory and have the path to the documents stored. This will greatly reduce the size of your database, allow you to use a variety of programs to access the documents and images natively, and will allow you to implement directory level control to keep unauthorized users form viewing them.

This also means that, should something happen to your database you don't loose all the scanned documents.

 

[tvanstiphout]

In one project I have tblDocuments with all documents.
The filename references the document in a server folder. All 120K docs are in the same folder.
The OrigFileName allows for reconstructing the file in the TEMP folder with the original filename.
After that, I have tables that link the records to either a Builder, a Subdivision, or a Home.

One nice touch is that I added a form showing Recent Files (just like Windows Explorer can), because those are the docs that most likely need to get added to the documents subform(s).

 

[Mark_]

What reports or other outputs will your system need to generate?
Will this need timeline presentation and tracking?

 

[arnelgp]

cross posted here:

Import-export permit tracking system

Hi everyone, I'm working on optimizing an import-export permit tracking system and need some suggestions on improving document storage, data linking, and retrieval. Files: https://drive.google.com/file/d/1I5FLFr3x4iWjMQbjA2YjxK7NXRWzIIH9/view?usp=sharing

www.accessforums.net

 

[Gasman]

Serial crossposter

Import-export permit tracking system

Hi everyone, I'm working on optimizing an import-export permit tracking system and need some suggestions on improving document storage, data linking, and retrieval. Files: https://drive.google.com/file/d/1I5FLFr3x4iWjMQbjA2YjxK7NXRWzIIH9/view?usp=sharing

www.accessforums.net

Positive Test Flagging & Reminders

I am developing a Microsoft Access-based system to manage virus/bacterial retesting for plant blocks. The system tracks testing schedules, flags positive tests, and automates retest reminders using VBA and a subform to display test records. Files(upload function dont...

www.accessforums.net

 

[plog]

Duplicate posted here:

Positive Test Flagging & Reminders

I am developing a Microsoft Access-based system to manage virus/bacterial retesting for plant blocks. The system tracks testing schedules, flags positive tests, and automates retest reminders using VBA and a subform to display test records.

www.access-programmers.co.uk

Huge on writing outlines of the project, but not much for discussing things.

 

[yrstrulysa]

What reports or other outputs will your system need to generate?
Will this need timeline presentation and tracking?

Please find the link to my DB attached.
The DB contains scrambled/censored data.

The import form for Plant Permits already exists.

It just lacks a few columns, which I brought in:
Add a checkbox/drop-down field to track fumigation upon arrival. Include fields for: Permit Authorization Approved Date Expected Release Date & structured document storage(meaning the files/permits are not linked to batch numbers/permit numbers etc):

The Export function of these permit documents doesn't exist. T
his would need:
No centralized system for storing export data.
No linkage between export documentation and import records.
Missing tracking of essential export details.

1. I will start with amending the Import Form to add the additional columns.
2. Do I create an Export Form also with the required columns or is there a simpler way to get done what I want to?

 

[yrstrulysa]

I would, personally, never store documents or images in a database. I'd instead have them stored in a directory and have the path to the documents stored. This will greatly reduce the size of your database, allow you to use a variety of programs to access the documents and images natively, and will allow you to implement directory level control to keep unauthorized users form viewing them.

This also means that, should something happen to your database you don't loose all the scanned documents.

I agree. I intend to store files on Sharepoint, just the file details like @tvanstiphout demonstrated below. I have a Form that holds this data.

I would, personally, never store documents or images in a database. I'd instead have them stored in a directory and have the path to the documents stored. This will greatly reduce the size of your database, allow you to use a variety of programs to access the documents and images natively, and will allow you to implement directory level control to keep unauthorized users form viewing them.

This also means that, should something happen to your database you don't loose all the scanned documents.

 

[Mark_]

And do you have questions?

 

[mike60smart]

I agree. I intend to store files on Sharepoint, just the file details like @tvanstiphout demonstrated below. I have a Form that holds this data.

Tom has given you an example of how to manage documents.
Have you tried to implement it in your database?
No one is actually going to do this for you.

 

[yrstrulysa]

Tom has given you an example of how to manage documents.
Have you tried to implement it in your database?
No one is actually going to do this for you.

Like I said I already have an upload form. It's available in the files I made available via the link.
My question was more would the tables and forms differ? I guess to an extent, I was not sure about how much.

 

[Pat Hartman]

My question was more would the tables and forms differ? I guess to an extent, I was not sure about how much.

Tables store data. Forms display and update data. Unlike Excel, they don't need to look like each other. Forms can be bound to a table or they can be bound to a query. If you like to write a lot of unnecessary code, you can use unbound forms and do whatever you want.

I believe that someone recommended in a different thread that rather than asking us to design an application for you, you ask specific questions. Based on your current description, the file should not be even close to too big to update. You should compact and repair so you can upload the file to this site.

Your posts look like they are written by an AI. Are you using a translator program to translate to English from your native language?

 

[mike60smart]

Like I said I already have an upload form. It's available in the files I made available via the link.
My question was more would the tables and forms differ? I guess to an extent, I was not sure about how much.

Hi
In the attached I have imported all of the relevant objects for the process of attaching documents related to a Specific Permit for a specific Plant.

The Form that opens allows you to select a Specific Plant and add all relevant details for the Permit.
Then in the Subform you can locate specific files to be stored in the tblDocumentStorage.
You can also View the Files which you have saved to the tblDocumentStorage.