Multiple table union problem.

I've been struggling with this problem for quite some time now and I'm stuck

I hope someone can give me a little nudge in the right direction in solving my problems

My problems are separated in a few parts...

I have 4 different tables, named(in caps), with colums(non-caps)
REP_TRANSACTION
projid, work_number, tl, orderid, itemid(null), itemname(null), companyname, sidotut
PUR_ORDERBATCH
projid, work_number, tl, orderid, itemid(null), itemname(null), companyname, toteutuneet
STO_NEEDS
projid, work_number, tl, orderid(null), itemid, itemname, companyname(null), sidotut
REPRO_NEEDS_BUFFER
projid, work_number, tl, orderid(null), itemid, itemname, companyname(null), sidotut


I have to merge REP_TRANSACTION with PUR_ORDERBATCH into a new table where there will be both sidotut and toteutuneet columns (sidotut and toteuneet are number values, cost of items)
That means, taking one row from each table, merging them into one, on the basis of:
projid must be same, work_number must be same, tl must be same, orderid must be same

That was part one.


Part two is merging STO_NEEDS with REPRO_NEEDS_BUFFER, but in this case the problem is that STO_NEEDS has got a value already SUM'ed but REPRO_NEEDS_BUFFER has still got values to sum, which makes it hard to merge them into one query (at least for me).
Here are both the separate querys for the individual tables, but should merge SIDOTUT based on:
projid must be same, work_number must be same, tl must be same, itemid must be same, itemname must be same

-STO_NEEDS query-
SELECT STO_NEEDS.PROJID, STO_NEEDS.WORK_NUMBER, Left(STO_NEEDS.ITEM_TYPE_NAME,2) AS LAJI, Null AS ORDERID, STO_NEEDS.ITEMID, STO_NEEDS.ITEMNAME, Null AS COMPANYNAME, Sum(STO_NEEDS.QTY*STO_NEEDS.MAT_TARP_HINTA) AS SIDOTUT
FROM STO_NEEDS
GROUP BY STO_NEEDS.PROJID, STO_NEEDS.WORK_NUMBER, STO_NEEDS.ITEMID, STO_NEEDS.ITEMNAME, STO_NEEDS.ITEM_TYPE_NAME;

-REPRO_NEEDS_BUFFER query-
SELECT REPRO_NEEDS_BUFFER.PROJID, REPRO_NEEDS_BUFFER.WORK_NUMBER, Left(REPRO_NEEDS_BUFFER.ITEM_TYPE,2) AS LAJI, NULL AS ORDERID, REPRO_NEEDS_BUFFER.ITEMID, REPRO_NEEDS_BUFFER.ITEMNAME, NULL AS COMPANYNAME, REPRO_NEEDS_BUFFER.SumOfAVGPRICE AS SIDOTUT
FROM REPRO_NEEDS_BUFFER;


I think that one can merge them with union, and I've tried, but it don't come out as I want it with one row from each table into one single row in a new table (provided the first rows fulfilled the basis for merge).

If I haven't made any sense with this, I can provide further information...

Hena

no, a union won't do what you want. You need to join the tables together and group the columns appropriately.

Back at work, pondering on the same problems :/

If anyone knows the solution to adding a Sum'd value to a non sum'd value in a GROUP BY sql query, please let me know...

Here's the query:
SELECT STO_NEEDS.PROJID, STO_NEEDS.WORK_NUMBER, Left(STO_NEEDS.ITEM_TYPE_NAME,2) AS LAJI, NULL AS ORDERID, STO_NEEDS.ITEMID, STO_NEEDS.ITEMNAME, NULL AS COMPANYNAME, (SUM(STO_NEEDS.QTY*STO_NEEDS.MAT_TARP_HINTA)+REPRO_NEEDS_BUFFER.SumOfAVGPRICE) AS SIDOTUT
FROM STO_NEEDS, REPRO_NEEDS_BUFFER
WHERE
(REPRO_NEEDS_BUFFER.PROJID = STO_NEEDS.PROJID) AND (REPRO_NEEDS_BUFFER.WORK_NUMBER = STO_NEEDS.WORK_NUMBER) AND (Left(REPRO_NEEDS_BUFFER.ITEM_TYPE,2) = Left(STO_NEEDS.ITEM_TYPE_NAME,2)) AND (REPRO_NEEDS_BUFFER.ITEMID = STO_NEEDS.ITEMID) AND (REPRO_NEEDS_BUFFER.ITEMNAME = STO_NEEDS.ITEMNAME)
GROUP BY STO_NEEDS.PROJID, STO_NEEDS.WORK_NUMBER, STO_NEEDS.ITEM_TYPE_NAME, STO_NEEDS.ITEMID, STO_NEEDS.ITEMNAME


