Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Update table with inner select (different table)

Posted on 2013-01-19
4
Medium Priority
?
238 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 85
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 2000 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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