Numbering Records In Query

[jf03cg]

Hello, I have a question. I don't really know much about access, but I am really comforatable with SQL (DB2 and MySQL Programmer). Anyways, I was wondering if there is any way to do the following in SQL.

I have a Students Table that holds the names of students, an applications table that holds different applications, and an offers table that holds offers for the different applications.

The relationships for the tables are :
One Student Has Many Applications, One Application has Many Offers

IE: One Application can have up to three offers attached to it. I have the following query running to get all of the offers for all students in a specified term:

SELECT DISTINCT Offers.tblStudentProgCodeFK, Names.StudentID, Names.FName, Names.LName, (Offers.Code), tblPrograms.ProgName, Names.[Country Of Birth], Offers.[OCAS Number]
FROM qryApplications_Offers AS Offers, [Names], tblPrograms, (SELECT DISTINCT StartingDate, EndingDate FROM StartDates WHERE Term LIKE "*"+[Forms]![frmSelector]![Term]+"*") AS B
WHERE (Names.StudentID = Offers.StudentID AND (tblPrograms.ProgCode = Offers.Code OR Offers.Code=0) )
AND
(Offers.StartDate BETWEEN B.StartingDate AND B.EndingDate);

What I was wondering is if there is any way to add a field in the output that would number the selections. Right now it returns a table like:

tblFK StdID Code
34440 20394 0112
34440 20394 0123
34440 20394 0234
34234 25847 0100
47364 34857 0111
47364 34857 0311

I would like to do something like this:

tblFK StdID Code Choice
34440 20394 0112 0
34440 20394 0123 1
34440 20394 0234 2
34234 25847 0100 0
47364 34857 0111 0
47364 34857 0311 1

Where the choice column would count the choice for that student. Is this even possible? There is no choice number in the Applications or Offers table so I would need to do this in the query....


Any help would be appreciated

 

[RuralGuy]

Maybe this will help: http://www.lebans.com/rownumber.htm

 

[mresann]

Although this is not the same tables you use, I use the three fields tblFK, StdID, and Code in a table called "Test". When you add aggregate fields to Access or other Jet based queries, you have to create a subquery using the same underlying tables or queries as aliases.

SELECT Test.tblFK, Test.StdID, Test.Code, (SELECT Count (*)
FROM Test AS Test_1
WHERE Test.StdID = Test_1.StdID 
AND Test.tblFK = Test_1.tblFK
AND Test.Code >= Test_1.Code) AS Choice
FROM Test
ORDER BY Test.tblFK, Test.StdID, Test.Code;

Note the subquery groups the tblFK and StdID fields together, then creates a COUNT mechanism on all records associated with those fields, allowing the Choice column to display a record count.

 

[jf03cg]

Thank you, I had to modify it a little bit because Access Loved to crash, but it works now