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

Aha!  Thanks.
TNF

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

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