troubleshooting Question

Subquery in VB

Avatar of cpx_Support
cpx_Support asked on
.NET ProgrammingVisual Basic.NET
11 Comments1 Solution575 ViewsLast Modified:
I know how to do in Access but not in VB 2005.

I have two tables in access:

Table1: two Fields "Ref" and "Cost"    Data: aa,2 ; bb,3 ; cc,4 ; dd.6
Table2 two Fields "Ref" and "Cost2"  Data: aa,5 ; bb,3 ; aa,2

I want to group the two tables by ref and then make a union,
In access I do:

SELECT Table1.Ref, Sum(Table1.Cost) AS SumOfCost
FROM Table1
GROUP BY Table1.Ref;

SELECT Table2.Ref, Sum(Table2.Cost2) AS SumOfCost2
FROM Table2
GROUP BY Table2.Ref;

Then I make a union query using the Query1 and Query2

SELECT Query1.Ref, Query1.SumOfCost, Query2.SumOfCost2
FROM Query1 LEFT JOIN Query2 ON Query1.Ref = Query2.ref
UNION SELECT Query2.ref, Query1.SumOfCost, Query2.SumOfCost2
FROM Query1 RIGHT JOIN Query2 ON Query1.Ref = Query2.ref;
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros