INSERT INTO, UPDATE, ADD

[Tina]

I am really struggling with my database class. I'm taking it online which
was a HUGE mistake & just don't understand the lessons. I need some more
help with an assignment.

1) I need to insert 2 rows into a table I created. I don't know if there's
a way to combine them, so I did 2 separate ones -- the 1st one added, but the
2nd didn't. Does anyone see something that I'm not seeing???

INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (12000,56,30.35,5,0);

INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (12000,77,10.25,3,5); THIS WON’T ADD

2) I had to select Customers with no orders. The following worked:

SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE ((Orders.CustomerID) Is Null);

Next, I need to delete these particular customers. I tried the following &
got "could not delete from specified tables":

DELETE Customers.*
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE ((Orders.CustomerID) Is Null);

3) I created a table called BigOrders and need to insert rows from Orders &
OrderDetails where the Totals are > $10,000. (Totals is UnitPrice *
Quantity). I don't know how to write a statement to insert rows from 2
separate tables AND with this Totals over >$10,000.

4) I need to add a Column called "Allocated" (sml integer) and set all
values to zero. I did the 2 below statements separately - they worked, but
should I be able to do this in 1 combined statement??

ALTER TABLE Products
ADD Allocated SMALLINT;

UPDATE Products SET Allocated = 0;

5) And lastly, the final question that makes me want to drop this class is
updating all area codes from 206 to 209 where state = Washington. Area code
is part of the whole phone #, ex. (206) 555-1234. I know this has to do with
MID, LEN, etc.. but I just can't seem to get this down.

Any help is appreciated. I have spent so much time on this & am not getting
anywhere.

Thanks in advance.

 

[Smartin]

Hi Tina,

I am really struggling with my database class. I'm taking it online which
was a HUGE mistake & just don't understand the lessons. I need some more
help with an assignment.

1) I need to insert 2 rows into a table I created. I don't know if there's
a way to combine them, so I did 2 separate ones -- the 1st one added, but the
2nd didn't. Does anyone see something that I'm not seeing???

INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (12000,56,30.35,5,0);

INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (12000,77,10.25,3,5); THIS WON’T ADD

I don't see any glaring reason why the second statement won't work. It
would be helpful (to yourself as well as the rest of us) if you included
information about any error message you get. Is there possibly a
constraint on the Discount field that limits the allowed values?

2) I had to select Customers with no orders. The following worked:

SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE ((Orders.CustomerID) Is Null);

Next, I need to delete these particular customers. I tried the following &
got "could not delete from specified tables":

DELETE Customers.*
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE ((Orders.CustomerID) Is Null);

Strange... the first hit on "could not delete from specified tables" in
groups.google.com is someone with your nym asking the same question
almost exactly over two years ago. http://tinyurl.com/ycgpqg

No matter, the solution is the same. You need to reduce the deletion to
a single table. Place the Join in a subquery. This might get you going:

....
WHERE Customers.CustomerID IN
(SELECT Customers.CustomerID FROM Customers LEFT JOIN Orders ON
Customers.CustomerID = Orders.CustomerID
WHERE ((Orders.CustomerID) Is Null));

3) I created a table called BigOrders and need to insert rows from Orders &
OrderDetails where the Totals are > $10,000. (Totals is UnitPrice *
Quantity). I don't know how to write a statement to insert rows from 2
separate tables AND with this Totals over >$10,000.

I doubt you need to insert from two tables (not that it matters), but
nonetheless, figure out how to select the big orders first. These are
based on the sum of (unit prc * qt) in order details grouped by orderID.
Hint: create a calculated field. Make sure it's in the HAVING clause.

4) I need to add a Column called "Allocated" (sml integer) and set all
values to zero. I did the 2 below statements separately - they worked, but
should I be able to do this in 1 combined statement??

ALTER TABLE Products
ADD Allocated SMALLINT;

UPDATE Products SET Allocated = 0;

