CODEWITHSUNDEEP
ms-accessms-access-2010
MarkDownMark
MarkDownMark

Reputation: 45

Append query result Access 2010

Sorry if there is an easy solution to this. My Access skills are severely lacking...but I'm trying to learn. I have this solution complete in excel but I was looking for a more robust, long term solution and thought Access could help.

I have a Microsoft 2010 Access database to store soap making ingredients. My goal with this database is to allow my wife to enter her soap ingredients, recipes for a new soap and it will tell her how much each batch of soap cost and then further, how much each bar of soap cost. I have a table (Ingredients) where she can enter all of the ingredients she buys, the measurement of those items (pounds, ounces) and how much they cost. I want to automatically calculate those pounds and ounces into ounces to make it easier to calculate the cost. I have come up with several solutions that get me the answer BUT, I have been unable to find a way to store those results.

First, I created an After Update event that will enter the TOTAL OUNCES in a text field after she moves out of the ounces box. The problem with this is that the TOTAL OUNCES does not get stored any where and all table records show the last updated value.

I tried to create a query which works fine, but again, I have not been able to append those results to the Ingredients table. The latest iteration of my query looks like this. I have a field in the Ingredients table (OuncesConverted) that I want to store this information.

INSERT INTO Ingredient ( OuncesConverted )
 SELECT 16*[Pounds]+[Ounces] AS OuncesConv
 FROM Ingredient;

When I run this query, I get the following error.

Microsoft Access can't append all the records in the append query. Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 0 records to the table due to key violations, 0 records due to lock violations, and 9 records due to validation rule violations. Do you want to run the action query anyway? To ignore the error(s) and run the query, click Yes. For an explanation of the causes of the violations, click Help.

When I click yes, nothing happens.

Any help is greatly appreciated. I am probably overthinking this, what I do when I don't know a program.

Upvotes: 0

Views: 140

Answers (1)

Gustav
Gustav

Reputation: 56026

You shouldn't insert, neither update, just select:

SELECT 
    *, (16 * [Pounds] + [Ounces]) As OuncesConverted
FROM
    Ingredient

This will replace the field OuncesConverted which you can delete from your table. Then use this query as source for the form.

Upvotes: 2

Related Questions