I'm currently struggling a bit with an Incidents database. I have a main table for entry of incident details, linked one-to-many with an actions table via the incidents RefNo field.
What I would like to do is generate an Action code number for each action consisting of the RefNo and the Action number, as shown below. I can do this via concatenation of the RefNo and ActionID (autonumber primary key) but as shown, the action number must 'reset' itself for each new incident RefNo, and I can't work this out. Is it possible to do this via a query, or am I looking at using code?
What I would like to do is generate an Action code number for each action consisting of the RefNo and the Action number, as shown below. I can do this via concatenation of the RefNo and ActionID (autonumber primary key) but as shown, the action number must 'reset' itself for each new incident RefNo, and I can't work this out. Is it possible to do this via a query, or am I looking at using code?
| Book1 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | RefNo | ActionID | ActionNumber | ActionCode | ||
| 2 | 01/FA/03 | 1 | 1 | 01/FA/03-1 | ||
| 3 | 01/FA/03 | 2 | 2 | 01/FA/03-2 | ||
| 4 | 01/FA/03 | 3 | 3 | 01/FA/03-3 | ||
| 5 | 01/FA/03 | 4 | 4 | 01/FA/03-4 | ||
| 6 | 02/FA/03 | 5 | 1 | 02/FA/03-1 | ||
| 7 | 03/FA/03 | 6 | 1 | 03/FA/03-1 | ||
| 8 | 03/FA/03 | 7 | 2 | 03/FA/03-2 | ||
| 9 | 03/FA/03 | 8 | 3 | 03/FA/03-3 | ||
Sheet1 | ||||||
