I am designing a database for a vehicle maintenance repair centre (theoretically) and have the following relational schema:
Repairs-empNo and regNo as PK (formed from (*:*))
Buys-custNo and itemNo as Pk (Formed from (*:*))
Stores-branchNo and itemNo (Formed from (*:*))
EmployeeQualifications-empNo and qualifications as PK (Formed from multi-value attribute in Employee conceptual relation)
Once I had the relational schema from the conceptual mapping I entered it into MS Access-set all PK and relationships then started doing queries.
The first 2 queries worked fine but then seemed to have problem with Employee-Branch tables and it seems that no data shows when I have the 2 tables in the query design. When I put 'Branch' table in the query and extract fields it shows data in datasheet OK but when I put Employee table in then run the query no data shows on the datasheet?
The Employee relation has a recursive relationship, which when viewed in the query the tables have a (1:*) and an also the recursive line.
Could it be the recursive relationship in Employee causing the query problem? I'm not 100% sure that the recursive is right in the access design (although I have never done a DB with recursive relationship). I have defined relationship in access as per relational schema, which is OK.
I haven't included every step in this description as it would be too long but if you know what I'm trying to do and think you know where the problem might lie then i would be greatful to hear any advice.
Ok will do that. Something must have gone wrong because my first 2 queries, which were initially fine, now have wrong data in them. I have superviser empNo in Employee table as foreign key with null values in it-I thought this might be causing problems?