QUERIES USING THE FINANCIAL DATABASE
| No. | SQL Queries (Instructions) |
Required Tables (Instructions) |
Query Output |
| 1. | SELECT DISTINCTROW dbo_ib_header.NUMB_ACCT AS [Account Number], dbo_ib_header.NAME_EMPL_PROJ_DIR1 AS [Principal Investigator], Mid([NAME_EMPL_PROJ_DIR1],5) AS [Last Name], dbo_ib_header.NAME_ICOA AS [Account Name], dbo_ib_header.DATE_ICOA_ACCOUNT_BEGIN AS [Account Begin Date], dbo_ib_header.DATE_ICOA_ACCOUNT_END AS [Account End Date], Sum(IIf([dbo_ibis_detail#2]![CODE_UNIV_REC_TYPE] Like "??BD",[dbo_ibis_detail#2]![AMNT_FNST_DTL],0)) AS [Total Budget], Sum(IIf([dbo_ibis_detail#2]![CODE_UNIV_REC_TYPE] In ('IENC','IENS','IENP','PENC','PENS','PENP','RENC','RENS','RENP'),[dbo_ibis_detail#2]![AMNT_FNST_DTL],0)) AS [Total Encumbrances], Sum(IIf([dbo_ibis_detail#2]![CODE_UNIV_REC_TYPE] Like "?ACT",[dbo_ibis_detail#2]![AMNT_FNST_DTL],0)) AS [Total Actuals], [Total Budget]-([Total Encumbrances]+[Total Actuals]) AS Balance FROM dbo_ib_header INNER JOIN [dbo_ibis_detail#2] ON dbo_ib_header.NUMB_ACCT = [dbo_ibis_detail#2].NUMB_ACCT WHERE ((([dbo_ibis_detail#2].CODE_COBJ)>"0099")) GROUP BY dbo_ib_header.NUMB_ACCT, dbo_ib_header.NAME_EMPL_PROJ_DIR1, Mid([NAME_EMPL_PROJ_DIR1],5), dbo_ib_header.NAME_ICOA, dbo_ib_header.DATE_ICOA_ACCOUNT_BEGIN, dbo_ib_header.DATE_ICOA_ACCOUNT_END, [dbo_ibis_detail#2].DEPT_MNEMONIC HAVING (((dbo_ib_header.NUMB_ACCT) Not Like ("0202844 **10010")) AND (([dbo_ibis_detail#2].DEPT_MNEMONIC)=[Enter you department mnemonic:])) ORDER BY dbo_ib_header.NUMB_ACCT, Mid([NAME_EMPL_PROJ_DIR1],5); | ib_header, ibis_detail#2 | Report showing department budget information |
| 2. | Download MS Access file Salary Report |
ib_header, ibis_detail#2 | Report showing Staff Salary: Budget & PI Information |
| Go top | |||
This page is maintained by Rubina V. Nashine: rvn2@psu.edu, (814) 360-1882
Eberly College of Science, 517 Thomas Building, University Park, PA 16802-2112
This page was last updated on 10 December 2007 © Penn State University
If you would like to communicate with the keepers of the Eberly College of Science Web server, send electronic mail to: science-web@science.psu.edu
Technology Webmaster: Brian Broking < brb10@psu.edu >
Content Webmaster: Barbara Kennedy < science@psu.edu >