Access or Excel? (1 Viewer)

tmyers

Active member
Local time
Today, 15:14
Joined
Sep 8, 2020
Messages
692
The boss handed me another project that deals with tracking a construction project from start to finish. It would log all changes and the financials for each change during the life of a project. This would have a report/template that could be sent to the customer so show the jobs current total, or what the cost/deduct would be for each change made.

I initially started doing this in Excel, but after I realized I was formatting sheets to be tables, I started wondering if it might be better to do this in Access instead. I have a rough idea of how it would look within Access, but am not too sure how I would structure the tables for it.

I attached what I had already thrown together to give a rough idea of the design I was aiming for. Based on that, which program does this appear to be better suited for? I will mention that the current iteration (not the attached) that is being used is Excel, but the reason the boss gave it to me for a revamp is on large projects, the sheet can become quite unruly to manage and sort through.
 

Attachments

  • New Job Financial Sheet.zip
    874.7 KB · Views: 20

theDBguy

I’m here to help
Staff member
Local time
Today, 12:14
Joined
Oct 29, 2018
Messages
14,577
Hi. Without looking at your attachment, my vote would be to use Access.
 

tmyers

Active member
Local time
Today, 15:14
Joined
Sep 8, 2020
Messages
692
To add a little further clarification, the "Overview" sheet starts off with the designation of items and the quantity the customers initial PO was for. The sheet then just sort of "flows" from there, with changes being entered as they happen and the right side of the sheet updating with the cost changes and such. The sheet "Buy Quote" is the "base" of the workbook. This is the final quote that the customer uses to issue their PO from.
 

tmyers

Active member
Local time
Today, 15:14
Joined
Sep 8, 2020
Messages
692
Hi. Without looking at your attachment, my vote would be to use Access.
That was my thought once I noticed I was formatting sheets to tables. My problem is based off the sheet I have already thrown together and the overall requirements, I can't seem to picture a table structure for it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:14
Joined
Oct 29, 2018
Messages
14,577
That was my thought once I noticed I was formatting sheets to tables. My problem is based off the sheet I have already thrown together and the overall requirements, I can't seem to picture a table structure for it.
Then don't think about the sheet you already created. Start from the beginning. Analyze the data you need to collect and decide how you would put them into tables. After that, you can figure out a way to come up with a form or report, if you want to mimic the spreadsheet.
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 15:14
Joined
Apr 27, 2015
Messages
3,423
If your choices are Excel or Access, Access wins every time on a project like this. Personally, I would use MS Project - it is tailor made for this type of work.
 

Isaac

Lifelong Learner
Local time
Today, 12:14
Joined
Mar 14, 2017
Messages
4,382
Aw man, this brings back memories. I did a project for a guy in Mesa, AZ to track construction projects from start to finish. The main work of science in the database was the "Timeline". It was so interesting ... all these tasks with due dates, accompanying attachments, and Word documents. We actually ended up breaking up because he was unable to communicate, sadly.

Anyway - I'd say do it in Access. As for multiple projects, I started realizing early on that instead of creating a new database every time a need came up, I liked to make one big database - call it something super generic like Operations or something - and then just keep adding functionality to it. This is SO much more efficient to the end user than having them have to access numerous Access Databases. They always opened one, and based on their permissions is what tabs they would see or not see. You can pack a lot of functionality into one database! I really ended up preferring to do it this way when all was said and done.

...and I suppose that fact probably helps explain more why I don't really "get into" the relationships window so much either, I almost never make a database intended for one, narrowly defined purpose and that's it. I always encourage users to let me make a general workflow /operations database and pack everything they need into it as we go - and they ALWAYS go for it and prefer it. I've never heard someone say "I'd like to have to open more programs, please" lol
and when you take this approach, it doesn't feel so weird to "should I create a whole Access db for this?" - that's a valid intuitive question your mind is having, and I'd answer it by saying "Don't - add it in to an Access db if possible"

Go for it - there is a LOT to construction timelines, I'd hate to imagine doing it ALL in excel.
 

tmyers

