counting sessions

[jejeFT]

Hello,

Here is our problem:

id port connexion | idSession
1 20 1005 | 1
2 20 1005 | 1
3 20 1009 | 2
4 20 1009 | 2
5 20 1009 | 2
6 21 1009 | 3
7 21 1009 | 3

The first column is auto-incremented (primary key).
We would like the last column to be a counter which identifies each session regarding a port number and a connexion number.

Is there a SQL or Access function which would allow us to do that?

Thanks a lot.

 

[Guus2005]

No, you have to write it yourself. It will be a combination of queries and VBA code.

 

[dallr]

You can do this in one query but i decided to do it in 3 just so you can understand the steps. You can always put it into one query afterwards if you wish.

Step 1.
Create a query that will show the first time a port and a connexion was created as a record. I called this query qry_FirstSessions

SELECT Min(YourTable.ID) AS MinOfPK
     , YourTable.Port
     , YourTable.Connexion
FROM YourTable
GROUP BY YourTable.Port, YourTable.Connexion

STEP 2.
Create a new query from qry_FirstSessions to give a count starting from one.This is done to get the sequence numbers for each record grouped record above.

SELECT qry_FirstSessions.MinOfPK
     , qry_FirstSessions.Port
     , qry_FirstSessions.Connexion
     , (SELECT COUNT(MinofPK) 
      FROM qry_FirstSessions FS 
     WHERE FS.Port<= qry_FirstSessions.Port 
       AND FS.connexion <= qry_FirstSessions.Connexion) AS SessionSequenceNo
FROM qry_FirstSessions;

STEP 3
Now add the query in step 2 which i called qry_SessionsSequenceNumber to your base table with i called Yourtable.

SELECT YourTable.ID
     , YourTable.Port
     , YourTable.Connexion
     , qry_SessionsSequenceNumber.SessionSequenceNo
FROM qry_SessionsSequenceNumber 
   INNER JOIN YourTable 
    ON (qry_SessionsSequenceNumber.Connexion = YourTable.Connexion) 
     AND (qry_SessionsSequenceNumber.Port = YourTable.Port);

Look at the attached db for a sample. Simply run the query qry_FinalQuery.

Dallr