As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

I should have had [chat] prepared!

14849515354101

Posts

  • surrealitychecksurrealitycheck lonely, but not unloved dreaming of faulty keys and latchesRegistered User regular
    Irond Will wrote: »
    like, it tastes and feels almost just like potato but it's only 120 calories per pound.

    you could pretty much starve to death on a celeriac diet

    http://www.reactiongifs.us/wp-content/uploads/2013/09/dont_believe_you_anchorman.gif

    obF2Wuw.png
  • GooeyGooey (\/)┌¶─¶┐(\/) pinch pinchRegistered User regular
    snes shadowrun is best fyi hth

    919UOwT.png
  • ElkiElki get busy Moderator, ClubPA mod
    @Irond Will‌ celery picked up an Xbox One.

    smCQ5WE.jpg
  • ElkiElki get busy Moderator, ClubPA mod
    And Titanfall.

    smCQ5WE.jpg
  • DynagripDynagrip Break me a million hearts HoustonRegistered User, ClubPA regular
    Ludious wrote: »
    syndalis wrote: »
    Ludious wrote: »
    @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.

    @Ludious‌

    Thanks @syndalis I passed the questions onto her. I will reply when she does.

    Uh oh, using chat for productivity and not talking about dicks and/or butts. You guys are in trouble now.

    Rear Admiral Choco
  • JacobkoshJacobkosh Gamble a stamp. I can show you how to be a real man!Moderator mod
    I am thinking I will bake a cake this week

    I really need to practice my baking, it is totes my weak spot

    rRwz9.gif
    surrealitycheckRear Admiral Choco
  • BethrynBethryn Unhappiness is Mandatory Registered User regular
    Celeriac is vile.

    ...and of course, as always, Kill Hitler.
  • TTODewbackTTODewback Puts the drawl in ya'll I think I'm in HellRegistered User regular
    Well got signed up for OT for another weekend.
    Not gonna complain
    I need that money baddddddddd

    Bless your heart.
  • Shazkar ShadowstormShazkar Shadowstorm Registered User regular
    Gooey wrote: »
    Gooey wrote: »
    Deebaser wrote: »
    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
  • ShivahnShivahn Unaware of her barrel shifter privilege Western coastal temptressRegistered User, Moderator mod
    Jacobkosh wrote: »
    I am thinking I will bake a cake this week

    I really need to practice my baking, it is totes my weak spot

    I will keep this in mind, if you ever go mad from power and I have to kill you.

    21stCenturyJacobkosh
  • ThomamelasThomamelas Only one man can kill this many Russians. Bring his guitar to me! Registered User regular
    Jacobkosh wrote: »
    kaleedity wrote: »
    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.

    JacobkoshdescElldren
  • JacobkoshJacobkosh Gamble a stamp. I can show you how to be a real man!Moderator mod
    Gooey wrote: »
    snes shadowrun is best fyi hth

    3g6cti5.jpg

    rRwz9.gif
    descRear Admiral ChocosurrealitycheckDelmainMrMisterGooey
  • LudiousLudious I just wanted a sandwich A temporally dislocated QuiznosRegistered User regular
    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.

  • Element BrianElement Brian Peanut Butter Shill Registered User regular
    Ok, phone interview to get into a pre-masters summer accounting program for non-accounting majors

    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

    Switch FC code:SW-2130-4285-0059

    Arch,
    https://www.youtube.com/watch?v=t_goGR39m2k
  • DynagripDynagrip Break me a million hearts HoustonRegistered User, ClubPA regular
    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.

  • ronyaronya Arrrrrf. the ivory tower's basementRegistered User regular
    Jacobkosh wrote: »
    I am thinking I will bake a cake this week

    I really need to practice my baking, it is totes my weak spot

    Arcane_Cooking.gif

    aRkpc.gif
    kedinikJacobkoshRMS Oceanic
  • TTODewbackTTODewback Puts the drawl in ya'll I think I'm in HellRegistered User regular
  • ronyaronya Arrrrrf. the ivory tower's basementRegistered User regular
    Ok, phone interview to get into a pre-masters summer accounting program for non-accounting majors

    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

    get a enthusiastic reference from an undergrad tutor somewhere i guess

    aRkpc.gif
  • syndalissyndalis Getting Classy On the WallRegistered User, Loves Apple Products regular
    Ludious wrote: »
    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...
    DeebaserFeral
  • japanjapan Registered User regular
    Bethryn wrote: »
    Celeriac is vile.

    Lies

    Irond Will
  • Dread Pirate ArbuthnotDread Pirate Arbuthnot OMG WRIGGLY T O X O P L A S M O S I SRegistered User regular
    desc and i are engaged in an intense game of draw something

    Jacobkosh
  • descdesc Goretexing to death Registered User regular
  • Element BrianElement Brian Peanut Butter Shill Registered User regular
    ronya wrote: »
    Ok, phone interview to get into a pre-masters summer accounting program for non-accounting majors

    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

    get a enthusiastic reference from an undergrad tutor somewhere i guess

    'Yeah he totally like didn't fall asleep in ALL of my lectures'

    Switch FC code:SW-2130-4285-0059

    Arch,
    https://www.youtube.com/watch?v=t_goGR39m2k
    ronya
  • Element BrianElement Brian Peanut Butter Shill Registered User regular
    desc and i are engaged in an intense game of draw something

    oh yeah you stand no chance

    Switch FC code:SW-2130-4285-0059

    Arch,
    https://www.youtube.com/watch?v=t_goGR39m2k
  • LudiousLudious I just wanted a sandwich A temporally dislocated QuiznosRegistered User regular
    syndalis wrote: »
    Ludious wrote: »
    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.

  • descdesc Goretexing to death Registered User regular
    desc and i are engaged in an intense game of draw something

    oh yeah you stand no chance

    Dude she's going full German Expressionist

    I'm doomed

  • japanjapan Registered User regular
    edited March 2014
    Tirreno Adriatico is finished

    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
  • BethrynBethryn Unhappiness is Mandatory Registered User regular
    japan wrote: »
    Lies
    Every so often, someone surreptitiously adds it to my mashed potato because they think I won't notice.

    Every time, they are very, very wrong.

    ...and of course, as always, Kill Hitler.
    surrealitycheck
  • descdesc Goretexing to death Registered User regular
    Cass what was the other game like Draw Something with the better drawing tools with the cool algorithmic pen tools and stuff

  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    syndalis wrote: »
    Ludious wrote: »
    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.

    DelmainFeral
  • LudiousLudious I just wanted a sandwich A temporally dislocated QuiznosRegistered User regular
    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.

  • GreeperGreeper Registered User regular
    Anybody watching this Bates Motel shit?

    It's not exactly GOOD, but I'm oddly mesmerized.

  • ronyaronya Arrrrrf. the ivory tower's basementRegistered User regular
    Deebaser wrote: »
    syndalis wrote: »
    Ludious wrote: »
    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

    aRkpc.gif
  • syndalissyndalis Getting Classy On the WallRegistered User, Loves Apple Products regular
    @Ludious I will go a step further:

    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...
    Feral
  • AtomikaAtomika Socially lubricated for your pleasure Registered User regular
    Greeper wrote: »
    Anybody watching this Bates Motel shit?

    It's not exactly GOOD, but I'm oddly mesmerized.

    That's kinda the vibe i get from it.

    Like, not enjoyable, but more a nauseating trainwreck in slow motion.

  • LudiousLudious I just wanted a sandwich A temporally dislocated QuiznosRegistered User regular
    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

  • simonwolfsimonwolf i can feel a difference today, a differenceRegistered User regular
    ugh

    half an hour until Mondo prints should drop

    I'm dying squirtle

  • BethrynBethryn Unhappiness is Mandatory Registered User regular
    syndalis wrote: »
    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?"
    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.
    ronya
  • Irond WillIrond Will WARNING: NO HURTFUL COMMENTS, PLEASE!!!!! Cambridge. MAModerator mod
    Elki wrote: »
    @Irond Will‌ celery picked up an Xbox One.

    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?

    Wqdwp8l.png
  • ronyaronya Arrrrrf. the ivory tower's basementRegistered User regular
    syndalis wrote: »
    @Ludious I will go a step further:

    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

    aRkpc.gif
This discussion has been closed.