Access's Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Access's Query

Author

Topic

JazzChan
Starting Member

8 Posts

Posted - 2004-05-19 : 09:41:11

Dear All:
i have 3 table "T1","T2" & "T3"
T1
column name are "T1ID","BYear","Dept","BudgetName","Jan","Feb" & "Mar".
value: .1,2004,Accounts,Printing,200,500,400
.2,2004,Accounts,Printing,500,600,500
.3,2004,Sales ,Meeting,100,200,300
.4,2003,Sales ,Training,500,500,500
T2
column name are "T2ID","PO",OrderDate"
value: .1,A123,10/05/2003
.2,A124,10/05/2004
.3,B123,09/05/2004
T3
column name are "T3ID","T2ID",PO",",Amt","BudgetCode"
value: .1,1,A123,50,Sales-Training
.2,1,A123,40,Sales-Training
.3,2,A124,20,Accounts-Printing
.3,2,A124,30,Accounts-Printing
.4,3,B123,100,Sales-Meeting

The question is how can i use sql to see the following result?

Department BudgetYear BBal Expenses Outstanding
Accounts-Printing 2004 2700 50 2650
Sales-Meeting 2004 600 100 500
Sales-Training 2003 1500 90 1410

i tried to use 2 query for them, but i don't know how to make this in one.
Query1
SELECT T1.BYear, T1.DeptName & "-" & T1.BudgetName, [Jan]+[Feb]+[Mar] As [BBal] FROM T1
GROUP BY T1.BYear, T1.T1.DeptName & "-" & T1.BudgetName, [Jan], [Feb], [Mar];

Query2
SELECT T2.PO, Year(T2.OrderDate), SUM(T3.Amt) AS [Expenese], T3.BudgetCode
FROM T2 INNER JOIN T3 ON T2.PO = T3.PO
GROUP BY T2.PO,T2.OrderDate,T3.BudgetCode;

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-19 : 19:29:49

Access will let you query and existing query, if that's what you're looking for:

SELECT Q1.BYear, Q2.PO
FROM Query1 Q1 INNER JOIN Query2 Q2

etc etc etc....

JazzChan
Starting Member

8 Posts

Posted - 2004-05-19 : 20:06:18

Hi:
that mean, i need to use 3 query to fulfill the result mention before instead of 1?...

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-19 : 23:53:48

Here it is:

SELECT Q1.BudgetCode, Q1.BYear, Q1.BBal, Q2.Expense, [BBal]-[expense] AS Outstanding
FROM Query1 AS Q1 INNER JOIN Query2 AS Q2 ON (Q1.BudgetCode = Q2.BudgetCode) AND (Q1.BYear = Q2.Expr1);

This gives you what you need

btw - You'll need to change Query1 to:

SELECT [T1].[BYear], [T1].[Dept] & "-" & [T1].[BudgetName] AS BudgetCode, Sum([Jan]+[Feb]+[Mar]) AS BBal
FROM T1
GROUP BY [T1].[BYear], [T1].[Dept] & "-" & [T1].[BudgetName];

Probably best to keep them in separate queries - it might be too complicated doing it in one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources