Generating age wise report of sundry debtors

[sr1811]

I have a database consisting of bill number, bill date. Current no of days outstanding is derived out of datediff("d",bill date,now). I wants to generate different columns for less than one year, 1 to 2 years, 2-3 years etc of bills outstanding. Please help me do the same by using sub queries.

regards

SR

[alansidman]

Post your current SQL statement so that a viable solution can be provided that matches your particular needs.

[sr1811]

Hi

There are two table Customer with Customer ID and Customer Name and Order with Customer ID,Order ID, Order Date and Payment Date

I am looking for a multiple result in different columns. The SQL looks like:
SELECT Customer.[Customer ID],
SELECT(Order.[Amount] FROM Order WHERE DateDiff("d",[Order Date],Now) < 90 AND Order.[Payment Date] is NULL AND Customer.[Customer ID] = Order[Customer ID] As [Less than 90],
(SELECT(Order.[Amount] FROM Order WHERE DateDiff("d",[Order Date],Now) < 180 AND Order.[Payment Date] is NULL AND Customer.[Customer ID] = Order[Customer ID] As [Less than 180],
(SELECT(Order.[Amount] FROM Order WHERE DateDiff("d",[Order Date],Now) < 360 AND Order.[Payment Date] is NULL AND Customer.[Customer ID] = Order[Customer ID] As [Less than 360],

regards

SR1811

[alansidman]

I did this with four queries.

See the attached database. I created a query for each of the particular agings and then created a UNION query to join them

[Dal Jeanis]

This will get you your different date ranges in a single query.

SELECT 
   TC.[Customer ID],
   IIF(DateDiff("d",T2.[Order Date],Now) < 90, T2.[Amount],0) AS [Less than 90],
   IIF(DateDiff("d",T2.[Order Date],Now) BETWEEN 90 and 179, T2.[Amount],0) AS [Less than 180],
   IIF(DateDiff("d",T2.[Order Date],Now) BETWEEN 180 and 359, T2.[Amount],0) AS [Less than 360],
... etc ...
FROM
   tblCustomer AS TC 
   INNER JOIN 
   tblOrder as T2 
   ON TC.[Customer ID] = T2.[Customer ID];

Note that I have changed your table names, since ORDER is a reserved word. It's also best to avoid spaces in field names, but I've left those alone.