Calculate value from two field in third field

Posted by terence6 on Stack Overflow See other posts from Stack Overflow or by terence6
Published on 2010-06-09T15:39:20Z Indexed on 2010/06/09 15:42 UTC
Read the original article Hit count: 175

Filed under:

ms-access

|

queries

I'm trying to create a query, that will calculate sum of products on invoice. I have 3 tables :

Product (with product's price)
Invoice (with invoice id)
Products on invoice (with invoice id, product id and number of particular products)

So in my query I take invoice_id (from invoice), price (from product),number of products sold and invoice_id (from products on invoice) and calculate their product in fourth column. I know I sohuld use 'Totals' but how to achieve that ?

Model:
alt text

© Stack Overflow or respective owner

Related posts about ms-access

SQLAuthority News – MS Access Database is the Way to Go – April 1st Humor

as seen on SQL Authority - Search for 'SQL Authority'
First of all, today is April 1- April Fool’s Day, so I have written this post for some light entertainment. My friend has just sent me an email about why a person should go for Access Database. For a short background, I used to be an MS Access user once (I will not call myself MS Access DBA), and… >>> More
LINKED TABLES BETWEEN MS ACCESS 2003 AND MS ACCESS 2007-WRITE PERMISSIONS DENIED

as seen on Super User - Search for 'Super User'
We are in the process of switching over to ACCESS 2007. We have numerous data tables in ACCESS 2003 files. In one case, the user has 2007 on his PC and opend the front end in 2007. No problems. When the the user is done, he clicks a button that executes a macro full of update queries. The macro… >>> More
Working with Compressed (zipped) folders in MS Access

as seen on Internet.com - Search for 'Internet.com'
Doug Steele presents a simple technique to add zip files from within your application. >>> More
Extending the InputBox function for MS Access Forms

as seen on Internet.com - Search for 'Internet.com'
While it's not possible to change the behavior of the InputBox function, this article illustrates how to create your own Access form that includes a text box with its InputMask property set to Password, and then use that form instead of the one generated by the VBA InputBox function. >>> More
Uses for Cartesian Products in MS Access

as seen on Internet.com - Search for 'Internet.com'
Less well known than inner and outer joins is the Cartesian product, which produces every possible combination of records between the two tables. Doug Steele offers four examples to demonstrate some legitimate uses for Cartesian products. >>> More

Related posts about queries

SQL 2005 indexed queries slower than unindexed queries

as seen on Stack Overflow - Search for 'Stack Overflow'
Adding a seemingly perfectly index is having an unexpectedly adverse affect on a query performance... -- [Data] has a predictable structure and a simple clustered index of the primary key: ALTER TABLE [dbo].[Data] ADD PRIMARY KEY CLUSTERED ( [ID] ) -- My query, joins on itself looking for a certain… >>> More
Parameterized Queries /Without/ using queries.

as seen on Stack Overflow - Search for 'Stack Overflow'
I've got a bit of a poor situation here. I'm stuck working with commerce server, which doesn't do a whole lot of sanitization/parameterization. I'm trying to build up my queries to prevent SQL Injection, however some things like the searches / where clause on the search object need to be built up… >>> More
How to avoid this PDO exception: Cannot execute queries while other unbuffered queries are active

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, I'd like to print a simple table in my page with 3 columns, building name, tags and architecture style. If I try to retrieve the list of building names and arch. styles there is no problem: SELECT buildings.name, arch_styles.style_name FROM buildings INNER JOIN buildings_arch_styles ON buildings… >>> More
When optimizing database queries, what exactly is the relationship between number of queries and siz

as seen on Stack Overflow - Search for 'Stack Overflow'
To optimize application speed, everyone always advises to minimize the number of queries an application makes to the database, consolidating them into fewer queries that retrieve more wherever possible. However, this also always comes with the caution that data transferred is still data transferred… >>> More
FirePHP not returning ajax request queries, yet returns normal page load queries

as seen on Stack Overflow - Search for 'Stack Overflow'
I am using firePHP(Zend profiler) on on FF 3.6.3(firephp addon installed) and it returns queries, when I do a normal page load. Ajax requests, however, are not returning the proper queries. I am using Prototype 1.6.0.2 for ajax requests. Is there something I am missing? Most of the write-ups seem… >>> More