Results 1 to 10 of 10

Thread: report troubles

  1. #1
    Join Date
    Oct 2003
    Posts
    13

    Unanswered: report troubles

    I am trying to combine values from a table.
    Problem, I am using the query wizard to sum a set of laon codes. this works ok but then i need to seperate some of those loan codes out and get another balance. So i am summing things twice. I was trying to do this hard code in the report but I am having trouble getting any result So i was hoping i could get some help doing it through sql in a query then just output it to a report

    SELECT dbo_loan.code, dbo_loan.shortdesc, dbo_loan.status, dbo_loan.balance
    FROM dbo_loan
    WHERE (((dbo_loan.code)="01" Or (dbo_loan.code)="02 0R 03" Or (dbo_loan.code)="07" Or (dbo_loan.code)="13" Or (dbo_loan.code)="1E" Or (dbo_loan.code)="1L" Or (dbo_loan.code)="1P" Or (dbo_loan.code)="2L" Or (dbo_loan.code)="2P" Or (dbo_loan.code)="3L" Or (dbo_loan.code)="3P" Or (dbo_loan.code)="41" Or (dbo_loan.code)="87") AND ((dbo_loan.status)<>"WRITEOFF") AND ((dbo_loan.balance)<0));

    but now i would like to sum say the 01, 02 and 03 loan codes after i have selected them from the database

    any help greatly appreciated.

    ryan

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: report troubles

    Originally posted by ryancoc22
    I am trying to combine values from a table.
    Problem, I am using the query wizard to sum a set of laon codes. this works ok but then i need to seperate some of those loan codes out and get another balance. So i am summing things twice. I was trying to do this hard code in the report but I am having trouble getting any result So i was hoping i could get some help doing it through sql in a query then just output it to a report

    SELECT dbo_loan.code, dbo_loan.shortdesc, dbo_loan.status, dbo_loan.balance
    FROM dbo_loan
    WHERE (((dbo_loan.code)="01" Or (dbo_loan.code)="02 0R 03" Or (dbo_loan.code)="07" Or (dbo_loan.code)="13" Or (dbo_loan.code)="1E" Or (dbo_loan.code)="1L" Or (dbo_loan.code)="1P" Or (dbo_loan.code)="2L" Or (dbo_loan.code)="2P" Or (dbo_loan.code)="3L" Or (dbo_loan.code)="3P" Or (dbo_loan.code)="41" Or (dbo_loan.code)="87") AND ((dbo_loan.status)<>"WRITEOFF") AND ((dbo_loan.balance)<0));

    but now i would like to sum say the 01, 02 and 03 loan codes after i have selected them from the database

    any help greatly appreciated.

    ryan
    Are you running the report through a form?

    If so, you could filter the records through the selections in the form. Have a default selection of <<All>> that would run the report unfiltered but provide a means of entering values if you want to filter. If there aren't a large number of loan codes, you could use a multiselect listbox to allow multiple selections. You'll need to use code to loop through the selected items and concatenate them into a string for the where clause but I think this is how I would approach it. One form. one query, one report.

    Hope this is the direction you were looking for. Good Luck.

    Gregg

  3. #3
    Join Date
    Oct 2003
    Posts
    13
    no i was trying to pull the values straight from a query. I was not using any forms at this point since noone is inputting anydate im just pulling it out.

    thank you though

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Run another query based on the previous one where you sum on the desired loan codes ...

    SELECT Sum(AmtFieldHere) FROM QueryNameHere WHERE (LoanCode IN ("01","02","03");

  5. #5
    Join Date
    Oct 2003
    Posts
    13
    SELECT Sum(balance)
    FROM qry LoansExcProperty
    WHERE (Code IN ("01","02","03"));

    i run this and get Microsoft jet database could not find query or table that starts with qry

    should i rename it?

  6. #6
    Join Date
    Oct 2003
    Posts
    13
    Ok I figured that out so far. I just renamed it to another query.

    I have run into another problem though

    SELECT Sum(balance) AS LoanTotals
    FROM LoansExcProperty
    WHERE ( ( (LoansExcProperty.code) In ("01","02 , 03","07","13","1E","1L","1P","2L","2P","3L","3P"," 41","4L","60","66","71","72","7L","90","95","AX"," BX","CX","OV","PS","SI","SL","XB","XD","XE","XF"," XJ","XM","XN","XQ","XR","XS","XT","XU","XV","XW"," XX","XY","XZ","87") ) );


    query is working fine now but i want to add some qualifiers to the select statement. When i do it gives me an error msg about not being part of the aggregate function.

    I want to add int the select statement ; shortdesc of the loan

    and in the where clause that it not equal a status of writeoff.

    thank you soo much for the help you guys are wesome

  7. #7
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by ryancoc22
    Ok I figured that out so far. I just renamed it to another query.

    I have run into another problem though

    SELECT Sum(balance) AS LoanTotals
    FROM LoansExcProperty
    WHERE ( ( (LoansExcProperty.code) In ("01","02 , 03","07","13","1E","1L","1P","2L","2P","3L","3P"," 41","4L","60","66","71","72","7L","90","95","AX"," BX","CX","OV","PS","SI","SL","XB","XD","XE","XF"," XJ","XM","XN","XQ","XR","XS","XT","XU","XV","XW"," XX","XY","XZ","87") ) );


    query is working fine now but i want to add some qualifiers to the select statement. When i do it gives me an error msg about not being part of the aggregate function.

    I want to add int the select statement ; shortdesc of the loan

    and in the where clause that it not equal a status of writeoff.

    thank you soo much for the help you guys are wesome
    You have just entered into the wild and wooley world of aggregate queries that are concerned with groups of data.

    If you want to add some "qualifiers" you will have to use a Having clause. This is the equivelant of the Where clause but works with groups.

    I use them occasionally but there are probably members
    on the forum better suited than I to explain the details.

    Hope you get it straightened out.

    Gregg

  8. #8
    Join Date
    Oct 2003
    Posts
    13
    well can anyone out there throw some syntax out that could get me going in any direction. ANy help is appreciated

    thanks ryan

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    the latter part for your WHERE is ... AND (Status <> 'WriteOff') ... As for the former ... You tried to add a non-aggregate column to this query and it puked?

  10. #10
    Join Date
    Oct 2003
    Posts
    13
    Thanks M Owen you da man.

    Yes I tried to add a selection to my query. I wanted it to list the loan codes it was summing. but as Im typing I think understand why it will not let me. And since this will be outputted to a report I could type in what I wanted the listing to be.

    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •