Complex Query to count Loan Number

  • Thread starter Thread starter kay
  • Start date Start date
K

kay

Table: Outstanding
Age LoanNum Doc Status TP Status RET status
20 00123 '100' '120' '101'
30 00277 '104' '100' '120'

--------------
i have around 50000 such rows in my table.
i have tried to solve this but did not get success, its a bit complecated.
Question1: in about table i want to write a query to count loan number by
Age for each code -'100' ,both together'104 and 120' ,'101'.
so for eg. In above table I have loan count for '100' by age is
Age for100 Loancount for (104 & 120)LoanCount
20 1 1
30 1 2
 
I would start by normalizing your data with a union query:
Select Age, LoanNum, "Doc" as Status, [Doc Status] as SomeNumber
FROM Outstanding
UNION ALL
Select Age, LoanNum, "TP", [TP Status]
FROM Outstanding
UNION ALL
Select Age, LoanNum, "RET", [RET Status]
FROM Outstanding;

I would then create a small table that groups 104 and 120 together
SomeNumber GroupTitle
100 100
101 101
104 104_120

Then create a crosstab query based on the union query joined to the small
table. Join the SomeNumber fields.

Set the
Row Heading to the Age field,
Column Heading to "For " & GroupTitle & " Loan Count"
Value will be Count of Age the Age field.
 
Back
Top