how to calculate sum of the repeating values in queries

[cyrus05 via AccessMonster.com]

hi
How can I calculate sum of repeated values. Tables are (contracts,
Jobsin_contracts,Contract_detail).
A bit of the problem is I have a contracts, jobs_in_contract outer join with
contract_id and contract_detail table outer join with (contract_id, Job_Id).
Stock_ID is entered for every job_id. thus making it possible that same
stock_ID can be entered for more than once with different quanities for each
job in that contract, but while it comes to invoicing, I want to show the
total of the stock_ID quantity given to that particular contract.
I hope I could explain my problem, can anyone please tell me how to calculate
the sum of the repeating items seperately grouped by job_ID?
Below is example of the data
Contract Table
Contract ID
1

Jobs_in_contract
Contract_ID Job_ID
1 1
1 2

Contract_Detail
Contract_ID Job_ID Stock_ID Quantity
1 1 001 10
1 1 002 4
1 2 001 5

Until this point my application works perfactly, but now I want to create an
invoice for the contract NO 1 where it should be as follow

Contract_ID Stock_ID Quantity
1 001 15
1 002 4

Can any one please help me how to calculate the sum of the repeating values
in queries based on the above example.

 

[Bob Barrows [MVP]]

hi
How can I calculate sum of repeated values. Tables are (contracts,
Jobsin_contracts,Contract_detail).
A bit of the problem is I have a contracts, jobs_in_contract outer
join with contract_id and contract_detail table outer join with
(contract_id, Job_Id). Stock_ID is entered for every job_id. thus
making it possible that same stock_ID can be entered for more than
once with different quanities for each job in that contract, but
while it comes to invoicing, I want to show the total of the stock_ID
quantity given to that particular contract.
I hope I could explain my problem, can anyone please tell me how to
calculate the sum of the repeating items seperately grouped by job_ID?
Below is example of the data
Contract Table
Contract ID
1

Jobs_in_contract
Contract_ID Job_ID
1 1
1 2

Contract_Detail
Contract_ID Job_ID Stock_ID Quantity
1 1 001 10
1 1 002 4
1 2 001 5

Until this point my application works perfactly, but now I want to
create an invoice for the contract NO 1 where it should be as follow

Contract_ID Stock_ID Quantity
1 001 15
1 002 4

Can any one please help me how to calculate the sum of the repeating
values in queries based on the above example.

It's a grouping query. Create a new query in Design View, clicking Cancel on
the Choose Table dialog without selecting a table. Then switch to SQL View
using the View menu, or the toolbar button, or the right-click context menu.
Then paste the following sql statement into the sql window:

SELECT Contract_ID, Stock_ID, Sum(Quantity) As StockQuantity
FROM Contract_Detail
GROUP BY Contract_ID, Stock_ID

Run it to see the results, then switch back to Design View to see how you
would have constructed it in the grid.

 

[cyrus05 via AccessMonster.com]

Thanks bob, it worked perfactly.

hi
How can I calculate sum of repeated values. Tables are (contracts,

[quoted text clipped - 33 lines]

Can any one please help me how to calculate the sum of the repeating
values in queries based on the above example.

It's a grouping query. Create a new query in Design View, clicking Cancel on
the Choose Table dialog without selecting a table. Then switch to SQL View
using the View menu, or the toolbar button, or the right-click context menu.
Then paste the following sql statement into the sql window:

SELECT Contract_ID, Stock_ID, Sum(Quantity) As StockQuantity
FROM Contract_Detail
GROUP BY Contract_ID, Stock_ID

Run it to see the results, then switch back to Design View to see how you
would have constructed it in the grid.