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
5  1360  MMcCarthy 14,534
Recognized Expert Moderator MVP
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
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 14,534
Recognized Expert Moderator MVP
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
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 14,534
Recognized Expert Moderator MVP
Glad you got it to work out.
Mary
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Eric |
last post by:
Hello,
I'm sure your all tired of these "alignment" question, but please
endure mine.
Question. Simple put: What makes a table sit beside another table, as
oppose to going down below it.
...
|
by: DFS |
last post by:
I've written several survey systems in which the majority of the questions
have the same or similar responses (Yes/No, True/False, scale of 1 - 5,
etc).
But this latest survey system I'm working...
|
by: teddysnips |
last post by:
This is a rather abstract question about data design, but I ask it here
because a) the database is SQL Server, and b) you're such a learned
bunch!
Let's assume the classic relation of Customers...
|
by: Steve Jorgensen |
last post by:
In a recent thread, RKC (correctly, I believe), took issue with my use of
multiple parameters in a Property Let procedure to pass dimensional
arguments on the basis that, although it works, it's...
|
by: Ekqvist Marko |
last post by:
Hi,
I have one Access database table including questions and answers. Now
I need to give answer id automatically to questionID column. But I
don't know how it is best (fastest) to do?
table...
|
by: Jack Schitt |
last post by:
I thought I was starting to get a handle on Access, until I tried
doing something useful...now I'm stuck. I have a DB with two tables -
to keep it simple I'll say that one is an Employee File...
|
by: MP |
last post by:
Greets,
context: vb6/ado/.mdb/jet 4.0 (no access)/sql
beginning learner, first database, planning stages
(I think the underlying question here is whether to normalize or not to
normalize this...
|
by: Gary Peek |
last post by:
Can anyone tell us the browsers/versions that exhibit errors
when tables are nested too deeply? And how many levels of
nesting produces errors?
(not a tables vs CSS question)
|
by: smadden |
last post by:
I have a simple Access database with 4 tables so far. Here is my
question:
Talble 2 lists "vulns" and there descriptions. Each has its own
primary key and relates to table 1.
Table 3 lists...
|
by: nyathancha |
last post by:
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
