How can I create a value based on values from multiple rows?

[Brendan Donlin]

For each unique 'ScopeID' I would like to concatenate the 'Origin' and 'Destination' but not repeating the 'Origin' based on the previous row's 'Destination'. Example for 'ScopeID' 135, I would like to return the 'Scope' as 1875-300003-BOS-ABN and not 1875-300003-300003-BOS-BOS-ABN.

I thought that I may be able to use a loop to return all of the values for each 'Origin' and 'Destination' per each 'ScopeID' ordered by the 'StopSequence'.

Any nudges in the right direction would be great and very much appreciated.

Recordset to evaluate:

ScopeID  StopSequence  OriginType  Origin  DestinationType  Destination  Miles  Days  Hours
135          10            S         1875          S           300003      421    1    20
135          20            S       300003          H              BOS     2992    5    23
135          30            H          BOS          H              ABN      192    1     1
136          10            S         1875          S           300003      421    1    20
136          20            S       300003          H              SEA     1156    2     7
136          30            H          SEA          H              ACR     2379    4    18

Example of values that I would like to return:

ScopeID  Scope
135      1875-300003-BOS-ABN
136      1875-300003-SEA-ACR

[NeoPa]

Let me see if I can explain that a little better for you before we start.

You have records in a table which reflect, for each [ScopeID], the individula elements of some type of journey or process. Each record indicates the [ScopeID] as well as the start and end positions of that particular element (identified by [StopSequence]).

What you'd like to see is an output record for each [ScopeID] which includes a [Scope] value which reflects each position ([Origin]/[Destination]) of the process, in the correct order, and only once for each. Is that an accurate reflection of your question.

[Brendan Donlin]

Hi NeoPa

Thank you for your reply.

Yes, your explanation is spot on.

[hype261]

This is how I probably would tackle the problem. I would have a query to get the final product. findScope is a custom function that takes the ScopeID.

SELECT ScopeID, findScope(ScopeID) 
FROM SomeTbl;

You would have another query that looks like this that I named qry_GetScope

PARAMETERS @ScopeID Long;
SELECT Origin, Destination FROM someTbl
WHERE ScopeID = @ScopeID
ORDER BY StopSequence;

And your custom function would look something like this.

public Function findScope(scopeID as Long) as String
Dim qry as DAO.QryDef
Dim rcd as DAO.Recordset
dim str as string

set qry = CurrentDB.QryDefs("qry_GetScope")
qry.Parameters("@ScopeID") = scopeID

set rcd = qry.OpenRecordset

'Now loop through all your records appending the Origin and Desination to the string

findScope = str
'Don't forget to clean up the memory

End function

[NeoPa]

I need to ask another question. I should probably have seen this earlier :-(

Is it always true that the first element of the [ScopeID] sequence will have a [StopSequence] = 10?

If not, is there something that identifies the first record in the sequence easily?