Access or Excel?

[JMongi]

I just took a quick peek at your Excel file. It already shows the benefits of Access in that you are already hard fixing the number of available changes for each item to (3) which would be totally unnecessary in Access. Also, I don't know how long you've worked for this company or how intricately you understand their business practices. In my experience, these types of spreadsheets only partially capture the full intent of the process due to the underlying limitations of Excel. It's quite possible an Access app could be more useful than this spreadsheet.

Just another question if you can answer it. You called this a "project" handed to you by your boss. What type of project is it? Is it using the existing spreadsheet only? Is it reimplementing it in some fashion? If so, why? That would also inform your decision making.

 

[tmyers]

I just took a quick peek at your Excel file. It already shows the benefits of Access in that you are already hard fixing the number of available changes for each item to (3) which would be totally unnecessary in Access. Also, I don't know how long you've worked for this company or how intricately you understand their business practices. In my experience, these types of spreadsheets only partially capture the full intent of the process due to the underlying limitations of Excel. It's quite possible an Access app could be more useful than this spreadsheet.

Just another question if you can answer it. You called this a "project" handed to you by your boss. What type of project is it? Is it using the existing spreadsheet only? Is it reimplementing it in some fashion? If so, why? That would also inform your decision making.

Since I am known as the techy in the office, I get handed the projects to make excel sheets and such. Last year I started learning Access since I was hitting the limits of Excels ability to do what I needed it to.

The project is to develop a nicer, more clean and easy to use way of tracking a construction project from start to finish. He wants to know at any given time what money is in the job and also be able to send the customer a report showing such that is up to date at the time it is done.

So he wants to be able to see everything from the jobs start when we get the PO from the customer to begin ordering to the very end when we hand over all the final documentation and everything in between.

I have built another app that we are beginning to use to quote the projects, so I like the idea of using Access since I could export everything I needed out of that app, into this one. I initially thought of just doing this within my current app, but it would cause major duplication plus a lot of rework to work it into it. So I assumed it would be easier to start a new database and give the ability to export pertinent data from one to the other.

 

[JMongi]

Nicer, more clean, easier to use = Access
Least Time to Usability = Current Spreadsheet

You know you bosses better than we do. But, if the general concept is to improve the existing tool I would strongly lean towards redoing it in Access. Just make sure they understand the short term loss (in development time) means long term gain in usability and capability.

 

[tmyers]

Nicer, more clean, easier to use = Access
Least Time to Usability = Current Spreadsheet

You know you bosses better than we do. But, if the general concept is to improve the existing tool I would strongly lean towards redoing it in Access. Just make sure they understand the short term loss (in development time) means long term gain in usability and capability.

Yup. I have told him I can probably do it, but rather than taking a day to a week to do, it could take me a month or more. Thankfully a lot of what my other database does can be re-tooled to work in this, saving me time.

 

[JMongi]

I don't know the dynamic at your place, but I'm in a similar situation (resident techie). Inevitably when I pick the path of least resistance, 2 weeks later someone is asking for functionality that would be 10x easier doing it the "right" way instead of the way that was quickest and easiest based on the original request. Once I got burned a few times I just decided to do it the right way. That's how my maintenance task app got started. It's taken a lot longer, but they've already asked for things that would be a royal pain to maintain in Excel that will be trivial in Access once its up and running. They think it can be done in Excel (because they don't know any better).

As Isaac said, no reason to do a separate DB. Just add it to your existing DB. Tables don't care what other tables are next to it (so-to-speak).

 

[The_Doc_Man]

So if I wanted to keep a "history", I could just use a child table with time stamps and maybe the quantity it was at that given time? So in essence, the table would have a PK, Date and something like Quantityatdate? Then obviously an ID to tie it to a project.

That is the general idea, pretty much as you said. Of course, the devil will be in the details, but yes.

And if you want reports showing history, you just group by PK and/or project, order by date ASCENDING, and there is your chronological history for whatever was in the grouping.

 

[The_Doc_Man]

Tables don't care what other tables are next to it (so-to-speak).

If everything is properly normalized, you are absolutely correct. But the added benefit is that sometimes (later down the road), something in one of those unrelated tables becomes back-door related somehow. And suddenly there you are with all that data waiting to be harvested. Yum!

 

[tmyers]

I don't know the dynamic at your place, but I'm in a similar situation (resident techie). Inevitably when I pick the path of least resistance, 2 weeks later someone is asking for functionality that would be 10x easier doing it the "right" way instead of the way that was quickest and easiest based on the original request. Once I got burned a few times I just decided to do it the right way. That's how my maintenance task app got started. It's taken a lot longer, but they've already asked for things that would be a royal pain to maintain in Excel that will be trivial in Access once its up and running. They think it can be done in Excel (because they don't know any better).