Active member
Local time
Today, 15:14
Joined
Sep 8, 2020
Messages
692
If your choices are Excel or Access, Access wins every time on a project like this. Personally, I would use MS Project - it is tailor made for this type of work.
I have access to the majority of the 365 suite. I am not aware of an MS Project, but I will look into it.
 

tmyers

Active member
Local time
Today, 15:14
Joined
Sep 8, 2020
Messages
692
Aw man, this brings back memories. I did a project for a guy in Mesa, AZ to track construction projects from start to finish. The main work of science in the database was the "Timeline". It was so interesting ... all these tasks with due dates, accompanying attachments, and Word documents. We actually ended up breaking up because he was unable to communicate, sadly.

Anyway - I'd say do it in Access. As for multiple projects, I started realizing early on that instead of creating a new database every time a need came up, I liked to make one big database - call it something super generic like Operations or something - and then just keep adding functionality to it. This is SO much more efficient to the end user than having them have to access numerous Access Databases. They always opened one, and based on their permissions is what tabs they would see or not see. You can pack a lot of functionality into one database! I really ended up preferring to do it this way when all was said and done.

...and I suppose that fact probably helps explain more why I don't really "get into" the relationships window so much either, I almost never make a database intended for one, narrowly defined purpose and that's it. I always encourage users to let me make a general workflow /operations database and pack everything they need into it as we go - and they ALWAYS go for it and prefer it. I've never heard someone say "I'd like to have to open more programs, please" lol
and when you take this approach, it doesn't feel so weird to "should I create a whole Access db for this?" - that's a valid intuitive question your mind is having, and I'd answer it by saying "Don't - add it in to an Access db if possible"

Go for it - there is a LOT to construction timelines, I'd hate to imagine doing it ALL in excel.
I wish I could show you the Excel file for one of the large jobs we are currently doing. Its a multimillion dollar job (for just the lighting!) and my god, the workbook is a massive pain to navigate and find things. This job will last for years and it will only get worse.
 

Isaac

Lifelong Learner
Local time
Today, 12:14
Joined
Mar 14, 2017
Messages
4,382
Sounds like quite a project to tackle actually - I'd let Access do the heavy lifting!
 

tmyers

Active member
Local time
Today, 15:14
Joined
Sep 8, 2020
Messages
692
Sounds like quite a project to tackle actually - I'd let Access do the heavy lifting!
I was hoping that would be the consensus. Now I have a blank Access open and am trying to picture a table structure.
How to handle the table(s) for changes is what I can't wrap my head around, but that would be a question better suited in the tables sub-forum.
 

Isaac

Lifelong Learner
Local time
Today, 12:14
Joined
Mar 14, 2017
Messages
4,382
Yes, probably a good idea to re-post. I just try to keep reminding myself "probably one table for each Entity or Event that has a relationship with another one" and it seems to cover about 99% of the scenarios.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 20:14
Joined
Sep 12, 2006
Messages
14,415
I would say Access, but if you are not used to it, it's an order of magnitude harder, and it's apples and pears. An Access table may look like a worksheet, but it really isn't.

You can't just dive in and "hack it" as you can with a spreadsheet. You also can't product a complex dbs without code. You don't have to write code in excel. Lots of people don't. If you have a lot of data, Access is far quicker.
 

Minty

AWF VIP
Local time
Today, 20:14
Joined
Jul 26, 2013
Messages
8,186
I'd agree with Dave.

If you haven't tackled something this complex before, doing it Access whilst ultimately will result in a much more robust and user-friendly application, it is probably a good 6-8 weeks of solid work for someone experienced in the black arts.

Time-tabling and displaying nice-looking Gantt style charts can become very complicated in Access, whereas it's 10 minutes work in Excel.
Equally, it can take an end-user 2 minutes to balls up all the data in Excel and break it. :cool:
 

Isaac

Lifelong Learner
Local time
Today, 12:14
Joined
Mar 14, 2017
Messages
4,382
I'd agree with Dave.

If you haven't tackled something this complex before, doing it Access whilst ultimately will result in a much more robust and user-friendly application, it is probably a good 6-8 weeks of solid work for someone experienced in the black arts.

