Acess SQL statment Help please.

[Mr Frog]

I'm currently taking an SQL class and its all being done on MS Access. I've done well so far but have now hit a section where I am stuck.

The question on the lab was:

"List employee id and the names of all employees. display the names as first initial then last name followed by a period after the first initial. In acess, you will need to change the data to upper case. name the formatted column employee_name. sort rows by employee_id"

Below is what I have done this far and the bottom section is where I'm stuck. any help or recommendations are greatly welcomed.

STEP 1:
alter table l_employees add column employee_name text(25);

STEP 2:
select employee_id, mid(First_name,1,1) & '. ' & UCase(last_name) as employee_name
from l_employees;

STEP 3:
Save the query like a view as names_view

STEP 4:
insert into l_employees (employee_name)
select employee_id, employee_name
from names_view f, l_employees l
where l.employee_id = f.employee_id
order by employee_id;

Step 4 is the portion I can not get to work?

 

[MarkK]

Hi. Welcome to the forum.
I would not add the column and store the formatted data. It seems unlikely you would be taught to do this, and poor form if you are.
You almost always want to store data in its rawest form, and format it only on retrieval.
Consider ...

SELECT FName, MName, LName, 
  FName + " " & UCase(Left(MName, 1)) + ". " & LName As FullName
FROM YourTable

The field Fullname is a composite of the raw data fields.
Cheers,

 

[Mr Frog]

Yes, you are absolutely right, but I am following the lab as written and they want me to take the generated data from the "View" (saved access query) and insert it into the newly created table column. I don't why they want it done this way, it may be just to teach us basic command syntax. Would you have a recommendation on how to resolve the errors generated by step 4?

 

[MarkK]

What errors are generated by step 4?

 

[Mr Frog]

If I run either of the following query's (I get the error below):

insert into l_employees (employee_name)
select employee_id, employee_name
from names_view f, l_employees l
where l.employee_id = f.employee_id
order by employee_id;

or

insert into l_employees (employee_name)
select employee_id, employee_name
from full_name_view
order by employee_id;

But they are the same? each has 11 rows and match employee id for id?

 

[MSAccessRookie]

If I run either of the following query's (I get the error below):

insert into l_employees (employee_name)
select employee_id, employee_name
from names_view f, l_employees l
where l.employee_id = f.employee_id
order by employee_id;

or

insert into l_employees (employee_name)
select employee_id, employee_name
from full_name_view
order by employee_id;

But they are the same? each has 11 rows and match employee id for id?

I have highlighted what I believe is the problem above. As you can see, The INSERT section refers to one column (employee_name), while the SELECT section refers to two columns (employee_id, employee_name). Resolving this issue should make your problem go away.

 

[Mr Frog]

Thank you that did help. If its ok I have a couple of other questions:

Could someone tell me what I am doing wrong below. I am just told to solve for n given the equation below with values from a table between 0 and 99 accurate to two decimal places and my statement does not seem to function as I expected. Mainly because the iif seems to want a charter and not a variable.

select iif(Sum(n^3-67*n^2+5*n-718=0), null,Format (n, "##0.00")) as Solution
from numbers_0_to_99;

 

[Mr Frog]

The problem I am having is that Format (n, "##0.00") will not produce the value of n to the query view. If i put "text" in place of "Format (n, "##0.00")" it will produce "text" to the query view. So how do I get it to produce the value of n?

 

[MSAccessRookie]

Thank you that did help. If its ok I have a couple of other questions:

Could someone tell me what I am doing wrong below. I am just told to solve for n given the equation below with values from a table between 0 and 99 accurate to two decimal places and my statement does not seem to function as I expected. Mainly because the iif seems to want a charter and not a variable.

select iif(Sum(n^3-67*n^2+5*n-718=0), null,Format (n, "##0.00")) as Solution
from numbers_0_to_99;

It looks like there is a problem with the Sum() Statement (See Above). I believe the code that is marked in RED should be outside the parenthesis.

There might also be a problem with the PEMDAS priority of the operations in the statement (I learned that it was BODMAS for people from Britain). Adding additional parenthesis to better define the calculation order might be needed (see the link below for details).

http://www.purplemath.com/modules/orderops.htm

 

[ByteMyzer]

Also, instead of Format (n, "##0.00"), you might try Round(n, 2).