By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,053 Members | 1,595 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,053 IT Pros & Developers. It's quick & easy.

order by property in a table

P: n/a
i have a (hopefully) small problem. i have created a system where a user
enters customer information into a table through a form. this table has no
primary key. there are 9 fields on the form to be filled in, one of which is
a date field. right now, i've been using this db myself and have entered
only 10 days of sample data to check my calculations but i realized that
there may be times when i need to add a record that was missed that would no
longer be in date order. the table itself is not sorted at all. my concern
is that if i add data today that was from 2 weeks ago, if i need to edit that
data in another month or two or 20, i may have a hard time finding it because
it's 2 weeks out of place (this could be 100 or more records off). is there
a way to keep the records in my table sorted by date WITHOUT affecting the
forms and queries that i have already written? i'm reluctant to experiment
in this area for fear of screwing this whole db up (i've been working on it
for 6 weeks now). if somone needs to edit a record, i want them to be able
to get there by the date but if the records go in order by how they were
entered, then a late entry would be difficult to find. i hope i've explained
this sufficiently for you to understand what i mean. i'm sorry if it's long-
winded but i lack the experience with access to be sure my point is
understood and be brief at the same time. thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1
Nov 15 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
"ka******@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message
news:5769823411cfd@uwe...
i have a (hopefully) small problem. i have created a system where a user
enters customer information into a table through a form. this table has no primary key. there are 9 fields on the form to be filled in, one of which is a date field. right now, i've been using this db myself and have entered
only 10 days of sample data to check my calculations but i realized that
there may be times when i need to add a record that was missed that would no longer be in date order. the table itself is not sorted at all. my concern is that if i add data today that was from 2 weeks ago, if i need to edit that data in another month or two or 20, i may have a hard time finding it because it's 2 weeks out of place (this could be 100 or more records off). is there a way to keep the records in my table sorted by date WITHOUT affecting the
forms and queries that i have already written? i'm reluctant to experiment in this area for fear of screwing this whole db up (i've been working on it for 6 weeks now). if somone needs to edit a record, i want them to be able to get there by the date but if the records go in order by how they were
entered, then a late entry would be difficult to find. i hope i've explained this sufficiently for you to understand what i mean. i'm sorry if it's long- winded but i lack the experience with access to be sure my point is
understood and be brief at the same time. thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1


If the standard toolbar buttons are visible on your form, then put your
cursor in the date field and click on the A-Z button. That will sort the
entire file by date and move you to the first record. Then you can scroll to
the desired record as needed.
Fred Zuckerman
Nov 15 '05 #2

P: n/a
well, i'm thinking of the other people who are going to use this that don't
know anything about access. they are going to be clicking the record
navigation arrows in order to get to where they need to go so i just wanted
the records to be in date-order. is that possible based on the info in my
first post?
Fred Zuckerman wrote:
i have a (hopefully) small problem. i have created a system where a user
enters customer information into a table through a form. this table has no

[quoted text clipped - 15 lines]
winded but i lack the experience with access to be sure my point is
understood and be brief at the same time. thanks.


If the standard toolbar buttons are visible on your form, then put your
cursor in the date field and click on the A-Z button. That will sort the
entire file by date and move you to the first record. Then you can scroll to
the desired record as needed.
Fred Zuckerman


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1
Nov 15 '05 #3

P: n/a
On Tue, 15 Nov 2005 19:51:08 GMT, ka******@comcast.net via
AccessMonster.com wrote:
i have a (hopefully) small problem. i have created a system where a user
enters customer information into a table through a form. this table has no
primary key. there are 9 fields on the form to be filled in, one of which is
a date field. right now, i've been using this db myself and have entered
only 10 days of sample data to check my calculations but i realized that
there may be times when i need to add a record that was missed that would no
longer be in date order. the table itself is not sorted at all. my concern
is that if i add data today that was from 2 weeks ago, if i need to edit that
data in another month or two or 20, i may have a hard time finding it because
it's 2 weeks out of place (this could be 100 or more records off). is there
a way to keep the records in my table sorted by date WITHOUT affecting the
forms and queries that i have already written? i'm reluctant to experiment
in this area for fear of screwing this whole db up (i've been working on it
for 6 weeks now). if somone needs to edit a record, i want them to be able
to get there by the date but if the records go in order by how they were
entered, then a late entry would be difficult to find. i hope i've explained
this sufficiently for you to understand what i mean. i'm sorry if it's long-
winded but i lack the experience with access to be sure my point is
understood and be brief at the same time. thanks.


No. The records in the table are stored in no particular order, and
you cannot, in the table itself, order them, except by manually
selecting the field and clicking on the A-Z or Z-A sort tool button.
Besides, there is no valid reason for the user to ever look at the
table.

Create a new query with all of the fields in the table. Set the date
Field Sort to Ascending (or Descending, if that is more suitable for
you). Use this query as the record source for the form for both data
viewing, editing, and entry.
The actual records can be entered in any order, but the form will
always be sorted by date.

