Select from ? details

[rzff]

I hope someone can help me out with the next problem . I have a two tables that looks like this.

Table

ID TotalCap Accname Accnumber
1 150000
1 175000
1 Brown
1 123
2 155000
2 195000
2 200000
2 Yellow
2 4567


Accnumber NewAccount
123 321
4567 8453



and a query:

SELECT Table.ID, Table_1.TotalCap, Table_3.Accname, convertaccount.NewAccount
FROM (

AS Table_3 RIGHT JOIN ((

LEFT JOIN

AS Table_1 ON Table.ID = Table_1.ID) LEFT JOIN

AS Table_2 ON Table.ID = Table_2.ID) ON Table_3.ID = Table.ID) LEFT JOIN convertaccount ON Table_2.Accnumber = convertaccount.Accnumber
GROUP BY Table.ID, Table_1.TotalCap, Table_2.Accnumber, Table_3.Accname, convertaccount.NewAccount
HAVING (((Table_1.TotalCap) Is Not Null) AND ((Table_2.Accnumber) Is Not Null) AND ((Table_3.Accname) Is Not Null));

The result is:
ID TotalCap Accname Accnumber
1 150000 Brown 321
1 175000 Brown 321
2 155000 Yellow 8453
2 195000 Yellow 8453
2 200000 Yellow 8453

Now the table is changed a little bit

Table

ID TotalCap Accname Accnumber Period CapDetails Type
1 150000 1
1 1 50000 Shares
1 1 100000 Bonds
1 175000 2
1 2 75000 Shares
1 2 100000 Bonds
1 Brown
1 123
2 155000
2 1 55000 Shares
2 1 100000 Bonds
2 195000
2 2 55000 Shares
2 2 100000 Bonds
2 Yellow
2 4567


I would like to have a result were I will see all details from a particular account I should be able to
enter an account and a period from until:

So something like which Newaccount ? 321
and from periode ? 1
untill ? 1

The result should be:
Period
Details Capital Newaccount Accname Period
321 Yellow 1
50000 Shares
100000 Bonds
150000

I would be very happy is some can help me out

 

[rzff]

Or even better

which ID ? 1
and from periode ? 1
untill ? 1

this is maybe even easier ?

regards

rzff

 

[rzff]

I hope this layout is more clear I dont no how else to show the table. I can use tabs.


IDTotalCap Accname Accnumber Period CapDetails Type
1 150000...........................1
1.....................................1......50000...Shares
1.....................................1......100000..Bonds
1 175000...........................2
1.....................................2......75000...Shares
1.....................................2......100000..Bonds
1....................Brown(record without period)
1..............................123(record without period)
2 155000...........................1
2.....................................1.......55000..Shares
2.................................... 1......100000..Bonds
2 195000...........................2
2.....................................2.......55000..Shares
2.....................................2......100000..Bonds 2....................Yellow(record without period)
2..............................4567(record without period)

 

[rzff]

Next try . I found out that if you just copy and past this table into notepad the layout stays the same with all the tabs.

So here it is but now correct.


ID TotalCap Accname Accnumber Period CapDetails Type
1 150000 1
1 1 50000 Shares
1 1 100000 Bonds
1 175000 2
1 2 75000 Shares
1 2 100000 Bonds
1 Brown
1 123
2 155000
2 1 55000 Shares
2 1 100000 Bonds
2 195000
2 2 55000 Shares
2 2 100000 Bonds
2 Yellow
2 4567

regards

rzff

I hope someone is able to help me. By the way the query I am using now is thanks to lobo123.