Solved

sql code in access 2007

Posted on 2010-11-20
9
395 Views
Last Modified: 2013-11-27
Dear experts,
i need help with some sql code in access 2007

this is code that i have created and ran multiple queries


CREATE TABLE Employee
(
EmployeeID int PRIMARY KEY,
Last_Name varchar(255),
First_Name varchar(255),
Address varchar(255),
City varchar(255),
State varchar(255),
Telephone_Area_Code int,
Telephone_Number int,
EE0_1_Classification varchar(255),
Hire_Date int,
Salary int,
Gender varchar(255),
Age int
)


CREATE TABLE Job_Title
(
Job_Id int FORIGEN KEY,
EE0_Classification varchar(255),
Job_Title varchar(255),
Job_Description varchar(255),
Exempt varchar(255)
)

*PLease not that the job_ID PRIMARY KEY is set up as 1-9
as you can see in the database. at the moment i have to change that job_ID primary key and make
it relate to the employee table.there should be a primary key in thejob_title table and an forgien key
in the employee table can you tell me how?
i have iused the insert syntax to instert records into the tables

I have to join two tables and use BETWEEN to restrict data .use salary to restrict?
join to tables and use BETWEEN to restrict data between to restrict data. use hire date?
join to tables and use LIKE to restrict data . telephone are codes to restrict?
join to tables and use LIKE to restrict data. age to restrict?

calculate the average salary for all employees?
Calculate the maximum salaries for exempt and non exempt employees?
calculate the mximum salaries for all employees?
Calculate the minimum salaries for exempt and non exempt employees?
calculate the minimum salaries for all employees?
increase all employess salaries with any EE0 classification by 10 %?
increase all employees salaries by 5%


THE ABOVE QUESTIONS I HAVE TO JOIN THE TABLES  AND SELECT
DATA FROM BOTH TABLES.

 CAN YOU HELP ME ANSWER
THE ABOVE QUESTIONS





0
Comment
Question by:collegestudent2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 14

Expert Comment

by:leoahmad
ID: 34181545
sounds like some homework ??
0
 
LVL 75
ID: 34181589
We are not allowed to help you with homework on EE.  Also, keep in mind that it's one question per question post.

mx
0
 

Author Comment

by:collegestudent2010
ID: 34181639
no its not is is a sql code that i have been working on to get certified practice test for myself to learn how to do sql in access using sql
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 84
ID: 34182742
You have no Primary Key defined on Job_Title. You'd need to add a field to that table and set it as the PK:

CREATE TABLE Job_Title
(
Job_Title_ID int PRIMARY KEY
Job_Id int FORIGEN KEY,
EE0_Classification varchar(255),
Job_Title varchar(255),
Job_Description varchar(255),
Exempt varchar(255)
)

Your Job_ID Foreign Key is most likely not needed, unless this table is used to "feed" other tables.

If you need to relate the Employee table to the Job_Title table, you'd have to include a Foreign Key field in the Employee table, and store the value of Job_Title_ID in that field.

Once you do that, to Join the tables, review the use of the sql JOIN Syntax. For example, to return all records from the Employees table, along with the text value of the related Job_Title record:

SELECT *.Employee, Job_Title.Job_Title FROM Employee JOIN Job_Title ON Employee.Job_Title_ID = Job_Title.Job_Title_ID

As to your other questions:

Restricting data is done with WHERE clauses in your SELECT statements.

AVG, MAX and MIN are SQL functions that can be used. As this is a practice test, it would seem that you would be best served by looking up those functions and applying them to your database.

Increasing the value of a field is done via an UPDATE statement.

If you build your statements and post them back here, we can review them and evaluate.

0
 
LVL 30

Expert Comment

by:hnasr
ID: 34182744
collegestudent2010,

Attach a sample database, and explain your requirement for one issue.  That may help you in other issues, because I see almost identical requests.

Good luck!
0
 

Author Comment

by:collegestudent2010
ID: 34183625
here is my builded statements below:




SELECT avg(Salary) AS Average
FROM Employee;

UPDATE Employee SET Salary = Salary*1.05;

UPDATE Employee SET Salary = Salary*1.1
WHERE EE0_1_Classification="Operatives";



somehow i have to select data in both tables in the above statements ?
which means i have to join employee table and the job title table ?

i have no clue where to start for min ,max for the exempt employess and non exempt employees
0
 

Author Comment

by:collegestudent2010
ID: 34183727
As you can here is my sample date base. the above calculations problems i need help without cuz i cann figuure how to jon the tables togther and using the MAX and MIN Fuctions for non exempt and exempt employees selecting data from both tables
i dont have a Query to join the two tables togther can you help? Sample.accdb Sample.accdb
0
 
LVL 30

Accepted Solution

by:
hnasr earned 300 total points
ID: 34184551
To join the two tables you need a join field. Added Job_Title to Employee table.
A query Employee_Job_Title_q is created.
Check and see if more help is needed.
Sample-2.accdb
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access syntax 1 34
Access #Deleted data 20 43
get and set file atrributes 5 12
calculate running total 8 17
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question