RE: INSERT INTO, UPDATE, ADD
- From: Tina <Tina@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 17 Oct 2006 18:26:01 -0700
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'sThis is my error for the above when I try 1 insert at a time - "Msoft access
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
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... :-)
.
- Follow-Ups:
- Re: INSERT INTO, UPDATE, ADD
- From: John Vinson
- Re: INSERT INTO, UPDATE, ADD
- Prev by Date: Re: Form/Table help
- Next by Date: Re: Form design to add new record when subforms exist
- Previous by thread: Re: INSERT INTO, UPDATE, ADD
- Next by thread: Re: INSERT INTO, UPDATE, ADD
- Index(es):
Relevant Pages
|
|
