Cannot get the correct results

[dealwi8me]

I have a table1 with fields ID(primary key-number),Val1-number,Val2-number,Val3-number, year-number

I created 3 diffrent queries:

query1 returns ID and SUM(val1) if val1 not null
query2 returns ID and SUM(val2) if val2 not null and year=2005
query3 returns ID and SUM(val3) if val3 not null and year<>2005

With an append query I append a table2 with fields ID(primary key-number),SUMVal1-number,SUMVal2-number,SUMVal3-number

My problem is that i have records that should have value to all fields but with this method i only get the first append.

What i mean:
if query1 returns 50001 2 0 0
and query2 returns 50001 0 1 0

then in table2 i get 50001 2 0 0 but i want to get 50001 2 1 0

(the best way is to use an update query but unfortunately i cannot update the data of a table through query)

any suggestions?

thank you in advance!

 

[FoFa]

query1 returns ID and SUM(val1) and 0 and 0 if val1 not null
query2 returns ID and 0 and SUM(val2) and 0 if val2 not null and year=2005
query3 returns ID and 0 and 0 and SUM(val3) if val3 not null and year<>2005

So it would return (using your example)
50001 2 0 0
50001 0 1 0
50001 0 0 0

Do a union all on it.
Base your append query off the union all query then and sum each column and group by ID.

Is one way.

 

[dealwi8me]

do u mean something like that?

SELECT table1.ID, SUM(table1.Val1) AS VAL1
FROM table1
WHERE (((table1.Val1) Is Not Null))
GROUP BY table1.ID
UNION
SELECT table1.ID, Sum(table1.Val2) AS VAL2
FROM table1
WHERE (((table1.Val2) Is Not Null) AND ((table1.Year)="2005"))
GROUP BY table1.ID
UNION
SELECT table1.ID, Sum(table1.Val3) AS VAL3
FROM table1
WHERE (((table1.Val3) Is Not Null) AND ((table1.Year)<>"2005"))
GROUP BY table1.ID;

because this returns only this
SELECT table1.ID, SUM(table1.Val1) AS VAL1
FROM table1
WHERE (((table1.Val1) Is Not Null))
GROUP BY table1.ID

 

[FoFa]

SELECT table1.ID, SUM(table1.Val1) AS VAL1, 0 as VAL2, 0 as VAL3
FROM table1
WHERE (((table1.Val1) Is Not Null))
GROUP BY table1.ID
UNION ALL
SELECT table1.ID, 0 AS VAL1, Sum(table1.Val2) AS VAL2, 0 as VAL3
FROM table1
WHERE (((table1.Val2) Is Not Null) AND ((table1.Year)="2005"))
GROUP BY table1.ID
UNION ALL
SELECT table1.ID, 0 as VAL1, 0 as VAL2, Sum(table1.Val3) AS VAL3
FROM table1
WHERE (((table1.Val3) Is Not Null) AND ((table1.Year)<>"2005"))
GROUP BY table1.ID;

 

[dealwi8me]

ok that works!

Thank you so much