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

 

[Pat Hartman]

I don't understand the table structure so I can't tell you how to join the tables to accomplish your purpose. What are the primary keys for each table and what are the foreign keys? What is the purpose of table2? And, why are Year and Acct# not in table3?

BTW,
1. It is poor practice to use embedded spaces or special characters such as the pound sign (#) in your table and column names.
2. Year and Month are function names and should not be used as names for your user defined objects. This can cause truely strange errors.
3. You will have more flexibility if you store the date in a field defined as a date data type. Don't call this field Date since Date is also a function. Use a more descriptive name. You can use the first of the month as the day component and when displaying the field, you can format it to show just month and year.

 

[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.

 

[Pat Hartman]

If the PK of table1 is only Acc#, how can you store data for multiple months and years? The PK needs to be three fields - Acc#, Year, and Month.

If AccSub# is a sub-account of Acc#, there are many values of AccSub# for each value of Acc# so you cannot store AccSub# in table1.

Acc# is missing from table3. Its PK needs to be Acc#, AccSub#, Year, and month.

I'm going away until Tuesday so I won't be able to continue with this. Hope you get it worked out.