Get the Difference from 2 Tables

[Ness]

Query Screenshot: Get the sum of project details and then deduct it from the contract cost to get the net amount, but gives wrong result. :(

Hello Forum!

I would like to bring up my trouble getting the difference from a query I have created. I am working on a Project Costing database (tables and interface) where I can encode the contract cost and project details. Here, I will get the sum of project details and then deduct it from the contract cost to get the net amount. So I created a query from 2 tables (main and details) but shows erroneous net result. There isn't an option to use in the query function.

Tables and Fields:
1 Project_Main [Project_No], [PO_Amount]
2 Project_Details [Project_No], [Item_Description], [Item_Amount]
(Inner Joined by Project_No)

Formula:
(PO_Amount)-Sum(Item_Amount) = Net Amount

Sample record test:
1) Project 1 has a contract cost of 1,000,000.00
2) It has 2 item details stored in the Amount field Item1 50,000.00 and Item2 40,000.00
3) This should give a result of 910,000.00 as NET but my query results to 2,990,000.00

SQL View:
SELECT DISTINCT "Project_Main"."Project_No", "Project_Main"."PO_Amount", SUM( "Project_Details"."Amount" ), SUM( "Project_Main"."PO_Amount" - "Project_Details"."Amount" ) AS "Net" FROM "Project_Details", "Project_Main" WHERE "Project_Details"."Project_No" = "Project_Main"."Project_No" GROUP BY "Project_Main"."Project_No", "Project_Main"."PO_Amount"

Attached is the screenshot of query I am working on. How can I possibly remedy this?

Thanks guys!

[eremmel]

In many cases is DISTINCT not what you want to use. Your GROUP BY should do that for you. Your formula is not correctly written in your second SUM. You are summing for each detail also the corresponding PO_Amount. Try:
"Project_Main"."PO_Amount" - SUM( "Project_Details"."Amount" ) AS "Net"
in your query

[Villeroy]

SimpleInventory.odb

Ingoing and outgoing items

(51.46 KiB) Downloaded 872 times

Edit: 2016-08-24: "F3K Total" pointed me to a mistake in the edit form where the ingoing and outgoing items were the same. This is the version corrected by "F3K Total"

[Ness]

In many cases is DISTINCT not what you want to use. Your GROUP BY should do that for you. Your formula is not correctly written in your second SUM. You are summing for each detail also the corresponding PO_Amount. Try:
"Project_Main"."PO_Amount" - SUM( "Project_Details"."Amount" ) AS "Net"
in your query

Thank you for your reply Sir, however, I have also tried this formula in the query but I do not know what 'Function' to select. I am having errors when it has the no function (blank) and the rest gives wrong results. While when SUM is chosen, it triples the PO_Amount that is why the net result is:
(No. of Records*PO_Amount)+(PO_Amount - Amount).

Thanks again.

[eremmel]

If you want to use the GUI to craft queries, I cannot help you further (I've no experience with that). But I advice you to examine the SimpleInventory.odb posted by Villeroy. It contains the solution pattern you need and I think you can stick with the SQL GUI as well. The example put aggregation into sub queries so they act like derived tables before joining them with the master data.

P.S.
Always pay close attention to Villeroy's contributions even at first glance if you do not get it...

[chrisb]

hello Ness,

you need two queries.
1. select the required values from the table "Project_Main".
no problems here as the required data is stored in a single record.

2. select the required values from the table "Project_Details".
we need to create a single record from all rows of data which share the value of "Project_No".
the subquery selects the "Project_No" & sums the field 'Amount'

we now have a one to one relationship between the tables based on "Project_No".

the code below merges both these queries into one.
i have used the alias's of PM & PD explicitly throughout instead of the table names. this is neater & saves on typing.

copy the code & paste it here>>> Database>Queries>Create Query in SQL View.
menu:Edit>'Run SQL command directly' or just hit the SQL icon.
hit execute & hopefully you have a result.

Edit: 25/8/16 sorry Ness i messed up on the code (now corrected)

Code: Select all

select
PM."Project_No",
PM."PO_Amount",

PD."SumAmount",

PM."PO_Amount" - PD."SumAmount" "Net"

from "Project_Main" PM

join
(
	select "Project_No", sum("Amount") "SumAmount"
	from "Project_Details"
	group by "Project_No"
)
PD
on PD."Project_No" = PM."Project_No"