Access 2007 - Add simple Line number to queried records

H

h2fcell

Hello,
I have a query that returns a list of records based on a date Criteria like
[Forms]![DateMsgboxRD]![SelectDate]

Depending on the date selected the query may return a different number of
records.

I would like the Query to contain a field â€Num†with an expression that
gives me a sequential line number for each record.

Example: If [Forms]![DateMsgboxRD]![SelectDate] = 2/25/2009
I get the below list.

DiscussionTopic DiscussionDate DiscussionSummary ID
Appointment 2/25/2009 Meeting with John. 4
Appointment 2/25/2009 Meeting with Paul. 12
Payments 2/25/2009 Payment of the listed claims. 15
Refunds 2/25/2009 Refunds 20
Refunds 2/25/2009 Overpayment refunds. 21

Below is what I’d like to see.

Num DiscussionTopic DiscussionDate DiscussionSummary
ID
1 Appointment 2/25/2009 Meeting with John. 4
2 Appointment 2/25/2009 Meeting with Paul. 12
3 Payments 2/25/2009 Payment of the listed claims. 15
4 Refunds 2/25/2009 Refunds 20
5 Refunds 2/25/2009 Overpayment refunds. 21

I tride using something like
Num: = [Num] + 1
But got a Circular reference error.

Is there a simple function I’m over looking?
 
H

h2fcell

SELECT tblMainData.DiscussionTopic, tblMainData.DiscussionDate,
tblMainData.DiscussionSummary, tblMainData.ID
FROM tblMainData
WHERE (((tblMainData.DiscussionDate)=[Forms]![DateMsgboxRD]![SelectDate]));



KARL DEWEY said:
Post the SQL of your query.

h2fcell said:
Hello,
I have a query that returns a list of records based on a date Criteria like
[Forms]![DateMsgboxRD]![SelectDate]

Depending on the date selected the query may return a different number of
records.

I would like the Query to contain a field â€Num†with an expression that
gives me a sequential line number for each record.

Example: If [Forms]![DateMsgboxRD]![SelectDate] = 2/25/2009
I get the below list.

DiscussionTopic DiscussionDate DiscussionSummary ID
Appointment 2/25/2009 Meeting with John. 4
Appointment 2/25/2009 Meeting with Paul. 12
Payments 2/25/2009 Payment of the listed claims. 15
Refunds 2/25/2009 Refunds 20
Refunds 2/25/2009 Overpayment refunds. 21

Below is what I’d like to see.

Num DiscussionTopic DiscussionDate DiscussionSummary
ID
1 Appointment 2/25/2009 Meeting with John. 4
2 Appointment 2/25/2009 Meeting with Paul. 12
3 Payments 2/25/2009 Payment of the listed claims. 15
4 Refunds 2/25/2009 Refunds 20
5 Refunds 2/25/2009 Overpayment refunds. 21

I tride using something like
Num: = [Num] + 1
But got a Circular reference error.

Is there a simple function I’m over looking?
Click to expand...
Click to expand...
 
K

Ken Sheridan

You can use a subquery to count the rows with an ID value less than or equal
to the current row:

PARAMETERS [Forms]![DateMsgboxRD]![SelectDate] DATETIME;
SELECT
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.DiscussionDate = T1.DiscussionDate
AND T2.ID <= T1.ID) AS Num,
DiscussionTopic, DiscussionDate, DiscussionSummary, ID
FROM YourTable As T1
WHERE DiscussionDate= [Forms]![DateMsgboxRD]![SelectDate]
ORDER BY ID;

The two instances of the table are differentiated by the aliases T1 and T2,
enabling the subquery to be correlated with the outer query.

In a report you can number rows by using an unbound text box control with a
ControlSource property of =1, and its RunningSum property set to 'Over All'.

Ken Sheridan
Stafford, England
 
K

KARL DEWEY

Try this --
SELECT Q.[DiscussionTopic], Q.[DiscussionDate], Q.[DiscussionSummary],
Q.[ID], (SELECT COUNT(*) FROM [tblMainData] Q1
WHERE Q1.[DiscussionTopic] & Q1.[DiscussionSummary] <
Q.[DiscussionTopic] & Q.[DiscussionSummary])+1 AS Num
FROM tblMainData AS Q
WHERE (Q.DiscussionDate)=[Forms]![DateMsgboxRD]![SelectDate]
ORDER BY Q.[DiscussionTopic], Q.[DiscussionDate], Q.[DiscussionSummary] ;


h2fcell said:
SELECT tblMainData.DiscussionTopic, tblMainData.DiscussionDate,
tblMainData.DiscussionSummary, tblMainData.ID
FROM tblMainData
WHERE (((tblMainData.DiscussionDate)=[Forms]![DateMsgboxRD]![SelectDate]));



KARL DEWEY said:
Post the SQL of your query.

h2fcell said:
Hello,
I have a query that returns a list of records based on a date Criteria like
[Forms]![DateMsgboxRD]![SelectDate]

Depending on the date selected the query may return a different number of
records.

I would like the Query to contain a field â€Num†with an expression that
gives me a sequential line number for each record.

Example: If [Forms]![DateMsgboxRD]![SelectDate] = 2/25/2009
I get the below list.

DiscussionTopic DiscussionDate DiscussionSummary ID
Appointment 2/25/2009 Meeting with John. 4
Appointment 2/25/2009 Meeting with Paul. 12
Payments 2/25/2009 Payment of the listed claims. 15
Refunds 2/25/2009 Refunds 20
Refunds 2/25/2009 Overpayment refunds. 21

Below is what I’d like to see.

Num DiscussionTopic DiscussionDate DiscussionSummary
ID
1 Appointment 2/25/2009 Meeting with John. 4
2 Appointment 2/25/2009 Meeting with Paul. 12
3 Payments 2/25/2009 Payment of the listed claims. 15
4 Refunds 2/25/2009 Refunds 20
5 Refunds 2/25/2009 Overpayment refunds. 21

I tride using something like
Num: = [Num] + 1
But got a Circular reference error.

Is there a simple function I’m over looking?
Click to expand...
Click to expand...
Click to expand...
 
H

h2fcell

Thank You Ken.
That's exactly what I needed.
***********************

Ken Sheridan said:
You can use a subquery to count the rows with an ID value less than or equal
to the current row:

PARAMETERS [Forms]![DateMsgboxRD]![SelectDate] DATETIME;
SELECT
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.DiscussionDate = T1.DiscussionDate
AND T2.ID <= T1.ID) AS Num,
DiscussionTopic, DiscussionDate, DiscussionSummary, ID
FROM YourTable As T1
WHERE DiscussionDate= [Forms]![DateMsgboxRD]![SelectDate]
ORDER BY ID;

The two instances of the table are differentiated by the aliases T1 and T2,
enabling the subquery to be correlated with the outer query.

In a report you can number rows by using an unbound text box control with a
ControlSource property of =1, and its RunningSum property set to 'Over All'.

Ken Sheridan
Stafford, England

h2fcell said:
Hello,
I have a query that returns a list of records based on a date Criteria like
[Forms]![DateMsgboxRD]![SelectDate]

Depending on the date selected the query may return a different number of
records.

I would like the Query to contain a field â€Num†with an expression that
gives me a sequential line number for each record.

Example: If [Forms]![DateMsgboxRD]![SelectDate] = 2/25/2009
I get the below list.

DiscussionTopic DiscussionDate DiscussionSummary ID
Appointment 2/25/2009 Meeting with John. 4
Appointment 2/25/2009 Meeting with Paul. 12
Payments 2/25/2009 Payment of the listed claims. 15
Refunds 2/25/2009 Refunds 20
Refunds 2/25/2009 Overpayment refunds. 21

Below is what I’d like to see.

Num DiscussionTopic DiscussionDate DiscussionSummary
ID
1 Appointment 2/25/2009 Meeting with John. 4
2 Appointment 2/25/2009 Meeting with Paul. 12
3 Payments 2/25/2009 Payment of the listed claims. 15
4 Refunds 2/25/2009 Refunds 20
5 Refunds 2/25/2009 Overpayment refunds. 21

I tride using something like
Num: = [Num] + 1
But got a Circular reference error.

Is there a simple function I’m over looking?
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 to validate data for new and past records being entered 2
Assign batch # to rcds so report can be recreated. 4
from excel to access - a specific question 2

Top