[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.

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.

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
;

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.

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
;

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
;