I don't think your join is right for starters.
You're effectively saying: "For each record in Table A join it to a corresponding record in Table B where the ID on TableA = the Seller ID on Table B AND it also equals the BuyerID on the same record in Table B.
For example, if your id in Tablea A was 1 and you had two records in Table B:
BuyerId SellerId Result 1 1 This would join 1 2 This would not join
Instead, what you need to do is left join A on the ID to B on the buyer ID and call this table 'Buyer' then left join A on the ID to B on the seller ID and call this table 'Seller'
Then create a calc like;
COUNTD([SaleId]) to get the number of buying instances.
COUNTD([SaleId (Seller)]) to get the number of selling instances.
thanks for the quick reply! im a little confused (by your table names)... I think i understand though:
(remember, tableA has account info, and tableB has transaction info)
so, basically i'm joining twice, but once on the seller id and once on the buyer id.
my only problem is that tableB is HUGE, so i don't want to load and join that thing twice. would it work if:
- i 'load' tableA and call it tableA1 (for the seller account side)
- left join tableB on tableA1.id = tableB.seller
- i load tableA again and calle it tableA2 (for the buyer)
- right join tableA2 to tableB on tableA2.id = tableB.buyer
(I want to use left and right joins b/c i want to show any account that has zero sales or buys)
I basically want the full list of accounts; if i have 1000 accounts i want 1000 rows (showing zeros for # sold and # bought)
will this setup work?
Your proposed join won't work because then you'll end up with two 'master' list of accounts.
If you're connecting to a SQL server, use a custom SQL connection and you can define a connection which is effectively an OR.
LEFT JOIN TableB on TableA.Id = TableB.BuyerId or TableA.Id = TableB.SellerId
If you're using Excel, take a look in the help for using the legacy Excel connector and you can also do custom SQL in that to the same effect as above.