Excel, Access or other?

Fed up talking videogames? Why?
User avatar
That's not a growth
Member
Joined in 2008

PostExcel, Access or other?
by That's not a growth » Fri Apr 14, 2017 12:42 pm

I have a large spreadsheet I use at work to manage a project I'm doing which isn't that user friendly when you need a quick overview of what's going on and how data is related, and was wondering the best way to proceed.

Each row is a location of an install, with the columns being various information, so you have general contact information having about half a dozen columns, pre install information taking up a dozen, equipment of the install taking up a few, invoice and PO information taking up a few, plus it also keeps track of the current status of that location since we deal with dozens and dozens simultaneously all at different stages, some with unique issues that need to be accounted for too.

I have other sheets which reference the first one to show only a few columns so I'm able to easily condence related information to fit on one screen, but this means these sheets are essentially read only, and there are limitations to how useful this is since if I re-sort the first page I need to re-setup these pages.

Problems keep happening, or take longer to solve than they should, because data isn't linking together well enough so we can't back trace things that happened months prior precisely and quickly, such like if a location have received two separate shipments of equipment on different dates and which PO and which invoice they were attached to.

I'm thinking an actual database is the way to go, and access is available to me but I was hoping for a bit of advice before I potentially go down the wrong path.

User avatar
Tomous
Member
Joined in 2010
AKA: Vampbuster

PostRe: Excel, Access or other?
by Tomous » Fri Apr 14, 2017 12:54 pm

From what you've described I don't think you need Access, you just need to use VLOOKUP formulas.

On your master sheet, give each row a unique identifier then on the other sheets use VLOOKUP to find the relevant columns you want to display.

That way, you will only need to ever change data in the master sheet and it will automatically update in the supporting sheets.

Image
User avatar
That's not a growth
Member
Joined in 2008

PostRe: Excel, Access or other?
by That's not a growth » Fri Apr 14, 2017 2:03 pm

Perhaps, but I'd have to make a few more sheets for POs, Invoices and Deliveries, since you could have several invoices for a site, each PO will have several invoices, each site could have several Deliveries, or a delivery could be multiple Invoices of equipment from multiple POs.

Also, changing data in the master sheet isn't ideal because it's about 6 screens wide, so if you need columns from either side it's a pain.

I could break down the master sheet to be multiple sheets, but depending on what I'm doing certain information could go in multiple sheets, and when editing data I don't want to move between sheets to minimise mistakes.

Unless there's a way to reference data from another sheet, but it also able to update the original, but I'm thinking not?

User avatar
Tomous
Member
Joined in 2010
AKA: Vampbuster

PostRe: Excel, Access or other?
by Tomous » Fri Apr 14, 2017 2:07 pm

Ok, that sounds more complex.

It sounds you need an accounting system to be honest. I mean you could try looking up excel/access solutions for three way match accounting on google and seeing what people suggest?

Image
User avatar
Death's Head
Member
Joined in 2009

PostRe: Excel, Access or other?
by Death's Head » Fri Apr 14, 2017 2:08 pm

You said you are already referencing data on one sheet from another. I think your problem might be that you need to step back and design a proper layout rather than just trying to patch it up.

Yes?
User avatar
That's not a growth
Member
Joined in 2008

PostRe: Excel, Access or other?
by That's not a growth » Fri Apr 14, 2017 3:01 pm

Death's Head wrote:You said you are already referencing data on one sheet from another. I think your problem might be that you need to step back and design a proper layout rather than just trying to patch it up.


Considering I'm asking if it's worth rebuilding from the ground up in a new program, I'd like to think it was evident I'm not looking to just do a patch up here.

I'm just trying to figure out when i start over is it worth doing it again in excel, or if i should look into access.

To add a bit more information into the mix, ideally I'd like this to work over multiple projects, but it's not essential.

User avatar
<]:^D
Member
Joined in 2008

PostRe: Excel, Access or other?
by <]:^D » Fri Apr 14, 2017 3:49 pm

the issue with Access is patchy support/knowledge base
i use Access for some basic stuff for some work i do and whenever i have a query and google it its extremely hard to find sensible input; half the responses are 'you can do this in excel lol' :lol: :x

User avatar
Rex Kramer
Member
Joined in 2008

PostRe: Excel, Access or other?
by Rex Kramer » Fri Apr 14, 2017 4:28 pm

What about local Web pages for data entry and reporting all linked to a mysql database?

User avatar
That's not a growth
Member
Joined in 2008

PostRe: Excel, Access or other?
by That's not a growth » Fri Apr 14, 2017 5:07 pm

<]:^D wrote:the issue with Access is patchy support/knowledge base
i use Access for some basic stuff for some work i do and whenever i have a query and google it its extremely hard to find sensible input; half the responses are 'you can do this in excel lol' :lol: :x

That is one advantage of excel, you can Google stuff quite easily.
Rex Kramer wrote:What about local Web pages for data entry and reporting all linked to a mysql database?

I have no experience with mysql, what's the learning curve like?

User avatar
Rex Kramer
Member
Joined in 2008

PostRe: Excel, Access or other?
by Rex Kramer » Fri Apr 14, 2017 5:29 pm

I'd say it depends on your php experience

User avatar
That's not a growth
Member
Joined in 2008

PostRe: Excel, Access or other?
by That's not a growth » Fri Apr 14, 2017 5:53 pm

None. :lol:


Return to “Stuff”

Who is online

Users browsing this forum: Rich and 396 guests