Konsolidere to tabeller sammen?

Is ther only one record for each vare/måned? If not then make two queries each where you group on Vare/måned and sum on budget or forbrug. THen make another query joining vare/måned and then select vare, måned, budget, forbrug

"Problemet er, at når jeg laver en inner/right/left join, så filtreres der resultater fra"
Maybe because you dont have data for each vare/måned in both tables ?

I have several "vare" for each "month" and sometimes they arent in both tables.

example:

Table1:
vare, måned, budget:
æble, januar, 10
æble, februar, 20
banan, april, 15

Table2:
vare, måned, forbrug:
æble, januar, 20
æble, marts, 30
appelsin, januar, 10

Should end up like this on a new table:
vare, måned, budget, forbrug;
æble, januar, 10, 20
æble, februar, 20,0
æble, marts, 0, 30
banan, april, 15, 0
appelsin, januar, 0, 10

Is it possible?

Yes its possible but I dont know what the easies way is. You could try the following approach.

Make a UNION Query to get a total list of all vare/måned

SELECT Vare, måned FROM Table1
UNION
SELECT Vare, måned FROM Table2

Now make a new table

Tabel3: vare, måned, budget, forbrug

Then make an append query where you append the data (vare/måned) you get from your union query into table3

Now make two update queries something like this

UPDATE table3 SET budget = (SELECT budget from table1 T1 WHERE T1.vare = table3.vare and T1.månde = table3.måned)

and something similar for forbrug in table2

I tried something similar with a UNION, but not as far as you made it.

Isn't there a easier way, sounds complicated?
And do i manually have to UPDATE, whenever new data is stored in table1 or table2?

The reason i ask for a easier way is because, the example i made, is only a small part of the whole consolidation, I wish to make.

It isnt so complicated but there is quite a bit of manual work to make table3.


And why do you need to have tables 1 and 2 if you  can have it all in one table?

Once its all in table 3 then you dont need the other two tables.

I am sure I could produce one query which would show what you want, but if you could have manage with only one table then I would suggest you do that, it will make things much easier in the long run.

I get the data from two different systems, which is why I cant start with just a table, that has it all.

That is the reason, I have to consolidate them.

if you can send me an example of your tables then I wil try and make a query which shows what you want.
ekspertenATsanthell.dk
AT = @
I'll first have time tomorrow evening, hope thats OK.

Forespørgsel1:
INSERT INTO Tabel3 ( vare, måned, budget )
SELECT Tabel1.vare, Tabel1.måned, Tabel1.budget
FROM Tabel1;

Forespørgsel2:
UPDATE Tabel3 INNER JOIN Tabel2 ON (Tabel3.vare = Tabel2.vare) AND (Tabel3.måned = Tabel2.måned) SET Tabel3.forbrug = Tabel2.forbrug;

Forespørgsel3:
INSERT INTO Tabel3 ( vare, måned, forbrug )
SELECT Tabel2.vare, Tabel2.måned, Tabel2.forbrug
FROM Tabel2 LEFT JOIN Tabel1 ON (Tabel2.vare = Tabel1.vare) AND (Tabel2.måned = Tabel1.måned)
WHERE (((Tabel1.måned) Is Null) AND ((Tabel1.vare) Is Null));

spg: Meningen med det hele er, at det skal ske automatisk ligeså snart ændringer fremkommer i tabel1 eller tabel2. Det skal helst undgå manuelle funktioner.

terry: That might be a little difficult, since its work related and private. Sorry.

Guess I'm stuck then?

SELECT Tabel1.vare, Tabel1.måned, Tabel1.budget, Tabel2.forbrug
FROM Tabel1 INNER JOIN Tabel2 ON (Tabel1.vare = Tabel2.vare) AND (Tabel1.måned = Tabel2.måned)
UNION SELECT Tabel1.vare, Tabel1.måned, Tabel1.budget, 0 AS forbrug
FROM Tabel1 LEFT JOIN Tabel2 ON (Tabel1.måned = Tabel2.måned) AND (Tabel1.vare = Tabel2.vare)
WHERE (((Tabel2.vare) Is Null) AND ((Tabel2.måned) Is Null))
UNION SELECT Tabel2.vare, Tabel2.måned, 0 AS budget, Tabel2.forbrug
FROM Tabel1 RIGHT JOIN Tabel2 ON (Tabel1.vare = Tabel2.vare) AND (Tabel1.måned = Tabel2.måned)
WHERE (((Tabel1.vare) Is Null) AND ((Tabel1.måned) Is Null));

You only need send examples of table1 and table2 with the fields I need and some test data, just to save me time having to make it. You dont need to send the full dB or anything not relevant to the question.

I figured it out, but it was as spg answered. Create 3 queries and then UNION them.

Thanx anyway.