I'm not so sure that it is wise for you not to have a prime key field.
I suspect this will come back someday in the future and bite you.
You most probably will have several entries with the same date. How
will you be assured that the record you are modifying is the correct
one? Today there may be only one customer named John Smith, a very
common name. Next year it's not unlikely that there will be more than
one John Smith as a customer. Which is the one you want to edit?

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Nov 15 '05 #4

P: n/a
well, i would be able to tell by the date AND name, wouldn't i? i mean, i'm
thinking that if i go back 3 weeks worth of records to find one on a
particular date, i would find it (normally) in order with the other records
from the same date, and just look for the appropriate name (i keep coming
back to date because this is how we're tracking this data. this all to
create a database to track customer traffic, not customer information). i
find it extremely unlikely to have 2 customers in the same day that have the
same name that also have the same salesperson. this is the only time that
two records may be confused. the real issue is when the record navigation
arrows are used, is it going to go in order of the records as it was entered
or is there a way to make it go chronologically? i know there are better
ways to do what i'm suggesting (building a query as you suggest) but this is
not for me to do. i'm trying to think of scenarios that may come up for the
other, less technically inclined people who are going to be using this db.
they couldn't navigate the raw table to save their lives. i swear i'm not
kidding. some, i'm sure, can muddle through just like i am but most of these
people would rather keep paper records than try to figure out something like
this on their own.

fredg wrote:
i have a (hopefully) small problem. i have created a system where a user
enters customer information into a table through a form. this table has no

[quoted text clipped - 15 lines]
winded but i lack the experience with access to be sure my point is
understood and be brief at the same time. thanks.


No. The records in the table are stored in no particular order, and
you cannot, in the table itself, order them, except by manually
selecting the field and clicking on the A-Z or Z-A sort tool button.
Besides, there is no valid reason for the user to ever look at the
table.

Create a new query with all of the fields in the table. Set the date
Field Sort to Ascending (or Descending, if that is more suitable for
you). Use this query as the record source for the form for both data
viewing, editing, and entry.
The actual records can be entered in any order, but the form will
always be sorted by date.

I'm not so sure that it is wise for you not to have a prime key field.
I suspect this will come back someday in the future and bite you.
You most probably will have several entries with the same date. How
will you be assured that the record you are modifying is the correct
one? Today there may be only one customer named John Smith, a very
common name. Next year it's not unlikely that there will be more than
one John Smith as a customer. Which is the one you want to edit?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1
Nov 15 '05 #5

P: n/a

"ka******@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message
news:576aaaa5731e1@uwe...
the real issue is when the record navigation
arrows are used, is it going to go in order of the records as it was entered or is there a way to make it go chronologically?
As fredg indicated, if you do not use a query as the record source for the
form, you will never be able to reliably predict the order that the records
appear. Never. The order can change from adding new records, deleting
records, modifying them, compacting the database or the phase of the moon.

You don't need to create a saved query to control the order. Open the form
in design mode, go to properties, then Record Source. Create the query
there, it will remain integral to the form. Then the records will appear in
chronological order (or whatever order you specify). It will be totally
transparent to your users.

fredg wrote:
i have a (hopefully) small problem. i have created a system where a user enters customer information into a table through a form. this table
has no[quoted text clipped - 15 lines]
winded but i lack the experience with access to be sure my point is
understood and be brief at the same time. thanks.


No. The records in the table are stored in no particular order, and
you cannot, in the table itself, order them, except by manually
selecting the field and clicking on the A-Z or Z-A sort tool button.
Besides, there is no valid reason for the user to ever look at the
table.

Create a new query with all of the fields in the table. Set the date
Field Sort to Ascending (or Descending, if that is more suitable for
you). Use this query as the record source for the form for both data
viewing, editing, and entry.
The actual records can be entered in any order, but the form will
always be sorted by date.

I'm not so sure that it is wise for you not to have a prime key field.
I suspect this will come back someday in the future and bite you.
You most probably will have several entries with the same date. How
will you be assured that the record you are modifying is the correct
one? Today there may be only one customer named John Smith, a very
common name. Next year it's not unlikely that there will be more than
one John Smith as a customer. Which is the one you want to edit?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1


Nov 15 '05 #6

P: n/a
If you want, you can provide those users a "fully developed application" so
they can enter, or even better, choose a value for one or more fields, and
you locate that record and display it. Many of us got our start as
developers when we began to need to do things that required, or were simpler
with, VBA code.

There's another school of thought, however -- teach the users some basics
and give them access to some of Access' native features.

Larry Linson
Microsoft Access MVP
"ka******@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message
news:576a27becc4b0@uwe...
well, i'm thinking of the other people who are going to use this that
don't
know anything about access. they are going to be clicking the record
navigation arrows in order to get to where they need to go so i just
wanted
the records to be in date-order. is that possible based on the info in my
first post?
Fred Zuckerman wrote:
i have a (hopefully) small problem. i have created a system where a
user
enters customer information into a table through a form. this table has
no

