Sarah didn't include her original SQL in her reply, so I had to go hunting for it.
SELECT Patient.[last name] & "," & Patient.[first name] AS [Registrants Name], insurance.iname AS MLTC, Patient.[start date] AS [Start Date], Patient.[end date] AS [End Date], Patient.recertdate AS [Recertification Date]
FROM (Patient LEFT JOIN shift ON Patient.[preferred shift] = shift.[Shift #]) INNER JOIN insurance ON Patient.insrcd = insurance.insrcd
WHERE ....
ORDER BY Patient.[last name], Patient.[first name], Patient.mi, Patient.[patient id];
The count has to be using the same sequence as the specified ORDER BY. Try this:
SELECT DCount("*", "Patient", "[Last Name] & [First Name] & [mi] <= """ & [Last Name] & [First Name] & [mi] & """") As RowID, Patient.[last name] & "," & Patient.[first name] AS [Registrants Name], insurance.iname AS MLTC, Patient.[start date] AS [Start Date], Patient.[end date] AS [End Date], Patient.recertdate AS [Recertification Date]
FROM (Patient LEFT JOIN shift ON Patient.[preferred shift] = shift.[Shift #]) INNER JOIN insurance ON Patient.insrcd = insurance.insrcd
WHERE ....
ORDER BY Patient.[last name], Patient.[first name], Patient.mi, Patient.[patient id];
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of wrmosca@comcast.net
Sent: Thursday, November 21, 2013 10:33 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: RE: RE: [MS_AccessPros] Add Row Number to Query
Sarah
I do it this way:
SELECT Customers.CustomerID,
Customers.CompanyName,
(SELECT Count(CustomerID)
FROM Customers c
WHERE c.CustomerID <= Customers.CustomerID
) AS RowCt
FROM Customers
Bill Mosca
---In MS_Access_Professionals@yahoogroups.com, <sarahk@schemesoftware.com> wrote:
John,
It is being output to an Excel file, not a report. So how else can I do it. After I use your corrected version of the Dcount command , all the rows result in '0'.
Sarah
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote:
Sarah-
It's not common to use this sort of expression in a query. If you want to have line numbers in a report, use your original query and add a Text Box to the Detail section. Set the Control Source to =1 and the Running Sum property to Yes.
But if you want to do it directly in your query, you need to fix your SQL. Like this:
SELECT DCount("[Registrants Name]","mltc1a","[Registrants Name] <= """ & [Registrants Name] & """) AS row, Patient.[last name] & "," & Patient.[first name] AS [Registrants Name], etc.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of sarahk@...
Sent: Thursday, November 21, 2013 8:06 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Add Row Number to Query
How can I add a Sequential Row number to my Query?.
My current Query looks like this:
SELECT Patient.[last name] & "," & Patient.[first name] AS [Registrants Name], insurance.iname AS MLTC, Patient.[start date] AS [Start Date], Patient.[end date] AS [End Date], Patient.recertdate AS [Recertification Date]
FROM (Patient LEFT JOIN shift ON Patient.[preferred shift] = shift.[Shift #]) INNER JOIN insurance ON Patient.insrcd = insurance.insrcd
WHERE ....
ORDER BY Patient.[last name], Patient.[first name], Patient.mi, Patient.[patient id];
The above works fine, but now I want to add a sequential row number to each line starting at 1 and increase it by 1 for each line.
So I changed it to:
SELECT DCount("[Registrants Name]","mltc1a","[Registrants Name] <= " & [Registrants Name]) AS row, Patient.[last name] & "," & Patient.[first name] AS [Registrants Name], etc.
But I get the following error:
Syntax error (comma) in query expression '[Registrants Name] <= John,Doe'.
I thought the cause might be the [registrant name] because it contains a comma.
Is there any way to do this?.
As always thanks in advance for all your help.
Sarah
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (7) |
Tidak ada komentar:
Posting Komentar