Number Records Returned in Query 1

[jgarry]

Hi, thanks in advace,

I have a simple access query showing last and first name with a simple where clause status and sex (Gender), this is not my table, I would like to show the number for each record. Not the Record Number but the number in the Querey.

an example would be Smith, Paul 1
Smith, Zeek 2
Zook, Able 3

etc.

this is just a number to display with the data from the query and nothing that has to be saved.

SELECT Trim([Last name]) & ", " & Trim([first name]) AS EmpName
FROM Personal
WHERE (((Personal.Status)<>"T") AND ((Personal.Sex)=[Forms]![frmEEO2]![intSex]))
ORDER BY Trim([Last name]) & ", " & Trim([first name]);


Thanks

Jim Garry

 

[CautionMP]

jgarry,

SELECT Trim([Last name]) & ", " & Trim([first name]) AS EmpName, [b](SELECT Count(P2.[First Name]) AS RecNo FROM Personal AS P2 WHERE (((P2.Status)<>"T") AND ((P2.Sex)=[Forms]![frmEEO2]![intSex]) AND ((Trim([Last name]) & ", " & Trim([first name]))<=Trim(Personal.[Last name]) & ", " & Trim(Personal.[first name])));) AS RecNum[/b]
FROM Personal
WHERE (((Personal.Status)<>"T") AND ((Personal.Sex)=[Forms]![frmEEO2]![intSex]))
ORDER BY Trim([Last name]) & ", " & Trim([first name]);

Hopet his helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[jgarry]

Thanks for the answer.

What Im seeing is almost( not sure of the terms) a double query in one. The first part is selecting each person and doing a count of the record placing that count into the RecNo field. the second is P2 is looking at the pramaters in the SQL statement. am I on track here.

Again that you.

Jim Garry

 

[marcus101]

Hello:

Weird. I tried running a variation on this code and I got a total (sum) of numbers rather than 1, 2, 3, etc for my RCount customized field-that-uses-an-SQL-query-to-populate.

Here's my revised SQL code:

SELECT (SELECT Count(G2.[LoanFile]) AS RecNo FROM [Clients] AS G2 WHERE((G2.Status)='Cancelled')) AS RCount, [Clients].LoanFile, [Clients].AppLast, [Clients].Status
FROM [Clients]
WHERE ((([Clients].Status)="Cancelled"));

I even tried using DISTINCT but no luck there either. Am I missing something?

Thanks,


marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[CautionMP]

jgarry,
I believe the term is subquery.
marcus101,
You got the sum because you need the order the recordsets then count all the records less than or equal to the current record.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)