Show last year first, but keep data sorted cronologically

For each customer I have a summary of sales activity for each year. I have a
different summary page for each year of customer activity. When I go to the
summary form I want to show the most recent year first. Then, if the user
wants to see a summary for a prior year, they use the navigation bar.

Of course I can sort the data descending by year, and that will show the
most recent (highest) year first. However, it is not intuitive to go forward
on the navigation bar from 2004 to 2003.

I'd like to store the data in chronological order, but show the last record
(the highest year) first. Then the user can go BACKWARDS in the file to see
results from earlier years.

Data isn't stored in any order: tables are "bags of data", where records go
wherever they fit. The only way to be sure of the order of your records is
to use a query with the appropiate ORDER BY clause.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Bob Richardson" wrote in message
...
For each customer I have a summary of sales activity for each year. I have
a
different summary page for each year of customer activity. When I go to
the
summary form I want to show the most recent year first. Then, if the user
wants to see a summary for a prior year, they use the navigation bar.

Of course I can sort the data descending by year, and that will show the
most recent (highest) year first. However, it is not intuitive to go
forward
on the navigation bar from 2004 to 2003.

I'd like to store the data in chronological order, but show the last
record
(the highest year) first. Then the user can go BACKWARDS in the file to
see
results from earlier years.

Yes, I want to order the table by year, ASCENDING. The problem is, I want
the user to be looking at the most recent, (LAST YEAR) when the form is
first opened. That would mean that the navigation bar would indicate that
the user is at the end, and would have to click the -- arrow to see another
record (last year's record).

"Douglas J. Steele" wrote in message
...
Data isn't stored in any order: tables are "bags of data", where records
go
wherever they fit. The only way to be sure of the order of your records is
to use a query with the appropiate ORDER BY clause.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Bob Richardson" wrote in message
...
For each customer I have a summary of sales activity for each year. I
have
a
different summary page for each year of customer activity. When I go to
the
summary form I want to show the most recent year first. Then, if the
user
wants to see a summary for a prior year, they use the navigation bar.

Of course I can sort the data descending by year, and that will show the
most recent (highest) year first. However, it is not intuitive to go
forward
on the navigation bar from 2004 to 2003.

I'd like to store the data in chronological order, but show the last
record
(the highest year) first. Then the user can go BACKWARDS in the file to
see
results from earlier years.

On Wed, 21 Jul 2004 11:02:38 -0700, "Bob Richardson"
wrote:

I'd like to store the data in chronological order, but show the last record
(the highest year) first. Then the user can go BACKWARDS in the file to see
results from earlier years.

The data is *STORED* in a table in no particular order at all - a
table is, by design, an unordered heap of data.

What you can do is *DISPLAY* the data in order in a Query. To do so,
include the date field in the query, sorted ascending; but before it
(to the left of it) in the query grid, include a calculated field

SaleYear: Year([datefield])

Sort this field descending; as it is to the left in the grid, the
records will be sorted first descending by year, and within each year
ascending by date.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

There is only one record for each year. The query sorts the data by year
ascending. When a user firsts looks at the results for this customer, I want
them to see the record for the most recent year, i.e. the last one for that
customer. Since the current record would be at the end, the - button would
be disabled. If the user wanted to see records from an earlier year, I want
them to click the - button in the navigation bar. What's the best way to
achieve this result?

What I don't want to do is sort the query by year descending, and then have
the user click the - button to go back in time.


"John Vinson" wrote in message
...
On Wed, 21 Jul 2004 11:02:38 -0700, "Bob Richardson"
wrote:

I'd like to store the data in chronological order, but show the last
record
(the highest year) first. Then the user can go BACKWARDS in the file to
see
results from earlier years.

The data is *STORED* in a table in no particular order at all - a
table is, by design, an unordered heap of data.

What you can do is *DISPLAY* the data in order in a Query. To do so,
include the date field in the query, sorted ascending; but before it
(to the left of it) in the query grid, include a calculated field

SaleYear: Year([datefield])

Sort this field descending; as it is to the left in the grid, the
records will be sorted first descending by year, and within each year
ascending by date.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

On Wed, 21 Jul 2004 16:35:21 -0800, "Bob Richardson"
wrote:

There is only one record for each year. The query sorts the data by year
ascending. When a user firsts looks at the results for this customer, I want
them to see the record for the most recent year, i.e. the last one for that
customer.

Ok, I did misunderstand!

Try this: in the Form's Load event put code

Private Sub Form_Load(Cancel as Integer)
DoCmd.RunCommand acCmdGoToRecord, acLast
End Sub

Base the form on a query sorting ascending; this will put you at the
last record instead of the first.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Bob Richardson wrote:
For each customer I have a summary of sales activity for each year. I
have a different summary page for each year of customer activity.
When I go to the summary form I want to show the most recent year
first. Then, if the user wants to see a summary for a prior year,
they use the navigation bar.

Of course I can sort the data descending by year, and that will show
the most recent (highest) year first. However, it is not intuitive to
go forward on the navigation bar from 2004 to 2003.

I'd like to store the data in chronological order, but show the last
record (the highest year) first. Then the user can go BACKWARDS in
the file to see results from earlier years.

Bob,

Easier way.
Create your own navigation buttons. Lie about which is which. g

--
Harvey Thompson
Bloomfield, Connecticut USA

This looks like the right solution, but I can't get it to compile. I'm using
Access 2003.

First I get the error message "Procedure declaration does not match
description of event..." When I remove "Cancel as Integer" I then get a
compile error on
DoCmd.RunCommand acCmdGoToRecord, acLast

for the wrong number of arguments on RunCommand.

"John Vinson" wrote in message
...
On Wed, 21 Jul 2004 16:35:21 -0800, "Bob Richardson"
wrote:

There is only one record for each year. The query sorts the data by year
ascending. When a user firsts looks at the results for this customer, I
want
them to see the record for the most recent year, i.e. the last one for
that
customer.

Ok, I did misunderstand!

Try this: in the Form's Load event put code

Private Sub Form_Load(Cancel as Integer)
DoCmd.RunCommand acCmdGoToRecord, acLast
End Sub

Base the form on a query sorting ascending; this will put you at the
last record instead of the first.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

I think John may have been typing a little too quickly.

Try

Private Sub Form_Load()
DoCmd.GoToRecord , , acLast
End Sub

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)


