About Geoff Expertise I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.
See my
website for example apps and downloads
Experience I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.
Commercial database design and development work undertaken.
Question I build a database using MS Access, When I delete a record, My Numbers get out of squence order. Please tell me how to fix it.
Thanks In Adance
Carter
Answer Hello Carter,
Databases in general and tables in particular are not really designed for keeping numbers in sequence, but they can be forced by using a VBA procedure.
You should ask yourself what is behind the sequence, is it the alphabetical sorting of a Name, or perhaps a Date the record was created, or something else, for instance an autonumber field (primary key).
To present the data in sequence, then becomes very easy, use a query based on the table(s) required, then for the fields concerned, use the 'Sort' query property to get the correct sequence (in SQL this is the ORDER BY phrase).
If you have to have a sequence number, without any gaps, then you will have to have a separate, non-primary key field of LONG data type, and populate this using a VBA procedure.
The sort of procedure to then do this will use a recordset on the table and step through the all records in the table and set the sequence number.
If you need to go this route, please come back, but with details of the table and the field(s) that you need to sort it by...
