Select-query (I guess not so easy..)

[key]

Hi guys,

Now, I really need your help. I tried left joins, I tried subselects... :-( I gave up! You are my last hope...
Here are the tables:


Table1
--------
Acc#
Amount
Month
Year
AccSub#

Table2
-----------
AccSub#
AccSubName
Acc#

Table3
--------
AccSub#
changedAmount
changedType
Month


Example values:

Table1
--------
1000
55,-
January
2002
1010

Table2
--------
1010
Name
1000

Table3
--------
1010
12,-
'Type'
'January'


The question is: select Amount from Table1 where Month=January and Year = 2002 lower changedAmount.
Additionally in Table3 there can be more than one record (e. g. 1020, 1030) as well as in Table1 (e. g. or no entries 1020, 1030...)

Do you have any idea how to do it?

Many, many thanks

Key

 

[key]

you're right...

Pat,

- o.k. the # was just the abbrivation for 'number' (in the table it's 'acc_number').
- Table2 is reference table (to define the subaccounts)
- My fault: Table3 contains also Year
keys:
Table1: Acc# (PK)
Table2: AccSub#(PK), Acc#(FK)
Table3: AccSub#, Month and Year are PK

Let's say you want to see the actual amount: what I have to do is to subtract the changedAmount from table3 from the amount from table1.