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?

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?
>