Need query help...

[jnmunsey]

I have two tables:

MAIN
-quotenumber
-quoteamount

REVISIONS
-quotenumber
-revisionnumber
-quoteamount


I want to get a total sum of quote amounts for each quote number from both tables grouped by quote number

i.e.


MAIN
00012 $100
00013 $45
00014 $90


REVISIONS
00012 REVA $50
00012 REVB $30
00012 REVC $10
00013 REVA $80
00013 REVB $60
00014 REVA $20
00015 REVB $10


I'd like the query to return:

00012 $190
00013 $185
00014 $120

I have tried various joins but with no success..

Any help is appreciated..

 

[jfgambit]

Join the two table by Quote Number add the Quote Number and the Quote Amount from the revisions table to the Query.

Click the Sum Key in the toolbar (looks like a funny E) and change the GroupBy under QuoteAmount to Sum...

Should do the trick

 

[jfgambit]

Oops...silly me...you want all the amounts added up...

Add the Revisions Table to a Query and add the Number and Amount to the fileds, Click the sum button and change the GroupBy of the Revision Amount to Sum. Save the Query

make another Query and add the main Table and the saved Query, join by Number.

Add the Number and in the next field type:
TTL Amount: [QuoteAmount] + [SumofQuoteAmount]

Run the query...

 

[jnmunsey]

NEVER MIND looks like I figured it out..

Thanks

Is there any way to do this in one query?

Thx

John M


_____________-
Hi, not sure what you mean by "Int he next field type:" Which field?? Please be a little more specific.

I appreciate the help..

Oops...silly me...you want all the amounts added up...

Add the Revisions Table to a Query and add the Number and Amount to the fileds, Click the sum button and change the GroupBy of the Revision Amount to Sum. Save the Query

make another Query and add the main Table and the saved Query, join by Number.

Add the Number and in the next field type:
TTL Amount: [QuoteAmount] + [SumofQuoteAmount]

Run the query...

 

[joeyreyma]

pat's remarks is correct and practical, but maybe you can try this one. it's not efficient though and i do not recommend especially if the db is big.

SELECT Main.quotenumber, nz([quoteamount])+nz(DSum("quoteamount","Revisions","quotenumber ='" & [quotenumber] & "'")) AS Total
FROM Main;