[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Accounting Tree

Jess L.

3/22/2007 8:40:00 AM

Hi;

I have a database holding accounts tree, where the tables look like this:
==Table #1==
RootAccounts = (RootAccount, AccountName, Description,....) [RootAccount=PK]
1001 Fixed Assets bla bla ....
1002 Moveable Assets, bla bla ....
==Table #2==
FixedAssets = (FACode, AccountName, Description, FatherCode,..) [FACode=PK,
FatherCode = FK]
2001 Buildings bla bla 1001
2002 xxxx bla bla 1001
2003 yyyy bla bla 1002
2004 zzzz bla bla 1001


I want to write a SQL Query that generates results as follows:

Account Code Account Name Child
Accounts
---------------- -----------------
---------------------------------------
1001 Fixed Assets Account Code
Account Name

----------------- ------------------
2001
Buildings
2002
xxxx
2004
zzzz

1002 Moveable Assets
2003
yyyy


Would you please help in generating that?

Thanking you
3 Answers

xyb

3/22/2007 9:09:00 AM

0

On 3?22?, ??4?39?, Jess L. <J...@discussions.microsoft.com> wrote:
> Hi;
>
> I have a database holding accounts tree, where the tables look like this:
> ==Table #1==
> RootAccounts = (RootAccount, AccountName, Description,....) [RootAccount=PK]
> 1001 Fixed Assets bla bla ....
> 1002 Moveable Assets, bla bla ....
> ==Table #2==
> FixedAssets = (FACode, AccountName, Description, FatherCode,..) [FACode=PK,
> FatherCode = FK]
> 2001 Buildings bla bla 1001
> 2002 xxxx bla bla 1001
> 2003 yyyy bla bla 1002
> 2004 zzzz bla bla 1001
>
> I want to write a SQL Query that generates results as follows:
>
> Account Code Account Name Child
> Accounts
> ---------------- -----------------
> ---------------------------------------
> 1001 Fixed Assets Account Code
> Account Name
>
> ----------------- ------------------
> 2001
> Buildings
> 2002
> xxxx
> 2004
> zzzz
>
> 1002 Moveable Assets
> 2003
> yyyy
>
> Would you please help in generating that?
>
> Thanking you
how will you utilize this set? only at QA or it is usefull for some
other applications?
if you only want this form of report from QA,use temp table, or you
will not need this type of dataset because you can format at front.
select
[Table #1].[Account Code],[Table #1].[Account Name],[Table #2].
[AccountName],[Table #2].[AccountCode]... into #t
from
[Table #1] join [Table #2] on ...

update #t
set [Account Code] = ''
where #t.[AccountCode] not in (select max([Table #2].[AccountCode])
from [Table #2] where [Table #2].FatherCode = #t.[Account Code])

select * from #t

masri999

3/22/2007 9:15:00 AM

0

On Mar 22, 1:39 pm, Jess L. <J...@discussions.microsoft.com> wrote:
> Hi;
>
> I have a database holding accounts tree, where the tables look like this:
> ==Table #1==
> RootAccounts = (RootAccount, AccountName, Description,....) [RootAccount=PK]
> 1001 Fixed Assets bla bla ....
> 1002 Moveable Assets, bla bla ....
> ==Table #2==
> FixedAssets = (FACode, AccountName, Description, FatherCode,..) [FACode=PK,
> FatherCode = FK]
> 2001 Buildings bla bla 1001
> 2002 xxxx bla bla 1001
> 2003 yyyy bla bla 1002
> 2004 zzzz bla bla 1001
>
> I want to write a SQL Query that generates results as follows:
>
> Account Code Account Name Child
> Accounts
> ---------------- -----------------
> ---------------------------------------
> 1001 Fixed Assets Account Code
> Account Name
>
> ----------------- ------------------
> 2001
> Buildings
> 2002
> xxxx
> 2004
> zzzz
>
> 1002 Moveable Assets
> 2003
> yyyy
>
> Would you please help in generating that?
>
> Thanking you

select a.rootaccount,0 as child ,a.accountnamefrom RootAccounts a
union all
select a.rootaccount,b.facode as child ,b.accountname from
RootAccounts a
inner join fixedassets b
on a.rootaccount = b.fathercode
order by a.rootaccount ,child

Jess L.

3/28/2007 2:01:00 AM

0

Thank you v. Much,

that works fine, exactly as required.

however, it requires a temp table to generate the report, which i am not
going to use the SQL Server, instead it will be MS-Access db. the issue when
I try to use ADODB.NET to create an MS Access db, it doesnot work, it
requires adding ADO COM+ "ActiveX Data Objects 2.8 Library" but this
generates errors similar to the following"

'Recordset' is ambiguous in the namespace 'ADODB'

How do I resolve this Issue

Many Thanks in Advance

"M A Srinivas" wrote:

> On Mar 22, 1:39 pm, Jess L. <J...@discussions.microsoft.com> wrote:
> > Hi;
> >
> > I have a database holding accounts tree, where the tables look like this:
> > ==Table #1==
> > RootAccounts = (RootAccount, AccountName, Description,....) [RootAccount=PK]
> > 1001 Fixed Assets bla bla ....
> > 1002 Moveable Assets, bla bla ....
> > ==Table #2==
> > FixedAssets = (FACode, AccountName, Description, FatherCode,..) [FACode=PK,
> > FatherCode = FK]
> > 2001 Buildings bla bla 1001
> > 2002 xxxx bla bla 1001
> > 2003 yyyy bla bla 1002
> > 2004 zzzz bla bla 1001
> >
> > I want to write a SQL Query that generates results as follows:
> >
> > Account Code Account Name Child
> > Accounts
> > ---------------- -----------------
> > ---------------------------------------
> > 1001 Fixed Assets Account Code
> > Account Name
> >
> > ----------------- ------------------
> > 2001
> > Buildings
> > 2002
> > xxxx
> > 2004
> > zzzz
> >
> > 1002 Moveable Assets
> > 2003
> > yyyy
> >
> > Would you please help in generating that?
> >
> > Thanking you
>
> select a.rootaccount,0 as child ,a.accountnamefrom RootAccounts a
> union all
> select a.rootaccount,b.facode as child ,b.accountname from
> RootAccounts a
> inner join fixedassets b
> on a.rootaccount = b.fathercode
> order by a.rootaccount ,child
>
>