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
(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
(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
(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
(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
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?
Jim Wagner
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (12) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar