Numbering lines in a query
- Thread starter Gkirkup
- Start date
Thanks. I didn't think that I'd get a response to a secondary question, so I posted it as a primary question, but you beat me to it!
I'm an Access newby. Is there any way that I can number the lines in a query dynaset from design view?
Robert
I'm an Access newby. Is there any way that I can number the lines in a query dynaset from design view?
Robert
I never used the procedure but check this site out:
http://easyweb.easynet.co.uk/~trevor/AccFAQ/
see article "Sequence number of the fly" under Queries
Dave
http://easyweb.easynet.co.uk/~trevor/AccFAQ/
see article "Sequence number of the fly" under Queries
Dave
OK, took me a while but I found it.
the basics of the SQL:
SELECT [tbl_Orig].[UniqueKey], (SELECT Count([tbl_Orig].[FieldToCount]) AS AutoNum
FROM [tbl_Orig]
WHERE ((([tbl_Orig].[UniqueKey])<=[tbl_Alias].[UniqueKey]))";") AS Total
FROM [tbl_Orig] AS [tbl_Alias]";"
my example:
SELECT (SELECT COUNT(tblStaff.RecID) AS AutonNum
FROM tblStaff
WHERE (((tblStaff.FirstName) <= tblT1.FirstName))";") AS Total, tblT1.FirstName
FROM tblStaff AS tblT1
ORDER BY tblT1.FirstName";"
my output:
Total FirstName
1 Dave
2 Duc
3 Ineke
4 Jyothi
5 Owen
6 Paul
7 Robert
8 Ros
9 Siobhan
10 Terry
11 Tom
OK?
Dave
the basics of the SQL:
SELECT [tbl_Orig].[UniqueKey], (SELECT Count([tbl_Orig].[FieldToCount]) AS AutoNum
FROM [tbl_Orig]
WHERE ((([tbl_Orig].[UniqueKey])<=[tbl_Alias].[UniqueKey]))";") AS Total
FROM [tbl_Orig] AS [tbl_Alias]";"
my example:
SELECT (SELECT COUNT(tblStaff.RecID) AS AutonNum
FROM tblStaff
WHERE (((tblStaff.FirstName) <= tblT1.FirstName))";") AS Total, tblT1.FirstName
FROM tblStaff AS tblT1
ORDER BY tblT1.FirstName";"
my output:
Total FirstName
1 Dave
2 Duc
3 Ineke
4 Jyothi
5 Owen
6 Paul
7 Robert
8 Ros
9 Siobhan
10 Terry
11 Tom
OK?
Dave
Last edited:
Cosmos75
Registered User.
- Local time
- Today, 14:35
- Joined
- Apr 22, 2002
- Messages
- 1,281
Keep in mind that field you are using in the WHERE clause for the subquery needs to have a unique value for every record. Say for example you had two people with the same first name, they would have the same count. For that reason, an autonumber field works best. Or concatenating several fields together.
Also, the 'count' may not match the sort order or your results unless you can somehow use a WHERE clause in your subquery that mimics your sort order.
Also, the 'count' may not match the sort order or your results unless you can somehow use a WHERE clause in your subquery that mimics your sort order.
