@syndalis@deebaser@Feral
Guys my wife is having a problem with a thing at work and I do not know Excel or database stuff because I am dumb. Can you answer this possibly?
Our department has around 160 spreadsheets uploaded to various directories on sharepoint - more are added sporadically as new projects come through each month. Each spreadsheet is updated by users at various times in the month.
There are column names going from A to AA. The column types are varied; dates, dollar amounts, a comments field, y/n field, etc. Several of the columns contain formulas. Each spreadsheet is populated with a varying number of rows. Some have only 1 row, a few have as many as 5k, but the average is probably under 200 rows.
I need to be able to pull the rows with data in them from all 160(ish) spreadsheets in to one source so that reports can be created based on the most up to date data. I’m limited to using excel and access. The varying number of rows makes excel problematic, at least with what I know about excel. I don’t have much experience doing linked tables in access so I’m not sure if that could even work with the large number of sources.
What would be the best way to combine the data in to one source?
Ok, I can totes help with this, but I do need some basic questions answered.
1) Is the data expected to be "live" in that if someone updates a spreadsheet, a quick and easy report needs to be generated across all 160 with said updated data?
2) are the 160 spreadsheets in static locations that can all be easily referenced over the network by a single account?
3a) Is there any need to relate these tables to each other in a one-to-many fashion? Like, for instance
--- Spreadsheet A has a listing of all clients, and further info regarding the client
----Spreadsheets B-C have a column for the client name, and then individual contracts for said client(s)
----Spreadsheet D has a completely different set of info that relates to the client, but is not contract related per se... like expenses.
And you would need a report with the sum of contract info per client along with relevant expenses?
3b) id 3a is true, are the client names spelled / typed exactly the same on all spreadsheets enough that they could be used as a key?
If all of this is true, you can link a shitload of excel spreadsheets in access, set up a quick and dirty schema to relate these spreadsheets, use the query wizard to build you SQL in a GUI like a mini-boss, and then tie the query to a report.
As spreadsheets are updated by other staffers, all you would need to do is open access, run the report, and print it while doing a sick backflip or something because people will think you are a goddamned wizard. Tell them it took you hours, even though it takes you 1-2 minutes.
Boss asks me how my morning's going. I tell him that it's going great and that I had a really refreshing mango greek yogurt for breakfast. He called me a pussy. We laffed. It still hurt*.
fak u @gooey and the chobani club. it is you who are the real pussies.
*not really
i am enjoying a nice chobani mango right now
and i didnt even have to plant a mango tree or squeeze an udder
i had one at like 1am or 2am
i shoulda had another now
i finished my work at 430am and was just mad at myself because it was soemthing that took me literally like 2 hours to do
and i got home at 7pm to do it
and my internet came back before 9pm
(then by the time i was in bed and with lights off it was 5am cuz i was like fk dis i need a wank)
mehhh
3.5 hrs of sleep
slept 5 night before
mrehh
shazbro
go work for a bank
that wouldn't help gooey, that would just make things worse
no one was expecting me to do that work until 5am
i should've been done with that work by 6pm the day before
poo
0
Options
ShivahnUnaware of her barrel shifter privilegeWestern coastal temptressRegistered User, Moderatormod
only problem with srr is I want to hit dudes with pointy objects, but not katanas
they have a perfectly visually appropriate axe but it doesn't do AP damage for some arbitrary reason and it is so much weaker than its weaboo cousins
I feel like since the Shadowrun IP was created in the late 80s/early 90s the katanas being unaccountably better than everything else is working as intended
It wouldn't be a Shadowrun if one dude isn't carrying a katana.
+3
Options
JacobkoshGamble a stamp.I can show you how to be a real man!Moderatormod
LudiousI just wanted a sandwich A temporally dislocated QuiznosRegistered Userregular
My wife is good with excel and access. She's worked with them for a long time and knows a smattering of VB, etc. Unfortunately she's working on a system she didn't set up and the person that did is nice enough but really hard to work with. They are constantly making changes to this clusterfuck of a web of databases and sheets and it fucks her reports up. It is making her look bad. She had a come to jesus meeting with her boss about this, but no matter how true it is, it looks bad when your reports are wrong because someone else is making changes to the system. A system you didn't design and can barely comprehend, and the designer refuses to communicate properly about these constant changes. I dont normally like asking for help and her even less so, but it's getting to be a matter of survival.
DynagripBreak me a million heartsHoustonRegistered User, ClubPAregular
I think I'm gonna make more of on an effort to learn something new and or interesting each day. I do a decent job of this already but I should probably widen my focus. Heck, I used to read encyclopedias for fun.
My wife is good with excel and access. She's worked with them for a long time and knows a smattering of VB, etc. Unfortunately she's working on a system she didn't set up and the person that did is nice enough but really hard to work with. They are constantly making changes to this clusterfuck of a web of databases and sheets and it fucks her reports up. It is making her look bad. She had a come to jesus meeting with her boss about this, but no matter how true it is, it looks bad when your reports are wrong because someone else is making changes to the system. A system you didn't design and can barely comprehend, and the designer refuses to communicate properly about these constant changes. I dont normally like asking for help and her even less so, but it's getting to be a matter of survival.
I would ask the bigger question of:
"why the fuk are there 160 and growing spreadsheets when you have access, and can probably turn the whole thing into 3-4 tables with primary keys and some sweet data entry forms?"
SW-4158-3990-6116
Let's play Mario Kart or something...
My wife is good with excel and access. She's worked with them for a long time and knows a smattering of VB, etc. Unfortunately she's working on a system she didn't set up and the person that did is nice enough but really hard to work with. They are constantly making changes to this clusterfuck of a web of databases and sheets and it fucks her reports up. It is making her look bad. She had a come to jesus meeting with her boss about this, but no matter how true it is, it looks bad when your reports are wrong because someone else is making changes to the system. A system you didn't design and can barely comprehend, and the designer refuses to communicate properly about these constant changes. I dont normally like asking for help and her even less so, but it's getting to be a matter of survival.
I would ask the bigger question of:
"why the fuk are there 160 and growing spreadsheets when you have access, and can probably turn the whole thing into 3-4 tables with primary keys and some sweet data entry forms?"
I am not a data guy obviously but it's because there are 160 different people updating 160 different spreadsheets and it's the way things are done and this is the government and my hatred of change pales in comparison to the government's hatred of change.
The winner of the overall was last to go (it was an individual time trial today) and on finishing there was a queue for doping control, so he wasn't allowed back to his team to get on the rollers and warm down
So instead he's just riding slow loops in the finishers compound
The Eurosport cameraman is filming a fat Italian cameraman running round and round after the stage winner trying to keep his face in shot
japan on
0
Options
BethrynUnhappiness is MandatoryRegistered Userregular
My wife is good with excel and access. She's worked with them for a long time and knows a smattering of VB, etc. Unfortunately she's working on a system she didn't set up and the person that did is nice enough but really hard to work with. They are constantly making changes to this clusterfuck of a web of databases and sheets and it fucks her reports up. It is making her look bad. She had a come to jesus meeting with her boss about this, but no matter how true it is, it looks bad when your reports are wrong because someone else is making changes to the system. A system you didn't design and can barely comprehend, and the designer refuses to communicate properly about these constant changes. I dont normally like asking for help and her even less so, but it's getting to be a matter of survival.
I would ask the bigger question of:
"why the fuk are there 160 and growing spreadsheets when you have access, and can probably turn the whole thing into 3-4 tables with primary keys and some sweet data entry forms?"
^THIS
If the project leads have time to dick around with up to 27 different columns in 160+ spreadsheets, they can update the information in a single fucking source.
+2
Options
LudiousI just wanted a sandwich A temporally dislocated QuiznosRegistered Userregular
I agree with you both by the way and my wife does too, and I'm sure if you ask her about it at Pax she will explain in no uncertain terms why and in what way her hands are tied.
You guys would blow your brains out if you had to work for the government.
My wife is good with excel and access. She's worked with them for a long time and knows a smattering of VB, etc. Unfortunately she's working on a system she didn't set up and the person that did is nice enough but really hard to work with. They are constantly making changes to this clusterfuck of a web of databases and sheets and it fucks her reports up. It is making her look bad. She had a come to jesus meeting with her boss about this, but no matter how true it is, it looks bad when your reports are wrong because someone else is making changes to the system. A system you didn't design and can barely comprehend, and the designer refuses to communicate properly about these constant changes. I dont normally like asking for help and her even less so, but it's getting to be a matter of survival.
I would ask the bigger question of:
"why the fuk are there 160 and growing spreadsheets when you have access, and can probably turn the whole thing into 3-4 tables with primary keys and some sweet data entry forms?"
^THIS
If the project leads have time to dick around with up to 27 different columns in 160+ spreadsheets, they can update the information in a single fucking source.
could be regular spreadsheets coming from a bunch of vendors, who don't feel inclined to be consistent
Ludious, would it help to write enough checks into a spreadsheet somewhere to display an alert when there are changes in the spreadsheets
0
Options
syndalisGetting ClassyOn the WallRegistered User, Loves Apple Productsregular
I am assuming the person made some spreadsheet templates and starts a new spreadsheet for each unit of work, be it a client or a contract or whatever. Probably drops that spreadsheet in is own folder on the public drive or in sharepoint.
Pro tier would be making a table that is the top level unit (client, contract), and have one row for what used to represent each spreadsheet. Put whatever important information that is that top level as a column on this table (who is responsible for the unit of work, project start date, client contact info, etc). And most importantly, set up an AutoNumber field, right click it and say "Primary Key"
Now, make a new table, that used to represent the rows of each individual spreadsheet. The only difference being that the first column should be called unitID or clientID or projectID, and should be a number field. then put every other column from the old spreadsheets here.
Now, set up a form for the top level table, and put a few clients/unit in there.
Now, set up a form for the row-based data, and make the unitID field a dropdown/autocomplete field that has to match an entry from the top level table. You have now "related" the data to the client, which will allow you to have two tables that does the lifting for an infinite number of spreadsheets in the old system.
Whenever you want a classic "client" spreadsheet, you can go into queries, link the two tables by unitID, and drag the fields you are interested in to the bottom area, with the unitID number as the filter by option in the unitID field.
SW-4158-3990-6116
Let's play Mario Kart or something...
+1
Options
AtomikaLive fast and get fucked or whateverRegistered Userregular
Like, not enjoyable, but more a nauseating trainwreck in slow motion.
0
Options
LudiousI just wanted a sandwich A temporally dislocated QuiznosRegistered Userregular
I don't know. I will show her this later. She can't forum from work because she's not I.T. Special
Anything I say is basically relaying quantum physics to a retarded kid. This Data shit scares the bejesus out of me can't I just go make servers go woosh
"why the fuk are there 160 and growing spreadsheets when you have access, and can probably turn the whole thing into 3-4 tables with primary keys and some sweet data entry forms?"
Because Excel is really easy and you just write data into the cells and maybe it formats for you, and you can do basic algebra by adding = into the formula box.
Very easy to train people to use Excel for things that databases would be far better for.
OFGEM, incidentally, seems to store a whole lot of their stuff in spreadsheets. One of the joys of my current work is trying to sort all of that out into a database, so you can actually do useful things like record output histories and so on.
...and of course, as always, Kill Hitler.
+1
Options
Irond WillWARNING: NO HURTFUL COMMENTS, PLEASE!!!!!Cambridge. MAModeratormod
I am assuming the person made some spreadsheet templates and starts a new spreadsheet for each unit of work, be it a client or a contract or whatever. Probably drops that spreadsheet in is own folder on the public drive or in sharepoint.
Pro tier would be making a table that is the top level unit (client, contract), and have one row for what used to represent each spreadsheet. Put whatever important information that is that top level as a column on this table (who is responsible for the unit of work, project start date, client contact info, etc). And most importantly, set up an AutoNumber field, right click it and say "Primary Key"
Now, make a new table, that used to represent the rows of each individual spreadsheet. The only difference being that the first column should be called unitID or clientID or projectID, and should be a number field. then put every other column from the old spreadsheets here.
Now, set up a form for the top level table, and put a few clients/unit in there.
Now, set up a form for the row-based data, and make the unitID field a dropdown/autocomplete field that has to match an entry from the top level table. You have now "related" the data to the client, which will allow you to have two tables that does the lifting for an infinite number of spreadsheets in the old system.
Whenever you want a classic "client" spreadsheet, you can go into queries, link the two tables by unitID, and drag the fields you are interested in to the bottom area, with the unitID number as the filter by option in the unitID field.
bethryn is right
the perverse reality is that the skill level of data entry peons is much, much lower than the skill level of People Charged With Assembling Key Reports, so it is easier to ask the latter to adapt than the former
Posts
http://www.reactiongifs.us/wp-content/uploads/2013/09/dont_believe_you_anchorman.gif
Uh oh, using chat for productivity and not talking about dicks and/or butts. You guys are in trouble now.
I really need to practice my baking, it is totes my weak spot
Not gonna complain
I need that money baddddddddd
that wouldn't help gooey, that would just make things worse
no one was expecting me to do that work until 5am
i should've been done with that work by 6pm the day before
I will keep this in mind, if you ever go mad from power and I have to kill you.
It wouldn't be a Shadowrun if one dude isn't carrying a katana.
how do I convince them that despite my liberal arts degree and 3.1 gpa that i'm totally a prime candidate and ready to make the monies
Arch,
https://www.youtube.com/watch?v=t_goGR39m2k
http://media.heavy.com/media/2013/05/2.gif
get a enthusiastic reference from an undergrad tutor somewhere i guess
I would ask the bigger question of:
"why the fuk are there 160 and growing spreadsheets when you have access, and can probably turn the whole thing into 3-4 tables with primary keys and some sweet data entry forms?"
Let's play Mario Kart or something...
Lies
Noooooo
'Yeah he totally like didn't fall asleep in ALL of my lectures'
Arch,
https://www.youtube.com/watch?v=t_goGR39m2k
oh yeah you stand no chance
Arch,
https://www.youtube.com/watch?v=t_goGR39m2k
I am not a data guy obviously but it's because there are 160 different people updating 160 different spreadsheets and it's the way things are done and this is the government and my hatred of change pales in comparison to the government's hatred of change.
Dude she's going full German Expressionist
I'm doomed
The winner of the overall was last to go (it was an individual time trial today) and on finishing there was a queue for doping control, so he wasn't allowed back to his team to get on the rollers and warm down
So instead he's just riding slow loops in the finishers compound
The Eurosport cameraman is filming a fat Italian cameraman running round and round after the stage winner trying to keep his face in shot
Every time, they are very, very wrong.
^THIS
If the project leads have time to dick around with up to 27 different columns in 160+ spreadsheets, they can update the information in a single fucking source.
You guys would blow your brains out if you had to work for the government.
It's not exactly GOOD, but I'm oddly mesmerized.
could be regular spreadsheets coming from a bunch of vendors, who don't feel inclined to be consistent
Ludious, would it help to write enough checks into a spreadsheet somewhere to display an alert when there are changes in the spreadsheets
I am assuming the person made some spreadsheet templates and starts a new spreadsheet for each unit of work, be it a client or a contract or whatever. Probably drops that spreadsheet in is own folder on the public drive or in sharepoint.
Pro tier would be making a table that is the top level unit (client, contract), and have one row for what used to represent each spreadsheet. Put whatever important information that is that top level as a column on this table (who is responsible for the unit of work, project start date, client contact info, etc). And most importantly, set up an AutoNumber field, right click it and say "Primary Key"
Now, make a new table, that used to represent the rows of each individual spreadsheet. The only difference being that the first column should be called unitID or clientID or projectID, and should be a number field. then put every other column from the old spreadsheets here.
Now, set up a form for the top level table, and put a few clients/unit in there.
Now, set up a form for the row-based data, and make the unitID field a dropdown/autocomplete field that has to match an entry from the top level table. You have now "related" the data to the client, which will allow you to have two tables that does the lifting for an infinite number of spreadsheets in the old system.
Whenever you want a classic "client" spreadsheet, you can go into queries, link the two tables by unitID, and drag the fields you are interested in to the bottom area, with the unitID number as the filter by option in the unitID field.
Let's play Mario Kart or something...
That's kinda the vibe i get from it.
Like, not enjoyable, but more a nauseating trainwreck in slow motion.
Anything I say is basically relaying quantum physics to a retarded kid. This Data shit scares the bejesus out of me can't I just go make servers go woosh
half an hour until Mondo prints should drop
I'm dying squirtle
Very easy to train people to use Excel for things that databases would be far better for.
OFGEM, incidentally, seems to store a whole lot of their stuff in spreadsheets. One of the joys of my current work is trying to sort all of that out into a database, so you can actually do useful things like record output histories and so on.
awesome. i have tribia tonight but might have time before i go after i get back.
might be a little late tho
does cel still have the same gamertag he did?
bethryn is right
the perverse reality is that the skill level of data entry peons is much, much lower than the skill level of People Charged With Assembling Key Reports, so it is easier to ask the latter to adapt than the former