[quoted text clipped - 15 lines]
winded but i lack the experience with access to be sure my point is
understood and be brief at the same time. thanks.


If the standard toolbar buttons are visible on your form, then put your
cursor in the date field and click on the A-Z button. That will sort the
entire file by date and move you to the first record. Then you can scroll
to
the desired record as needed.
Fred Zuckerman


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1

Nov 16 '05 #7

P: n/a
What others have been telling you, in everyday terms, is that "relational
database tables are, by definition, unordered" and that you have to use the
SQL or query that retrieves the records, or some property of the display (a
number of objects in Access have an OrderBy property for this purpose), to
put them in a particular order.

Of course, Access has to display the records in _some_ order, and that
happens to be what you are seeing. BUT, at least one person here has told
you, and truthfully, that you can't _count_ on the records being displayed
in that order, unless you specifically order them at time of retrieval.
Under different circumstances, at a different time, you may be surprised to
see them NOT in the same order you are used to, nor the order in which they
were entered, nor in date/time order.

Do you have the database you are developing separated into a front-end
(queries, reports, forms, macros, modules) and a back-end (tables,
relationships, and the data contained in the tables)? If so you should be
able just to make a copy to use for experimentation without endangering the
data that you have worked so hard to enter.

You are _assuming_ that the records are always going to be in the order
entered, which they are not. But, you can enforce an order on them when you
retrieve them (and that could be in Date/Time order, if you wish).

It is, almost certainly, not good application design to give users only
scrolling as a way to find particular data -- it is well worthwhile to
understand the users' problems and provide them a simple way to locate the
information they need.

Larry Linson
Microsoft Access MVP
"ka******@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message
news:576aaaa5731e1@uwe...
well, i would be able to tell by the date AND name, wouldn't i? i mean,
i'm
thinking that if i go back 3 weeks worth of records to find one on a
particular date, i would find it (normally) in order with the other
records
from the same date, and just look for the appropriate name (i keep coming
back to date because this is how we're tracking this data. this all to
create a database to track customer traffic, not customer information). i
find it extremely unlikely to have 2 customers in the same day that have
the
same name that also have the same salesperson. this is the only time that
two records may be confused. the real issue is when the record navigation
arrows are used, is it going to go in order of the records as it was
entered
or is there a way to make it go chronologically? i know there are better
ways to do what i'm suggesting (building a query as you suggest) but this
is
not for me to do. i'm trying to think of scenarios that may come up for
the
other, less technically inclined people who are going to be using this db.
they couldn't navigate the raw table to save their lives. i swear i'm not
kidding. some, i'm sure, can muddle through just like i am but most of
these
people would rather keep paper records than try to figure out something
like
this on their own.

fredg wrote:
i have a (hopefully) small problem. i have created a system where a
user
enters customer information into a table through a form. this table has
no

[quoted text clipped - 15 lines]
winded but i lack the experience with access to be sure my point is
understood and be brief at the same time. thanks.


No. The records in the table are stored in no particular order, and
you cannot, in the table itself, order them, except by manually
selecting the field and clicking on the A-Z or Z-A sort tool button.
Besides, there is no valid reason for the user to ever look at the
table.

Create a new query with all of the fields in the table. Set the date
Field Sort to Ascending (or Descending, if that is more suitable for
you). Use this query as the record source for the form for both data
viewing, editing, and entry.
The actual records can be entered in any order, but the form will
always be sorted by date.

I'm not so sure that it is wise for you not to have a prime key field.
I suspect this will come back someday in the future and bite you.
You most probably will have several entries with the same date. How
will you be assured that the record you are modifying is the correct
one? Today there may be only one customer named John Smith, a very
common name. Next year it's not unlikely that there will be more than
one John Smith as a customer. Which is the one you want to edit?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1

Nov 16 '05 #8

P: n/a
"ka******@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message
news:576a27becc4b0@uwe...
well, i'm thinking of the other people who are going to use this that don't know anything about access. they are going to be clicking the record
navigation arrows in order to get to where they need to go so i just wanted the records to be in date-order. is that possible based on the info in my
first post?
Fred Zuckerman wrote:
i have a (hopefully) small problem. i have created a system where a user enters customer information into a table through a form. this table
has no[quoted text clipped - 15 lines]
winded but i lack the experience with access to be sure my point is
understood and be brief at the same time. thanks.


If the standard toolbar buttons are visible on your form, then put your
cursor in the date field and click on the A-Z button. That will sort the
entire file by date and move you to the first record. Then you can scroll tothe desired record as needed.
Fred Zuckerman


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1


Then, I suppose that you can put code behind the OnCurrent event that
requeries the recordsource and moves back to the current record. But I
imagine that might be slow, depending on how many records in the source.
Fred

Nov 17 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.