How to add line numbers to groups within a query?

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
 
K

KARL DEWEY

Try this query. I removed your Trim's.

SELECT Q.CustId AS Cust_ID, Q.DocDate AS Doc_Date, Q.DocType AS Doc_Type,
Q.DocDesc AS Doc_Desc,
Round(IIf(Q.[doctype]="IN",Q.[OrigDocAmt],IIf(Q.[doctype]="DM",Q.[origdocamt],-1*Q.[OrigDocAmt])),2) AS DocAmt, (SELECT COUNT(*) FROM dbo_ARDoc Q1
WHERE Q1.[CustId] = Q.[CustId]
AND Q1.[DocDate] = Q.[DocDate]
AND Q1.[DocType]&Q1.[OrigDocAmt] < Q.[DocType]&Q.[OrigDocAmt])+1 AS
RecNo
FROM dbo_ARDoc AS Q
GROUP BY Q.CustId, Q.DocDate, Q.DocType, Q.DocDesc,
Round(IIf(Q.[doctype]="IN",Q.[OrigDocAmt],IIf(Q.[doctype]="DM",Q.[origdocamt],-1*Q.[OrigDocAmt])),2), Q.OrigDocAmt
HAVING (((Q.DocDate)>=#1/1/2008# And (Q.DocDate)<=#1/6/2008#) AND
((([Q].[CustId]))=555700));

--
KARL DEWEY
Build a little - Test a little


StratfordWard said:
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
Click to expand...
 
S

StratfordWard

Karl:

That worked...thanks!!!!!!!!!!!!!!!!!!

(It is a bit slow on larger queries, but worked perfectly once I got the key
fields correctly defined!)

Thanks again,
Stratford

KARL DEWEY said:
Try this query. I removed your Trim's.

SELECT Q.CustId AS Cust_ID, Q.DocDate AS Doc_Date, Q.DocType AS Doc_Type,
Q.DocDesc AS Doc_Desc,
Round(IIf(Q.[doctype]="IN",Q.[OrigDocAmt],IIf(Q.[doctype]="DM",Q.[origdocamt],-1*Q.[OrigDocAmt])),2) AS DocAmt, (SELECT COUNT(*) FROM dbo_ARDoc Q1
WHERE Q1.[CustId] = Q.[CustId]
AND Q1.[DocDate] = Q.[DocDate]
AND Q1.[DocType]&Q1.[OrigDocAmt] < Q.[DocType]&Q.[OrigDocAmt])+1 AS
RecNo
FROM dbo_ARDoc AS Q
GROUP BY Q.CustId, Q.DocDate, Q.DocType, Q.DocDesc,
Round(IIf(Q.[doctype]="IN",Q.[OrigDocAmt],IIf(Q.[doctype]="DM",Q.[origdocamt],-1*Q.[OrigDocAmt])),2), Q.OrigDocAmt
HAVING (((Q.DocDate)>=#1/1/2008# And (Q.DocDate)<=#1/6/2008#) AND
((([Q].[CustId]))=555700));

--
KARL DEWEY
Build a little - Test a little


StratfordWard said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I add a specific format to a series of codes 0
VBA Query to look for a string within a field 3
How do I link a lookup query to an ADO table? 2

Top