As I wrote in my previous message, multiplying the ClassID field by one million worked and the Access desktop database accepted it just fine as the "Unique Record Identifier". As a follow-up and out of curiosity I wanted to see if it would accept a text field as well so created the following field:
TextID: Cast([ClsID],Text)+"-"+IIf(Not [EnrolID] Is Null,Cast([EnrolID],Text),"NS")
The Access desktop frontend accepts this as the "Unique Record Identifier" but then gives me the message "Cannot create index on the given field." I won't use the TextID field in any way--only creating it to meet Access's linking requirements--so will Access not being able to create an index on the field cause any problems? Would I be better using the ClasID field multiplied by one million or does it matter? I'm one of those who likes to know how things work in the background.
Thank you and I appreciate your time.
Ryan
Hello Ryan
How about creating a calculated column by concatenating the ClassID and the EnrollmentID:
SELECT qry_Schedule_Classes.*, qry_Schedule_Enrollments.*,
qry_Schedule_Classes.ID & "_" & qry_Schedule_Enrollments.EnrollmentID AS UniqueID
FROM qry_Schedule_Classes LEFT JOIN qry_Schedule_Enrollments
ON qry_Schedule_Classes.ID = qry_Schedule_Enrollments.ClassID;
If the unique identifier needs to be numeric, then multiply the ClassID by a power of ten that is guaranteed to be greater that the highest EnrollmentID, then add EnrollmentID:
qry_Schedule_Classes.ID * 1000000 + qry_Schedule_Enrollments.EnrollmentID AS UniqueID
Good luck!
Graham Mandeno [Access MVP 1996-2014]
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, 8 December 2014 12:43
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Create autonumber field in an Access online query
John,
Thanks for the reply. There is a Primary Key on the Student Enrollments table, but that field is null for the classes where no one is enrolled so it won't work as a unique identifier for Schedule_ClassesAndEnrollments.
Any onsite lesson we teach at one of our schools will have at least one enrolled student. We have offsite lessons we teach at kindergartens, for example however, that have up to 35 children that are not actually our students because we are sending our teacher to the kindergarten to teach. In such cases we need the class on the schedule but there is no one technically enrolled. Thus the Student Enrollments table ID--which is a Primary Key for the Student Enrollments table--has a null value in the Schedule_ClassesAndEnrollments query in such cases and doesn't work as a unique identifier.
Ryan
--------
Ryan-
Is there no Primary Key on the Student Enrollments table? There should be, and you could use that.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
------
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?
I have an Access database for my language school. My database frontend is on Access 2013 desktop and the backend is on Microsoft 365. I have two queries on the backend:
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 (6) |
Tidak ada komentar:
Posting Komentar