SQL Trouble

[Kyon]

Heya there,
I'm just fiddling around with Microsoft Access and I ran into some trouble. As I was building a simple database for my mom, I had to eventually create a query that would summate the calculated costs. This amount is calculated by the formula: cost_per_hour * hours_worked. They are sorted per job per cost_per_hour scale.

The query-produced table containing the calculated costs and the according ID (AdviesID). As said, the next step is to go from the below table to a table that contains just this:

AdviesID: 1
Totaal: 60

AdviesID: 2
Totaal: 10

http://imageshack.us/photo/my-images/545/naamloosch.png/

Relationships within the database:
http://imageshack.us/photo/my-images/41/naamloos2cl.png/

But I can't seem to figure out how to do that, any help?

[ne555]

The translation could help.

I don't get your first image ¿Why is the ID 1 appearing 3 times?
IIRC

select AdviesID, sum(cost_per_hour*hours_worked) from benut
  inner join Kosten on SchID 
  group by AdviesID;

[Kyon]

Basically, my mom runs a single-person interior design company, and needed a database system. She offers customers (Klanten) a design plan (Advies) and the costs have to be calculated automagically by inputting how much time she worked on it.

It's not that simple, though. Since there's different types of design plans (or even stages in one plan) that have different costs per hour. So I saved costs per hour in a new table (Kostenschaal - Cost scale). And connected it with Advies via benut (which means "uses"). It basically binds a design plan to a Cost scale and defines how much hours she worked for that price. This is also the reason that I got more than one price after the first calculation (since all I really did was do hours*price_per_hour). I'll check if your solution works in a bit, thanks already mate. This has been bothering me for some time.

Edit:
I tested it and yes, it did the trick (with some slight modifications)! I have a new problem now, though. Since the code isn't at all as I expected it to be, I'm having trouble in combining it with a different query I had. The follow queries have to be combined to create a table containing the following fields:
AdviesID, KlantID, Naam, Adres, Woonplaats, Telefoon, Email, Geld

SELECT AdviesID, sum(Kosten*Uren) AS Geld
FROM benut
  INNER JOIN Kostenschaal
  ON benut.SchaalID=Kostenschaal.SchaalID
  GROUP BY AdviesID;

SELECT *
FROM Klant
WHERE Klant.KlantID IN
    (SELECT KlantID
    FROM Advies
    WHERE Advies.AdviesID = AdviesID AND Advies.Betaald = FALSE);

[ne555]

I guess that you could nest the queries, but it will look quite ugly.
It should be simpler to create a view that will hold the geld, and just combine it with the other one.

[Kyon]

That will do. Thank you!

Okay, what the?

[Kyon]

I figured it all out and it worked a charm! Thanks for the help there ne555. ;)

Final codes:
KlantBijAdvies Query

SELECT AdviesID, Klant.*
FROM Klant
     INNER JOIN Advies
     ON Klant.KlantID = Advies.KlantID
WHERE Klant.KlantID IN
   (SELECT KlantID
   FROM Advies
   WHERE Betaald = FALSE);

InkomstenBijAdvies Query

SELECT AdviesID, sum(Kosten*Uren) AS Geld
FROM benut
     INNER JOIN Kostenschaal
     ON benut.SchaalID = Kostenschaal.SchaalID
GROUP BY AdviesID;

GegevensVoorFactuur Query

SELECT KlantBijAdvies.AdviesID, KlantID, Naam, Adres, Woonplaats, Telefoon, Email, Geld
FROM KlantBijAdvies
     INNER JOIN InkomstenBijAdvies
     ON KlantBijAdvies.AdviesID = InkomstenBijAdvies.AdviesID
WHERE KlantBijAdvies.AdviesID IN
   (SELECT AdviesID
   FROM Advies
   WHERE Betaald=FALSE);

[Kyon]

New trouble:

How do I execute several SQL instructions in one query? I have the following (to delete all the information related to a single KlantID):

DELETE FROM benut
WHERE AdviesID IN
 (SELECT AdviesID
 FROM Advies
 WHERE KlantID=invoer);

DELETE FROM Advies
WHERE KlantID=invoer;

DELETE FROM Afspraak
WHERE KlantID=invoer;

DELETE FROM Klant
WHERE KlantID=invoer;

But Access doesn't seem to like this. Any thoughts? :/

[ne555]

to delete all the information related to a single KlantID

Create a trigger then.
Or a procedure.
Or

begin transaction; 
/*queries
error handling*/
commit;

[Kyon]

I found out about macro's, they seemed to do the trick (although I have a buttload of delete-queries right now, but that's not really a problem).