If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database Design and Import
Hi,
I'm trying to figure out the best way to do a database design... I have two files that have a whole bunch of data that is similar to the other file. One file has columns with: State DistrictID District (only specific districts can belong to a particular state) Qtr Employment Type (Full time, part time, contract) Cost Pool (cost pool 1, cost pool 2) Category (counselor, teacher, adminstrator, etc) Value (how many people) Another file has these columns: State DistrictID District (only specific districts can belong to a particular state) Qtr Cost Pool (cost pool 1, cost pool 2) Category (counselor, teacher, adminstrator, etc) Expense (salary, benefits, etc) Value (how much was spent) I basically want to take this information and try to figure out how to get ave salary per person, ave salary per Category, etc. How can I link this data up? I just can't quite get my head around how to import the data from these two excel files and have the access know that cost pools from file 1 are the same as cost pools from file 2, etc. I understand how to import this data if it was one spreadsheet, but their are a couple columns that are different, so I can't just put them together... Ah help. Any thoughts? |
#2
|
|||
|
|||
Database Design and Import
Import or link the Excel data and try this query --
SELECT [File1].Category, Avg(Sum(File1.Value) /Sum(File2.Value))) AS Category_Average_Salary, (SELECT Avg(Sum(File1.Value) /Sum(File2.Value))) FROM File1 INNER JOIN File2 ON File1.Category = File2.Category WHERE Expense = "SALARY") AS Person_Average_Salary FROM File1 INNER JOIN File2 ON File1.Category = File2.Category WHERE Expense = "SALARY" GROUP BY [File1].Category; -- Build a little, test a little. "nathanelz" wrote: Hi, I'm trying to figure out the best way to do a database design... I have two files that have a whole bunch of data that is similar to the other file. One file has columns with: State DistrictID District (only specific districts can belong to a particular state) Qtr Employment Type (Full time, part time, contract) Cost Pool (cost pool 1, cost pool 2) Category (counselor, teacher, adminstrator, etc) Value (how many people) Another file has these columns: State DistrictID District (only specific districts can belong to a particular state) Qtr Cost Pool (cost pool 1, cost pool 2) Category (counselor, teacher, adminstrator, etc) Expense (salary, benefits, etc) Value (how much was spent) I basically want to take this information and try to figure out how to get ave salary per person, ave salary per Category, etc. How can I link this data up? I just can't quite get my head around how to import the data from these two excel files and have the access know that cost pools from file 1 are the same as cost pools from file 2, etc. I understand how to import this data if it was one spreadsheet, but their are a couple columns that are different, so I can't just put them together... Ah help. Any thoughts? |
Thread Tools | |
Display Modes | |
|
|