Some SQL beyond my abilities (please assist)

[hellfire45]

So here is what I'm trying to do. I have one table with a bunch of Claim Numbers and each of these claim numbers may have a "judgment" date. Then I have another table that has SOME (maybe more or less) of the same claim numbers that have payments with payment amounts and payment dates. The first table has unique values in the claim number column but the second table has many entries with the same claim number as a claim can have many payments.

I need to determine the sum total of a claims payments that occured before and after the judgment date. So If claim "A" has a judgment date of 1/1/2012 and has 10 payments, 3 before the judgment date totalling $150, then I need the "PRE-judgment total" column to say $150 and the rest "POST-JUDGMENT". I put some tables below that show Table 1, table 2, and the the desire result of the query.

I think maybe a "correlation subquery" could be used to do this but I'm not confident enough to write it. I'll give a couple sample tables below. Can anybody please give me any guidance? These tables are very large. Hundreds of thousands of rows. I just need some SQL code for use in Microsoft Access that can make this possible. thank you so much!

Table 1:

Claim Number	Judgment DAte
104010	4/3/2012
103050	2/2/2012
103030	4/4/2012
103031	8/8/2012

Table2:

Claim Number	Payment Date	Amount paid
104010	2/2/2012	$10
104010	3/2/2012	$10
104010	4/6/2012	$10
104010	5/6/2012	$10
104010	6/6/2012	$30
103050	1/1/2012	$30
103050	1/15/2012	$30
103050	2/1/2012	$30
103050	2/15/2012	$30
103030	2/2/2012	$30
103030	3/2/2012	$15
103030	4/2/2012	$15
103030	5/2/2012	$15
103030	6/2/2012	$15
103031	6/6/2012	$15
103031	7/6/2012	$20
103031	8/6/2012	$20
103031	9/6/2012	$20

RESULT OF QUERY:

Claim Number	Pre Judgment Amt	Post Judgment Amt	Total Amt
104010	$20	$50	$70
103050	$90	$30	$120
103030	$60	$30	$90
103031	$55	$20	$75

[NTC]

make a query in design view - with these 2 tables; join them by ClaimNumber and by date field; then you must go from design view to SQL view to see the text version of this query - find the join between the dates and manually alter the = symbol to be < symbol - save that query as PreQ

repeat with a new query but this time change = to > and save that query as PostQ

then you make a new query using PreQ as its record source with just 2 fields;option it to be an Aggregate Query with group on Claim and sum on Amount; save as SumPreQ
repeat to make SumPostQ

now make a new final query with SumPreQ and SumPostQ joined at the ClaimNumber - drag on your fields, and create the calculated field for the Total Amount.