How to create a query when one of the linked tables has no data

[SagarHussain]

Hi. At the outset, I would like to thank all the promoters and developers of this wonderful Forum for providing solutions to the 'trivial to the mind-boggling issues' that



Although I am not new to Access, I am poor when it comes to an efficient database design. I do need your help in resolving the following issue.

I have created a contacts database. Following tables are in a one-to-many relationship in the following hierarchy (primary key from each table with its counterpart foreign key in the linked table):
1. Tbl1OrganizationType (Eg., Education; Health; Government etc.)
2. Tbl2Organizations (Eg., Xyz University; Ministry of Health etc.)
3. Tbl3Institutions (Eg., College of Medicine; College of Science, Research Council etc.)
4. Tbl4Departments (Eg., Department of Psychology; Department of Research etc.)
5. Tbl5Sections (Eg., Transport Section, Communication Section, Housing Section etc.)
6. Tbl6EmployeeContactInfo (Eg., Title, FirstName, MiddleName, FamilyName, Position, Tel. Ext., Mobile1, Mobile2, Email1, Email2)

A. My first question is: Is this a 'Normal' database design. Or do I need to create an additional table each for Employees and contact information? If so, should I have separate tables again for [Mobile numbers] and [Email addresses] as many employees can have more than 1 mobile or email?

B. Although Tbl5Sections is linked with Tbl4Departments, all Departments may not have any Sections at all, and therefore, the Tbl5Sections may not have data in it; however, the Tbl5Sections is linked with Tbl6EmployeeContactInfo. Here is the main issue: when I run a query based on the ab
ove tables, the departments that have no sections under them, are not being retrieved.

C. Please advise how to design a multi-form interface for data input form based on all the above tables.

Please advise if I need to create a JunctionTable (many-to-many relation table) as a link table between the 2 Tables: Tbl4Departments and Tbl5Sections, in order to solve the above issue, so that my query output or report output will show all related data of an employee in a Department whether or not the Department concerned has a Section under it or not.

I hope I have presented the scenario clearly.

Thank you in anticipation of your valuable advice, which I highly appreciate.

Kind regards.
Sagar

[CJ_London]

A. Normal is what normal is - the database is designed to suit the data and processing requirements - if your employees can have more than one telephone number then these would normally be held in a separate table, but if the numbers are 'landline' and 'mobile' and only one of each then these could be in separate fields in the employee table - all depends on what you are going to do with the data (autocalls, call logging etc)

B - think about what you have written there - if employees are linked to sections and a department does not have sections, then a department without sections does not have employees. If you are saying they should have employees then you have not got your relationships defined properly

C - so many ways, so best to google to find a suitable solution to match what you want to do but in principle you would have a main form and subforms - the general rule is one table per form and link using the subform linkchild and linkmaster properties

[orange]

I agree with Ajax's comments. Further I would say that getting a clear description of the "business issue/problem/opportunity" - that is WHAT your database is intended to support - is key to setting up tables and relationships.
You can often reduce some descriptive text to the basic (business rules)
-An Employee works in 1 Department
-An Employee may have 0,1 or many XXX

where Employee, Department and XXX become Entities /tables and the 1,0,1 (Cardinality) the number of records in one table that relate to records in the related table.

Also, there is no reason to include the 1,2..5 in the table names.

Here is a tutorial to work from a problem description through an established process to identify entities, attributes and relationships leading to an efficient database design. ( a blueprint for a database).
The tutorial includes an answer.
Good luck.

[SagarHussain]

First, thanks you all for your quick and useful information and feedback. Sorry for the belated response.The Tutorial link is quite useful.

In our organization, some Departments do not have any Sections whatsoever, but have employees directly under these Departments, while the remaining departments have employees under sections which are linked to those departments.

Shall be grateful how to design my tables and establish a relationship in this particular scenario for the Departments - Sections - Employees and Departments - Employees.

Thanks in anticipation for your input and support.