Is Access the best program to use? 2

[alw6589]

In my first month of employment, at my first job out of college (NOT a programmer!), I have been tasked with the daunting project of automating our job quoting process. I have been leaning heavily toward using Access because we all already have the license and I know it can do all of the things I'm trying to do, I just don't exactly know how to do them. I was hoping you guys could reaffirm or disaffirm that choice and give me some ideas as to what the best way to do this in Access would be. The following is a description of our quoting process and the general scope of my project.

We receive what's called a "due list" from an aircraft operator that states all of the tasks that need to be completed for a particular maintenance event. We then take that due list and, using a guide put together by the aircraft manufacturer that was imported into a handy Excel spreadsheet, total the amount of man-hours that are required to complete each task. For items that are outside the manufacturer's provided tasks, we use a pen and paper (what is this the stone ages?!) to total these, add it to the amount from the due list items, then export all of this into a word template that is our final quote, ready to be saved in PDF and sent.

Three separate processes. Access can do everything in one.

First, I still need to build the database, but I am waiting for help from the manufacturer on this. Or I need to spend 20 hours manually compiling it, which I want to avoid at all costs as I also have ten other projects going on right now. It will likely contain the following columns: Task # (primary key), Description, Man-Hours, Interval (how often that task is completed, in case the scope broadens).

Then I need to create a query (or form, which would work better?) that prompts the user to input all of the tasks for the job he is quoting. This would ideally be some type of list in which the user clicks each task that needs to be done. The program must then ask what we are charging for an hourly rate and for a description of additional tasks (ones not tethered to the database) and their associated costs. It will then ask for the other variable information in the quote--the company work is being provided for, the aircraft, its base, the estimated down time, etc. From this, I must tell Access to sum up the man hours from all input tasks, multiply it by the shop rate, add it to the additional tasks, and provide me with a total quote.

Finally, I have to create a report that will serve as my final quote. It will have to take the total quote, as well as a description of each task, input all the variable items from above, and spit out a PDF form that is ready to be printed and sent to the customer.

I understand that there are MANY issues that need to be addressed, some of which have already been pointed out to me. I will give you a better understanding of what I'm looking for.

-I need this program to be able to have additional aircraft added into the database. For now, the aforementioned db will just be for one aircraft to test it out, but will eventually expand to include around a dozen.

-I would ideally like for it to also include a database of our customers so that we can auto-populate those fields in the report. We currently use SalesForce for our customer db, which I believe can easily be exported into Excel.

-As I mentioned above, a user interface is going to be necessary. Some of the people using this program don't know what the hell Access is, let alone how to run a query/report. I have seen some examples of this being done using Visual Basic, would you recommend any other ways?

-I understand Access can only support 2gb of data and I may need to hook to an external database. Would Microsoft SQL Server be a better choice? Is it free?

-Multiple users need to be able to use this database/application concurrently. I want to house it on our internal documents website for salesman to use (and so that it's backed up), but this creates a lot of issues with updating. Any recommendations?

-One of my projects for today is to create a mock of what the application could look like. I'll upload it here in an image file when it's done.

I want to reiterate that I'm not a programmer. I have stumbled into a task that I believe will truly define how flexible I can be for my company, but unfortunately it's a little above my head. I greatly appreciate any help you guys can offer, whether it's an explanation of how I can do any of the forms/queries/reports, a tip for solving any of the issues above, or simply a link to a good resource for learning the information myself.

Thanks for the help.

 

[kray4660]

This is my experience with Access. Just observations, I do not have any hard facts.

Access is a quick way to create a small database, preferably for a single user (does not always play well with a Novell network). Proper database design will avoid a lot of headaches later. The 2GB limit is unrealistic, most times when I see an Access DB get larger than 1GB, performance really drops off.

Sometimes a good balance is to have the front end (forms, reports,etc.) on the local machine and have the backend (tables) on SQL Server. SQL Server Express (it is free) will work, provided you can live within the limitations (I believe it still limits DB size to 4GB). Reporting from Access is difficult at best (I am spoiled, I can use Crystal Reports for reporting).

I hope this helps.

 

[SkipVought]

Hi,

You may get a better response in forum181.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xwb]

The max size is 10Gb if you use SQL Server Express 2008 R2.

SQL Server CE in comparison is really small (defaults to something like 128M although it could be configured for up to 4Gb).

 

[hmlhml]

