Help With A Shipment Tracking Project in Access

[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 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.

 

[spikepl]

Where do shipments originate? It is not clear whether your customer ships internally or what? This is important in that the data flow begins with ordering and then tracking.

I've done some shipping stuff before, and the big boys have a multitude of ways to access shipment data. Online (where you could screen scrape or di it manually), email (an email with AWB-numbers can generate an automatic response with status of all listed AWB's - but this presupposes that YOU are the originator, and know the AWB's), web services (where your code can sign in and interrogate status of shipments, and also place shipment orders)... Billing is another story - the big boys send a file you'd ingest into your ERP, and hopefully reconcile with the shipment records already existing there (somehow).

The smaller boys? Depends. One thing I would not bet the project on is the small boys in any way amending their workflows (unless you are a very very privileged customer). So for starters I'd find out what facilities each and one offers for information transfer.

Also, be precise: "how much a shipment should cost and when they can expect to pay it)." this is ambiguous as hell. A shipment costs what it costs - so is this some wish list? And services rendered normally have a due date for payment according to whatever credit and payment terms are agreed between the two parties.

 

[iitenaciousg]

Thank you for responding!

The data flow begins with the trucking companies. The shipments are contracted, but only the cost. The frequency of the shipments change and are not set.

Screen scraping seems like a great concept and I will definitely check more into that! As of right now, the plan is to get the data straight from the trucking companies. I have already started receiving sample data and am getting a good response so far in terms of needed changes. Fortunately, I work for a 'big boy" 3PL. Unfortunately, we don't have access to the client's ordering or planning systems.

On the billing side, my company already handles all the freight billing for the client. I am hoping to leverage the data in there to get "Payment Terms" and also contracted cost.

You are totally right about the ambiguity of the accrual Phase (Phase 2). This section is not expected to be 100% accurate for active shipments as we will not know the actual freight class until after the load has been completed. MOST shipments will fall into a freight class range and have a set cost. At the same time, MOST shipments will be paid on their normal payment terms. It seemed that they only wanted to know (roughly) the cost of the shipment and what week that they can expect cut the check for it. I am going to have to clarify the client's expectations for sure.

 

[spikepl]

It would help if you clarified who is who and who does what exactly. This is totally unclear by now.

So far we have these actors: "me", "Company X", "Client", "Corporate side of client", "Client's multiple facilities", "Trucking companies","Big Boy 3PL", and no clarity who exactly does what for who, or whether all these are separate actors or some of them the same under different names

Further: "The data flow begins with the trucking companies. " HUH? Do the trucking companies go off and ship stuff?

To help you we need to have an idea what this is about. Write an explanation and run it past a friend/spouse/parent or the infamous 8-year old boy, as a test for whether it makes any sense for an outsider, before posting.

 

[iitenaciousg]

My apologies for the confusion.

I work for a 3rd Party Logistics company that provides shipment management services to the client.

Data Flow for this project: Trucking companies (origin) ---> Me(processor) ---> Client (recipient)

- The trucking companies will submit shipment data to me on a daily basis.

- I will compile and process this data (with a database) into a report that will be distributed to the client.

Unfortunately, there is no way for me to predict or know when a shipment is going to happen, other than when a trucking company tells me that a shipment has begun. I know the contracted trucking company, freight class, and rate for each lane.

That being said, let me re-address my questions:

1) Is it feasible to build a system with Access where the trucking companies can "drop off" their Excel shipment data files into a web/cloud based drop box, and then have a web based front end that can be accessed by the client?

2) Is it possible to have the compiling and processing of the data happen automatically in a web based environment?

(Basically, once the system is built, I don't want to have to do anything).

Again, thank you so much so far. Your input really is appreciated.

 

[jdraw]

I am not familiar with 3PL/TPL so did a little googling. It seems this whole subject is a piece of something bigger and none of the pieces is well defined with precise boundaries.
http://en.wikipedia.org/wiki/Third-party_logistics

It also seems there are a number of commercial software packages to support this sort of project. I mention these because of your intent to have nothing to do with the project/database once it is installed. No software product/package/customized solution works without some level of support. And if part of the "business" is dependent on this software, better make sure the support is part of the "vision" before starting the project.

As for your specific work I think, as spikepl has noted, that you are still looking at this from the proverbial "20,000 ft level". You need details to define who are the players, what are the various activities, what are the initiator/terminators for these processes....

Do you have enough information to do a reasonableness check against commercial packages; could you rationalize why you should design and build a custom product instead of acquiring a commercial package with training and support. Just a few things to consider...

Good luck.

 

[iitenaciousg]

Thanks for the input jdraw. You are right that I am still at the "20,000 ft level" and that is for good reason. I can't really start to define the details until I answer these two questions (which I have already asked). I honestly feel like we can forget about what I do, in what industry, and for whom. I think these questions are relatively general in terms of Access.

My apologies again for not being concise originally but here are the two questions again. Any help would be greatly appreciated.

1) Is it feasible to build a system with Access where someone can "drop off" their Excel data files into a web/cloud based drop box, and then have a web based front end that can be accessed by the client?

2) Is it possible to have the compiling and processing of the data happen automatically in a web based environment? Or will I always have to click a button to fire up the database's functions?

(Basically, once the system is built, I don't want to have to do anything. That's not to say I won't support this database because I most certainly will. I am just trying to limit the amount of support that will be needed.)

 

[spikepl]

Despite multiple prompts you have been unable to explain how your project ticks, but you have stuck on maintaining focus on a few details and Access. To me this signifies an urge to go for some specific purported solution, and it is rather unclear whether identification of any alternative approaches has been carried out.

Be that as it may:

1. You can drop anything into the cloud. Many companies are quite reluctant to do so, because of just/unjust concerns about data security. What has dropping of Excel into the cloud got to do with Access? You could run your own server where others could drop off things. Whether this mechanism is acceptable to (or doable by) all participants you have not mentioned.

2. Access is two things: a db-enginelet and a rapid development tool for a user interface. The edb-enginlet can drive a web solution, that is not highly loaded - else go for ms sql/mysql . The user interface, in 2007/2010/2013, can talk with Sharepoint, which offers some emasculated capabilities on the web. I have no experience in that. The Sharepoint bit is not free. Ask your local db/web/Access consultancy for details about this, or pose a specific question here on AWF in one of the other more-frequently visited fora. Developing a .NET/PHP web is not free either.

3. A "web based environment" is some software running somewhere on some server - you can more or less make it do anything.

Since the company you work for (if I understood the list of actors correctly) is a "big boy" I'd suggest they spend a bit of money on a business analyst to sort out the specs for this system.