Query union all to get a grand total from two tables

[Saeed Saj]

Can anybody help me solving this problem


Here I have 3 tables. I want to retrieve data from these three tables in order to get a grand total for a Pcode during a particular year


I have attached the db

Here in Query1 the display of data is in a row which I am looking for but the figure is wrong as it calculate qty from table Purchasedetail twice (61) + qty from adustments (3+2)


in the second query2


The figure shown are correct but it is displayed in two rows rather than one row which I need

Appreciate help

 

[plog]

UNION queries should never be that complex. Avoid, WHERE clauses, JOINS, and GROUP BY clauses in them. You just end up biting off more than you can chew and when things go sideways its a pain to debug them.

The structure of a good UNION query should have a SELECT and a FROM. Like so:

SELECT * FROM DatSource1
UNION
SELECT * FROM DataSource2
UNION
...

So what you do is put the WHERE, GROUP BY and JOIN clauses before or after the UNION query. If the entire datasource needs criteria applied, you build a query on your UNION and apply the criteria there. If only one of the tables that will feed your UNION needs to be have a GROUP BY, then you build a query and then use that query as a DataSource in the UNION.

With that said, I don't think you need a UNION--all your tables are related. UNIONs are for datasources you can't easily relate. I think you need to total up your individual tables in sub-queries, then JOIN those queries together to achieve what you want.

Your issue arises from your JOINs. It's simple multiplication--if one side of the JOIN has 1 record with that value and the other side has 3 records, then your query will use that record 3 times. If one side of the JOIN has 2 records with a value and the other side has 3 records, then your query will use that value in 6 records. That's what happened to your query.

What you need is to add some sub-queries to make those values unique (only occur once), then when you JOIN them to another data source it won't multiply your records.

 

[Saeed Saj]

Thanks for your explanation and it is quite clear.
Appreciate if you can modify the query as explained in your reply in the attached db and post it.
Thanks in advance

 

[Simon_MT]

Don't be afraid to store total values - SQL Server call these Cubes - but it is quicker to store total values from subsystems. I have thousands of records in sub-systems to do queries is inefficient - so total values are stored within the entry forms for these sub systems. I have a file with 350,000 records the maximum number of records within one LOT is 250 no problem manipulating this amount data upon entry but another to so this combining information for reporting.

Simon

 

[Saeed Saj]

Noted the contents and thanks
The values cannot be stored each time as the values in each column changes at every transactions. So it can only be calculated at the time when required with the available figures in each column

 

[Simon_MT]

I have the same problem and update the totals on transactional change.

Simon