Crosstab Query for Summing up values from dif. tables? 1

[vasnas]

Hello, im trying to do a crosstab(?) query and im lost (Access2007)...

Two tables with ID's and some Values:
tblA: AID, BValue
tblB: BID, BValue
Many-to-many Relationship with Junction Table:
tblA_B: AID, BID

I want to create a query that returns a table with all the ID combinations and for each one the sum of the respective A and B Values, ie.:
AID, BID, AValue, BValue, ABValuesSum (Query fields)

Help on Access is not very clear and its examples irrelevant to my case (I think), and i can't figure it out through the Crosstab Query Wizard. It keeps asking for setting Row/Column and Value options(?). I've got the Query with AID, BID, AValue and BValue ready...It should be fairly easy but im still new to Access...

Any help much appreciated! Vas

 

[lespaul]

Rirst, I'm going to assume this is a typo:
[tt]
Two tables with ID's and some Values:
tblA: AID, BValue
tblB: BID, BValue
Many-to-many Relationship with Junction Table:
tblA_B: AID, BID[/tt]
and should be AValue (maybe I'm wrong?)

How about (typed not tested, total guess even!):

SELECT A.AID, B.BID, A.AValue, B.BValue, SUM(A.AValue + B.BValue) 
FROM TblA A, TblB B
INNER JOIN TblA_B AB ON A.AID = AB.AID AND B.BID = AB.BID

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[vasnas]

Thank you Leslie, this looks very promising. unfortunately im always trying to avoid SQL code, but yes! this is very good. I rewrote it like :

SELECT..., AValue+BValue AS TOTAL (or Sum) FROM...

and it does create the TOTAL/Sum Column on its own...
It be interesting to know how to do this in the Query Designer, but this works just as well...

And yes that was a typo!
Thank you again!!
Vas