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

Tina Norris Fields tinanfields at torchlake.com
Thu Jan 1 12:37:40 CST 2015


And, the error was?  Thanks for posting.
TNF

Tina Norris Fields
tinanfields-at-torchlake-dot-com
231-322-2787

On 1/1/2015 12:57 PM, Bill Benson wrote:
> 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
>



More information about the AccessD mailing list