ACCESS FEED: RE: [MS_AccessPros] Data Analysis question

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 [MS_Access_Professionals]" <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]
Sent: Tuesday, January 20, 2015 2:33 PM
To: 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 [MS_Access_Professionals]" <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 [MS_Access_Professionals] <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 [MS_Access_Professionals]" <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 [MS_Access_Professionals] <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 [MS_Access_Professionals]" <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 [MS_Access_Professionals] <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 [MS_Access_Professionals]" <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 [MS_Access_Professionals] <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 [MS_Access_Professionals]" <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]
Sent: 15 January 2015 23:27
To: 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?