Solved

Update table with inner select (different table)

Posted on 2013-01-19
4
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38797847
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
ID: 38798029
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
ID: 38798151
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 40

Accepted Solution

by:
als315 earned 500 total points
ID: 38798242
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

733 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