Complex Query to count Loan Number

[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

 

[Duane Hookom]

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.