"Bob Richardson" wrote in message
...
This looks like the right solution, but I can't get it to compile. I'm
using
Access 2003.

First I get the error message "Procedure declaration does not match
description of event..." When I remove "Cancel as Integer" I then get a
compile error on
DoCmd.RunCommand acCmdGoToRecord, acLast

for the wrong number of arguments on RunCommand.

"John Vinson" wrote in message
...
On Wed, 21 Jul 2004 16:35:21 -0800, "Bob Richardson"
wrote:

There is only one record for each year. The query sorts the data by
year
ascending. When a user firsts looks at the results for this customer, I
want
them to see the record for the most recent year, i.e. the last one for
that
customer.

Ok, I did misunderstand!

Try this: in the Form's Load event put code

Private Sub Form_Load(Cancel as Integer)
DoCmd.RunCommand acCmdGoToRecord, acLast
End Sub

Base the form on a query sorting ascending; this will put you at the
last record instead of the first.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Thanks Doug. It didn't work on "Load", but it works fine on the "On Enter"
event.

"Douglas J. Steele" wrote in message
...
I think John may have been typing a little too quickly.

Try

Private Sub Form_Load()
DoCmd.GoToRecord , , acLast
End Sub

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)


"Bob Richardson" wrote in message
...
This looks like the right solution, but I can't get it to compile. I'm
using
Access 2003.

First I get the error message "Procedure declaration does not match
description of event..." When I remove "Cancel as Integer" I then get a
compile error on
DoCmd.RunCommand acCmdGoToRecord, acLast

for the wrong number of arguments on RunCommand.

"John Vinson" wrote in message
...
On Wed, 21 Jul 2004 16:35:21 -0800, "Bob Richardson"
wrote:

There is only one record for each year. The query sorts the data by
year
ascending. When a user firsts looks at the results for this customer,
I
want
them to see the record for the most recent year, i.e. the last one
for
that
customer.

Ok, I did misunderstand!

Try this: in the Form's Load event put code

Private Sub Form_Load(Cancel as Integer)
DoCmd.RunCommand acCmdGoToRecord, acLast
End Sub

Base the form on a query sorting ascending; this will put you at the
last record instead of the first.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public