This is Access right? I don't see another way to do the requirement.
Good Job (^:

5) And lastly, the final question that makes me want to drop this class is
updating all area codes from 206 to 209 where state = Washington. Area code
is part of the whole phone #, ex. (206) 555-1234. I know this has to do with
MID, LEN, etc.. but I just can't seem to get this down.

Oh eww... this isn't about SQL at all. Are you expected to know some VB
here?

The opening paren can be found as =INSTR(1, TelStr, "(")

So the Area code part is =MID(TelStr, OpenP + 1, 3)

You could wrap that all in a Function.

Any help is appreciated. I have spent so much time on this & am not getting
anywhere.

Thanks in advance.

OK HTH

 

[John Vinson]

I am really struggling with my database class. I'm taking it online which
was a HUGE mistake & just don't understand the lessons. I need some more
help with an assignment.

1) I need to insert 2 rows into a table I created. I don't know if there's
a way to combine them, so I did 2 separate ones -- the 1st one added, but the
2nd didn't. Does anyone see something that I'm not seeing???

INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (12000,56,30.35,5,0);

INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (12000,77,10.25,3,5); THIS WON’T ADD

Did you try to incorporate both INSERT statements into the *same*
stored query? If so, it won't work: unlike SQL/Server, Oracle, or some
other languages, each stored Query can contain only one SQL statement.

2) I had to select Customers with no orders. The following worked:

SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE ((Orders.CustomerID) Is Null);

Next, I need to delete these particular customers. I tried the following &
got "could not delete from specified tables":

DELETE Customers.*
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE ((Orders.CustomerID) Is Null);

Is CustomerID the Primary Key of Customers? Is there a Relationship,
referential integrity enforced, between Customers and Orders?

3) I created a table called BigOrders and need to insert rows from Orders &
OrderDetails where the Totals are > $10,000. (Totals is UnitPrice *
Quantity). I don't know how to write a statement to insert rows from 2
separate tables AND with this Totals over >$10,000.

Do this in two steps. Create a Totals query Grouping By OrderID, and
Summing amount. Put a criterion on the sum field as stated.

Now change this query into an Append query. Append the desired fields.

4) I need to add a Column called "Allocated" (sml integer) and set all
values to zero. I did the 2 below statements separately - they worked, but
should I be able to do this in 1 combined statement??

ALTER TABLE Products
ADD Allocated SMALLINT;

UPDATE Products SET Allocated = 0;

No, you shouldn't. You did it right.

5) And lastly, the final question that makes me want to drop this class is
updating all area codes from 206 to 209 where state = Washington. Area code
is part of the whole phone #, ex. (206) 555-1234. I know this has to do with
MID, LEN, etc.. but I just can't seem to get this down.

The Mid() function is all you need. It takes three arguments: a
string, a starting position within that string, and a length of a
substring. The string in this case is your phone number (I don't know
the name of that field in your table). You need to figure out the
starting position (it depends on whether you store the formatting
characters like ( in your Input Mask or not); the length is 3.

John W. Vinson[MVP]

 

[J. Goddard]

Hi -

What is the primary key of the [order details] table? It should be
OrderID + ProductID.

so we don't do your assignment for you, here are some hints:

For #2,

Check out the IN operator of a where clause (In_operator in the help
file). Your select statement worked, so you are almost there.

For #3

Look into what "append" queries are. I don't know what will go into
BigOrders, if you want to find orders where the total of ALL the detail
lines is > 10,000, you will need a Summation select query first, and
once you have it working, you can make it into an Append query (easily)

For #4

From looking at the help file, I think you have it right; SQL ALTER
TABLE does not have provision for a default value.

For #5

Assuming all the phone numbers are the same format, i.e. with the area code.

The MID *function* RETURNS specified parts of strings, and can be used
in WHERE clauses (big hint!)

To use an update SQL, you will replace the current phone number with 2
concatenated strings, the second of which uses a MID function.

Yout SQL statement might look something like this:

UPDATE YourTable SET Phone = "????" & MID(Phone,??????) where MID(Phone,
????) = ??? AND [State] = ?????

You have to fill in the ???? marks.. (I guess my teaching experience is
showing through a bit)

I encourage you to use the MS Access help files - they give good
explanations, and often examples. To see some topics you need to be in
a code module.

Hang in there with your course.


John

I am really struggling with my database class. I'm taking it online which
was a HUGE mistake & just don't understand the lessons. I need some more
help with an assignment.

1) I need to insert 2 rows into a table I created. I don't know if there's
a way to combine them, so I did 2 separate ones -- the 1st one added, but the
2nd didn't. Does anyone see something that I'm not seeing???

INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (12000,56,30.35,5,0);

INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (12000,77,10.25,3,5); THIS WON’T ADD

2) I had to select Customers with no orders. The following worked:

SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE ((Orders.CustomerID) Is Null);

Next, I need to delete these particular customers. I tried the following &
got "could not delete from specified tables":

DELETE Customers.*
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE ((Orders.CustomerID) Is Null);

3) I created a table called BigOrders and need to insert rows from Orders &
OrderDetails where the Totals are > $10,000. (Totals is UnitPrice *
Quantity). I don't know how to write a statement to insert rows from 2
separate tables AND with this Totals over >$10,000.

4) I need to add a Column called "Allocated" (sml integer) and set all
values to zero. I did the 2 below statements separately - they worked, but
should I be able to do this in 1 combined statement??

ALTER TABLE Products
ADD Allocated SMALLINT;

UPDATE Products SET Allocated = 0;

5) And lastly, the final question that makes me want to drop this class is
updating all area codes from 206 to 209 where state = Washington. Area code
is part of the whole phone #, ex. (206) 555-1234. I know this has to do with
MID, LEN, etc.. but I just can't seem to get this down.

Any help is appreciated. I have spent so much time on this & am not getting
anywhere.

Thanks in advance.

 

[Tina]

Thanks everyone for the responses. Maybe I won't drop the class after
all..... Smartin, John, & John - I've answered your questions below...
Thanks for the ray of hope...

1) I need to insert 2 rows into a table I created. I don't know if there's
a way to combine them, so I did 2 separate ones -- the 1st one added, but the
2nd didn't. Does anyone see something that I'm not seeing???

INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (12000,56,30.35,5,0);

INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (12000,77,10.25,3,5); THIS WON’T ADD

This is my error for the above when I try 1 insert at a time - "Msoft access
can’t append all the records in the append query… set 0 fields to Null due to
a type conversion failure, 0 records due to key violation, 0 due to
violations, 1 due to validation rule…."

I did try them together and separately. I’ve also tried totally retyping
the 2nd statement & still no luck. Its' very strange

The primary key of the [order details] table is OrderID + ProductID.

2) I had to select Customers with no orders. The following worked:

SELECT Customers.CustomerID, Customers.CompanyName
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE ((Orders.CustomerID) Is Null);

Next, I need to delete these particular customers. I tried the following &
got "could not delete from specified tables":

DELETE Customers.*
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE ((Orders.CustomerID) Is Null);

Smartin - I saw your note about a posting from another Tina - VERY similar
but not me - this is all new to me..

CustomerID is the primary key. I went this route to pull the fields & then
changed the SELECT to DELETE to remove it -- it worked.

SELECT CustomerID,CompanyName
FROM Customers
WHERE CustomerID NOT IN
(SELECT DISTINCT CustomerID
FROM Orders
WHERE CustomerID IS NOT NULL);

3) I created a table called BigOrders and need to insert rows from Orders &
OrderDetails where the Totals are > $10,000. (Totals is UnitPrice *
Quantity). I don't know how to write a statement to insert rows from 2
separate tables AND with this Totals over >$10,000.

