If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
[Field1]=[Field2] Same Table
Hi all,
I have a table similar to [StudentID] PK further down [ATSNo]. When first assigned on a temp bases the [ATSNo] is the same as the [StudenID] field. Is their a way to may the default value of [ATSNo] equal to that of the [StudentID]. Many thanks for taking the time to read this. Any help appreciated. Debbie D. |
#2
|
|||
|
|||
If you're using a form to fill it in, you can put in Me.StudentID in the
defaultvalue for ATSNo. HTH; Amy "Debbie D." wrote in message ... Hi all, I have a table similar to [StudentID] PK further down [ATSNo]. When first assigned on a temp bases the [ATSNo] is the same as the [StudenID] field. Is their a way to may the default value of [ATSNo] equal to that of the [StudentID]. Many thanks for taking the time to read this. Any help appreciated. Debbie D. |
#3
|
|||
|
|||
Amy Blankenship wrote: I have a table similar to [StudentID] PK further down [ATSNo]. When first assigned on a temp bases the [ATSNo] is the same as the [StudenID] field. Is their a way to may the default value of [ATSNo] equal to that of the [StudentID]. If you're using a form to fill it in, you can put in Me.StudentID in the defaultvalue for ATSNo. In case the OP may wants to implement constraints at the engine level (and this is the tablesdbdesign group, after all g), here's a suggested structu CREATE TABLE Students ( StudentID CHAR(10) NOT NULL PRIMARY KEY, CHECK(StudentID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ) ; CREATE TABLE ATS ( StudentID CHAR(10) NOT NULL UNIQUE REFERENCES Students (StudentID) ON DELETE CASCADE ON UPDATE CASCADE, ATSNo CHAR(10) NOT NULL UNIQUE, CHECK(StudentID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), PRIMARY KEY (StudentID, ATSNo) ) ; CREATE View StudentsATS AS SELECT Students.StudentID, IIF(ATS.ATSNo IS NULL, Students.StudentID, ATS.ATSNo) AS student_ATS FROM Students LEFT JOIN ATS ON Students.StudentID = ATS.StudentID ; INSERT INTO Students (StudentID) VALUES ('8818377710') ; INSERT INTO Students (StudentID) VALUES ('8260033186') ; INSERT INTO ATS (StudentID, ATSNo) VALUES ('8260033186', '5066735852') ; SELECT StudentID, student_ATS FROM StudentsATS ; |
#4
|
|||
|
|||
Thanks Amy your advise was great and work. Much appreciated. Debbie D.
"Amy Blankenship" wrote: If you're using a form to fill it in, you can put in Me.StudentID in the defaultvalue for ATSNo. HTH; Amy "Debbie D." wrote in message ... Hi all, I have a table similar to [StudentID] PK further down [ATSNo]. When first assigned on a temp bases the [ATSNo] is the same as the [StudenID] field. Is their a way to may the default value of [ATSNo] equal to that of the [StudentID]. Many thanks for taking the time to read this. Any help appreciated. Debbie D. |
#5
|
|||
|
|||
What can I say, worked like a dream. Thank you. Debbie D.
" wrote: Amy Blankenship wrote: I have a table similar to [StudentID] PK further down [ATSNo]. When first assigned on a temp bases the [ATSNo] is the same as the [StudenID] field. Is their a way to may the default value of [ATSNo] equal to that of the [StudentID]. If you're using a form to fill it in, you can put in Me.StudentID in the defaultvalue for ATSNo. In case the OP may wants to implement constraints at the engine level (and this is the tablesdbdesign group, after all g), here's a suggested structu CREATE TABLE Students ( StudentID CHAR(10) NOT NULL PRIMARY KEY, CHECK(StudentID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ) ; CREATE TABLE ATS ( StudentID CHAR(10) NOT NULL UNIQUE REFERENCES Students (StudentID) ON DELETE CASCADE ON UPDATE CASCADE, ATSNo CHAR(10) NOT NULL UNIQUE, CHECK(StudentID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), PRIMARY KEY (StudentID, ATSNo) ) ; CREATE View StudentsATS AS SELECT Students.StudentID, IIF(ATS.ATSNo IS NULL, Students.StudentID, ATS.ATSNo) AS student_ATS FROM Students LEFT JOIN ATS ON Students.StudentID = ATS.StudentID ; INSERT INTO Students (StudentID) VALUES ('8818377710') ; INSERT INTO Students (StudentID) VALUES ('8260033186') ; INSERT INTO ATS (StudentID, ATSNo) VALUES ('8260033186', '5066735852') ; SELECT StudentID, student_ATS FROM StudentsATS ; |
#6
|
|||
|
|||
You're welcome :-)
-Amy "Debbie D." wrote in message ... What can I say, worked like a dream. Thank you. Debbie D. " wrote: Amy Blankenship wrote: I have a table similar to [StudentID] PK further down [ATSNo]. When first assigned on a temp bases the [ATSNo] is the same as the [StudenID] field. Is their a way to may the default value of [ATSNo] equal to that of the [StudentID]. If you're using a form to fill it in, you can put in Me.StudentID in the defaultvalue for ATSNo. In case the OP may wants to implement constraints at the engine level (and this is the tablesdbdesign group, after all g), here's a suggested structu CREATE TABLE Students ( StudentID CHAR(10) NOT NULL PRIMARY KEY, CHECK(StudentID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ) ; CREATE TABLE ATS ( StudentID CHAR(10) NOT NULL UNIQUE REFERENCES Students (StudentID) ON DELETE CASCADE ON UPDATE CASCADE, ATSNo CHAR(10) NOT NULL UNIQUE, CHECK(StudentID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), PRIMARY KEY (StudentID, ATSNo) ) ; CREATE View StudentsATS AS SELECT Students.StudentID, IIF(ATS.ATSNo IS NULL, Students.StudentID, ATS.ATSNo) AS student_ATS FROM Students LEFT JOIN ATS ON Students.StudentID = ATS.StudentID ; INSERT INTO Students (StudentID) VALUES ('8818377710') ; INSERT INTO Students (StudentID) VALUES ('8260033186') ; INSERT INTO ATS (StudentID, ATSNo) VALUES ('8260033186', '5066735852') ; SELECT StudentID, student_ATS FROM StudentsATS ; |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help again from Ken Snell (Query) | Randy | Running & Setting Up Queries | 22 | August 29th, 2005 08:15 PM |
Help with relationship plase | Rock | Database Design | 5 | July 4th, 2005 03:54 AM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
Manual line break spaces on TOC or Table of tables | Eric | Page Layout | 9 | October 29th, 2004 04:42 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |