Thank you for all the advice and support everyone on this list has given. I have learned a lot by reading as many posts as possible, though I don't understand nearly as many of the responses as I would like.
Is there a way to create an autonumber-style field in an Access online query?
Schedule_Classes produces a list of classes where the class ID is unique
Schedule_Enrollments produces a list of students enrolled in classes where the enrollment ID is unique.
Most classes have students enrolled, but some don't (for various reasons) so I want a list of all classes that also shows enrolled students if their are any. To do this I created a third query, Schedule_ClassesAndEnrollments, with an outer join that shows all the records in the Schedule_Classes query and those records on the Schedule_Enrollments query where there is a match on the ClassID field. Access online doesn't appear to have a way to show the underlying SQL statement, but I believe it would look like this if I could:
SELECT qry_Schedule_Classes.*, qry_Schedule_Enrollments.*
FROM qry_Schedule_Classes LEFT JOIN qry_Schedule_Enrollments ON qry_Schedule_Classes.ID = qry_Schedule_Enrollments.ClassID;
When linking the Access desktop front end to the Microsoft 365 backend via ODBC, however, the frontend wants each record in the Schedule_ClassesAndEnrollments query to have a unique identifier. If I choose a field as the identifier that does not have all unique values, the frontend will only display the first record for each value that is not unique. (All values are visible on the backend, however.) The problem I have is that because of the multiple enrollments in many of the classes, the ClassID field is not unique in the Schedule_ClassesAndEnrollments query. The enrollment ID field is null for all the records where a class lacks any enrolled students, so that is not an option either. I could easily make a new ID field in the Schedule_ClassesAndEnrollments query, but I can't figure out how to ensure it would be unique for each record. Is there a way to create an autonumber-style field in an Access online query? I could create a query on the frontend to join Schedule_Classes and Schedule_Enrollments, but queries work more quickly with Microsoft 365 when located on the backend, so I would like to have as much of the work happen there as possible.
I appreciate your help, support, and patience.
Ryan Hagglund
__._,_.___
Posted by: rhagglund@myeigo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar