Question about a Table

[piko1981]

I'm using Access 2003 (Windows XP), and I'm trying to determine if I need to break a table down farther

There are two tables directly related, Employee, and Company. Employee has a SSN, lName, fName, MI, and companyNum. Every Employee has one companyNum they belong to, and one companyNum can have many employees so it's a one-to-many relationship.

The company table has the unique companyNum, as well as 10 columns. The remaining columns are total_authorized, total_assigned, HHSB_Authorized, HHSB_Assigned, Alpha_Authorized, Alpha_Assigned (Same for Bravo and Charlie...). Here are some examples of companyNums..(All companyNums that start with 1 are HHSB)........ 101-01, A202-04, B202-04, C202-04, C202-05, C202-06.

You could consider the designators A,B,C, as *Departments*, but if I put them each in their own table I run into several problems with queries because I don't know how they relate. If they each had their own table how would I set the relations? The plan is to have a form that displays Employee Info, and the companyNum they belong to can be selected from a combobox.

I am also going to need a *Strength Report* Which is why there's all the authorized and assigned fields for each companyNum.

There'll be another question coming, but I have to get past the hurdle first, thanks.
Piko

[MMcCarthy]

Piko

If the columns in the company table are calculating the number of employees in each of these 'Departments' then this won't work.

Create a third Department table with just a list of the departments and an autoID as follows

Department
DeptID (Primary key - AutoNumber)
DeptName

Now add a DeptID Foreign key and an Authorised/Assigned to the Empoyee table and the following query will give you the records you want.

TRANSFORM Count(SSN) As CountEmployees
SELECT companyNum, Authorised/Assigned, Count(SSN) As NumEmployees
FROM Employee INNER JOIN Department
ON Employee.DeptID = Department.DeptID
GROUP BY  companyNum, Authorised/Assigned
PIVOT Department.DeptName
 

Mary

[piko1981]

Close, very close. Another question though. Wouldn't it make sense to leave the Authorized/Assigned field in the company table? I see what this query does, and it helps a lot, but this way would require an employee being assigned to a companyNum for it to have an Authorized/Assigned value correct? I still need an Authorized value for compayNum(s) that don't have anyone assigned to them. Also, by Authorized/Assigned do you mean Authorized? It displays like authorized so I was just curious, thanks for the help,
Piko

[MMcCarthy]

Close, very close. Another question though. Wouldn't it make sense to leave the Authorized/Assigned field in the company table? I see what this query does, and it helps a lot, but this way would require an employee being assigned to a companyNum for it to have an Authorized/Assigned value correct? I still need an Authorized value for compayNum(s) that don't have anyone assigned to them. Also, by Authorized/Assigned do you mean Authorized? It displays like authorized so I was just curious, thanks for the help,
Piko

The Authorised/Assigned field came from your references to Authorised and Assigned. You should be able to switch it to the Company table without affecting the query.

Mary

[piko1981]

Thanks! The code didn't do exactly what I needed, but it led me in the right direction. Basicaly I have the company table with the companyNum in one column and authorized in the other.

I'm able to get the % of people assigned to each "Department" by doing a query on the first character of the companyNum. I then do a SUM of the authorized for that department, and then do a count of the records in the query gives me how many people are assigned :-D

Thanks again,

Piko

[MMcCarthy]

Glad you got it to work out.

Mary