Help with query

froggy

Registered User.
Local time
Today, 14:46
Joined
Sep 15, 2006
Messages
13
Hi,
i have a database containing employees' information. Each employee will belong to one division, one department and up to 3 sections.
My tables are named div, dept, sect, sectII, sectIII.
In my report, i would like to see the employee contact numbers group according to the sections. As such, should one employee belong to 3 sections, his information will appear thrice in the different sections.
Eg.
Section/Role | Name | Mobile No
--------------------------
Sports
Player1 | Michael | 1234567
Player2 | Tammy | 2345678
--------------------------
Arts
Player1 | Michael | 1234567
Player3 | Nicholas | 3456789
---------------------------
Language
Player1 | Michael | 1234567
Player4 | Evelyn | 7654321
 
Looks like a design flaw to me. I suspect sect, sectII, sectIII should all be one table. Could you give us a bit more info on what the tables represent and how they are related?
 
Oh no.. design flaw.. okok.. here is my table structure:
1) Employee ID
2) Name
3) Division
4) Department
5) Section
6) SectionII
7) SectionIII
8) Office Num
9) Mobile Num
10) Role

Each employee will have to be in one division, one department and one section and playing one role. However, only some of them will belong to few sections( SectionII and SectionIII) while division, dept and role remains the same. What i have to return now is should the person belong in 2 or 3 sections, his particulars will appear twice/thrice under the respective sections. Is there a way for this should the design of the database remains the same?
 
I expect you would have a Table of section names and are just storing the SectionID in the Employee Table yes?

I think if you create a query with multiple Employee tables (1 for each section) you should be able to achieve what you want.
 
how easy it is depends on what tables you have

aside from lookup tables for div, dept, and section

your employee table is the key table.

if you can only ever have 1 div, and 1 dept, then you can store the foreign/lookup key in there. you then need a sectionmembers table including employeeid and sectionid.

now it is trivial to join the sections to the employees and the other lookup tables, and structure your report in the way you want it, simply by sorting on section first

----------------
so in summary, as neil says, you should only have one table to include ALL your sections, and you already have one table for your employees, but you need another table for section membership which will show you which employees are in which sections.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom