Wow. Thanks for the education Ryan. That's something I would like to learn more of.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, December 10, 2014 4:14 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Create autonumber field in an Access online query
Thank you for the advice, Liz. I actually initially tried to use nz, but it's apparently a function not recognized within Access web apps so used iif as plan B. (I'm building my queries within the Access web app backend to improve performance when linking from my Access desktop frontend. I think Crystal gave this advice on this forum quite a while back.) While the right choice for a backend in my situation, Access web apps on Microsoft 365 appear to have a much smaller number of available functions than the Access desktop version and even when the function is the same or comparable the appropriate syntax and/or available parameters are often different. It's a bit annoying to be honest.
Thanks!
Ryan
-----
You can also use nz
ID: [ClsID]*1000000+nz([EnrolID],0)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, December 08, 2014 7:15 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Create autonumber field in an Access online query
Thank you, Graham. That worked. It appears you can't enter SQL directly into queries on the Microsoft 365 Access web app--at least I can't figure out how--so I created the following field in the query design view:
ID: [ClsID]*1000000+IIf(Not [EnrolID] Is Null,[EnrolID],0)
I had to add the iif function because the ID filed would come up null if the EnrolID had a null value
Appreciate the help!.
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 199 6-2014]
From: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogrou!%20ps.com> [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, 8 December 2014 12:43
To: MS_Access_Professionals@yahoogroups.com<mailto: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
http://www.viescas.com/
(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 databa! se 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_Sc! hedule_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 i n an Access online query? I could create a query on the frontend to jo! in 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
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Posted by: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (9) |
Tidak ada komentar:
Posting Komentar