Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 839
  • Last Modified:

Structure of a table in microsoft access 2013

hi

i have a  sub table on microsoft access 2013 (that have a primary key and a foreign key ), and i want to change that order of the records to  the records on the parent  table (in other words i want  to match the order of the primary key to the order of the foreign key), so i delete the primary key and save the table and then i sort the table by the foreign key, and then i re-add a field that it's a auto number and i set it for the  primary key but then the order of the records was like the old order like  it's was before i deleted  the primary key, why is that, is there some place on the database that  saves the order of the records even after  i deleted the primary key ?
0
bill201
Asked:
bill201
3 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
There's no reason to do this.

You order the records when you go to use them in the way that you need them.

What is it your trying to accomplish?

Jim.
0
 
PatHartmanCommented:
Tables and queries are by definition unordered sets.  The only way to give them a defined order is to use an Order By cause in a query.

Access fools you into thinking it keeps records in primary key order but that is an illusion.  When a database is compacted, Access copies the entire database and rewrites all the tables in primary key order.  So, for a brief moment in time, the physical and logical sequence will be the same.  However, as soon as you start adding/deleting/updating, records will be placed wherever the database engine finds it convenient.  Usually people don't scroll through every record of a large table so they don't see the issue and just assume that some order is being maintained - it isn't.  ALWAYS use a query to retrieve data and ALWAYS include an Order By if you care about the sequence.  Except for reports --  Access is smarter than we are (at least it thinks so) and so it rewrites your RecordSource query for a report and removes any column not bound to a control as well as any Order By.  It does retain your Where clause though.  So, for reports, you MUST use the Sorting and grouping options to specify record order.
0
 
pcelbaCommented:
You have three options:

1) MS SQL Server allows to create clustered index which ensures the physical record order in a table. It is useful for speed optimization sometimes.

2) MS Visual FoxPro or other xBase clone. Each record of the fixed size has given the position in a DBF file. You may reorder records physically in the way you need. Each record is also accessible by its record number.

3) Add one more column, e.g. cOrder, to your table and define the order in this column.

But I agree to Jim and Pat. The physical record order is not as important in properly designed data and you should use ORDER BY clause in SQL commands to achieve the output order requested. xBase architecture started without SQL and the physical record order was more important in the past times.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
bill201Author Commented:
thanks all of you for your excellent answers. The reason I asked was Mainly curiosity to understand behind the scenes  of the tables, why It is ordered by a certain order.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<The reason I asked was Mainly curiosity to understand behind the scenes  of the tables, why It is ordered by a certain order. >>

  The answer to your question is more how a specific RDBMS manages records internally.

  JET for example lays records down in the order you enter them, but may or may not compact them into PK order based on a registry setting (the default is to compact in PK order, but you can turn it off).   This is close to the clustered index concept, but it's not quite.

 In other RDBMs's, records are actually re-ordered as they are inserted.

Jim.
0
 
bill201Author Commented:
thanks for your excellent explanation.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now