Hi
I have a report based on this query :
SELECT
A.COMPANY_NAME AS COMPANY_NAME,B.MEMNAME AS MEMNAME,C.AMOUNTDUE AS AMOUNTDUE,C.COMPANY_AMOUNTDUE AS COMPANYAMOUNTDUE,C.FROMDATE,C.TODATE,B.GROUP_TYPE AS GROUP_TYPE
FROM
COMPANY_MASTER A,
MEMMASTER B,
[Fees Maintenance] C
WHERE
(A.COMPANY_CODE=B.COMPANY_CODE
AND B.MEMNUM=C.MEMNUM
AND C.FROMDATE = [ENTER_FROM_DATE]
AND C.TODATE=[ENTER_TO_DATE])
UNION SELECT
A.COMPANY_NAME AS COMPANY_NAME,NULL AS MEMNAME,NULL AS AMOUNTDUE,C.COMPANY_AMOUNTDUE AS COMPANYAMOUNTDUE,C.FROMDATE,C.TODATE,"MEMBER COMPANY" AS GROUP_TYPE
FROM
COMPANY_MASTER A,
[Fees Maintenance] C
WHERE
A.COMPANY_CODE=C.COMPANY_CODE
AND C.COMPANY_AMOUNTDUE IS NOT NULL
AND C.FROMDATE = [ENTER_FROM_DATE]
AND C.TODATE=[ENTER_TO_DATE];
UNION SELECT
nULL AS COMPANY_NAME,B.MEMNAME AS MEMNAME,C.AMOUNTDUE AS AMOUNTDUE,NULL AS COMPANYAMOUNTDUE,C.FROMDATE,C.TODATE,B.GROUP_TYPE AS GROUP_TYPE
FROM
COMPANY_MASTER A,
MEMMASTER B,
[Fees Maintenance] C
WHERE
(b.company_codE IS null AND
B.MEMNUM = C.MEMNUM
AND C.FROMDATE = [ENTER_FROM_DATE]
AND C.TODATE=[ENTER_TO_DATE]);
layout of a report is given in attachment
[/IMG]
The problem is that in case of member company the serial number of members is starting from 2 (as it is taking company as 1st) ... But i want it to start with 1 (as in other cases ie non member company and individual).
How to do that
..pls help !!!
I have a report based on this query :
SELECT
A.COMPANY_NAME AS COMPANY_NAME,B.MEMNAME AS MEMNAME,C.AMOUNTDUE AS AMOUNTDUE,C.COMPANY_AMOUNTDUE AS COMPANYAMOUNTDUE,C.FROMDATE,C.TODATE,B.GROUP_TYPE AS GROUP_TYPE
FROM
COMPANY_MASTER A,
MEMMASTER B,
[Fees Maintenance] C
WHERE
(A.COMPANY_CODE=B.COMPANY_CODE
AND B.MEMNUM=C.MEMNUM
AND C.FROMDATE = [ENTER_FROM_DATE]
AND C.TODATE=[ENTER_TO_DATE])
UNION SELECT
A.COMPANY_NAME AS COMPANY_NAME,NULL AS MEMNAME,NULL AS AMOUNTDUE,C.COMPANY_AMOUNTDUE AS COMPANYAMOUNTDUE,C.FROMDATE,C.TODATE,"MEMBER COMPANY" AS GROUP_TYPE
FROM
COMPANY_MASTER A,
[Fees Maintenance] C
WHERE
A.COMPANY_CODE=C.COMPANY_CODE
AND C.COMPANY_AMOUNTDUE IS NOT NULL
AND C.FROMDATE = [ENTER_FROM_DATE]
AND C.TODATE=[ENTER_TO_DATE];
UNION SELECT
nULL AS COMPANY_NAME,B.MEMNAME AS MEMNAME,C.AMOUNTDUE AS AMOUNTDUE,NULL AS COMPANYAMOUNTDUE,C.FROMDATE,C.TODATE,B.GROUP_TYPE AS GROUP_TYPE
FROM
COMPANY_MASTER A,
MEMMASTER B,
[Fees Maintenance] C
WHERE
(b.company_codE IS null AND
B.MEMNUM = C.MEMNUM
AND C.FROMDATE = [ENTER_FROM_DATE]
AND C.TODATE=[ENTER_TO_DATE]);
layout of a report is given in attachment
[/IMG]
The problem is that in case of member company the serial number of members is starting from 2 (as it is taking company as 1st) ... But i want it to start with 1 (as in other cases ie non member company and individual).
How to do that
..pls help !!!
