sql - MC Access: Create Select query that always returns value -

good day! hope can me. below wrote down structure , question. have invoice details table: invoice details table

 invoice | detail | product | price | vat % | vat € | total |  ------- | -------|---------|-------|-------|-------|-------|  0001    |   1    |     x   | € 100 |  21   | € 21  | € 121 |  0001    |   2    |     x   | € 200 |  21   | € 42  | € 242 |  0001    |   3    |     x   | € 100 |   6   | €  6  | € 106 | 

now want make select query following values:

desired query result:

 invoice | sumofvat 6% | sumofvat 21% |   ------- | ------------|--------------|  0001    |   €6        |     € 63     |  

after query can use update query write data invoice table:

 invoice | client | date    | price | vat 6% | vat 21% |  ------- | -------|---------|-------|--------|---------|  0001    |   b10  |01-01-01 | € 469 |  € 6   | € 63    | 

i have tried many things. problem run when particular has 1 or more products 1 type of vat in %. query returns null.

i think should have:

1) query calculate total vat 6% in € invoice xxxx ( return invoice number , total vat of 6%)

2) query calculate total vat 21% in € invoice xxxx ( return invoice number , total vat of 21%)

3) if 1 of these queries returns null should return invoice number , total number of vat € 0,00

hope can me this.

image of 6% query: (21% equal criteria '21')

sql version:

 select factuurdetails.factuurnummer, factuurdetails.[btw in %],sum(factuurdetails.[btw in euro]) [sumofbtw in euro]  factuurdetails  group factuurdetails.factuurnummer, factuurdetails.[btw in %]  having (((factuurdetails.factuurnummer)=[forms]![facturen]! 

here see if invoice has products 21% ( or other way around ) query result empty.

surely not answer expect, , receive few negative vote, let's go:
writing these things invoices table wrong: violates relational design principles of normalization, , introduces transitive depency.

unless have objective reasons denormalize, don't that.

additionally, correct design, invoice records have created before invoicedetails, otherwise can't implement referential integrity.

i suggest read bit mmore relational database design , normalization.

as simplified rule: don't store can recalculated, unless need historical reason (but here keep tax history in details)

---

to calculate vat totals, can this:

select invoices.id, details.vatrate, sum([quant]*[unitprice]*[vatrate]) vatamount invoices left join details on invoices.id = details.invid group invoices.id, details.vatrate;  

or crosstab query:

transform sum([quant]*[unitprice]*[vatrate]) vatamount select invoices.id invoices left join details on invoices.id = details.invid group invoices.id pivot details.vatrate;