J
Jill
Hi all,
Access new bie here.
I have created faculty database. The main tables are tblClassList and
tblPersonalInfo. The tables look like this:
tblClassList
ID/CourseNumber/Description/AccountCode/SalaryAmountPerCourse, etc.
Salary amount per course is basically full salary amount devided by the
class credit unit. To make things complicated, if an instructor gets a
stipend, it goes in the class list table, too.
tblPersonalInfo
ID/Name/ SalaryScale/Workload/SalaryAmount, etc.
I created a query to see who gets how much stipend and how much each
instructor makes per year. That query looks like this:
SELECT tblPersonalInfo.PSID, qryTotalSalaryforEachF.Status,
qryTotalSalaryforEachF.Instructor, qryTotalSalaryforEachF.SumOfCredit,
tblPersonalInfo.Salary,
qryTotalSalaryforEachF!SumOfPerCourse-tblPersonalInfo!Salary AS
AdditiontoSalary, qryTotalSalaryforEachF.SumOfPerCourse,
tblPersonalInfo.ScaleLevel, tblPersonalInfo.FullScaleAmount
FROM tblPersonalInfo INNER JOIN qryTotalSalaryforEachF ON
tblPersonalInfo.PSID=qryTotalSalaryforEachF.PSID
GROUP BY tblPersonalInfo.PSID, qryTotalSalaryforEachF.Status,
qryTotalSalaryforEachF.Instructor, qryTotalSalaryforEachF.SumOfCredit,
tblPersonalInfo.Salary, qryTotalSalaryforEachF.SumOfPerCourse,
tblPersonalInfo.ScaleLevel, tblPersonalInfo.FullScaleAmount;
(Ok, actually this query was created from queries already.)
Now I need to join tblClassList and the query above for a co-worker who will
manipulate it the way she needs to for her job. The problem is how to create
a spread sheet without repeating the values in the above query in each row.
(The co-worker wants it on Excel spread sheet.) Right now the result looks
like this if I just join them:
ID/CourseNumber/Description/AccountCode/SalaryAmountPerCourse/Salary/AdditionstoSalary/TotalSalary
01/PR 101/Basic Printing/10110/$200/$600/$15/$615
01/PR 201/InterMediate Printing/10110/$200/$600/$15/$615
01/PR 301/Adv Printing/10110/$200/$600/$15/$615
01/PR 0/Stpend/10112/$15/$600/$15/$615
I hope I make sense. Please let me know if I am not clear.
Thank you.
Access new bie here.
I have created faculty database. The main tables are tblClassList and
tblPersonalInfo. The tables look like this:
tblClassList
ID/CourseNumber/Description/AccountCode/SalaryAmountPerCourse, etc.
Salary amount per course is basically full salary amount devided by the
class credit unit. To make things complicated, if an instructor gets a
stipend, it goes in the class list table, too.
tblPersonalInfo
ID/Name/ SalaryScale/Workload/SalaryAmount, etc.
I created a query to see who gets how much stipend and how much each
instructor makes per year. That query looks like this:
SELECT tblPersonalInfo.PSID, qryTotalSalaryforEachF.Status,
qryTotalSalaryforEachF.Instructor, qryTotalSalaryforEachF.SumOfCredit,
tblPersonalInfo.Salary,
qryTotalSalaryforEachF!SumOfPerCourse-tblPersonalInfo!Salary AS
AdditiontoSalary, qryTotalSalaryforEachF.SumOfPerCourse,
tblPersonalInfo.ScaleLevel, tblPersonalInfo.FullScaleAmount
FROM tblPersonalInfo INNER JOIN qryTotalSalaryforEachF ON
tblPersonalInfo.PSID=qryTotalSalaryforEachF.PSID
GROUP BY tblPersonalInfo.PSID, qryTotalSalaryforEachF.Status,
qryTotalSalaryforEachF.Instructor, qryTotalSalaryforEachF.SumOfCredit,
tblPersonalInfo.Salary, qryTotalSalaryforEachF.SumOfPerCourse,
tblPersonalInfo.ScaleLevel, tblPersonalInfo.FullScaleAmount;
(Ok, actually this query was created from queries already.)
Now I need to join tblClassList and the query above for a co-worker who will
manipulate it the way she needs to for her job. The problem is how to create
a spread sheet without repeating the values in the above query in each row.
(The co-worker wants it on Excel spread sheet.) Right now the result looks
like this if I just join them:
ID/CourseNumber/Description/AccountCode/SalaryAmountPerCourse/Salary/AdditionstoSalary/TotalSalary
01/PR 101/Basic Printing/10110/$200/$600/$15/$615
01/PR 201/InterMediate Printing/10110/$200/$600/$15/$615
01/PR 301/Adv Printing/10110/$200/$600/$15/$615
01/PR 0/Stpend/10112/$15/$600/$15/$615
I hope I make sense. Please let me know if I am not clear.
Thank you.