The important part being:
(SUM(STO_NEEDS.QTY*STO_NEEDS.MAT_TARP_HINTA)+REPRO_NEEDS_BUFFER.SumOfAVGPRICE) AS SIDOTUT

And the error I receive is due to not having SumOfAVGPRICE in the group by clause, which I can't put there either

MSACCESS says:
"You tried to execute a query that doesn't include the specified expression 'SUM(STO_NEEDS.QTY*STO_NEEDS.MAT_TARP_HINTA)+REPRO_NEEDS_BUFFER.SumOfAVGPRICE' as part of an aggregate function."


Hena

Further update, now trying to do the 2nd query in the simplest of ways, that's by using the two individual querys ( as stated in the first message) and then merging them into a 3rd final query.

Now the problem is that sometimes I have 3 lines in one table with data to be added, and the other has 1, and vice versa, which means that it adds them incorrectly.

I'll show you by example,
table one is (simplified)
PROJID VALUE
AAAG 100

table 2
PROJID VALUE
AAAG 100
AAAG 200
AAAG 300


These should be added to a grand total of 700, but as my query is doing it now It adds 100 to each of those values, then sums them up, which makes for a total of 900.

My query (that uses the previous querys)
SELECT EXCEL_SUM_STO.PROJID, EXCEL_SUM_STO.WORK_NUMBER, EXCEL_SUM_STO.LAJI, NULL AS ORDERID, NULL AS COMPANYNAME, Sum(EXCEL_SUM_STO.SIDOTUT+EXCEL_SUM_REPRO.SIDOTUT) AS SIDOTUT
FROM EXCEL_SUM_STO, EXCEL_SUM_REPRO
WHERE (EXCEL_SUM_REPRO.PROJID = EXCEL_SUM_STO.PROJID) AND (EXCEL_SUM_REPRO.WORK_NUMBER = EXCEL_SUM_STO.WORK_NUMBER) AND (EXCEL_SUM_REPRO.LAJI = EXCEL_SUM_STO.LAJI)
GROUP BY EXCEL_SUM_STO.PROJID, EXCEL_SUM_STO.WORK_NUMBER, EXCEL_SUM_STO.LAJI

um, can you -- no pun intended -- summarize what you want to do?

joining an already-summed row to multiple to-be-summed rows is easy, just adjust the GROUP BY

you talk about 4 queries but by the time of your 3rd posting you only have two, so i'm a bit lost

would like to help, but...

I have four queries, which I then intend to sum up using 2 queries then a final query to sum the whole thing up.

The four querys are:

-EXCEL_SUM_PUR-
SELECT PUR_ORDERBATCH.PROJID, PUR_ORDERBATCH.WORK_NUMBER, DLG_ITEM.ITEM_TYPE_NAME AS LAJI, PUR_ORDERBATCH.ORDERID, NULL AS ITEMID, NULL AS ITEMNAME, PUR_ORDERBATCH.COMPNAME AS COMPANYNAME, Sum(PUR_ORDERBATCH.AMOUNT_BATCH) AS SumOfAmount_Batch
FROM PUR_ORDERBATCH, DLG_ITEM
WHERE (PUR_ORDERBATCH.PROJID IS NOT NULL) AND (DLG_ITEM.ITEMID = PUR_ORDERBATCH.ITEMID)
GROUP BY PUR_ORDERBATCH.PROJID, PUR_ORDERBATCH.WORK_NUMBER, DLG_ITEM.ITEM_TYPE_NAME, PUR_ORDERBATCH.ORDERID, PUR_ORDERBATCH.COMPNAME;

-EXCEL_SUM_REP-
SELECT REP_TRANSACTION.PROJID, REP_TRANSACTION.WORK_NUMBER, REP_TRANSACTION.TRANSACT_TYPE AS LAJI, REP_TRANSACTION.ORDERID, NULL AS ITEMID, NULL AS ITEMNAME, REP_TRANSACTION.COMPNAME AS COMPANYNAME, Sum(REP_TRANSACTION.AMOUNT_LOC) AS TOTEUTUNEET
FROM REP_TRANSACTION
WHERE (REP_TRANSACTION.PROJID IS NOT NULL)
GROUP BY REP_TRANSACTION.PROJID, REP_TRANSACTION.WORK_NUMBER, REP_TRANSACTION.TRANSACT_TYPE, REP_TRANSACTION.ORDERID, REP_TRANSACTION.COMPNAME;

and

-EXCEL_SUM_REPRO-
SELECT REPRO_NEEDS_BUFFER.PROJID, REPRO_NEEDS_BUFFER.WORK_NUMBER, Left(REPRO_NEEDS_BUFFER.ITEM_TYPE,2) AS LAJI, NULL AS ORDERID, NULL AS COMPANYNAME, SUM(REPRO_NEEDS_BUFFER.QTY*REPRO_NEEDS_BUFFER.AVGPRICE) AS SIDOTUT
FROM REPRO_NEEDS_BUFFER
GROUP BY REPRO_NEEDS_BUFFER.PROJID, REPRO_NEEDS_BUFFER.WORK_NUMBER, Left(REPRO_NEEDS_BUFFER.ITEM_TYPE,2);

-EXCEL_SUM_STO-
SELECT STO_NEEDS.PROJID, STO_NEEDS.WORK_NUMBER, Left(STO_NEEDS.ITEM_TYPE_NAME,2) AS LAJI, NULL AS ORDERID, NULL AS COMPANYNAME, SUM(STO_NEEDS.QTY*STO_NEEDS.MAT_TARP_HINTA) AS SIDOTUT
FROM STO_NEEDS
GROUP BY STO_NEEDS.PROJID, STO_NEEDS.WORK_NUMBER, Left(STO_NEEDS.ITEM_TYPE_NAME,2);



They are pairs in the way that they need to be merged in pairs, and the resulting pair should be merged in a final query.

Here is the merging query for the 2nd pair...

-EXCEL_SUM_STO&REPRO_SUM-
SELECT EXCEL_SUM_STO.PROJID, EXCEL_SUM_STO.WORK_NUMBER, Left(EXCEL_SUM_STO.LAJI,2), NULL AS ORDERID, NULL AS COMPANYNAME, (EXCEL_SUM_STO.SIDOTUT+EXCEL_SUM_REPRO.SIDOTUT) AS SIDOTUT
FROM EXCEL_SUM_STO, EXCEL_SUM_REPRO
WHERE ((EXCEL_SUM_REPRO.PROJID = EXCEL_SUM_STO.PROJID) AND (EXCEL_SUM_REPRO.WORK_NUMBER = EXCEL_SUM_STO.WORK_NUMBER) AND (EXCEL_SUM_REPRO.LAJI = EXCEL_SUM_STO.LAJI));


Using this method I have avoided the previous problems mentioned with the sum of one value from one table and a constant value in the other table.

What still remains to solve in the SUM query (EXCEL_SUM_STO&REPRO_SUM), is that it now checks for identical projects, work_numbers and item_types and then merges the values of those, as it should...
But, it leaves out the values for the projects that only exist in one of the tables, for example if I have a project in STO table it should be printed out also, but only once, the method I tried(not implemented in this query at the moment) printed it out once for every row in the second table.
And the same vice versa, that is from REPRO table if there is a row that does not match a row in STO table, print it out once also.

