[Field1]=[Field2] Same Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Amy said:
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].
Click to expand...

If you're using a form to fill it in, you can put in Me.StudentID in the
defaultvalue for ATSNo.
Click to expand...

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 structure:

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 said:
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. said:
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.
Click to expand...
Click to expand...
 
What can I say, worked like a dream. Thank you. Debbie D.

[email protected] said:
Amy said:
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].
Click to expand...

If you're using a form to fill it in, you can put in Me.StudentID in the
defaultvalue for ATSNo.
Click to expand...

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 structure:

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
;
Click to expand...
 
You're welcome :-)

-Amy

Debbie D. said:
What can I say, worked like a dream. Thank you. Debbie D.

[email protected] said:
Amy said:
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.
Click to expand...

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 structure:

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
;
Click to expand...
Click to expand...
 
Back
Top