Create a query

[Carrie]

I am relatively new to Access. I can do simple queries and such but I am running into a problem.

I am trying to take this spreadsheet.

Staff Listing 10.2003.xls
	A	B	C	D	E	F	G	H	I	J
1	ActiveSupportServicesStaff(IncludesTemp)
2		CENTRAL	EAST	WEST	EDI	ATS	CRM	SP	DeptDirector	Overall
3	SR.Mgmt.	1	1	1	1	1	1	1	1	8
4	TeamManagers	3	5	2	4	2	3			19
5	FLS	27	37.5	25						89.5
6	HWFLS	13	10	3						26
7	EDIAnalysts				51					51
8	ATSAnalysts					20				20
9	Dispatch	13	12	5						30
10	Admin/Other	1	1	0		0		2		4
11	NCRCustomerLiaisons						3			3
12	TOTAL	58	66.5	36	56	23	7	3	1	250.5
Sheet1

It is kind of hard to see in the black but I am trying to recreate this Datasheet in Access.

I have the table in Access set up. My concern is with the information in black. I need to recreate this in Access. I don't know how I can do this. So far, I have come up with individual queries such as:

SELECT Sum([Support Services]!Div Like "C" And [Support Services]!Position Like "FLS Division Manager")*-1 AS [Senior Management], Sum([Support Services]![Div] Like "C" And [Support Services]![Position] Like "Team Manager")*-1 AS [Team Managers], Sum(([Support Services]![Div] Like "C") And ([Support Services]![Position] Like "FLS Analyst" Or [Support Services]![Position] Like "Team Lead"))*-1 AS [FLS Analysts], Sum([Support Services]![Div] Like "C" And [Support Services]![Position] Like "FLS HW Analyst")*-1 AS [FLS HW Analysts], Sum([Support Services]![Div] Like "C" And [Support Services]![Position] Like "Dispatcher")*-1 AS Dispatch, Sum([Support Services]![Div] Like "C" And [Support Services]![Position] Like "Admin")*-1 AS [Admin/Other], Sum([Support Services]![Div] Like "C")*-1 AS Total
FROM [Support Services];

This basically shows column C2:C12 in the XL Sheet above. I have done this with each division.

Is there a way to create this in Access so that it looks similar to XL?

Keep in mind that don't know SQL...Design View is my best friend.

Thanks for any help you can give me.

 

[clares]

Hi Carrie

I think I understand what it is you want to acheive!

I think one way to apprach this could be to set up three tables:

tblEmployee
tblDepartment (SR.Mgmt, Team Managers, etc)
tblDistrict (Central, East, West, etc)

tblDepartment and tblDistrict are linking tables, they each contain a unique ID, with description.

tblEmployee will contain fields appropriate to employee, plus the unique ID for each of the other two tables. They will have N:M relationships.

You can then do many queries, also using the find duplicates ect to find your totals etc.

I hope you understand what I am saying! Not sure if I have made my self clear.

Kindest Regards

 

[Carrie]

Ok...you are going to have to help me a little more please.

I created the Department and Division table with a key on each. I put columns in the Employee table that had the corresponding number keys that are in the other two tables.

For instance: Ken Stoltz: Employee ID:1 (primary key for employee table) Department ID: 1 (primary key for Department table. Is next to value Senior management) Division ID: 1 (primary key for Division table. Is next to value Central).

I related Dept ID and Div ID to the corresponding tables. You mentioned something about an N:M Relationship. I don't know what that is and I cannot find it in the help.

I am still stumped on how to create the above report.

Need more help please.

 

[clares]

Hi Carrie

If you want to you can email what you have done and I will take a look at it for you and then explain what I have done. Sorry its taken a while to get back to you, but I did keep checking for a response and presumed you had sorted it.

my email address is: pduk1@yahoo.com