Sum of Column in Union Query

[Arcanant]

Could someone assist me on how I should syntax this sql union query to return the sum of sales.charge and visits.charge?
I have tried several methods to no avail

SELECT SALES.DATE, [SUDDLE] & " " & [FIRSTNAME] AS FIRSTNAME, SALES.CHARGE, "SALES" AS SourceTable
FROM (INNER JOIN CUSTOMER (OWNERSHIP INNER JOIN VISITS ON OWNERSHIP.OWNERSHIP = VISITS.OWNERSHIP) ON CUSTOMER.OWNERSHIP = OWNERSHIP.OWNERSHIP) INNER JOIN SALES ON CUSTOMER.OWNERSHIP.OWNERSHIP = SALES.OWNERSHIP
WHERE (((SALES.DATE)>=[Forms]![FORMALISTREPORTS]![FROMDATE] And (SALES.DATE)<=[Forms]![FORMALISTS]![TODATE]))

UNION SELECT VISITS.VISITDATE, [SUDDLE] & " " & [FIRSTNAME] AS FIRSTNAME, VISITS.CHARGE, "VISIT" AS SourceTable
FROM (INNER JOIN CUSTOMER (OWNERSHIP INNER JOIN VISITS ON OWNERSHIP.OWNERSHIP = VISITS.OWNERSHIP) ON CUSTOMER.OWNERSHIP = OWNERSHIP.OWNERSHIP) INNER JOIN SALES ON CUSTOMER.OWNERSHIP.OWNERSHIP = SALES.OWNERSHIP
WHERE (((VISITS.VISITDATE)>=[Forms]![FORMALISTREFERENCES]![FROMDATE] And (VISITS.VISITDATE)<=[Forms]![FORMALISTREFERENCES]![TODATE]));

 

[arnelgp]

create New query (Total Query) from your Union query to Sum those 2 fields.

 

[Arcanant]

create New query (Total Query) from your Union query to Sum those 2 fields.

I tried that but they return empty cells.
I started making a new query based on this union query and doubled clicked the CHARGE as the field then clicked on the SUM and chose Sum. I run the query and it doesn't show anything.

EDIT: I am dump, I think I get what you mean, let me try it.

 

[arnelgp]

the Criteria of Each Select statement of your Union comes from 2 different Forms?

 

[Arcanant]

the Criteria of Each Select statement of your Union comes from 2 different Forms?

No, it is one form with two fields "From Date" and "To Date"

 

[arnelgp]

you build first "small" queries from sales and visits table:

query1:
SELECT sales.ownership, Sum(sales.charge) AS SumOfcharge
FROM sales
WHERE (((sales.DATE)>=[Forms]![FormName]![FROMDATE] And (sales.DATE)<=[Forms]![FormName]![TODATE]))
GROUP BY sales.ownership;


query2
SELECT visits.ownership, Sum(visits.charge) AS SumOfcharge
FROM visits
WHERE (((visits.visitDate)>=[Forms]![FormName]![FROMDATE] And (visits.visitDate)<=[Forms]![FormName]![TODATE]))
GROUP BY visits.ownership;



now combine them with customer table:

query3:
SELECT [suddle] & " " & [firstname] AS FName, Nz([Query1].[SumOfcharge],0) AS Sales, Nz([Query2].[SumOfcharge],0) AS [Visit Charge]
FROM (customer LEFT JOIN Query1 ON customer.ownership = Query1.ownership) LEFT JOIN Query2 ON customer.ownership = Query2.ownership;

 

[Arcanant]

That is what I thought, that I would have to make small queries at the end but I was wondering if there is a way to make it in one go. All my tried ended up bringing messed up values and the issue is that the dates are inconsistent with the criteria and it makes up non existing values.
Thank you very much for your time and for supplying me with an answer.

 

[plog]

Do the sales and visits tables have the exact same structures?

 

[Arcanant]

Do the sales and visits tables have the exact same structures?

No but they are similar. The union works fine. I have found the answer to my question and now everything works as intended (for now )

What I ended up doing is make small seperate queries with calculations on each one till I reach the final result.