[kevinm1019]

Hi... I was wondering if you could help me solve a few problems I am having with SQL 2000 and my queries? I am very confused because when I create the following INSERT statement and use the "Return All Rows" function, the results display duplicates??

Here is my code:

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Office/Clerical' , 'Accounting Clerk', 'Computes, classifies, records, and verifies numerical data for use in maintaining
accounting records.', 'Non-Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Officials & Managers', 'Assistant Manager', 'Supervises and coordinates activities of workers in department of food store.
Assists store manager in daily operations of store.', 'Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Sales Worker', 'Bagger', 'Places customer orders in bags. Performs carryout duties for customers.', 'Non-Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Sales Worker', 'Cashier', 'Operates cash register to itemize and total customer’s purchases in grocery
store.', 'Non-Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Computer Support Specialist', 'Technician', 'Installs, modifies, and makes minor repairs to personal computer hardware and
software systems, and provides technical assistance and training to system users.', 'Non-Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Officials & Managers', 'Director of Finance & Accounting', 'Plans and directs the finance and accounting activities for
 Kudler Fine Foods.', 'Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Craft Workers (Skilled)', 'Retail Assistant Bakery & Pastry', 'Obtains or prepares food items requested by customers 
in retail food store.', 'Non-Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Operatives (Semi-skilled)', 'Retail Assistant Butchers & Seafood Specialists', 'Obtains or prepares food items requested 
by customers in retail food store.', 'Non-Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Office/Clerical', 'Stocker', 'Stores, prices and restocks merchandise displays in store.', 'Non-Exempt')

I have tried re-creating a new database and copying the statements into the new queries... but this did not resolve the issue.

--------------------------------------------------------------------------

Secondly... I am creating JOIN and GROUP statements and would like the last name of the employee to appear in the 'LastName' column. Instead, a count of employees appears in the 'LastName' column. How do I change this? Here is my code:

[b]Query - Group_EEO1_Classification[/b]

select count (*) LastName, EEO1_Classification as EEO1_Classification from tbl_Employee e
group by e.EEO1_Classification
 
[b]Query - Group_Salary within EEO1_Classification[/b]

select count (*) LastName, Salary, EEO1_Classification as EEO1_Classification from tbl_employee 
group by Salary, EEO1_Classification

Any help you can provide will be greatly appreciated. Thank you in advance.

[Amadeus]

Your are selcting a count of all records and placing the results in the last name field...you need to separate them.

If counting last names, your query is:

SELECT COUNT(LastName) as LastName, LastName

or if you want a count separate from the name, then

SELECT COUNT(*) as Total, LastName

[kevinm1019]

Amadeus, on 21 Jul, 2006 - 04:10 AM, said:

Your are selcting a count of all records and placing the results in the last name field...you need to separate them.

If counting last names, your query is:

SELECT COUNT(LastName) as LastName, LastName

or if you want a count separate from the name, then

SELECT COUNT(*) as Total, LastName

Thank you for the feedback. To make sure I am understanding correctly, the second statement you displayed will actually show the last names of each person in the 'Last Name' column??

This post has been edited by kevinm1019: 21 July 2006 - 10:52 PM

[kevinm1019]

kevinm1019, on 21 Jul, 2006 - 09:43 PM, said:

Amadeus, on 21 Jul, 2006 - 04:10 AM, said:

Your are selcting a count of all records and placing the results in the last name field...you need to separate them.

If counting last names, your query is:

SELECT COUNT(LastName) as LastName, LastName

or if you want a count separate from the name, then

SELECT COUNT(*) as Total, LastName

Thank you for the feedback. To make sure I am understanding correctly, the second statement you displayed will actually show the last names of each person in the 'Last Name' column??

Ok thank you. I'll try it.

[kevinm1019]

I tried using the code example given to me, but I ended up getting these results:

LastName Salary Status
3 21000 Exempt
3 10530 Exempt
3 15000 Exempt
3 51000 Exempt
3 21500 Exempt
3 18500 Exempt
3 13500 Exempt
3 25500 Exempt
3 20500 Exempt

What I would like to display is the actual last name of the employee in the 'LastName' column.

[kevinm1019]

The other problem I am having is my results keep producing duplicate information. How do I get rid of that?

[Amadeus]

Can you please post the schema for your tables, and describe exactly what you wish to get out based on those schema?

[kevinm1019]

Ok... what I am doing is creating an employee database. Instead of counting the number of employees and displaying the total counts for each category in the 'LastName' column... I actually want to display the actual employee's last name in the 'LastName' column as it applies in the query statement. For example, if I want to know the names of the exempt vs. non-exempt employees, I don't want the query to count the number of exempt vs. non-exempt... I actually want the results to display the result in the following manner:

Last Name Status
Doe Exempt
Williams Non-Exempt

versus

Last Name Status
2 Exempt
3 Non-Exempt

[Amadeus]

Is the information in different tables? Unless I'm missing something, all you need is

SELECT LastName, Status FROM mytable

[kevinm1019]

No all the data is in the same table. I'll give it a try and let you know how it worked out. Thank you very much.