Time-tabling and displaying nice-looking Gantt style charts can become very complicated in Access, whereas it's 10 minutes work in Excel.
Equally, it can take an end-user 2 minutes to balls up all the data in Excel and break it. :cool:
Ha ha - @Minty make some very good points!
That inspired me to add something, though - remember that when you are working on your Access database, if some need comes up that's absolutely perfect for Excel, but absolutely horrible by comparison in Access (like charting), don't hesitate to just build it in Access using code that DOES utilize Excel for the output and bypasses Access....I used to have some users that really liked getting their data in pivot tables. But the database was in Access. No problem, I created a button in Access that utilized Excel application & code to output the pivot, you can harmonize and mix the two infinitely, if possible. By early binding (check a reference to Excel in your access db project), you'll even get full Excel vba intellisense -quite priceless, combine that with some macro recording in Excel to refresh your memory on how to do things, and you have the full picture.
 

tmyers

Active member
Local time
Today, 15:14
Joined
Sep 8, 2020
Messages
692
You guys have made it seem like this kind of thing would involve a very, very complex system. Maybe I should shelve this one and just keep fiddling in Excel.
 

JMongi

Member
Local time
Today, 15:14
Joined
Jan 6, 2021
Messages
335
Is it involved? Yes. Is it any MORE involved than Excel? My gut says no. The fact that you have an existing excel file does give you inertia to NOT change. So that is a consideration. If this was your very FIRST exposure to Access, that would be cause for pause too. But, you've been rooting around in Access for a little while now. I think you'll find it EASIER to use Access. The main thing is what you've already been instructed to do i.e. really understand the data you are capturing and how it relates to one another.

There are really two facets to DB design (imo) and that is:
1. How is the data structured so that it can be properly normalized and contained in a DB (table structure).
2. How can I put data in and get data out of my DB. (i.e. Forms and Reports and the queries that make that easier as well).

Your new Access skills can help you with #1 and your existing excel spreadsheet can help you with #2.

Also, I would say that while MS Project COULD work for you, it's a whole new learning curve and an entirely new way of tackling that problem. I also personally think its rather poor at capturing the data you are looking for and VERY particular about the structure of the project data its tracking. Without trying to be mean, anyone that threw MS Project out as a possibility for someone who has NEVER used it before either doesn't use it themselves or doesn't use it properly to begin with. I consider myself pretty quick to pick up computer programs and while it wasn't "tricky", there was a whole lotta learning and tutorials on how to use the program, let alone points of view and processes on the topic of project management itself. I would NEVER suggest to someone without the backing of management to just implement MS Project on your own.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:14
Joined
Feb 28, 2001
Messages
19,399
The trick that makes Access the winner every time in something like this is the "historical" requirements - which for Access is simply a child table that has time-tags for various events to go with something related to the prime key. Correlating time-tagged events in Excel is a real P.I.T.A. because (technically) each cell in Excel is unrelated to any other cell, so gathering together all of the stuff you need is very tedious and may actually require MORE design effort in Excel.

EDIT: Removed one word that totally reversed the meaning of my comments.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 12:14
Joined
Mar 14, 2017
Messages
4,382
The trick that makes Access the winner every time in something like this is the "historical" requirements - which for Access is simply a child table that has time-tags for various events to go with something related to the prime key. Correlating time-tagged events in Excel is a real P.I.T.A. because (technically) each cell in Excel is unrelated to any other cell, so gathering together all of the stuff you need is very tedious and may actually require MORE design effort than in Excel.
clicking "new sheet" is just too easy. it should come with a little warning shock to your mouse finger. like if you hit F5 in SSMS for an action query without a where clause, you should get shocked :p
 

tmyers

Active member
Local time
Today, 15:14
Joined
Sep 8, 2020
Messages
692
The trick that makes Access the winner every time in something like this is the "historical" requirements - which for Access is simply a child table that has time-tags for various events to go with something related to the prime key. Correlating time-tagged events in Excel is a real P.I.T.A. because (technically) each cell in Excel is unrelated to any other cell, so gathering together all of the stuff you need is very tedious and may actually require MORE design effort than in Excel.
Doc, have a slight question regarding this.

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.
 

Users who are viewing this thread

Top Bottom