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
Solved

Update table with inner select (different table)

Posted on 2013-01-19
4
234 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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

829 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