How can I update an MS Access table with a sequential number based on a record date?

[simon penny]

I have a table in which I record referral records. I have a second table in which I record service records. One referral can have many service records attached. The tables are linked by referral ID - the service table incudes the referal ID field. A representation of the service table follows:

Col1 Col2 Col3 Col4
1122 160 01/01/2001 1
1256 160 01/10/2001 2
1899 160 01/06/2004 3
1999 160 02/07/2005 4
2150 160 05/08/2006 5
1200 100 1/08/1999 1
1466 100 1/07/2003 2

Where:

Col1 = ServiceRecordID (AutoNum)
Col2 = ReferralD
Col3 = ServiceStartDate
Col4 = AuditServiceRecordCount

Each time a new service record is created it is assigned a sequencial 'record count' (1,2,3,4,5 . . . Etc). This allows me to identify the order each service relating to a specific referral occurred.

The database allows for service records to be deleted (using a button connected to a delete query). What I need the database to do is automatically update the record counter when a deltion takes place. . . So if record 3 or 5 is deleted, the database automatically updates record 4 to 3 and record 5 to 4.

I have looked at some other answers on in this forum and have come up with these two queries. . .

Query # 1

SELECT ServiceTable.ServiceRecordID, ServiceTable_1.ServiceRecordID, ServiceTable_1.ReferralID, ServiceTable_1.ServiceStartDate, ServiceTable_1.ServiceEndDate, ServiceTable_1.AuditServiceRecordCount
FROM (ReferralTable INNER JOIN ServiceTable ON ReferralTable.ReferralID = ServiceTable.ReferralID) INNER JOIN ServiceTable AS ServiceTable_1 ON ReferralTable.ReferralID = ServiceTable_1.ReferralID
WHERE (((ServiceTable.ServiceRecordID)=[Forms]![DeleteRecordServiceForm]![ServiceRecordID]))
ORDER BY ServiceTable_1.ServiceStartDate DESC;
 

Query # 2

UPDATE DeleteServiceRecordCountUpdateQuery1 SET DeleteServiceRecordCountUpdateQuery1.AuditServiceRecordCount = Dcount(1,"DeleteServiceRecordCountUpdateQuery1","ServiceTable_1.ServiceRecordID<>[Forms]![DeleteRecordServiceForm]![ServiceRecordID] And ServiceStartDate <= [ServiceStartDate]")
WHERE (((DeleteServiceRecordCountUpdateQuery1.ServiceTable_1.ServiceRecordID)<>[Forms]![DeleteRecordServiceForm]![ServiceRecordID]));
 

Query 1 pulls out all service records that are attached to the same referral as the service being deleted

Query 2 updates all service records from query 1, except for the one being deleted . . . And should assign a sequential number to each of the other. However it doesn’t work. It just assigns a dcount value (i.e if two records will be left, both will be given a count of 2 rather than 1 and 2 based on date value).

Can anyone help? I ideally want to use update queries to do this . . . I think the problem may be with the dcount element in query 2.

Many thanks!!

[NeoPa]

Assuming you have a form ([DeleteRecordServiceForm]) which has controls reflecting both the [ReferralID] and the [AuditServiceRecordCount] of the record that has been deleted (for now we will assume they have the same name as the fields they represent, except with "txt" prepended), then after the record has been deleted, and in the same Event Procedure, code similar to the following could be run, which will execute the simple SQL to do the job :

Dim strSQL As String
 
'Code to delete the record
 
strSQL = "UPDATE [ServiceTable] " & _
         "SET    [AuditServiceRecordCount] = [AuditServiceRecordCount] - 1 " & _
         "WHERE  (([ReferralID] = " & Me.txtReferralID & ")" & _
         "  AND   ([AuditServiceRecordCount] > " & Me.txtAuditServiceRecordCount & "))"
Call DoCmd.SetWarnings(False)
Call DoCmd.RunSQL(strSQL)
Call DoCmd.SetWarnings(False)

[NeoPa]

I assume Simon Penny is a pseudonym of James Jones as this exact same question was posted earlier and answered. As you are now posting from a registered account I deleted the anonymous one and moved my answer across to here.

Please ensure you don't double-post any questions in future.