[AccessD] Getting an Item number (Sequence) in a query

Outlook sent the below prematurely. I will follow in a minute with the
actual schema that matters.

 

 

From: Bill Benson [mailto:bensonforums at gmail.com] 
Sent: Thursday, January 01, 2015 12:46 PM
To: 'Access Developers discussion and problem solving'
Subject: Getting an Item number (Sequence) in a query

 

I want to create an item number column in a query via DCOUNT.

 

The syntax below is "correct" in that the query runs; however the result in
the Item column is #Error.

 

Can it be fixed? My schema appears below:

 

SELECT 

B.PhonePhoneTypeID, A.Phone, C.PhoneType,
DCount("PhonePhoneTypeID","PhonePhoneTypes","PhonePhoneTypeID<=" &
B.PhonePhoneTypeID & " And FKCompanyPhoneID In (Select CompanyPhoneID From
CompanyPhone Where FKCompanyID = " & A.FKCompanyID&")") as Item

FROM PhoneType as C RIGHT JOIN (Phone as A INNER JOIN PhonePhoneTypes as B
ON A.CompanyPhoneID = B.FKCompanyPhoneID) ON C.PhoneTypeID = B.FKPhoneTypeID

order by B.PhonePhoneTypeID ASC

 

 

Phone (A)


CompanyID


Company


FKCompanyTypeID


Address


Deleted

 

CompanyPhone (A)