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

Ah, saw my error.

 

I am amazed frankly that this works.

 

SELECT 

A.FKCompanyID as CompanyID, B.PhonePhoneTypeID, A.Phone, C.PhoneType,
DCount("PhonePhoneTypeID","PhonePhoneTypes","PhonePhoneTypeID<=" &
B.PhonePhoneTypeID & " And FKCompanyPhoneID In (Select CompanyPhoneID From
Phone Where Phone.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

 

 

 

 

From: Bill Benson [mailto:bensonforums at gmail.com] 
Sent: Thursday, January 01, 2015 12:50 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: 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

 

 

CompanyPhone (A)

    CompanyTypeID

    CompanyType

 

PhonePhoneType (B)

    PhonePhoneTypeID

    FKCompanyPhoneID

    FKPhoneTypeID

 

PhoneType (C)

    PhoneTypeID

    PhoneType