altonomus said:
I am making a database that will allow me to create records for students and
participants in our College Weekend event. Students will host College
Weekenders in their rooms. I want to match College Weekenders to Students
according to Major. I have two tables: Students and CWtable both of which
have a "major" field. My query is a "create table" query. I joined the two
"major" fields together and selected that only to include rows that are equal.
The query runs fine, but assigns each College Weekender to each Student with
the same major. I only want to assign one College Weekender to one Student.
altonomus,
You mention you have a CREATE TABLE query, but you say you joined two
major fields in it (sounds like a SELECT), and that you "selected that
only to include" (which also sounds like a SELECT).
But, aside from that, I can't think of how except by VBA:
Air Code to follow:
Public Sub sAssignCW()
Dim db As DAO.Database
Dim rsStudents As DAO.Recordset
Dim rsCWtable As DAO.Recordset
Dim rsMajors As DAO.Recordset
Dim SQL_Students As String
Dim SQL_CWtable As String
Dim SQL_Majors As String
Dim CurrentMajor As String
SQL_Majors = "SELECT DISTINCT M1.Majors " & _
" FROM Students AS M1 "
Set db = CurrentDb()
set rsMajors = db.OpenRecordset(SQL_Majors, dbOpenForwardOnly)
'Insert EOF file checking
do until rsMajors.eof
CurrentMajor = rsMajors.Fields("Majors")
SQL_Students = "SELECT * " & _
" FROM Students AS S1 "
" WHERE S1.Majors = " & CurrentMajor & ";"
SQL_CWtable = "SELECT * " & _
" FROM CWtable AS C1 "
" WHERE C1.Majors = " & CurrentMajor & ";"
Set rsStudents = db.OpenRecordset(SQL, dbOpenDynaset)
Set rsCWtable = db.OpenRecordset(SQL_CWtable, dbOpenForwardOnly)
'Insert EOF file checking
With rsStudents
.MoveFirst
Do Until .EOF
.Edit
.Fields("CollegeWeekender") = rsCWtable.Fields("CWtableID")
.Update
.MoveNext
rsCWtable.MoveNext
Loop
End With
rsStudents.Close
rsCWtable.Close
rsStudents = Nothing
rsCWtable = Nothing
rsMajors.MoveNext
Loop
rsMajors.Close
rsMajors = Nothing
db.Close
db = Nothing
' Along with the giantic assumption that there cannot
'be more College Weekenders of a particular Major
'than there are Students of a particular Major.
' Extra code will be required to handle that
'situation (which doesn't look pretty at this point).
End Sub
Yes, it is untested. My apologies, but I don't have the table
structures handy. But the basic idea *should* work.
1: Summon up unique list of majors.
2: Assign CWtable's PK to a new column in Students, "WHERE" Majors
match.
Sincerely,
Chris O.
