S
StratfordWard
Hello:
I have the following query:
SELECT Trim([CustId]) AS Cust_ID, dbo_ARDoc.DocDate AS Doc_Date,
Trim([DocType]) AS Doc_Type, Trim([DocDesc]) AS Doc_Desc,
Round(IIf(Trim([doctype])="IN",[OrigDocAmt],IIf(Trim([doctype])="DM",[origdocamt],-1*[OrigDocAmt])),2) AS DocAmt
FROM dbo_ARDoc
GROUP BY Trim([CustId]), dbo_ARDoc.DocDate, Trim([DocType]),
Trim([DocDesc]),
Round(IIf(Trim([doctype])="IN",[OrigDocAmt],IIf(Trim([doctype])="DM",[origdocamt],-1*[OrigDocAmt])),2)
HAVING (((Trim([CustId]))=555700) AND ((dbo_ARDoc.DocDate)>=#1/1/2008# And
(dbo_ARDoc.DocDate)<=#1/6/2008#));
Which produces the following (as expected):
Cust_ID Doc_Date Doc_Type Doc_Desc DocAmt
555700 01/01/08 IN INVOICE 4745.57
555700 01/01/08 PP CREDIT -7052.20
555700 01/02/08 IN INVOICE 266.51
555700 01/02/08 IN INVOICE 5906.66
555700 01/02/08 PA DRAFT -9813.19
685700 01/03/08 IN INVOICE 426.12
685700 01/03/08 IN INVOICE 8500.22
685700 01/03/08 PP CREDIT -3290.12
555700 01/04/08 IN INVOICE 14414.25
555700 01/04/08 PA DRAFT -7055.91
555700 01/04/08 PP CREDIT -3950.27
685700 01/05/08 IN INVOICE 756.00
685700 01/05/08 IN INVOICE 12615.37
I would like to insert a column that increments for each record found for
each Cust_ID AND Doc_Date combination without regard to the other fields that
fall in each record – for example:
Cust_ID Doc_Date RecNo Doc_Type Doc_Desc DocAmt
555700 01/01/08 1 IN INVOICE 4745.57
555700 01/01/08 2 PP CREDIT -7052.20
555700 01/02/08 1 IN INVOICE 266.51
555700 01/02/08 2 IN INVOICE 5906.66
555700 01/02/08 3 PA DRAFT -9813.19
685700 01/03/08 1 IN INVOICE 426.12
685700 01/03/08 2 IN INVOICE 8500.22
685700 01/03/08 3 PP CREDIT -3290.12
555700 01/04/08 1 IN INVOICE 14414.25
555700 01/04/08 2 PA DRAFT -7055.91
555700 01/04/08 3 PP CREDIT -3950.27
685700 01/05/08 1 IN INVOICE 756.00
685700 01/05/08 2 IN INVOICE 12615.37
I can count the total records for each Cust/Date combination, but I am
having trouble figuring out how to increment the count for each line.
There must be a simple answer!
Thanks in advance!
Stratford
I have the following query:
SELECT Trim([CustId]) AS Cust_ID, dbo_ARDoc.DocDate AS Doc_Date,
Trim([DocType]) AS Doc_Type, Trim([DocDesc]) AS Doc_Desc,
Round(IIf(Trim([doctype])="IN",[OrigDocAmt],IIf(Trim([doctype])="DM",[origdocamt],-1*[OrigDocAmt])),2) AS DocAmt
FROM dbo_ARDoc
GROUP BY Trim([CustId]), dbo_ARDoc.DocDate, Trim([DocType]),
Trim([DocDesc]),
Round(IIf(Trim([doctype])="IN",[OrigDocAmt],IIf(Trim([doctype])="DM",[origdocamt],-1*[OrigDocAmt])),2)
HAVING (((Trim([CustId]))=555700) AND ((dbo_ARDoc.DocDate)>=#1/1/2008# And
(dbo_ARDoc.DocDate)<=#1/6/2008#));
Which produces the following (as expected):
Cust_ID Doc_Date Doc_Type Doc_Desc DocAmt
555700 01/01/08 IN INVOICE 4745.57
555700 01/01/08 PP CREDIT -7052.20
555700 01/02/08 IN INVOICE 266.51
555700 01/02/08 IN INVOICE 5906.66
555700 01/02/08 PA DRAFT -9813.19
685700 01/03/08 IN INVOICE 426.12
685700 01/03/08 IN INVOICE 8500.22
685700 01/03/08 PP CREDIT -3290.12
555700 01/04/08 IN INVOICE 14414.25
555700 01/04/08 PA DRAFT -7055.91
555700 01/04/08 PP CREDIT -3950.27
685700 01/05/08 IN INVOICE 756.00
685700 01/05/08 IN INVOICE 12615.37
I would like to insert a column that increments for each record found for
each Cust_ID AND Doc_Date combination without regard to the other fields that
fall in each record – for example:
Cust_ID Doc_Date RecNo Doc_Type Doc_Desc DocAmt
555700 01/01/08 1 IN INVOICE 4745.57
555700 01/01/08 2 PP CREDIT -7052.20
555700 01/02/08 1 IN INVOICE 266.51
555700 01/02/08 2 IN INVOICE 5906.66
555700 01/02/08 3 PA DRAFT -9813.19
685700 01/03/08 1 IN INVOICE 426.12
685700 01/03/08 2 IN INVOICE 8500.22
685700 01/03/08 3 PP CREDIT -3290.12
555700 01/04/08 1 IN INVOICE 14414.25
555700 01/04/08 2 PA DRAFT -7055.91
555700 01/04/08 3 PP CREDIT -3950.27
685700 01/05/08 1 IN INVOICE 756.00
685700 01/05/08 2 IN INVOICE 12615.37
I can count the total records for each Cust/Date combination, but I am
having trouble figuring out how to increment the count for each line.
There must be a simple answer!
Thanks in advance!
Stratford