Solved

Update table with inner select (different table)

Posted on 2013-01-19
4
232 Views
Last Modified: 2013-01-20
Hello,

I'm new to databases and trying to run the below query.  

When I run the query the following message is displayed
Undefined function SummaryTable.Total in expression

How to retrieve the value for SummaryTable.Total to use in the update.

My aim is to retrieve the relevant value for total from the SummaryTable (inner select) to use in the calculation for the update.  The text value for CarType, NumberDrivers and NumberClaims from the SummaryTable are together used as the primary key.

 The table profile data doesn't share a link with the summary table.  I want to be able to perform the calculation on all records ([ProfileData].CarValueO1 = [ProfileData].CarValue *  SummaryTable.Total)

UPDATE [ProfileData] SET [ProfileData].CarValueO1 = [ProfileData].CarValue *  SummaryTable.Total
(SELECT SummaryTable.Total
FROM SummaryTable
WHERE ((SummaryTable.CarType ="Sedan") AND (SummaryTable.NumberDrivers="NumberDrivers1") AND (SummaryTable.NumberClaims="NumberClaims1")));

Open in new window


SummaryTable structure:
CarType  Text
NumberDrivers  Text
NumberClaims Text
Total  Number

Thanks
0
Comment
Question by:AndyC1000
  • 2
4 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
Have you tried to use query designer? I don't see in SummaryTable feld with name Total.
Even with proper syntax you may get "Non updatable query" error if you join fieds, which are  not primary keys.
May be you can upload sample DB with these tables?
0
 
LVL 84
Comment Utility
If the SummaryTable doesn't share any common fields, then how do you know what data to use when calculating  the SummaryTotal?

For example, why did you choose "Sedan" and "NumberDrivers1" as the filter criteria for SummaryTable instead of something else?
0
 

Author Comment

by:AndyC1000
Comment Utility
Thanks for your reply.

I need to update the field [ProfileData].CarValueO1 with [ProfileData].CarValue * by the total field value for the profile fields, i.e. Sedan, NumberDrivers1, NumberClaims1.

I've attached an example db.
profiledb.accdb
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
Comment Utility
This qury seems to be workng:
UPDATE ProfileData, SummaryTable SET ProfileData.CarValueO1 = [Total]*[CarValue]
WHERE (((SummaryTable.CarType)="Sedan") AND ((SummaryTable.NumberDrivers)="NumberDrivers1") AND ((SummaryTable.NumberClaims)="NumberClaims1"));

Open in new window

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now