Max of Date with criteria (1 Viewer)

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

Since you are good with Access, may I suggest that you do your query in Access then switch it to SQLview. It will add some «()» for nothing, but it will give you what you want.

Newman said:

Since you are good with Access, may I suggest that you do your query in Access then switch it to SQLview. It will add some «()» for nothing, but it will give you what you want.

Click to expand...

I can't. The users of this server don't use Access, they are actually fearful of it. It has to be done completely in sql server.

Hehe, what he means mate is do your query inside of Access on the tables and data you want, and settings up the relationships etc. But then at the top click the SQL button and it will give you the source code, which u can then copy and paste into your code, sometimes with a few minor changes needed but not many, saves a lot of time.