RE: [MS_AccessPros] Data Analysis question

Jim:

Please re-state the end game here - I was writing a lengthy answer but I am not sure that the prior set up is right for individuals; I was going for aggregates.


Steve




________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Thu, 12 Feb 2015 19:21:28 +0000
> Subject: Re: [MS_AccessPros] Data Analysis question
>
>
>
> Stephen,
>
> Okay after looking at this a little more and deciding I did not want to
> get into this project at all, powers that be would like this to work.
> But the data should be by month not daily. the data they want by day is
> the name of employees, for which there is a data warehouse table
> already for that.
>
> So I am back to the project and I have questions before I build the
> last table
>
> So I created 3 of the 4 tables and wanted to touch base with you before
> I create the many to many table.
>
>
> The many-to-many where your data is imported:
> tbl_(campus)_dept_metrics: (campus_id optional FK), dept_id_fk,
> metric_id_fk, metric_value, metric_date.
>
> How does the data get into the table? What I mean is how does the
> append query look? Is there multiple queries that have criteria for
> each campus and for each department?
>
> Thank You
>
> Jim Wagner
> ________________________________
>
>
> On Wednesday, January 21, 2015 3:46 PM, "Stephen Conklin
> StephenMConklin@hotmail.com [MS_Access_Professionals]"
> <MS_Access_Professionals@yahoogroups.com> wrote:
>
>
>
> If your business need is daily, then so be it … You can connect an
> Excel dashboard to Access data, and create pivots with slicers or
> filters against the schema I outlined below.
>
> Steve
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com]
> Sent: Wednesday, January 21, 2015 5:24 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Data Analysis question
>
>
> Steve,
>
> Because the count for employees count can change daily, such as hires
> and terms, we are thinking daily. Right now I copy monthly into excel
> and have a dashboard. that does the graphs. But it does not
> show an detailed history of a year.
>
>
>
> Jim Wagner
> ________________________________
>
> On Tuesday, January 20, 2015 6:56 PM, "Stephen Conklin
> StephenMConklin@hotmail.com<mailto:StephenMConklin@hotmail.com>
> [MS_Access_Professionals]"
> <MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>
> wrote:
>
>
> Daily seems to be overkill for this type of data, but that's just IMO.
> But I would not store all of these in separate tables. As John said
> prior, store all of these in a table, where you have each campus/dept
> listed by day (again, couldn't this be monthly?)
>
> All your metrics tracked go into 1 table; just because Campus A doesn't
> want to track widgets today, doesn't mean they won't want it next
> Tuesday.
>
> Your data model, I would suggest as something like this:
>
> tbl_campus: pk_id, campus_name
> tbl_department: pk_id, campus_id_fk, dept_name
> tbl_metric: pk_id, metric_name
>
> The many-to-many where your data is imported:
> tbl_(campus)_dept_metrics: (campus_id optional FK), dept_id_fk,
> metric_id_fk, metric_value, metric_date.
>
> Hth,
> Steve Conklin
>
>
>
>
>
>
> From:
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com]
> Sent: Tuesday, January 20, 2015 2:33 PM
> To:
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> Subject: Re: [MS_AccessPros] Data Analysis question
>
>
> John,
>
> Okay. I will let you know how it goes.
>
> Thank You For the help.
>
>
> Jim Wagner
> ________________________________
>
> On Tuesday, January 20, 2015 12:30 PM, "John Viescas
> JohnV@msn.com<mailto:JohnV@msn.com> [MS_Access_Professionals]"
> <MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>
> wrote:
>
>
> Jim-
>
> Putting campuses into separate tables will depend on what metrics you
> eventually need to report. If each campus requires different metrics,
> then maybe that's the way to go.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> On Jan 20, 2015, at 8:07 PM, Jim Wagner
> luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com>
> [MS_Access_Professionals]
> <MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>
> wrote:
>
> John,
>
> that will probably be the direction to go in with a table. Should I
> separate the campuses into their own tables? each campus has different
> departments also. Should the departments be a column in the table?
>
>
>
>
> Jim Wagner
> ________________________________
>
> On Tuesday, January 20, 2015 11:49 AM, "John Viescas
> JohnV@msn.com<mailto:JohnV@msn.com> [MS_Access_Professionals]"
> <MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>
> wrote:
>
>
> Jim-
>
> If you're not willing to share your SQL, it may be tough to help you.
>
> Break down what you ultimately need and try to design a "working" table
> that will be able to contain all the metric data. Then build several
> totals / append queries to load the rows into the working table. Don't
> try to do it all in one query.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> On Jan 20, 2015, at 5:47 PM, Jim Wagner
> luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com>
> [MS_Access_Professionals]
> <MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>
> wrote:
>
> John,
>
> In my attempt to do it in one query, I created a massive piece of
> monster query. Then Realized that it was too much and may not help me.
> It is long and does not even have the individual departments yet. The
> issue may be that it does not reflect the campuses correctly. I do not
> want to add the sql because it may show some sensitive metrics that may
> not be appropriate to show in the forum.
>
>
>
> Jim Wagner
> ________________________________
>
> On Tuesday, January 20, 2015 9:33 AM, "John Viescas
> JohnV@msn.com<mailto:JohnV@msn.com> [MS_Access_Professionals]"
> <MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>
> wrote:
>
>
> Jim-
>
> If you're not interested in the raw data - just the metrics - it does
> make sense to dynamically link to the downloaded table, run a query or
> queries to get the initial counts, and append those counts by day to a
> "working" table in Access. I assume the raw data has columns for
> employment status (full, part, on leave), employee classification,
> campus name, pay type, FICA type, minority type, grade, department, and
> FTE %. You might need to run multiple Totals queries to get the
> metrics you want to save. I would need to know more about the specific
> types of metrics to advise you further.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> On Jan 20, 2015, at 5:20 PM, Jim Wagner
> luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com>
> [MS_Access_Professionals]
> <MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>
> wrote:
>
> John,
>
> Because the downloaded file has about 479 rows each day, I did not want
> to store each table for each day in the database because of bloat. That
> is why my thought was to create a query that would get the counts for
> each of the metrics, append to a table the data for each day. then I
> was going to do the analysis in excel or even in Access. If there is
> another idea out there, let me know. I figure if I can get the counts
> for each day the analysis part will be the easy piece. I was thinking
> of a series of crosstabs but I could not figure how to connect the data
> without using power pivot.
>
> the type of metrics I am looking for is
>
> There are 4 campuses at the university.
> Each campus and department has different metrics that they are tracking.
>
> Total Employees
> Full time
> Part time
> On Leave
> Emp Classification type which has 3 types including Student
> Total Employees per campus
> Pay type like hourly, salary, or student
> Fica type
> Gender
> Minority type
> Grade includes 7 types like 01, 02, 03, 04, 2,3,4
> FTE % type includes 25%, 50%, 75% or 100%
> then each department break down for each of the metrics above.
>
> Thank You
>
>
>
> Jim Wagner
> ________________________________
>
> On Tuesday, January 20, 2015 2:19 AM, "John Viescas
> JohnV@msn.com<mailto:JohnV@msn.com> [MS_Access_Professionals]"
> <MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>
> wrote:
>
>
> Jim-
>
> Do the calculations simply involve applying formulas to columns in the
> imported data? If so, why not do the calculations in a query when
> needed or in Excel? What sort of analysis do you plan to do in Excel?
> Unless you need some complex financial functions, it might be best to
> do your "analysis" in Access with a report. Use the right tool for the
> job.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> On Jan 20, 2015, at 3:28 AM, Jim Wagner
> luvmymelody@yahoo.com<mailto:luvmymelody@yahoo.com>
> [MS_Access_Professionals]
> <MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>
> wrote:
>
> Bob,
>
> The calculations are in the query and then appended to the table. That
> is what is in my head at this point. I have created a query with some
> calculations but have not even converted the select query to an append
> query.
>
> I have a downloaded table every day. I would like to capture the
> calculations for the various metrics and then have the append query add
> the calculated values in the query to the table based on the date. Then
> I was going to do the analysis on the table in Excel. This is what I
> have in my head.
>
>
>
> Jim Wagner
> ________________________________
>
> On Friday, January 16, 2015 2:40 AM, "'Bob Phillips'
> bob.phillips@dsl.pipex.com<mailto:bob.phillips@dsl.pipex.com>
> [MS_Access_Professionals]"
> <MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>
> wrote:
>
>
> I have to ask, why would you add totals to the table? That is a no-no
> surely, totals should be calculated in the query so that you always
> include all of the latest data.
>
> From:
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com]
> Sent: 15 January 2015 23:27
> To:
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Data Analysis question
>
>
> So I have a question that I have been working on a solution for 2 days
> and I am unable to come up with a way to accomplish this. We would like
> to do some analysis for some data that we download every day. I would
> like to have the totals for each metrics appended to a table each day
> for a week from the current download each day. there are 4 campuses and
> 20 departments and we would like to track things like
>
> Gender
> FTE like .25%, .50% etc
> Employee status
> Race
> Grade
> Full or part time
>
> I figure that we could use Excel to do the analysis but my issue is how
> do I get to append the data each day from the new table download?
>
>
>
>
> Jim Wagner
> ________________________________
>
>
>
>
>
>
>
>
>
>
>
>
>
>

------------------------------------

------------------------------------


------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/