D'oh! There's a bug with the MAX.

-- Site data examples

pkSiteID, siteID, fkSectorID

...

11, TOR8, 1

12, TOR9, 1

13, TOR10, 1

14, TOR10, 1 -- The max doesn't sort the data with...

Cool! I made everything work.

The function :

ALTER FUNCTION dbo.GetLastSiteNumber

(

@sectorID int

)

RETURNS int

AS

BEGIN

DECLARE @lastSite nvarchar(15)

DECLARE @lastNumber int

SELECT @lastSite = MAX (siteID)

FROM sites

WHERE (fkSectorId = @sectorID)

IF (@lastSite IS NOT NULL)

SET @lastNumber...

Hi Gail,

I can't compile, I get following error :

Column "sites.siteID" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP...

Instead of using the pkSiteID as the number to concatenate the siteID I would like to increment the number from the last site from the same sector.

Example :

-- Site data...

Can someone tell me what's wrong with this syntax? It tells that there's a syntax error near SELECT...

CREATE PROCEDURE dbo.getLastSiteNumber

(

@sectorID int = 5,

@lastSite nvarchar(15),

@lastNumber int

)

AS

/* SET NOCOUNT ON */

SET @lastSite...

Cool it works like a charm!

Now let's take that a step further. Let say I would like to generate the site with the last number from the same sector.

Examples...

CREATE TABLE sectors

(

pkSectorID INT IDENTITY(1,1) PRIMARY KEY,

sectorName NVARCHAR(100) NOT NULL,

sectorCode NVARCHAR(5) NOT NULL,

...

)

CREATE TABLE sites

(

pkSiteID...

For now I'm using Filemaker to develop at my job. I've been doing so for the last two years. This DBMS is quite useful to develop rapid solutions.

The...