Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Structure of a table in microsoft access 2013

Posted on 2014-02-17
6
Medium Priority
?
837 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 668 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 40

Assisted Solution

by:PatHartman
PatHartman earned 668 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 43

Accepted Solution

by:
pcelba earned 664 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 58
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

885 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