combining multiple records into one record

[RedGoneWILD]

I'm going to try and explain this as easy as I can.



I have a students database. The students table is linked to a funding table. A record will exist in the funding table for each semester a student is enrolled. So if a student is enrolled in the fall and spring for a given school year, then there will be two student records in the funding table. Further, a student may have multiple sources of funds for a given semester, so there may be two student records in each semester.

Basically what I want to be able to do is when I filter by school year (ex. School Year "2010-11") instead of returning two or more records for each student, it only returns one record with a column displaying fall funding and a column displaying spring funding.

The purpose of this is so I can eventually run a report that will group by funding source and then list all students who receive money from each source, as well as the amounts they received in the fall and spring on one line. Then I want to be able to total the amounts received for each source so I can keep track of how much money is used and how much is left.

Any help or pointers would be greatly appreciated.

[TheShabz]

Assuming you have the following fields in your funding table, a crosstab query should work out for you:

studentID
Semester
Funds

http://allenbrowne.com/ser-67.html

[RedGoneWILD]

Thanks for the reply! I'm not familiar with Transform or Pivot commands and have read a couple examples since you posted. I'm not following the logic real well.

Here's my current SQL statement...any chance you could show me an example of how to modify it to achieve the fall funding as one field and the spring funding as another field in the same record? And without getting any duplicate StudentID's unless they have two different funding sources?

SELECT tblSemesters.FiscalYear, tblBudgets.FundID, qryGradStudentsExtended.FileAs, tblStudentFunding.Semester, tblStudentFunding.Stipend, qryGradStudentsExtended.StudentID
FROM tblBudgets LEFT JOIN (tblSemesters RIGHT JOIN (tblStudentFunding LEFT JOIN qryGradStudentsExtended ON tblStudentFunding.StudentID = qryGradStudentsExtended.StudentID) ON tblSemesters.SemesterID = tblStudentFunding.Semester) ON tblBudgets.FundID = tblStudentFunding.StipendFundSource
WHERE (((tblSemesters.FiscalYear)="2010-11") AND ((qryGradStudentsExtended.StudentID) Is Not Null))
ORDER BY tblBudgets.FundID, qryGradStudentsExtended.FileAs, qryGradStudentsExtended.StudentID;

I assume the TRANSFORM statement always goes first, and PIVOT goes last but what are they really doing?

[TheShabz]

I'm really not the best with step-by-step instructions. try this

http://www.ehow.com/how_2020409_crea...ry-access.html

[RedGoneWILD]

holy shit dood, that worked!!!

thanks a ton!

[TheShabz]

yay woooo! /dances

Please mark the thread solved so other can find it useful as well. take care =]