data validation concerning two tables

[johnnyram]

hi guys,

i have some problems with the validation of some access data.
granted that i have the following tables:

table emp
columns: int ID primary key, string NAME, int DEPTNO, int A

table dept
columns: int DEPTNO primary key, string DNAME, int B references EMP(DEPTNO)

when i insert some data into dept, it should be checked whether B < A (in the corresponding row of emp). if not, the data mustnt be inserted.

this is the second day i am using access, hence dont blame me for this easy question ;-)

thanks, johnny

 

[lespaul]

Hi Johnny,

Welcome to Access.

I'm confused by your table structure. You have tblEmployee with DeptNo (I would expect this to be a Foriegn Key to tblDepartment). But then you say that tblDepartment.intB references the Employee Department number. That seems to be a circular reference.

What exactly are you trying to do? From your limited example, I would expect:

tblEmployee
EmpId
EmpLastName
EmpFirstName
EmpDepartment (FK to tblDepartment)

tblDepartment
DeptID
DeptName

Then you could gather information about the employee, but if you needed the DepartmentName on a report then you would join into the tblDepartment to get the name.

For instance, with the tables above, this query:

SELECT * FROM tblEmployee

EMPID EmpLastName EmpFirstName EmpDepartment
1 Blow Joe 1
2 Doe Jane 2

but if I wanted to get the name of the department, I would have to join into the table:

SELECT EMPID, EMPLASTNAME, DEPTNAME FROM tblEmployee
INNER JOIN tblDepartment on tblEmployee.EmpDepartment = tblDepartment.DeptName

EMPID EMPLASTNAME DEPTNAME
1 BLOW Administration
2 DOE Manufacturing

Does that make sense?

If you need some additional information, please provide more details about what you are trying to accomplish.

Leslie

 

[johnnyram]

i do not want to join two tables
i just want to validate if B < A


once again, now with another example:
create table BTestseries
(
BID number(5) primary key,
Bname varchar2(90),
Bn number(3) not null check (bn > 0),
Bstartdate date,
Bcomments varchar2(150)
);

create table Bsamples
(
BSID number(5) primary key,
BID number(5) not null,
Bd number(3) not null check (bd > 0),
constraint foreign_bsamples foreign key (BID) references BTestseries(BID)
);

i suppose that you are familiar with oracle, these are my oracle scripts. i do not know how to write them for access, i have just created the tables in design view

however, when i am adding a new row to bsamples, i want to check whether [bsamples].[bd] <= [btestseries].[bn]

if not, the row must not be inserted.

johnny