?
Solved

Append qry runs but does not add the dat

Posted on 2011-05-02
8
Medium Priority
?
312 Views
Last Modified: 2012-05-11
Hi
The attached append qry runs with warning but does not update the products table.
i can manually add the same data directly into the table without problem.
i have tried including the autonumber primary key field(in the qry) and not including it .
Thanks for looking at this.
Pete

INSERT INTO tblPrices ( tblPricesProductsID, tblPricesSupplierID, tblPricesItemRetailPrice, tblPricesItemDeliveryCost )
SELECT tblPrices.tblPricesProductsID, tblPrices.tblPricesSupplierID, tblPrices.tblPricesItemRetailPrice, tblPrices.tblPricesItemDeliveryCost
FROM tblPrices
WHERE (((tblPrices.tblPricesProductsID)=1) AND ((tblPrices.tblPricesSupplierID)=70) AND ((tblPrices.tblPricesItemRetailPrice)=110) AND ((tblPrices.tblPricesItemDeliveryCost)=10));

Open in new window

0
Comment
Question by:petePrinter
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 5

Expert Comment

by:computerdoctorservice
ID: 35504035
What warning?
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 35504044
You are selecting from and appending to the same table - was that intended?

If, so, does the select part on it's own actually return data?

Kelvin
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35504050
If you run just the select part of the query do you see the records you are expecting to see?

If there are none then that suggests your selection criteria are not working the way you think they are.


SELECT tblPrices.tblPricesProductsID, tblPrices.tblPricesSupplierID, tblPrices.tblPricesItemRetailPrice, tblPrices.tblPricesItemDeliveryCost
FROM tblPrices
WHERE (((tblPrices.tblPricesProductsID)=1) AND ((tblPrices.tblPricesSupplierID)=70) AND ((tblPrices.tblPricesItemRetailPrice)=110) AND ((tblPrices.tblPricesItemDeliveryCost)=10));
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:computerdoctorservice
ID: 35504054
Looking again, this query does not add anything to the products table.  It is trying to insert data into itself which would result in a table that grows every time you run the query (assuming there is data that matches the criterion, but does nothing if not.
0
 

Author Comment

by:petePrinter
ID: 35504123
Hi
You can guess this is a bit new to me.
The data to be appended is new and does not exist in the table.
i am seeking to add a new row into the prices table.
i used the query wizard to build a select qry.
i the changed it to a append qry and picked the same table.
the actual number in this test qry are just test data that i know is not in he table.
Does this help?
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 2000 total points
ID: 35504135
You cannot select data from a table if it does not exist, what you need is

INSERT INTO tblPrices ( tblPricesProductsID, tblPricesSupplierID, tblPricesItemRetailPrice, tblPricesItemDeliveryCost )
VALUES (1,70,110,10)

Kelvin
0
 
LVL 5

Expert Comment

by:computerdoctorservice
ID: 35504142
You know Kelvin ... just have the points.
0
 

Author Closing Comment

by:petePrinter
ID: 35504185
Thank you all for your prompt and helpful replies
i was able to follow the logic and comments
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

569 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