none
query for cr dr result RRS feed

  • 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