Show last year first, but keep data sorted cronologically

[Bob Richardson]

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.

 

[Douglas J. Steele]

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.

 

[Bob Richardson]

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).

 

[John Vinson]

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.

 

[Bob Richardson]

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.

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 Vinson]

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.

 

[Harvey Thompson]

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>

 

[Bob Richardson]

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.

 

[Douglas J. Steele]

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

(No private e-mails, please)

 

[Bob Richardson]

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

 

[Douglas J. Steele]

That's odd. It worked fine for me in Form_Load when I tested before posting.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

 

[Bob Richardson]

Perhaps it's because my form is a sub-form....On my main form I have a Tab
Control with 4 tabs. On one of the tab sheets I've put the form in question.
When I click that tab to reveal the sub-form the On_Enter event is
triggered. Perhaps the On_Load is triggered earlier, before the dataset for
the subform is opened. I know it takes perhaps 0.5 seconds after entering
this sheet but before the total number of records in the subforms dataset is
"registered" on the navigation bar...thus indicating to me that the
sub-form's dataset isn't opened until later.

Thanks for all your help.