UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Setting Up Db For Work Project - A Little Stuck, Access 2010    
 
   
MydnyteSyn
post May 21 2019, 01:52 PM
Post#1



Posts: 5
Joined: 20-May 19



Hello all!

I'm so glad I found this community! I'm very new to Access. I downloaded the Access 2010 for Dummies PDF, I bought the Access 2010 Bible, and I've been carefully reading and following along in those on how to use Access, but I am a little stuck.

One of my co-workers created this amazing dashboard in Excel that generates metrics my company uses daily as we work on a $2 billion dollar project. However, this excel dashboard contains way too much information and the managers do not have the time to sift through it to find the numbers they need. I've been tasked with simplifying things and I thought a database via Access would be the best way to go about this.

My database needs to accomplish the following:
  • Pull existing data from several sheets in the Excel workbook
  • Perform calculations to show totals on a month-to-month basis
  • Generate reports that are automatically emailed to a specific group of people (if this is possible)

I have attached a PDF of showing the tables I have created already, showing one of the relationships I need. I'm kind of stuck on how to set up the rest.

The other area I'm really stuck with concerns Hours. I need to show not only how many hours an employee worked, but also where that time was spent. For example:

Regular Hrs
Overtime Hrs
Out of Office hrs
Travel hrs
Admin hrs

Above being the typical work day hours a works. Below, showing the areas of a project where their workday hours were spent.

BAU
WR
Projects
Admin

And I need to know how to set up the rest of the relationships.

Any help is truly appreciated.

Thank you in advance!
This post has been edited by MydnyteSyn: May 21 2019, 01:53 PM
Attached File(s)
Attached File  Access_Tables_Screencapture.pdf ( 146.28K )Number of downloads: 10
 
Go to the top of the page
 
theDBguy
post May 21 2019, 02:20 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,508
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

I hope you realize the difference between Excel and Access! Although both could probably do similar things, they are typically used for different purposes. Excel is could for charting and "what-if" analysis. Access is a database program for storing and retrieving information (mostly historical). So, I just want to make sure you think about your goal and be certain if using Access is the way to go (especially if you already have a working Excel solution).

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MydnyteSyn
post May 21 2019, 02:23 PM
Post#3



Posts: 5
Joined: 20-May 19



Oh yeah, I understand the differences. I just need to extract specific data from the over-sized excel document and I need to automate processes.
Go to the top of the page
 
MadPiet
post May 21 2019, 03:16 PM
Post#4



Posts: 3,120
Joined: 27-February 09



At the risk of losing you completely, this sounds like you want something like a PowerPivot data model. (Except you're using Excel 2010, which used an add-in for that. Wasn't native until 2013.) You could try it out using PowerBI Desktop, but the caveat is that it helps to understand DAX (query language) unless you're doing really simple stuff. Your design looks like you're doing a lot of YTD calculations etc, so DAX may work well for you... well, if you can get your head around it (it's a really strange language in some ways).
Go to the top of the page
 
MydnyteSyn
post May 21 2019, 03:21 PM
Post#5



Posts: 5
Joined: 20-May 19



Well, my Access is 2010. My Excel is 2013. And yes, I need to run all Year-to-date stuff and make it easy for my managers to understand. I'll be the one generating the reports from the data and emailing it to them.
Go to the top of the page
 
MadPiet
post May 21 2019, 03:32 PM
Post#6



Posts: 3,120
Joined: 27-February 09



what does the data in your spreadsheets look like?
Can you sanitize one or two so they're safe for public consumption? I don't care about real numbers or people's names or any of that. I'm trying to understand where you're starting from. So if you rename someone by his initials or something similar, that's fine.

Maybe check out www.powerpivotpro.com and see if that gives you any ideas.
This post has been edited by MadPiet: May 21 2019, 03:32 PM
Go to the top of the page
 
MydnyteSyn
post May 21 2019, 03:51 PM
Post#7



Posts: 5
Joined: 20-May 19



I've attached a PDF containing 4 screen captures from one of the 5 excel dashboards we use; one per manager.
Attached File(s)
Attached File  ExcelSample.pdf ( 425.25K )Number of downloads: 12
 
Go to the top of the page
 
MadPiet
post May 21 2019, 03:59 PM
Post#8



Posts: 3,120
Joined: 27-February 09



Where is the data? on spreadsheets behind this? (One spreadsheet? A folder full of them? Or?)

To some degree, how you do this depends on what you're trying to get out of your data? Are you analyzing it for patterns or are you just reporting back on it? (How do you do the reporting now? What parts of Excel functionality are you using?) You could do this with PowerBI and use drill-through etc, but without more detail, it's hard to be completely sure.

One thing that's really handy about PowerBI is that you can do things like automagically get all the data from a folder, import it into your model, and report on it. But as I said before, it requires DAX.
This post has been edited by MadPiet: May 21 2019, 04:01 PM
Go to the top of the page
 
MydnyteSyn
post May 21 2019, 04:02 PM
Post#9



Posts: 5
Joined: 20-May 19



That sampling of 4 of the spreadsheets in the one workbook shows some data. We enter this information daily. All those tabs at the bottom of the screen captures are sheets that contain more data. So we're entering numbers in certain areas, those numbers are auto-populated into cells they need to be in, math functions are being calculated, etc.

Go to the top of the page
 
MadPiet
post May 21 2019, 04:36 PM
Post#10



Posts: 3,120
Joined: 27-February 09



Without seeing a real workbook and some raw data, I can't say for sure, but if I had to do this, I'd probably enter the data into Excel, connect to it with PowerBI Desktop, and build my reports there. But given that you're using 2010, I'm guessing that it might not be an option. I can barely remember 2010 anymore. I could probably untwist the data and dump it in Access... (actually I'm sure I could).

I guess in your case, I'd use Excel as a front end reporting tool. I just never used 2010 a lot. I think I started getting into Excel when 2013 came out.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th June 2019 - 12:33 PM