CODEWITHSUNDEEP
ms-access
UltimateAlloy
UltimateAlloy

Reputation: 55

MS Access 2016 Adding Values From Other Tables

So I have two access tables:

Jobs Table:

JobID,
Customer,
Address,
Measure Date,
Install Date

Job Items:

JobID,
Item Name,
Item Length,
Item Width,
Item Quantity,
Item Cost

So what I want to do is add a field to the Jobs Table that shows the Total Value of all of the Item Costs with the same JobID.

How do I do this?

Edit:

Jobs:

+----+-------------+-----------------+--------------+--------------+
| ID |  Customer   |     Address     | Measure Date | Install Date |
+----+-------------+-----------------+--------------+--------------+
|  1 | Josh Manson | 22 Louis Lane,  | 13/08/2016   | 27/08/2016   |
+----+-------------+-----------------+--------------+--------------+

Job Items:

+------------+-------------------------+-------------+------------+---------------+--------------------+
| Job Number |        Item Name        | Item Length | Item Width | Item Quantity | Item Material Cost |
+------------+-------------------------+-------------+------------+---------------+--------------------+
|          1 | Security Screen         |         800 |        600 |             2 | $250.00            |
|          1 | Window, white aluminium |         700 |        400 |             3 | $150.00            |
+------------+-------------------------+-------------+------------+---------------+--------------------+


I would like there to be an extra column in the first table that adds all the Item Costs for that ID. For JobID 1 it would have a value of $400.

Sorry I'm not quite sure how to format tables properly in StackOverflow.

Upvotes: 0

Views: 90

Answers (1)

Andre
Andre

Reputation: 27634

Create a query in the query designer, with both tables and their INNER JOIN.

Add functions/grouping by clicking the "Totals" button.

Add all fields from the Jobs table, they will all be set to "Group by".

Add Item Material Cost from the Items table, and set its "Total" to Sum.

That's it (well, you should set a column title for the sum column, or it will show in the query result as SumOfItemMaterialCost or something).

Upvotes: 1

Related Questions