Help With A Shipment Tracking Project

[iitenaciousg]

Hello everyone! I have been reading the forums for a couple weeks brushing up on my Access skills and finally decided to get some feedback on a project I started working on. I am a project manager but have never managed an Access or a database project. I have a pretty good idea of where I want the project to end up, but I wanted to get some feedback from the fine folks here at AccessForums before I dive in. Forgive me if this gets long-winded.



Scenario: I work for Company "X" and have been tasked with creating a Daily Shipment Report for my client that has multiple facilities that receive shipments. The corporate side of my client wants this report to also show accrual information (ie, how much a shipment should cost and when they can expect to pay it).

Vision: I am imagining a system where the trucking companies can "drop off" their Excel shipment data files into a cloud based drop box, and then have an online front end that can be accessed by both the facilities and the corporate office where they can pull reports.

Phases: I have the project broken up into two phases at the moment: Phase 1 is just the tracking part. Being able to deliver a daily report to facilities showing active shipments and expected delivery date. Phase 2 would be be the cost accrual part for the corporate office. In order to show much a shipment will cost and expected pay date, this phase will bounce shipment data off of an existing accounting database that my company already manages.

Questions:
1) When looking at my "Vision" section above, is there a particular way that I should approach the database building process if the eventual goal is to have it all relatively automated online? Would it be an issue if I go from a scenario where I have the trucking companies email me the data files every day, then I would "scrub" them through the database, and then personally email the report out; to a scenario where it is mostly cloud based as described? Basically, going from manual to auto.

2) Is my "Vision" even possible with Access? I know this question might be more concerned with web/cloud design, but I figured some of you might have an idea or experience.

3) When looking at the "Phases" section, are there any concerns that I can address early on with using the second database in order to avoid rebuilding the database?


Any other suggestions or criticism are welcome! Thank you in advance for any help with my project.

[June7]

I've never built a web database but seen and read enough on forum to recognize it is not easy. Explore Sharepoint and/or ASP.Net software. Web databases deployed through Sharepoint cannot use VBA code.

I suppose the 'web' part would be an interface for trucking companies to submit their data. Then a non-web part for your use in manipulating the submitted data and generating the reports and emails. However, I have no idea how the data submitted through a Sharepoint web db is available to a non-web interface.

Access 2007/2010 has a feature called CollectData that allows data retrieval via email. For good or bad, MS has removed the utility with Access 2013.

[iitenaciousg]

Thank you for the reply and the information!

Would it be possible to have the trucking companies submit an Excel file into a single drop box and then have my database log into that drop box and extract the files automatically? Perhaps I could have the database log into a SharePoint portal to extract the files? Does CollectData operate similar to what I described above?

Is it possible to have the database do the compiling and processing of the data automatically at a certain time of day? And then email that report to a set mailing list automatically as well?

You can probably see by now that my goal is to set this up correctly and do very little management afterwards.

[June7]

'Automatic' is subject to interpretation. In most cases someone still has to push a button, even if it is just to open the db, although that can be done with an external script file that is executed by Windows Task Manager. I do this to open db each morning then code behind default open form creates another db, exports data to that db, zips the db, and sends an email with the zip file attached, then deletes that db and zip files.

Automating interface with web is not easy no matter what. I know one poster (redbull) on this forum that has done a lot of code for IE web browser. Might review some of those threads: https://www.accessforums.net/search.php?searchid=87407

Why don't you experiment with CollectData and see if it offers what you want.

[iitenaciousg]

Thank you so much for the input! This will be an ongoing process so I am sure you will see more posts from me over the next few months haha.