How to multiply two tables in Access

want to multiply values from two columns of a table.

Example:

Our database has a table named purchase with data in the following columns: id, name, price, quantity, and discount_id.

Let’s multiply the price by the quantity of the products to find out how much you paid for each item in your order.

Solution:

This query returns records with the name of the product and its total price:

Discussion:

Do you need to select the name of each record (in our case, name) and compute for it the result of multiplying one numeric column by another (price and quantity)? All you need to do is use the multiplication operator (*) between the two multiplicand columns (price * quantity) in a simple SELECT query. You can give this result an alias with the AS keyword; in our example, we gave the multiplication column an alias of total_price.

Note that you can also use data from two columns coming from different tables. We have another table in our database named discount that has columns named id and value; the latter represents the percent discount on the item with the given ID.

Look at the example below.

Solution:

Here’s the result:

As you can see, it’s quite easy to multiply values from different joined tables. In our example above, we multiplied the price of each product by its quantity from one table (purchase) and then multiplied this total price by the percent discount using the discount table.

Hmmm, that's the simplest description of JOINS I have come across.. But anyway.. Let me use your words..

Basically I have created a relation ship between these two tables Car data and Order data.

This RELATIONSHIP, is the field that is common to the two fields, in other words both the tables will be LINKED by this one Key field..
NOTE: If the relationship has been designed using the Database tools, the JOIN would automatically link the two tables when you bring them into a Query..

All of the cars have their own unique primary key which is connected to the order data, what i need is go into the car data table pull the price out and multiply it by the quantity in the order data and put the sum into the Total price.

Since they are LINKED to each other, when you join them, you will be able to PULL the price from the Car table.. Then all you have to do is Create a new field by Multiplying the Car.Price with the Customers.Quantity.. That's all you need to do..

Apparently I cannot post links so if you can help and need more information I will PM you the pictures.

Your post count needs to be a minimum of 10 Posts before adding Hyperlinks/Pics.. If you do want to upload pics, you can ZIP your files and UPLOAD them..

back to square one why would the values be in two different tables? that sounds like a normalisation issue to me.

This is quite an annoying problem that I have run into with my work on this database. What I want to do is have the sum of a customers purchase on one table, however the values needed to multiply are on two different tables. I will post some pictures to explain it a little better.But what I want to do is have the Order table multiply the total cost of the cars together.

just to clarify what I meant. I orginally thought you meant there were two tables with some purchases in each, but i now realise you mean the purchase details are in one tble, and the prices are in another. (is that right?)

I just can't see why you would have a structure like this - hence the comment that this seemed to be a normalisation issue.

/en/access2013/designing-your-own-database/content/

Calculated fields and totals rows let you perform calculations with the data in your tables. Calculated fields perform calculations using data within one record, while totals rows perform a calculation on an entire field of data.

Calculated fields

When you create a calculated field, you are adding a new field in which every row contains a calculation involving other numerical fields in that row. To do this, you must enter a mathematical expression, which is made up of field names in your table and mathematical symbols. You don't need to know too much about math or expression building to create a useful calculated field. In fact, you can write robust expressions using only grade-school math. For instance, you could:

• Use + to find the sum of the contents of two fields or to add a constant value (such as + 2 or + 5) to a field
• Use * to multiply the contents of two fields or to multiply fields by a constant value
• Use - to subtract one field from other or to subtract a constant value from a field

In our example, we will use a table containing the orders from one month. The table contains items listed by sales unit—single, half-dozen, dozen. One column lets us know the number sold of each sales unit. Another lets us know the actual numerical value of each of these units. For instance, in the top row you can see that two dozen fudge brownies have been sold and that one dozen equals 12 brownies.

To find the total number of brownies that have been sold, we'll have to multiply the number of units sold by the numerical value of that unit—here, 2*12, which equals 24. This was a simple problem, but performing that calculation for each row of the table would be tedious and time consuming. Instead, we can create a calculated field that shows the product of these two fields multiplied together on every row.

To create a calculated field:

1. Select the Fields tab, locate the Add & Delete group, and click the More Fields drop-down command.
   
   Clicking the More Fields drop-down command

2. Hover your mouse over Calculated Field and select the desired data type. We want our calculation to be a number, so we'll select Number.
   
   Selecting the calculated field type

3. Build your expression. To select fields to include in your expression, double-click the field in the Expression Categories box. Remember to include mathematical operators like the + or - signs. Because we want to multiply our two fields, we'll put the multiplication symbol (*) between them.
   
   Building the expression for a calculated field

4. Click OK. The calculated field will be added to your table. If you want, you can now sort or filter it.
   
   The calculated field shows the product of the two fields to its left

For more examples of mathematical expressions that can be used to create calculated fields, review the Arithmetic Expressions in the Expression Builder dialog box.

Totals rows

The totals row adds up an entire column of numbers, just like in a ledger or on a receipt. The resulting sum appears in a special row at the bottom of your table.

For our example, we'll add a totals row to our calculated field. This will show us the total number of items sold.

To create a totals row:

1. From the Home tab, locate the Records group, then click the Totals command.
   
   Clicking the Totals command

2. Scroll down to the last row of your table.
3. Locate the desired field for the totals row, then select the second empty cell below the last record for that field. When a drop-down arrow appears, click it.
   
   Clicking the totals row drop-down arrow

4. Select the function you want to perform on the field data. In our example, we'll choose Sum to add all of the values in the calculated field.
   
   Selecting the function to be performed on the field

5. The totals row will appear.
   
   The totals row showing the total number of products sold

/en/access2013/creating-a-parameter-query/content/