Solved

Structure of a table in microsoft access 2013

Posted on 2014-02-17
6
820 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

760 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

19 Experts available now in Live!

Get 1:1 Help Now