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

I had table CompanyPhone where I needed Phone in the DLOOKUP

DCount("PhonePhoneTypeID","PhonePhoneTypes","PhonePhoneTypeID<=" &  >
B.PhonePhoneTypeID & " And FKCompanyPhoneID In (Select CompanyPhoneID  From
CompanyPhone Where FKCompanyID = " & A.FKCompanyID & ")")

Needed to be

DCount("PhonePhoneTypeID","PhonePhoneTypes","PhonePhoneTypeID<=" &  >
B.PhonePhoneTypeID & " And FKCompanyPhoneID In (Select CompanyPhoneID  >
>From Phone Where Phone.FKCompanyID = " & A.FKCompanyID & ")" )


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tina Norris
Fields
Sent: Thursday, January 01, 2015 1:38 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Getting an Item number (Sequence) in a query

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
>

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com