Solved

Structure of a table in microsoft access 2013

Posted on 2014-02-17
6
823 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
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 34

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 41

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now