# query for cr dr result

• ### Question

• hi i have 1 table

id   code    amount

1    abc      1000

2   cde     -1000

3   def     250

4  efg      -500

5  fgh     -250

i want out put

like

code      amount        code     amount

cde        -1000          abc        1000

efg          -500          def           250

fgh          -250

i try sum query

but no result

amit patel

Thursday, January 3, 2013 11:41 AM

### Answers

• I don't think you can do that in a query. You could do the following:

1) Create a query based on the table that returns the codes and amounts for which amount < 0.

2) Create a continuous form based on this query.

3) Create a query based on the table that returns the codes and amounts for which amount >= 0.

4) Create a continuous form based on this query.

5) Create a blank, unbound form.

6) Place the subforms side by side on the blank form

Alternatively, you could create reports and place them as subreports on an unbound blank report.

Regards, Hans Vogelaar

Thursday, January 3, 2013 12:16 PM

### All replies

• I don't think you can do that in a query. You could do the following:

1) Create a query based on the table that returns the codes and amounts for which amount < 0.

2) Create a continuous form based on this query.

3) Create a query based on the table that returns the codes and amounts for which amount >= 0.

4) Create a continuous form based on this query.

5) Create a blank, unbound form.

6) Place the subforms side by side on the blank form

Alternatively, you could create reports and place them as subreports on an unbound blank report.

Regards, Hans Vogelaar

Thursday, January 3, 2013 12:16 PM
• i am use

in ms sql 2008 working fine but how to use ms acess

;with Positive as (select *, row_number() over (order by ID) as Rn from myTable T where Amount >=0),

Negative as (select *, row_number() over (order by ID) as Rn from MyTable where Amount <0)

select N.code, N.Amount, P.Code, P.Amount

from Negative N FULL JOIN  Positive P on N.Rn = P.Rn

amit patel

Thursday, January 3, 2013 12:21 PM
• I don't think there is a direct equivalent in Access SQL.

Regards, Hans Vogelaar

Thursday, January 3, 2013 12:33 PM
• You want to separate the positive numbers and the negative numbers.  Hummm, a Crosstab Query will get you close, but I don't think it will do exactly what you want.  Like a Pivot Query in SQL Server, a Crosstab Query will 'pivot' you data based on one single column, of your choosing.  See this for more information:

http://www.databasedev.co.uk/crosstab_queries.html

http://www.techrepublic.com/article/how-do-i-create-a-crosstab-query-in-microsoft-access/6121381

Remember, in MS Access, data goes down, and in Excel, data goes across.

Good luck!!

Ryan Shuell

Friday, January 4, 2013 3:56 AM