kray4660, I am deeply depressed that you consider yourself spoilt by being allowed to use Crystal reporting. It just goes to show the truly awful standards we've come to expect (particularly in user-friendliness). I have to admit you're right that Access reporting is limited, though.

 

[SkipVought]

-I need this program to be able to have additional aircraft added into the database. For now, the aforementioned db will just be for one aircraft to test it out, but will eventually expand to include around a dozen.

This is a troubling statement, as it infers that additional aircraft will take something additional, other than just entering the data.

If this is indeed the case, you desperately need a designer for your database, that understands databases.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alw6589]

I don't see what you mean. The design of the db now is pretty simple, it's as follows. Please critique and make suggestions.

Database now:

1 table
3 columns: task number, description, man-hours

Database with X amount of aircraft added:

X tables
3 columns each: task number, description, man-hours

Does it have to be more complex than that?

 

[SkipVought]

Can you simply just enter data for other aircrafts right now if you wanted to?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alw6589]

That's my project today. Right now we have an abbreviated database that may be outdated, but my boss basically told me she expects me to use that and not to search for a current one. The problem therein is that each new revision to the aircraft manufacturer's documents may change these tasks and the man-hours required to do them, so that would require manual data entry for each revision of each aircraft. Kind of defeats the purpose of automating the process, but meh.

 

[SkipVought]

Automation of a process and design of a database are two different things, and manual process is not necessarily an issue.

I was getting vibs, that the database STRUCTURE would have to change as new aricraft are entered.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

This is what troubles me:

Database with [red]X[/red] amount of aircraft added:
[red]
X[/red] tables
3 columns each: task number, description, man-hours

Looks like you want to have 12 tables for 12 aircrafts. Simply said - don't do it this way.
I agree with Skip: "you desperately need a designer for your database, that understands databases"

Have fun.

---- Andy

 

[alw6589]

Well that's getting ahead of the current scope of the project.

For now, let's focus on one aircraft. The image below is a mock-up of what I envision the user interface to be (again, for just the one aircraft).

What's the first step in programming that into my Access database?

Should I use Visual Basic to set up that interface? If so, should I use the Visual Basic tool to make the application or can it all be done through Access?

Let me reiterate, I am not a programmer! I appreciate you guys being patient with me, I just need something of substance to present to the president of my company later today and as of right now I have an Access query that doesn't do what I need it to do and a database that can't use the Task Number as a primary key because there is at least one duplicate task number that can't be removed.

 

[MasterRacker]

Access can and should be used for your user interface. If you use VB you might as well also use SQL Express since you're adding complexity.

As to the database structure, please, before you do anything else, read this:

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx.

Using a separate, identically structured table for each aircraft is the exact opposite of what a database is supposed to be.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[SkipVought]

You are in very dangerous territory. Quick decisions of this nature are not wise. You do not appear to have the knowledge & experience to offer a good suggestion on WHAT kind of db to use, let alone HOW to design & implement, as is demonstrated by your statements.

Reminds me of the cartoon, "Ok guys. Listen up! The boss gave us a new important project. You two start programming, so we don't fall behind and the rest of us will figger out what we need to do!"

The best thing that you could offer your management as of today is to say, "I am way over my head. We need professional help with a project like this."

Yes, MS Access may be the right tool. And MS Access is relatively easy to use. But even THAT is a two edged sword: MS Access is relatively easy to make bad mistakes, if you don't know what you are doing!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alw6589]

Thank you guys for all the helpful responses. Will uprate you guys as soon as I figure out how

I have brought that up to my company and they want to keep it internal if at all possible. Today I'm going to talk to my project manager about retaining a consultant to help design the database/application, so if any of you are interested in making some money helping with a project that is probably rudimentary to you, please email me at alw6589@gmail.com with your qualifications.

The only reason I was looking at making separate tables for each aircraft was because I haven't seen the format of the task numbers are so vastly different. But you are correct, I need to brush up on my database knowledge. Thank you for the resource, any additional ones would be greatly appreciated.

 

[SkipVought]

There are techniques for handling some widely varying data as you mentioned, without having to constantly redesign your db. That is why your decision to hire a consultant, will be to your advantage.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hmlhml]

Even if your boss refuses to hire a consultant, I would strongly recommend inventing a 2nd, somewhat different aircraft, and keeping it in mind as you design your 1-aircraft database. Keep asking yourself, with each step you take: "What if...". Your boss will really regret it if he doesn't listen. Sometimes building in the flexibility to add extra items doesn't actually cost much effort, but redesigning later to make it possible is a nightmare.