That, my friends, is the problem I'm facing now
(not to mention the final sum query and the query for the two first querys, but one step at a time :P)

I know I have changed the whole way of thinking here, so please disregard the previous messages, as they are no longer valid.

Hena

sounds like you want a full outer join

in access, you have to do a full outer join using a left join union a right exception join

like this --



rudy

Whoa!

Thanks rudy, your code worked like a charm, and it was without any typos also, I just put it in a new query, and the result looked (after a quick check of the values) to show exactly what it should show, nothing more, nothing less. Just splendid work!

Now I'm just wondering if it's possible to do something similar for the trickier pair of querys (the first two).
The first one of them returns SIDOTUT value (tied costs) and the second returns TOTEUTUNEET value (come through costs, already paid). These two values are not to be added, they should go into two separate columns, as SIDOTUT and TOTEUTUNEET, however, they should follow the same principle as the previous query, to put both SIDOTUT AND TOTEUTUNEET on the same row if the PROJECTS, ITEMIDS, ITEMNAMES, ORDERIDS, WORKNUMBERS, ITEMTYPES match

EDIT:
Forgot the querys, here they are:

-EXCEL_SUM_REP-
SELECT REP_TRANSACTION.PROJID, REP_TRANSACTION.WORK_NUMBER, REP_TRANSACTION.TRANSACT_TYPE AS LAJI, REP_TRANSACTION.ORDERID, DLG_ITEM.ITEMID AS ITEMID, DLG_ITEM.NABBR AS ITEMNAME, REP_TRANSACTION.COMPNAME AS COMPANYNAME, Sum(REP_TRANSACTION.AMOUNT_LOC) AS TOTEUTUNEET
FROM REP_TRANSACTION, DLG_ITEM
WHERE (REP_TRANSACTION.PROJID IS NOT NULL)
GROUP BY REP_TRANSACTION.PROJID, REP_TRANSACTION.WORK_NUMBER, REP_TRANSACTION.TRANSACT_TYPE, REP_TRANSACTION.ORDERID, DLG_ITEM.ITEMID, DLG_ITEM.NABBR, REP_TRANSACTION.COMPNAME;


-EXCEL_SUM_PUR-
SELECT PUR_ORDERBATCH.PROJID, PUR_ORDERBATCH.WORK_NUMBER, DLG_ITEM.ITEM_TYPE_NAME AS LAJI, PUR_ORDERBATCH.ORDERID, PUR_ORDERBATCH.ITEMID AS ITEMID, PUR_ORDERBATCH.ITEMNAME AS ITEMNAME, PUR_ORDERBATCH.COMPNAME AS COMPANYNAME, Sum(PUR_ORDERBATCH.AMOUNT_BATCH) AS SIDOTUT
FROM PUR_ORDERBATCH, DLG_ITEM
WHERE (PUR_ORDERBATCH.PROJID IS NOT NULL) AND (DLG_ITEM.ITEMID = PUR_ORDERBATCH.ITEMID)
GROUP BY PUR_ORDERBATCH.PROJID, PUR_ORDERBATCH.WORK_NUMBER, DLG_ITEM.ITEM_TYPE_NAME, PUR_ORDERBATCH.ORDERID, PUR_ORDERBATCH.ITEMID, PUR_ORDERBATCH.ITEMNAME, PUR_ORDERBATCH.COMPNAME;

EXCEL_SUM_REP is still subject to change a small bit, because REP_TRANSACTION table is lacking the ITEMID needed to link DLG_ITEM to it, which must probably be added (phew aloot of work, the querys that make up the main tables are many ;(
the main tables include hundreds of thousands to millions of rows)

But I think it's enough to give you a clue what I'm wanting to do, which is put sidotut and toteutuneet on the same row in a new table provided the rows exist in the two previous querys
Is it possible?

The final query will basically merge this query with the result from the previous query you made (the excel_sum_sto&repro_sum query), might it be possible to incorporate it in this same query?

-Will I be needing one or two querys then?

-Is it possible to merge them using the join functions you provided earlier when having two columns that won't be merged?

Thanks,
Hena