Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

Update table with inner select (different table)

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
AndyC1000
Asked:
AndyC1000
  • 2
1 Solution
 
als315Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
AndyC1000Author Commented:
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
 
als315Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now