HR Manpower Summary Report HELP!

[Confused Dawn]

I am attempting to duplicate a report that is NOT in Access - and put it into
Access.

I have a list of employees, their organizations, their pay grades and their
job titles.

What I would like is a report that groups by Orgs (departments) and then
gives me a total count of employees per grade / per title.

The end result should look like this:

Administration
Clerk 1.A.D 3
Clerk 2.A.D 2

Maintenance
Technician 3.M.R 17
Supervisor E.4.D 4

At the end I also need a summary of the total employees, broken down by
hourly and salary (anyone without an E on their pay grade - E indicates
salary)

I am stumped - even though I know this should be easy. I think I'm halfway
there - my current report shows the ORG grouped - but then lists each job
title (for each employee) and then lists the grades - duplicating information
I'd like summarized.

 

[OfficeDev18 via AccessMonster.com]

Hello, Dawn,

It seems to me that your report's Record Source is incorrect. Please tell us
the SQL (or structure, if it's a table) of the Record Source. I think you'll
find the problem staring at you.

Sam

 

[Confused Dawn]

The orignal table has all employee info in it. I've created a query that
filters out certain records which I do not want included on the report.
(Certain departments as well as crew information)

Is this what you are asking? I've checked into making adjustments in the
Query - such as using the "Group By" - but that was confusing me even more.

 

[OfficeDev18 via AccessMonster.com]

Hi, Dawn,

Ummm, I'm afraid you misunderstood. Open your report in design mode. If the
Property Sheet isn't showing, open it. Click on the upper left square of your
report (not in the menu bar, below it) to select the form level. The first
item in the property sheet is the form's Record Source. Please copy and paste
this information. That's what we need in order to help you.

Sam

The orignal table has all employee info in it. I've created a query that
filters out certain records which I do not want included on the report.
(Certain departments as well as crew information)

Is this what you are asking? I've checked into making adjustments in the
Query - such as using the "Group By" - but that was confusing me even more.

Hello, Dawn,

[quoted text clipped - 31 lines]

 

[Confused Dawn]

022806 Query
This is the name of the query that I am basing my report from.

Hi, Dawn,

Ummm, I'm afraid you misunderstood. Open your report in design mode. If the
Property Sheet isn't showing, open it. Click on the upper left square of your
report (not in the menu bar, below it) to select the form level. The first
item in the property sheet is the form's Record Source. Please copy and paste
this information. That's what we need in order to help you.

Sam

The orignal table has all employee info in it. I've created a query that
filters out certain records which I do not want included on the report.
(Certain departments as well as crew information)

Is this what you are asking? I've checked into making adjustments in the
Query - such as using the "Group By" - but that was confusing me even more.

Hello, Dawn,

[quoted text clipped - 31 lines]

title (for each employee) and then lists the grades - duplicating information
I'd like summarized.

 

[Confused Dawn]

At the risk of sounding extremely stupid, I have to admit that I have never
pushed to learn anything about SQL - it seemed so complicated!

Here goes though:

SELECT [022806].ID, [022806].ORG, [022806].COST, [022806].TITLE,
[022806].NAME, [022806].[EMP #], [022806].SHIFT, [022806].CREW,
[022806].HIRE_DATE, [022806].SSN, [022806].ADDRESS, [022806].CITY,
[022806].COUNTY, [022806].STATE, [022806].ZIP, [022806].PHONE,
[022806].GRADE, [022806].Field17, [022806].RATE, [022806].DOB, [022806].SUPV
FROM 022806
WHERE ((([022806].ORG) Like "*gs*") AND (([022806].CREW) Not Like "*term*"));

Hi, Dawn,

Now open the query 022806 Query in SQL mode and copy and paste the entire SQL
here. This is what we need. (If there are subqueries, we may need to drill
down further, but we have to start here.)

Sam

022806 Query
This is the name of the query that I am basing my report from.

Hi, Dawn,

[quoted text clipped - 18 lines]

title (for each employee) and then lists the grades - duplicating information
I'd like summarized.

 

[OfficeDev18 via AccessMonster.com]

Hi, Dawn,

Now open the query 022806 Query in SQL mode and copy and paste the entire SQL
here. This is what we need. (If there are subqueries, we may need to drill
down further, but we have to start here.)

Sam

022806 Query
This is the name of the query that I am basing my report from.

Hi, Dawn,

[quoted text clipped - 18 lines]

 

[OfficeDev18 via AccessMonster.com]

Hi, Dawn,

I also pushed off learning SQL until far too late, when I was already working
as an Access programmer and found myself feeling foolish because of my lack
of basic knowledge of SQL. (Shrug) So if you sound stupid, you have company -
probably lots of it!

Getting down to business. As I suspected, you have more fields in the query
than you're reporting on. Going back to your original post, you're looking
for

Administration
Clerk 1.A.D 3
Clerk 2.A.D 2

Maintenance
Technician 3.M.R 17
Supervisor E.4.D 4

This requires reporting on ... let's see ... Department, title, type, grade,
and quantity. Five fields, and the last one, the quantity, should be part of
the query, not part of the underlying recordset. Therefore, the first step in
your "surgery" is to cut out all fields in the query except the four
mentioned above: Department, title, type, and grade. Even the CREW field,
which is not reported on, should be in the query but uncheck the "display"
checkmark. After you've done the 'surgery,' re-try your report. It may still
need work, but one step at a time.

For 'quantity,' use

Count(NameOfGradeField) As CountOfGrade

in the query. Also, in the report, change the name of the textbox for this
field to CountOfGrade, and remove all extraneous textboxes.

Slowly, slowly,..........

Sam

At the risk of sounding extremely stupid, I have to admit that I have never
pushed to learn anything about SQL - it seemed so complicated!

Here goes though:

SELECT [022806].ID, [022806].ORG, [022806].COST, [022806].TITLE,
[022806].NAME, [022806].[EMP #], [022806].SHIFT, [022806].CREW,
[022806].HIRE_DATE, [022806].SSN, [022806].ADDRESS, [022806].CITY,
[022806].COUNTY, [022806].STATE, [022806].ZIP, [022806].PHONE,
[022806].GRADE, [022806].Field17, [022806].RATE, [022806].DOB, [022806].SUPV
FROM 022806
WHERE ((([022806].ORG) Like "*gs*") AND (([022806].CREW) Not Like "*term*"));

Hi, Dawn,

[quoted text clipped - 12 lines]

 

[OfficeDev18 via AccessMonster.com]

Hi, Dawn,

Uh-Oh, problemo. I forgot something; now you've changed the character of the
query, and you need to do more steps.

After you've done the 'surgery', click on the capital Greek Sigma in the
query design toolbar to make it a totals query. This will make the words
"Group By" appear in all the remaining fields. You can accept this for the
displayed fields, but the CREW field, which is not displayed, has to be
changed from "Group By" to "Where." Don't forget to make this change. Now
when you add the Count(), which is another choice in the aggregate functions
combobox, you can change it right there and the field will show up
automatically as CountOfGrade.

Sorry for the omission,

Sam

Hi, Dawn,

I also pushed off learning SQL until far too late, when I was already working
as an Access programmer and found myself feeling foolish because of my lack
of basic knowledge of SQL. (Shrug) So if you sound stupid, you have company -
probably lots of it!

Getting down to business. As I suspected, you have more fields in the query
than you're reporting on. Going back to your original post, you're looking
for

Administration
Clerk 1.A.D 3
Clerk 2.A.D 2

Maintenance
Technician 3.M.R 17
Supervisor E.4.D 4

This requires reporting on ... let's see ... Department, title, type, grade,
and quantity. Five fields, and the last one, the quantity, should be part of
the query, not part of the underlying recordset. Therefore, the first step in
your "surgery" is to cut out all fields in the query except the four
mentioned above: Department, title, type, and grade. Even the CREW field,
which is not reported on, should be in the query but uncheck the "display"
checkmark. After you've done the 'surgery,' re-try your report. It may still
need work, but one step at a time.

For 'quantity,' use

Count(NameOfGradeField) As CountOfGrade

in the query. Also, in the report, change the name of the textbox for this
field to CountOfGrade, and remove all extraneous textboxes.

Slowly, slowly,..........

Sam

At the risk of sounding extremely stupid, I have to admit that I have never
pushed to learn anything about SQL - it seemed so complicated!

[quoted text clipped - 14 lines]

 

[Confused Dawn]

Sam,

Thanks for all of your help. I think I’m still confusing some things.

Let me take this step by step:

Step 1. Cut out all fields in the query except the four mentioned above:
Department, title, type, and grade. Even the CREW field, which is not
reported on, should be in the query but uncheck the "display" checkmark.

**This is done – however the fields are ORG, TITLE, GRADE, CREW. Not sure
where the “type†reference came in.

Step 2. For 'quantity,' use

Count(NameOfGradeField) As CountOfGrade

in the query. Also, in the report, change the name of the textbox for this
field to CountOfGrade, and remove all extraneous textboxes.

**I think this is what I’m getting hung up on. Do I type “Quantity†in to
the Field Name section of the query? And where do I use the
Count(NameOfGradeField) As CountOfGrade
Is this under Criteria on the Query? And is it the entire line – or only a
portion of it?.

Currently the SQL view looks like this:
SELECT [022806].ORG, [022806].TITLE, [022806].GRADE
FROM 022806
WHERE ((([022806].CREW) Not Like "*term*"))
GROUP BY [022806].ORG, [022806].TITLE, [022806].GRADE
HAVING ((([022806].ORG) Like "*gs*"));

Step 3. Now when you add the Count(), which is another choice in the
aggregate functions combobox, you can change it right there and the field
will show up automatically as CountOfGrade.

**I’m confused as to where I’m adding the Count() and what combobox has the
aggregate functions.

I understand the text box reference in the report. And I think I’ve done
this correctly.

I’m sorry to be such a pain.