Thank You Chris. Now all working.

Please see updated code.

Oh and thanks for the obvious error that you found 🙂

SELECT [TenancyReference]

,[PlaceReference]

,[PersonReference]

,[Person Name]

,[NameOnTenancy]

,[TenantOrOccupant]

,[TenancyType]

,[TenancyCategory]

,[TenancyStartDate]

,[Current Balance]

,[EstateCode]

,[LocationType]

,[BuildingType]

,[ManagementType]

,[Scheme]

,[Patch / Arrears Officer]

,[NumberOfBedrooms]

,[Address]

,[PostCode]

,[Age]

,[Gender]

,[OriginCode]

,[Vulnerable]

,[LoggedComplaint0910]

,[LoggedComplaint1011]

,[MethodOfPayment]

,[Age Range] = CASE

...

Just a bit more information the [Arrears] is calculated via a CASE statement which uses the TEN.[curr-balance].

The TEN.[curr-balance] is held as a data type of Money.

The way I have got around this is by changing the object owner of the views in question, this meant that the user could play about with these particular views...

Having a nightmare with this -

The user is now only has a database role of public.

And I have granted the following permissions for this view -

06Jobs_RT

GRANT SELECT...

Running the following -

GRANT ALTER on 06Jobs_RT to richard2

GRANT SELECT on 06Jobs_RT to richard2

GRANT VIEW on 06Jobs_RT to richard2

But get the following errors -

Server: Msg 156, Level 15,...

I have now run the query over SQL2005 and it works how I want it to -

See sample below -

Tenancy Reference Arrears ...

Hi there,

Thanks for taking the time to responding -

Alas, I get the following error when I run your code -

Server: Msg 195, Level 15, State 10,...

What I just posted still doesn't work.

Can someone please explain how I in corporate a subquery into the FROM syntax or create a derived table

Hi Wayne, your help is appreaciated, but I have no idea how to go about doing what you suggest.

I have now got the query working how I would want it -

SELECT MAX (dbo.[IH_RE-TENANCY].[tncy-start])

,dbo.[IH_IH-LOCATION].[place-ref]

--,dbo.[IH_RE-TENANCY].[gross-rent]

from dbo.[IH_RE-TENANCY]

INNER JOIN

dbo.[IH_RE-TNCY-PLACE]

ON dbo.[IH_RE-TENANCY].[tncy-sys-ref] = dbo.[IH_RE-TNCY-PLACE].[tncy-sys-ref]

INNER JOIN

dbo.[IH_IH-LOCATION]

ON dbo.[IH_RE-TNCY-PLACE].[place-ref] = dbo.[IH_IH-LOCATION].[place-ref]

Where dbo.[IH_IH-LOCATION].[location-sts] not in...

SELECT dbo.[IH_RE-TENANCY].[tenancy-ref]

,dbo.[IH_IH-LOCATION].[place-ref]

,MAX(dbo.[IH_RE-TENANCY].[tncy-start])

from dbo.[IH_RE-TENANCY]

INNER JOIN

dbo.[IH_RE-TNCY-PLACE]

ON dbo.[IH_RE-TENANCY].[tncy-sys-ref] = dbo.[IH_RE-TNCY-PLACE].[tncy-sys-ref]

INNER JOIN

dbo.[IH_IH-LOCATION]

ON dbo.[IH_RE-TNCY-PLACE].[place-ref] = dbo.[IH_IH-LOCATION].[place-ref]

where dbo.[IH_IH-LOCATION].[place-ref] = '2010020080'

ORDER BY dbo.[IH_IH-LOCATION].[place-ref]

GROUP BY dbo.[IH_IH-LOCATION].[place-ref]

Throwing back error message -

Server: Msg 156, Level 15, State...

Following on from this I was wondering if you could do the following -

I am set to save this SQL Code as a View.

This View will be attached to...

Many Thanks.

I have played about with the code and this is now working.

Final Code as below -

SELECT TEN.[tenancy-ref] AS TenancyReference

, LOC.[place-ref] AS PlaceReference

, PER.[PERSON-REF] AS...

Final Code - Working exactly how I want it -

Thanks

SELECT [Communication Reference],

[Created Date],

[Main Code],

[Sub Code],

[Current Officer Code],

[Person Title],

[Forename],

[Surname],

[Date Of Birth],

[Age],

[Age Range] = CASE

...

Oh I see on the alias point of thing now.

Makes sense.

I'll remove the * and post my final statement once I have it working fully to the way I want...