Vassago
Former Staff Turned AWF Retiree
- Local time
- Today, 00:02
- Joined
- Dec 26, 2002
- Messages
- 4,751
I am very new to SQL coding, but have a lot of experience in Access. Please bare with me as I try to explain my situation.
I have five tables, as follow:
ACCOUNTS:
accountnum, typeid
11111, 01
22222, 02
33333, 01
44444, 01
55555, 02
TYPES:
typeid, typedes
01, Type1
02, Type2
03, Type3
TRANSACTIONS:
tranid, accountnum, trandate, trantype
001, 55555, 12/22/05, 147
002, 22222, 12/23/05, 148
003, 11111, 12/22/05, 147
004, 11111, 12/22/05, 147
005, 33333, 12/23/05, 147
006, 22222, 12/22/05, 147
007, 11111, 12/23/05, 147
008, 11111, 12/22/05, 147
009, 22222, 12/22/05, 147
010, 11111, 12/22/05, 147
011, 11111, 12/23/05, 147
012, 55555, 12/22/05, 148
013, 22222, 12/23/05, 147
014, 33333, 12/25/05, 148
ORIGINFO
accountnum, originid
11111, 03
33333, 01
44444, 02
ORIGTYPES:
originid, origindes
01, Origin1
02, Origin2
03, Origin3
The table named ACCOUNTS has every account listed on it. I want to create a query with the following fields based on the data from the above tables.
accountnum, typedes, typeid, origindes, originid, lasttran148date
My biggest problem is trying to write the last field, lasttran148date. I want sql to look on TRANSACTIONS for the last time a trantype value of 148 appears on the list, and fill in the trandate as lasttran148date where trantype was 148. This is my code so far:
Select ACCOUNTS.accountnum, TYPES.typedes, ACCOUNTS.typeid, ORIGTYPES.origindes, ORIGINFriginid
from ACCOUNTS
left join TYPES on ACCOUNTS.accountnum = TYPES.accountnum
left join ORIGINFO on ACCOUNTS.accountnum = ORIGINFO.accountnum
left join ORIGTYPES on ORIGINFriginid = ORIGTYPES.originid
Can anyone give me a suggestion for that last field? Also, are my joins correct or should I use a different type of join to get what I'm looking for?
All help is greatly appreciated.
Vassago
I have five tables, as follow:
ACCOUNTS:
accountnum, typeid
11111, 01
22222, 02
33333, 01
44444, 01
55555, 02
TYPES:
typeid, typedes
01, Type1
02, Type2
03, Type3
TRANSACTIONS:
tranid, accountnum, trandate, trantype
001, 55555, 12/22/05, 147
002, 22222, 12/23/05, 148
003, 11111, 12/22/05, 147
004, 11111, 12/22/05, 147
005, 33333, 12/23/05, 147
006, 22222, 12/22/05, 147
007, 11111, 12/23/05, 147
008, 11111, 12/22/05, 147
009, 22222, 12/22/05, 147
010, 11111, 12/22/05, 147
011, 11111, 12/23/05, 147
012, 55555, 12/22/05, 148
013, 22222, 12/23/05, 147
014, 33333, 12/25/05, 148
ORIGINFO
accountnum, originid
11111, 03
33333, 01
44444, 02
ORIGTYPES:
originid, origindes
01, Origin1
02, Origin2
03, Origin3
The table named ACCOUNTS has every account listed on it. I want to create a query with the following fields based on the data from the above tables.
accountnum, typedes, typeid, origindes, originid, lasttran148date
My biggest problem is trying to write the last field, lasttran148date. I want sql to look on TRANSACTIONS for the last time a trantype value of 148 appears on the list, and fill in the trandate as lasttran148date where trantype was 148. This is my code so far:
Select ACCOUNTS.accountnum, TYPES.typedes, ACCOUNTS.typeid, ORIGTYPES.origindes, ORIGINFriginid
from ACCOUNTS
left join TYPES on ACCOUNTS.accountnum = TYPES.accountnum
left join ORIGINFO on ACCOUNTS.accountnum = ORIGINFO.accountnum
left join ORIGTYPES on ORIGINFriginid = ORIGTYPES.originid
Can anyone give me a suggestion for that last field? Also, are my joins correct or should I use a different type of join to get what I'm looking for?
All help is greatly appreciated.
Vassago
Last edited: