how can I add a sequential order number to my groups of records?

[efandango]

I have two tables:

tbl_Street_Joiner_Main
tbl_Street_Joiner


I want to add a new field that is a sequential record order number for the
'tbl_Street_Joiner' entries which are grouped by the 'Joiner_Title_ID' field
in the master table 'tbl_Street_Joiner_Main'

This new field will then become a Sort Order field for when I rearrange the
sort order within each group, which I hope to do 'manually' via some vba
code; but...

If the sort order is generated automatically via the SQL, will it still be
possible to change the sort order via code?


This is my current SQL:

SELECT tbl_Street_Joiner.Street, tbl_Street_Joiner.Street_Name_Joins_ID,
tbl_Street_Joiner.Joiner_Title_ID, tbl_Street_Joiner_Main.Run_No
FROM tbl_Street_Joiner_Main INNER JOIN tbl_Street_Joiner ON
tbl_Street_Joiner_Main.Joiner_Title_ID = tbl_Street_Joiner.Joiner_Title_ID;

 

[Rod Plastow]

Hi,

Consider adding an autonumber field to your child table and making it the
primary key. This autonumber will increment in the sequence that child
records are added.

However if you wish to have control over your sequence that may not be the
order of additions then include a sort key field. Use a parent-child /
master-sub form to display and manipulate your data, including the sort key.

I don't think you should be thinking of doing this in SQL, that is if I have
understood you correctly.

Rod