[Field1]=[Field2] Same Table

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

Amy Blankenship

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

HTH;

Amy
 
P

peregenem

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
;
 
G

Guest

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

Guest

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

Amy Blankenship

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Won't Work 5
basing a field validation rule on another field in same table... 2
Copying a Field 1
Criteria - show records in text field1 based on a yes/no field2 7
"Multiple Autonumber" in same table 2
Shift several columns in table 4
Assistance with Many-to-Many Relationships 4
Default value based on input of corresponding list box field 2

Top