Solved

Structure of a table in microsoft access 2013

Posted on 2014-02-17
6
828 Views
Last Modified: 2014-02-20
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
Comment
Question by:bill201
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 167 total points
ID: 39864781
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
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 167 total points
ID: 39864835
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
 
LVL 42

Accepted Solution

by:
pcelba earned 166 total points
ID: 39865152
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:bill201
ID: 39872003
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
 
LVL 57
ID: 39872989
<<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
 

Author Comment

by:bill201
ID: 39875522
thanks for your excellent explanation.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question