As Isaac said, no reason to do a separate DB. Just add it to your existing DB. Tables don't care what other tables are next to it (so-to-speak).

Would it be wrong to utilize the same FE, but keep the BE's separated? Or would that still be overkill?

 

[Isaac]

I guess I can think of a couple possible benefits of separate back ends.
1) if I am likely to be editing tables on the back end for the first few months of production, it might be easier to deal with kicking a few people out than more people.
2) if your folder permissions structure needs to be different

but honestly if none of those is a major thing, i'd see no reason to separate

i said this a few months ago, this reminds me of as time went by as i started using Access more and more, i started trying to be a master of generic and meaningless nomenclature when it came to database names.

in january i create a database called "Lien Release Reports".
in feb they do work in it, not just view reports, i wish i'd named it "Lien Release"
in mar a new group comes on, they do Lien Perfection. i wish i'd just named it Liens.accdb
soon the whole auto finance document management group is using it, i wish i'd named it document management.
by the time the whole auto finance group starts using it for anything and everything, i realize my mistake.

next time i start a database for a "limited purpose", I called it something like Data Management or Operations.

 

[CarlettoFed]

In order to be able to give you some advice it would be advisable to see how far you were with the project, in access, which you say you have already completed.

 

[The_Doc_Man]

A front-end can open up to (I think) 16 databases. Remember that the FE is itself a DB (with no data, only other structures), so that is #1. A normal split database back-end is #2. But that gives you 14 to go. (I wouldn't advise it, but it is certainly possible.)

 

[tmyers]

Got it. I will import the work I have done so far into my existing DB and segregate them in the nav window with grouping (for my own sanity).

 

[The_Doc_Man]

(for my own sanity).

Excellent point. Organize what you do, because in real life, you work on this project but then some departmental doo-doo hits the rotating atmospheric redistribution device and you get called away for a while. Then when you CAN get back to it, you spend HOURS scratching your head down to a bald spot trying to remember what the heck you were doing. And from there lies to road to insanity...

 

[JMongi]

Some of us are already at the end of that road

 

[JMongi]

Got it. I will import the work I have done so far into my existing DB and segregate them in the nav window with grouping (for my own sanity).

Well, I hope you don't feel like it's all take and no give from this forum because you just helped me out tremendously. I didn't know that functionality existed and my sanity levels have improved greatly!

 

[tmyers]

Well, I hope you don't feel like it's all take and no give from this forum because you just helped me out tremendously. I didn't know that functionality existed and my sanity levels have improved greatly!

I discovered grouping in the nav window a couple months ago. I have (what I think is) a ridiculous amount of objects to sift through. Tons of queries and forms mostly. It really helped separating them into various groups of what each actually belonged to.

 

[Thales750]

After spending the last 15 years developing databases to manage construction, I would say you need to start with Excel.
In order to do the things you want to do, it will take years of decision making, hundreds of tables, and tens of thousands of lines of code.

Start with spreadsheets, use them until the reasons why they won't scale become evident.

Then hire a developer, or use a already made cloud solution

 

[JMongi]

I fail to see how managing something that requires 100s of tables and tens of thousands of lines of code is EASIER in Excel?

 

[Pat Hartman]

but it would cause major duplication plus a lot of rework to work it into it.

This should teach you something. That something is "defensive programming" What is the next logical business step after preparing a quote? Making that quote a job. You should have anticipated this result and built the quoting database with that in mind and saving your company thousands of dollars and hundreds of hours of your work in the process. Good thing they don't know what a missed opportunity the first app was.

Before you make a second database, you might want to rethink the company work flow. Will having two databases be awkward for the users? Will they need to be jumping from one to the other throughout the day? Would it be easier for them to have just one database to work with? Would that require security and having to keep the "quote" users isolated from the "job" users? If the staff that quotes does not overlap with the staff that implements, having two separate apps won't annoy anyone since they would only be using one of the two apps but it might annoy the manager who is the one who will need to use both for reporting.

Sometimes it makes sense to have a shared BE and separate FE's. Other times (more rarely), it makes sense to have a single FE and multiple BE's.

 

[JMongi]

Pat is very correct. That was a lesson I learned too. There is a caveat that management doesn't always agree that the time needed to consider the next logical business step is worthwhile...until they ask for it and you have to rework certain things. So, hopefully your boss or your bosses boss gives you the leeway to take this approach.