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: Fle is too big to upload and I also cant use a URL, please help?

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.

---

 

[yrstrulysa]

See files https:facebook.com

 

[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