The instructions from my prof say 2 tables, so I figure I should include
both. I’ll try again with HAVING – I was trying WHERE

I'll try the append query

4) I need to add a Column called "Allocated" (sml integer) and set all
values to zero. I did the 2 below statements separately - they worked, but
should I be able to do this in 1 combined statement??

ALTER TABLE Products
ADD Allocated SMALLINT;

UPDATE Products SET Allocated = 0;

Thank you! Glad I was doing this one right!

5) And lastly, the final question that makes me want to drop this class is
updating all area codes from 206 to 209 where state = Washington. Area code
is part of the whole phone #, ex. (206) 555-1234. I know this has to do with
MID, LEN, etc.. but I just can't seem to get this down.

Technically, we’re not supposed to need to know VB, but who knows – sure
seems like. I did this for the area code before I read your post - SELECT
HomePhone, Mid([HomePhone],2,3) AS Area FROM Employees; - I'll look at your
suggestions for updating, but not tonight --- my brain shut off about an hour
ago...

 

[John Vinson]

Thanks everyone for the responses. Maybe I won't drop the class after
all..... Smartin, John, & John - I've answered your questions below...
Thanks for the ray of hope...

1) I need to insert 2 rows into a table I created. I don't know if there's
a way to combine them, so I did 2 separate ones -- the 1st one added, but the
2nd didn't. Does anyone see something that I'm not seeing???

INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (12000,56,30.35,5,0);

INSERT INTO [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (12000,77,10.25,3,5); THIS WON’T ADD

This is my error for the above when I try 1 insert at a time - "Msoft access
can’t append all the records in the append query… set 0 fields to Null due to
a type conversion failure, 0 records due to key violation, 0 due to
violations, 1 due to validation rule…."

Well, maybe the table or a field in the table has a validation rule
that you're violating! Open the Table in design view; check each
field. Is there anything in the field's Validation Rule property,
especially anything that this record would violate?
Try:

DELETE * FROM Customers
WHERE NOT EXISTS
(SELECT CustomerID FROM Orders WHERE Orders.CustomerID =
Customers.CustomerID);

though I don't know why the join wouldn't work.

Smartin - I saw your note about a posting from another Tina - VERY similar
but not me - this is all new to me..

CustomerID is the primary key. I went this route to pull the fields & then
changed the SELECT to DELETE to remove it -- it worked.

SELECT CustomerID,CompanyName
FROM Customers
WHERE CustomerID NOT IN
(SELECT DISTINCT CustomerID
FROM Orders
WHERE CustomerID IS NOT NULL);

That's another way to do it.

3) I created a table called BigOrders and need to insert rows from Orders &
OrderDetails where the Totals are > $10,000. (Totals is UnitPrice *
Quantity). I don't know how to write a statement to insert rows from 2
separate tables AND with this Totals over >$10,000.

The instructions from my prof say 2 tables, so I figure I should include
both. I’ll try again with HAVING – I was trying WHERE

I'll try the append query

4) I need to add a Column called "Allocated" (sml integer) and set all
values to zero. I did the 2 below statements separately - they worked, but
should I be able to do this in 1 combined statement??

ALTER TABLE Products
ADD Allocated SMALLINT;

UPDATE Products SET Allocated = 0;

Thank you! Glad I was doing this one right!

5) And lastly, the final question that makes me want to drop this class is
updating all area codes from 206 to 209 where state = Washington. Area code
is part of the whole phone #, ex. (206) 555-1234. I know this has to do with
MID, LEN, etc.. but I just can't seem to get this down.

Technically, we’re not supposed to need to know VB, but who knows – sure
seems like. I did this for the area code before I read your post - SELECT
HomePhone, Mid([HomePhone],2,3) AS Area FROM Employees; - I'll look at your
suggestions for updating, but not tonight --- my brain shut off about an hour
ago...

Good luck!

John W. Vinson[MVP]